Reference no: EM132723222

CMM531 Data Warehousing - Robert Gordon University

Part - Data Modelling, ETL and Analysis with SSIS and SSAS

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):

Task 2: Data Analysis

2.1 Using Microsoft Visual Studio, create an SSAS project to build a cube and a set of dimensions related to your data warehouse. Build the relevant dimension hierarchies as per the requirements.

2.2 Use SQL Server Management Studio (Analysis Services) to create two MDX queries that will provide the same type of analysis produced in the PivotTables created in coursework part 1:
Query 1: For each day of 1998, show the profit on that day, alongside the month-to-date (MTD) profit.
Query 2: For each month of 1998, show the profit in that month, the profit in the previous month, and the percentage profit change of that month compared to the previous month.

Note: If you didn't manage to include profit in your cube, then replace profit with quantity in the above queries.

Deliverables for Task 2:
Add the following to your Word document: (4 pages max in total for this section) (2.1) For each dimension, show the following:
• Two screenshots: one for the dimension structure and hierarchy, and one for the browser view, like the example below (from the lab):

A screenshot of the cube, in browser mode, showing the total value of profit (or quantity, if you didn't manage to get profit in the cube).

(2.2) For each MDX query: the MDX statement used + a screenshot of the output obtained (just capture the few top lines of the result).

Attachment:- Coursework - Part 2.rar