How to Build a Cube and Write MDX with OLAP Using 2014 AdventureWork
Data Warehouse
Sunnie Chung
Project Description
Buding a Business Analytic Data Mining Model using Microsoft BI Data Mining Tool and Data Warehouse
and OLAP Cubes.
Preparation of Project
1.Installing the SSDT BI for Visual Studio 2013
Tutorial: https://www.youtube.com/watch?v=C5GpYd0y4wo
After successful installations, I start a project by following steps:
1) Open Visual Studio
2) File -> New Project
3) You should see following BI add-ons which lets you create Data mining projects.
a. Analysis Service
b. Integration Service
c. Reporting Service
4) Create an Analysis Services project and name it, then click ok.
2. Installing Adventure Works 2014 database for SQL Server 2014
Design a Cube with Dimensions
1.Building a Data Source
In The solution explorer, right click on Data Sources and click New Data Source and click New Data
Source. This will bring up the Data Source Wizard, which will walk you through the creation process. The
database I used is AdventureWorksDW2014.
Choose Inherit and then click next
Enter data source name ”Lab3” and then click finish.
2.Building a Data Source View
Right-click Data Source Views and choose New Data Source View. Make sure the Lab3 data source is
selected and then click Next.
On Select Table and Views , I choose these dimensions and fact table :
DimCustomer, DimGeography, DimProduct, DimDate , DimSalesTerritory and FactInternetSales
Name the view : Internet Sales
The star schema of Internet Sales data.
3.Creating an Analysis Services Cube
Right-click Cubes in the Solution Explorer and select New Cube to bring up the Cube Wizard and then
click next. On the Select Creation Method screen, make sure Use Existing Tables is selected, and click
Next.
Choose FactInternetSale as measure group and select measures that you want to include into cube.
After that, based on available tables, select new dimensions to be created.
Enter name and finished.
4. Deploying the cube
Right click project name and then click Deploy.
I got the first Error due to the connection cannot be made
Because the start mode of SQL server Analysis Service is manual, the state of it is default stopped. So I
open sql Server Configuration Manager to change the state of SQL server Analysis Service to Running.
Repeat deploying process, I got another error: Contains an Impersonation Mode that is not supported
for processing operations.
Fix: Double click on ds file (in this case which is Lab3.ds) to open Data source designer. In the
impersonation tab, select windows user name and password and enter the windows login user id and
password and now process the cube.
Deploying project works now.
Right click Internet Sales.cube and hit process. After deployment completed successfully, click run.
We can see cube deployed successfully.
5. Editing Dimensions Individually
Right click dimensions name and click New Attribute from Column or drag the attribute from the pane of
data source view to attributes pane. By using this way, I edit all dimensions individually.
Deploying the cube again
Writing MDX queries to retrieve data
1. Open SQL Server 2014 and select Analysis Services as server type. After connecting, we can find the
cube Internet Sales has already in SQL server.
2. Right click project name and select New Query and then click MDX.
We can find all the dimensions and their attributes in the panes.
Q1: Predict which cities we need more bikes supply to in 2014.
Find out how many bikes have been sold in particular city in 2013. The following statement find top 20
cities which has best order quantity.
select[measures].[order quantity] on 0, TopCount
({[dim geography].[city].members
*[order date].[calendar year].[2013]} ,20
,[measures].[order quantity]
) ON 1 from [Internet Sales]
Top 5 cities in order quantity:
Drill down on each quarter
Using following statement to see the purchase of bikes in each quarter in year 2013.
select ([order date].[calendar quarter].members)on columns,
filter([Dim geography].[city].members,
[order date].[calendar year].[2013]) on rows
from[internet sales] where([measures].[sales amount])
Save the table into excel and sorted in descending order of their total sales amount of year 2013
I select top 10 sale amounts cities and plot a quarter graph. We can see during quarter 4th the sales of
bikes usually get the peak value. We also can notice that some cities (e.g. Goulburn) have downward
tendency.
0
50000
100000
150000
200000
250000
300000
quarter1 quarter2 quarter3 quarter4
London
Paris
Wollongong
Warrnambool
Bendigo
Goulburn
Bellflower
Brisbane
Townsville
Geelong