step by step SSAS cube designing

Article
04/04/2015

Introduction

This article explain step by step process to implement SSAS Cube.SSAS Cube allow business user to quick analyse of different business measures based on different dimension. SSAS Cube is a database consist of Dimension and Measures. Cube designing process requires datawarehouse database as source of input data. Datawarehouse database should be in Star schema to avoid any design complexity. if Datawarehouse is in Snowflakeschema, then you can use SQL view to join multiple dimension table and make a common Dimension.

Background

Microsoft provides SQL Server Analysis Service (SSAS) to implement Cube Database which will contain predefine measures and dimensions. Measures will be aggregated based on different dimension. User can easily get access these measures value with different analysis from multiple dimension. Cube uses Multiple Dimension Expression (MDX) to read measures from multiple dimension. Cube maintainance is also become easy.we can deploy specific dimension or measures in cube database for any new changes. Cube also support Key Performance Indicator (KPI) to analyse performance of any Product.

Using the code

I have used AdventureWorksDW2008R2 DWH database as input source of SSAS cube. you can download this database from codeplex website. Below is the Analysis of busines requirement to implement SSAS cube.

Datawarehouse database is : AdventureWorksDW2008R2
cube required for business analysis is : SalesCube
All possible analysis will be perform on business transaction. business transactions are stored on Fact Table.

Reseller Sales transaction is available in FactResellerSales
Internet Sales transaction is available in FactInternetSales

 SELECT [ProductKey],[OrderDateKey],[DueDateKey],[ShipDateKey],[ResellerKey],[EmployeeKey],
[PromotionKey],[CurrencyKey],[SalesTerritoryKey],[SalesOrderNumber],[SalesOrderLineNumber]
,[RevisionNumber],[OrderQuantity],[UnitPrice],[ExtendedAmount],[UnitPriceDiscountPct]
,[DiscountAmount],[ProductStandardCost],[TotalProductCost],[SalesAmount],[TaxAmt],[Freight]
,[CarrierTrackingNumber],[CustomerPONumber]
FROM [AdventureWorksDW2008R2].[dbo].[FactResellerSales]

SELECT [ProductKey],[OrderDateKey],[DueDateKey],[ShipDateKey],
[PromotionKey],[CurrencyKey],[SalesTerritoryKey],[SalesOrderNumber],[SalesOrderLineNumber]
,[RevisionNumber],[OrderQuantity],[UnitPrice],[ExtendedAmount],[UnitPriceDiscountPct]
,[DiscountAmount],[ProductStandardCost],[TotalProductCost],[SalesAmount],[TaxAmt],[Freight]
,[CarrierTrackingNumber],[CustomerPONumber]
FROM [AdventureWorksDW2008R2].[dbo].[FactInternetSales]

Fact table Reseller Sales and Internet Sales can be analysed through following dimension

Product dimension (Product,Product Category and Product Sub-Category)
Date dimension (Calendar year,Calendar quarter etc)
Teritory dimension (Sales City,Sales State)
Currency dimension (Currency Name)
Employee dimension (Employee Name)

These dimension Can be apply on fact table (Reseller sales and Internet sales) and can describe following measures.

Order Quantity Count (Product wise,Geography wise,Calendar Date wise,Employee wise)
Sales Amount (Product wise,Geography wise,Calendar Date wise)
Tax Amount (Product wise,Geography wise,Calendar Date wise)
Freight Cost (Product wise,Geography wise,Calendar Date wise)
Total Sales order (Product wise,Geography wise,Calendar Date wise)

Business dimension analysis for above measures is also possible in Datawarehouse database. we can provide SSRS report to business user. which can get data from DWH database using T-SQL. But it will be very slow because size of DWH database will be huge.DWH database contains large business data transaction in its tables. DWH database uses scheduled job to insert new business transaction, update existing transaction, update existing dimension,insert new dimension etc.

 /*************Sample T-SQL Query to analyse business data ******************/

--Year wise Reseller Sales
Select D.CalendarYear,Sum(S.SalesAmount)SalesAmount from FactResellerSales S
INNER JOIN DimDate D ON S.OrderDateKey=D.DateKey
Group By D.CalendarYear

--Product category wise Reseller Sales
Select  PC.EnglishProductCategoryName,Sum(S.SalesAmount) SalesAmount from FactResellerSales S
INNER JOIN DimProduct P ON S.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory PS ON p.ProductSubcategoryKey=PS.ProductSubcategoryKey
INNER JOIN DimProductCategory PC on PS.ProductCategoryKey=PC.ProductCategoryKey
Group By PC.EnglishProductCategoryName

--Product category wise and year wise Reseller Sales
Select  PC.EnglishProductCategoryName,D.CalendarYear,Sum(S.SalesAmount) SalesAmount from FactResellerSales S
INNER JOIN DimDate D ON D.DateKey=S.OrderDateKey
INNER JOIN DimProduct P ON S.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory PS ON p.ProductSubcategoryKey=PS.ProductSubcategoryKey
INNER JOIN DimProductCategory PC on PS.ProductCategoryKey=PC.ProductCategoryKey
Group By PC.EnglishProductCategoryName,D.CalendarYear
ORDER By PC.EnglishProductCategoryName,D.CalendarYear

--Territory wise Reseller Sales
Select G.SalesTerritoryCountry, PC.EnglishProductCategoryName,D.CalendarYear,Sum(S.SalesAmount) SalesAmount from FactResellerSales S
INNER JOIN DimDate D ON D.DateKey=S.OrderDateKey
INNER JOIN DimSalesTerritory G ON S.SalesTerritoryKey=G.SalesTerritoryKey
INNER JOIN DimProduct P ON S.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory PS ON p.ProductSubcategoryKey=PS.ProductSubcategoryKey
INNER JOIN DimProductCategory PC on PS.ProductCategoryKey=PC.ProductCategoryKey
Group By G.SalesTerritoryCountry, PC.EnglishProductCategoryName,D.CalendarYear
ORDER By PC.EnglishProductCategoryName,D.CalendarYear

--Product wise sales with Calendar Year in column value 
With Sales
AS
(
Select  PC.EnglishProductCategoryName As ProductCategory,D.CalendarYear,Sum(S.SalesAmount) SalesAmount 
from FactResellerSales S
INNER JOIN DimDate D ON D.DateKey=S.OrderDateKey
INNER JOIN DimProduct P ON S.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory PS ON p.ProductSubcategoryKey=PS.ProductSubcategoryKey
INNER JOIN DimProductCategory PC on PS.ProductCategoryKey=PC.ProductCategoryKey
Group By PC.EnglishProductCategoryName,D.CalendarYear
)
Select  ProductCategory, [2005],[2006],[2007],[2008]
From Sales
Pivot
(
    Sum(SalesAmount) For CalendarYear IN ([2005],[2006],[2007],[2008])
)P

We can see simple analysis on business data requires multiple join with dimension tables. when size of tables are huge , joining process takes lots of time. When level of analysis process becomes complex, T-SQL takes more time.

Eq.
Year wise reseller sales details with Product category on pivot column
Product wise reseller sales with Calendar year in pivot column

When these data is available in SSAS Cube, MDX reads all simple and complex analysis in just few second. Business analysis process become very easy. MDX can read cube measures from multiple dimension in single query.

Step by Step process to implement SSAS Cube

Create New SSAS project using Microsoft Business Intelligence Development Studio (BIDS)

Define Datasource for SalesCube solution

Define new datasource for SalesCube using right click on datasource, Datasource Wizard appears.

Define new data connection in datasource

Provide source datawarehouse database information

Provide impersonation information for datasource. provide windows user name and password in impersonation,if current user credential option does not work.

Assign datasource name and click on finish.Datasource will appear in SalesCube solution

Define Datasource Views for SalesCube solution

Right click on Datasource views in SalesCube solution explorer,Datasource view wizard appears

Select Datasource used for Cube input

Select Table and Views used in Cube. I have selected FactInternetSales and FactResellerSales

Click on Add Related Table button. it will add all require dimension table.

Datasource views after adding all required table.

You can add or remove any object from datasource views using Add/Remove object button

I have removed DimPromotion,DimProduct and DimReseller object from datasource views

Dimproduct dimension table contains product details. but product category and product subcategory are stored in other dimension table Dimproductcategory and Dimproductsubcategory.so that if we require analysis on any measure based on product category or product subcategory, it will not be available. if we include all 3 dimension table, it will make Cube more complex. So that we can write SQL Named query to get complete product in one table set.

Click on New Named Query.Create Named Query dialog box appear.write require SQL code for this Named query.

 Select DP.*,PC.EnglishProductCategoryName,SPC.EnglishProductSubcategoryName from DimProduct DP
LEFT JOIN DimProductSubCategory SPC ON DP.ProductSubcategoryKey=SPC.ProductSubcategoryKey
LEFT JOIN DimProductCategory PC ON pc.ProductCategoryKey=SPC.ProductCategoryKey

DimProductDetails dataset is now appear on Datasource views. but it does not has any primary key and also relationship with this dataset to other object is missing. we can create logical primary key on this dataset by right click on column and set Logical primary key.