Designing SSAS Cube

• Download demo - 50.5 KB
• Download AdventureWorksDWH2008R2.zip - 9.7 MB

Introduction

This article explains in a step by step process how to implement SSAS Cube. SSAS Cube allows business users to quickly analyse different business measures based on different dimensions. SSAS Cube is a database consisting of Dimension and Measures. SSAS Cube designing process requires datawarehouse database as source of input data. The datawarehouse database should be in Star schema to avoid any design complexity. If Datawarehouse is in Snowflakeschema, then you can use the 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. Users can easily get access these measures value with different analysis from multiple dimension. SSAS Cube uses Multiple Dimension Expression (MDX) to read measures from multiple dimensions. SSAS Cube maintainance is easy. We can deploy specific dimensions or measuress in the SSAS Cube database for any new changes.

SSAS Cube also supports Key Performance Indicator (KPI) to analyse performance of any Product.

Using the code

I have used the AdventureWorksDW2008R2 DWH database as input source of SSAS Cube. you can download this database from the Download Source section of this article. 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 applied on fact table (Reseller sales and Internet sales) and can describe the 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 the above measures is also possible in Datawarehouse database. We can provide SSRS reports to business users, which can get data from DWH database using T-SQL. But it will be very slow because the size of the DWH database will be huge. DWH databases contain large business data transactions 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 the size of the tables is huge, the joining process takes lots of time. When the 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 this data is available in SSAS Cube, MDX reads all simple and complex analysis in just a few seconds. 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.

You can assign relationship with this object to other object by clicking on New relationship.

Specify relation ship of this object with destination object

Design Cube

Right click on Cubes in solution explorer and select New Cube

Select creation method to Use existing table

Select measure group tables. i have picked FactInternetSales and FactResellerSales. because it will contain measure value

Select require measures value from each measure

Select new dimension for this cube

Now complete this wizard by assigning cube name and click on finish.

Cube Deployment

Right click on SalesCube project in solution explorer and select properties.

Provide deployment option. pass server name and database name of cube. Click on Apply.

Now right click on project and click on Deploy.

Once deployment is successfull.open Analysis server to check cube.

Open cube database SalesCube.

Now your cube is ready to get query with MDX.

Points of Interest

This article explain each step to design SSAS cube. it will help developer to design SSAS cube.