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.