La Trobe Business School
BUS5WB Data Warehousing and Big Data, Semester 1 2019
Tutorial 07 – Creating Cubes (SQL Server Analysis Services)
In this tutorial we will develop a cube (Analysis Services Multidimensional and Data Mining Project) using
SQL Server Data Tools (SSDT) for analysis of the Bliss data warehouse.
Connecting to SQL Server Data Tools
Download Visual_Studio_SSDT.rdp file from LMS double click to open.
You will be prompted for credentials. The username is your “first name_last digit of student id” (example:
first name john and student id is 12345, type in “\john_5” as the username– without double quotes). The
password is in the following format: “
studentID
BUS5WB
La Trobe Business School
SQL Server Data Tools opens in the default view, inside Visual Studio.
3
•
You partially filled a data warehouse in last week’s tutorial, a completed version of the same warehouse is
available from BlissDW_v1.
Master of Business Analytics
Click Next. The Completing the Wizard page appears. Enter ‘Bliss DW v1’ for Data Source Name, if
it is not already there. Click Finish.
A data source view allows to specify the subset of tables and their fields from the star schema (warehouse)
that should be included in the OLAP cube. We can also define table relationships and calculated fields that
The ‘Bliss DW v1’ data source just created should be selected. Click Next. The Name Matching page
appears.
Master of Business Analytics
This page appears because we do not have foreign key constraints defined in our warehouse. We will allow
the wizard to create logical foreign keys in the data source view.
•
•
6
•
Move DimDate to the left of FactSales. Click on OrderDateKey and drag it out on to DateKey in
DimDate. Repeat the same for DeliveryDateKey. Now there should two arrows headed out of
FactSales on to DimDate as shown below. If you make a mistake right click on the arrow and delete
relationship.
Master of Business Analytics
•
In Solution Explorer, right click on Dimensions and select Create new Dimension.
Data Source View will remain as is: Bliss DW DSV (or any other name you gave to the DSV)
Main table: select DimCustomer
Key columns: CustomerKey – note how we can add multiple key columns for composite keys.
Name column: This is optional. A name column must be specified when a composite key is used.
Retain as CustomerKey (shown below - left).
•
Click Next to go to the ‘Completing wizard’ page, the dimension name will show up as Dim
Customer (we can change this if needed). Click Finish.
BUS5WB
8
•
BUS5WB
9
•
Master of Business Analytics
Enter ‘BlissDWCube’ for Cube Name. Click Finish. The Cube Design tab appears (as shown below).
Master of Business Analytics
Let’s process the cube, deploy it on the server and browse the raw cube as-is in the next few steps.
•
•
Click Apply and OK.
•
If the Build was successful, click on Build > Deploy MultidimensionalprojectX. The cube will be
deployed to the server now.
BUS5WB
La Trobe Business School
•
This will open up a query design space.
First, drag in Total Sales followed by Customer Suburb, and click on the link to execute the query.
– notice how the aggregates start to appear for each suburb.
Master of Business Analytics
•
Try another query with a hierarchy – Delivery Date.Hierarchy. (You can right click on the grid and
select Clear Grid to clear the query.)
Let’s look at improving the query presentation and many other functions of SSDT in next week’s tutorial.