Design, implement and evaluate a data warehousing application.
Learning Outcome 1:Design, implement and evaluate a data warehousing application.
Coursework Part 2
This coursework is based on the same scenario as coursework Part 1, namely a superstore with branches across North- America 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.
Deliverables for Task 1:
The Word document structured as follows (6 pages max in total for this section):
(1.1) SQL Script for creating the data warehouse tables; (1.2) For each ETL task, show the following:
A screenshot of the successful (or otherwise) running of that task (showing green ticks and number of rows loaded). For example:
A screenshot of the inside of each of the elements of that task (table or query used as source, any data transformation or lookup used, and mapping used in the destination).
A screenshot, from SQL Server Management Studio, that shows data loaded in that table (with a count of rows) as shown in this example (from the lab):