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.