Sunday 1 November 2015

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.

No comments:

Post a Comment