Module: ATELIER Système d’Information Décisionnel - Devoir Surveillé (Durée: 1h)

• Date (this should be the business key).

• Day number of week (for example 1 for Sunday, 2 for Monday, and so on).

• Day name of week (for example Sunday, Monday, Tuesday, and so on).

• Day number of month.

• Day number of year.

• Week number of year.

• Month name (for example, January, February, and so on).

• Month number of year (for example, 1 for January, 2 for February, and so on).

• Calendar quarter (for example, 1 for dates in January, February, and March).

• Calendar semester (for example, 1 for dates between January and June).

• Calendar year.

To achieve this, you should :

•

•

First create a Time Dimension Table using the Transact-SQL code in the DimDate.sql file in the

« BADS2021_DS_SSIS » folder.

Second, populate the Time Dimension Table. So, you should personalize the given Transact SQL

code in the GenerateDates.sql file (that can be founded in the « BADS2021_DS_SSIS » folder)

according to the need. N.B : Stored dates should be between 01/01/2020 and 31/12/2020.

PART2 : IMPLEMENTING AN ETL SOLUTION

After you have implemented the data warehouse, you should now design an ETL process to populate it with data

from various source systems.

Before creating the ETL solution, you decided to examine the source data so you can better understand it.

1. Restore backup file « ReselletSales.bak » in the « BADS2021_DS_SSIS/Backup » folder. This data base

contains three tables : « Resellers », « SalesOrderDetail », « SalesOrderHearder » and « PaymentTypes ».

Now, you want to have a comma-separated text file that contains a sample of resellers data, and a data profile

report that shows statistics for data in the ResellerSales database.

Module: ATELIER Système d’Information Décisionnel

Devoir Surveillé (Durée: 1h)

Université Virtuelle de Tunis

20 Janvier 2021

2. To better understand the « Resellers » table content, you want to extract and view sample data source.

To attend this, you should use the SQL Server 2014 Import and Export Data (64-bit). The output of

the analyse should be a flat file named « Top 750 Resellers.csv » and should be stored in the

« BADS2021_DS_SSIS/AnalyseOutput » folder.

3. Now, you want to report statistics on ResellerSales database. So, you should :

Firstly : create a solution named « Examine Reseller Sales » in the « BADS2021_DS_SSIS » folder

Secondly : Add a connexion to « ResellerSales » database ;

Thirdly : Add a data profiling task. The output of the profiling task should be a new file intitled : Reseller

Sales Data Profile.xml, which will contain results for the following profile requests :

Column Statistics Profile Request :

• ConnectionManager: ResellerSales

• TableOrView: SalesOrderHeader

• Column: OrderDate

Column Length Distribution Profile Request :

• ConnectionManager: ResellerSales

• TableOrView: Resellers

• Column: AddressLine1

•

•

IgnoreLeadingSpaces: False

IgnoreTrailingSpaces: True

Column Null Ratio Profile Request :

• ConnectionManager: ResellerSales

• TableOrView: Resellers

• Column: AddressLine2

Value Inclusion Profile Request :

• ConnectionManager: ResellerSales

•

•

•

SubsetTableOrView: SalesOrderHeader

SupersetTableOrView: PaymentTypes

InclusionColumns:

o Subset side Columns: PaymentType

o Superset side Columns: PaymentTypeKey