How to create a PreFilter Dimension in SSAS

In this tutorial we will see how we can filter Dimension Data and then use it in cube. Here we will filter data of dimension DimPromotion.

• Open your DSV then right click on middle window and select option "New Named Query" as in the image below

• Now in the Create Named Query window write your query, here we are filtering the data where promotion is not equal "No Discount", Give name to the new dimension "DimPromotionNew" and then click Ok.

                 SELECT *

                  FROM [dbo].[DimPromotion]

                     where EnglishPromotionName<>'No Discount'

• Now you will find a new dimension created in your dsv "DimPromotionNew". Now create relationship with your facts with this newly created dimension and delete the old one.
• If you explore the data in these two dimensions then you will find the difference of 1 row, in the new dimension the row with the Promotion "No Discount" is deleted.

DimPromotion Data

DimPromotionNew Data

Cheers,

SSAS hands on Part 1

We will start with AdventureWorksDW2012 database by which we will explore most of the SSAS features. We will use subset of the database with below set of tables

DimProduct

DimProductSubCategory

DimProductCategory

DimDate

DimReseller

DimEmployee

DimPromotion

DimCurrency

DimSalesTerritory

DimGeography

FactResellerSales

Below is the diagram of data mart.

 

Now to create a new SSAS project follow below step

First create a new SSAS project from FileàNewàProject Menu

 

Select Analysis Service MultiDimensional and Data Mining Project and give a name to the project.

 

      Create a Data Source by right clicking and selecting new Data Source on right hand side Solution Explorer Window

 

Create a Data Source View (by selecting above tables) by right clicking and selecting new Data Source View on right hand side Solution Explorer Window

 

You will get the dsv created in your SSAS project like below

 

In next part we will start and learn Dimension Design in SSAS.

SSAS hands on Part 3 (Dimension Design Contd.)

In this session we will create Employee Dimension and will learn how to create parent child hierarchy and ssas features.

We can create Employee dimension in similar way as we created in last post, select DimEmployee in dimension wizard. Now for name colum we don’t have any suitable attribute to select so in DSV we will create one column “EmployeeName” which will be a combination of First, middle and last name.

Right click on DimEmployee and click on New Named Calculation.

Write the expression as below and click ok. It will create a new column EmployeeName

Now go to Dimension design wizard and select DimEmployee. Change the Name Column property with the attribute EmployeeName we just created in step 2.

If you want to create employee hierarchy with sales territory then check the related table DimSalesTerritory otherwise uncheck it and click next. I am including DimSalesTerritory here.

Now select the attribute you want and rename them as per your requirement

I have changed here Employee key, Parent Employee key, sales territory key to Employee, ParentEmployee, SalesTerritoryRegion.

Give name to dimension and click finish button

Change the Name column property of ParentEmployee from Employee Key to EmployeeName

As ParentEmployee attribute is the parent in nature so change the usage property from key to Parent.

Now we see ParentEmployee hierarchy which have different levels Level01, Level02 and soon, as below

what if we will give these levels some useful names i.e. CEO, Sr Manager, Manager, Lead, Associates.  Go to the ParentEmployee attribute property and click NamingTemplate property and give them meaningful names

Click ok and process dimension and see result below

We still see that All level, what if we want to start with CEO level. Select ParentEmployee and change the value of IsAggregatable property to false as below

After dimension redeploying the All level will be removed as below

Stay tuned...In next Post we will explore some more SSAS features. 

SSAS hands on Part 2 (Dimension Design)

In this post we will learn how to design dimensions and will look deeper the options available in SSAS for dimension designing.

Let's start with Product Dimension

Right click on Dimension and select New Dimension on Solution Explorer Window

 

Click next on Dimension Wizard window pop up

Now select option “Use and existing table” and click next

 

Select dimension DimProduct and click next

 

Change the Name column value here I changed from Product Key to “English Product Name”, this we can change from property window later also. We changed the Name Column property from Product key to English Product Name because so that when we will drag product key then it will display product name instead of key which the users want. Click Next

 

1.      Select the related tables (ProductCategory & ProductSubCategory) on next page. If you don’t need attribute from these tables then you can uncheck them but we will create hierarchy so check them and click next

 

Select the attribute which you want in your dimension and click next

Give a name to your dimension and press finish

 

I have renamed some of the attribute names (Product Subcategory Key, Product Category Key, English Description, Product Key to ProductSubcategory, ProductCategory, Description, Product

 

Set the Name Column Property for ProductCategory & ProductSubcategory attribute to EnglishProductCategoryName & EnglishProductSubcategoryName otherwise when user browse the data it will show him keys, so click on the KeyColumn Property in Property window and select EnglishProductCategoryName and click ok.

 

 

Similarly do for Subcategory.

Now we will create Hierarchy�� Drag all attribute which will be in hierarchy in middle pane. Also change your hierarchy name from Hierarch to Product By Category

 

Define attribute relationship properly from child to master.

 

Now deploy solution from solution explorer window

 

Now click on Browser tab to check the data

 

Here we see that we can see created hierarchy “Product By Category” and the attribute Product, ProductCategory & ProductSubcategory individually so we can hide them from browsing. To do this go to Dimension structure tab. Go to the property window of attribute ProductCategory and change the property  “AttributeHierarchyVisible” from True to False. Repeat this for all the attribute which you do not want to repeat. Now Deploy, reconnect and browse again

 

When we browse data we see one value “unknown” as below

 

we can hide it. Click on DimProduct under dimension structure tab and change the value of UnknownMember property from Visible to Hidden

 

Now if we browse it then unknown will be removed

 

In part 3 we will go through next dimensions and some more concepts in details.