Module: ATELIER Système d’Information Décisionnel
Devoir Surveillé (Durée: 1h)
Université Virtuelle de Tunis
20 Janvier 2021
PART1 : IMPLEMENTING A DATA WAREHOUSE
1. Start SQL Server Management Studio and connect to the (local) instance of the SQL Server database
engine by using Windows authentication.
2. Open and review the Transact-SQL code in the SetupDS.sql file in the « BADS2021_DS_SSIS » folder.
3. Execute the query to create the « DWDS » database
4. Create a data warehouse schema for the newed created data warehouse « DWDS » and name it
« DWDS_schema »
5. The schema for the Adventure Works data warehouse now contains one fact table and three dimension
tables. However, users need to be able to analyze the fact table measures across consistent time periods.
To enable this, you must create a time dimension table.
Users will need to be able to aggregate measures across calendar years (which run from January to
December) and fiscal years (which run from July to June). Your time dimension must include the following
attributes:
Publicité
• 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
Publicité
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
Publicité
« 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
Publicité
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