Coursework – Part 2 – Data Modelling, ETL and Analysis with SSIS and SSAS
Coursework Part 2
This coursework is based on the same scenario as coursework Part 1, namely a superstore with branches across NorthAmerica that keeps a record of orders placed by customers, and is interested in analysing transactions (quantity sold
and profit made) by Date (Year, Month, Quarter, Day), Product (Product and Brand), Customer (Customer Location),
Store (Store Location and Sales Region/District), and Region.
However, you now want to explore the option of building a data warehouse solution in SQL Server, using an SSIS ETL
to load data, and building a cube and dimensions for analysis in SSAS.
Task 1: Data Modelling & ETL
1.1 Using Microsoft SQL Server, and the source data contained in the Coursework.xlsx file, implement a star schema
to meet the above requirements. The dimension tables, except Time, need to have surrogate keys. For each column
in your schema, choose an appropriate data type and size.
1.2 Using Microsoft Visual Studio, create an SSIS ETL package to load data from Coursework.xlsx into the data
warehouse. You may find it useful to first import the Excel file into SQL Server (using SQL Server Import Export tool)
instead of using the Excel directly as a source in your ETL. For time-related data, you can use, as source, an Excel
spreadsheet similar to the one covered in lab 3.