In the last post I showed you know to build a Parent-Child Hierarchy. This time we implement the same Parent-Child Hierarchy in a different way.
Building a Parent Child Hierarchy looks complicated but is easy if all the rules are followed correctly. I will here list down few steps which will help us building it better.
Scenario:
As you see below we have a simple table which you can build using SSIS and map the corresponding Parent and Child values.
Portfolio:
Primary Column With unique IDs.
ParentPortfolio:
Portfolio Id of the corresponding Parent.
RollUp:
Operator used while aggregrating values(Auto Sum) from Child to Parent node.
You can read more on RollUps here
https://msdn.microsoft.com/en-us/library/ms175417.aspx
Example: P0120 has Parent P0200 and P0200 has Parent as P0500
Step1:
You can create a New SSAS Project and create the DataSourceView.
After importing the table in the DataSource View we need to set the primary key.
Here Portfolio is set as Primary key.
After that a link is created between Portfolio and ParentPortfolio as shown below.
Step2:
Right Click "Dimension" folder in the Project in Visual Studio and Click "New Dimension".
The Dimesnion wizard opens up as shown below.
Select Portfolio Column as Key Column and for display we show the ID+Name combination which is already there in the Description Column.
Step3:
Select Portfolio and ParentPortfolio columns. You can add more columns like Name later on.
Step4:
Name it "Portfolio".
Step5:
Process the Dimension. The output is shown below.
Step6:
Now we need to get rid of the Unknown member and set Rollup operator. For the Portfolio Dimension set the following Properties:
Step7:
For Parent Portfolio Attribute set the following Properties:
Step 8:
Process the Dimension:
The Unknown Level will disappear and all the Levels will have the Unary Rollup sign.
Step 9:
Rename "Portfolio" attribute to "Portfolio Flat" and "Parent Portfolio" to Portfolio.
The reason we do is because I want to name my hierarchy attribute as "Portfolio".
Its helps a lot and is very user friendly for the EndUser.
This is how it should look like:
Scenario:
As you see below we have a simple table which you can build using SSIS and map the corresponding Parent and Child values.
Portfolio:
Primary Column With unique IDs.
ParentPortfolio:
Portfolio Id of the corresponding Parent.
RollUp:
Operator used while aggregrating values(Auto Sum) from Child to Parent node.
You can read more on RollUps here
https://msdn.microsoft.com/en-us/library/ms175417.aspx
Example: P0120 has Parent P0200 and P0200 has Parent as P0500
Step1:
You can create a New SSAS Project and create the DataSourceView.
After importing the table in the DataSource View we need to set the primary key.
Here Portfolio is set as Primary key.
After that a link is created between Portfolio and ParentPortfolio as shown below.
Step2:
Right Click "Dimension" folder in the Project in Visual Studio and Click "New Dimension".
The Dimesnion wizard opens up as shown below.
Select Portfolio Column as Key Column and for display we show the ID+Name combination which is already there in the Description Column.
Step3:
Select Portfolio and ParentPortfolio columns. You can add more columns like Name later on.
Step4:
Name it "Portfolio".
Step5:
Process the Dimension. The output is shown below.
Step6:
Now we need to get rid of the Unknown member and set Rollup operator. For the Portfolio Dimension set the following Properties:
Step7:
For Parent Portfolio Attribute set the following Properties:
Step 8:
Process the Dimension:
The Unknown Level will disappear and all the Levels will have the Unary Rollup sign.
Step 9:
Rename "Portfolio" attribute to "Portfolio Flat" and "Parent Portfolio" to Portfolio.
The reason we do is because I want to name my hierarchy attribute as "Portfolio".
Its helps a lot and is very user friendly for the EndUser.
This is how it should look like:
No comments:
Post a Comment