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.
Account:
Primary Column With unique IDs.
ParentAccount:
Account 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: 101010 has Parent 109999 and 109999 has Parent as 139999
Step1:
You can create a New SSAS Project and create the DataSourceView.
After importing the table in the DataSource View we start creating the Dimension.
Right Click "Dimension" folder in the Project in Visual Studio and Click "New Dimension".
The Dimesnion wizard opens up as shown below.
Select Account Column as Key Column and for display we show the ID+Name combination which is already there in the Description Column.
Step2:
Select Account and ParentAccount columns. You can add more columns like Name later on.
Step3:
Name it "Accounts".
Step4:
Rename "Account" attribute to "Account Flat" and "Parent Account" to Account.
So going forward Account attribute is "Parent Account".
The reason we do is because I want to name my hierarchy attribute as "Account".
Its a lot User friendly for the EndUser.
This is how it should look like:
Step5:
For the Account(orginally which was Parent Account) attribute set the following Properties:
usage:Parent.
Step6:
In the attribute relationships Windows delete the first relation shown below.
This is how it should look like:
Step7:
Set the "Account Flat" attribute as Key Attribute.
Step7:
Process the Dimension so we see how the hierarchy looks like now.
Step8:
Set following Properties:
MembersWithData:NonLeafDataHidden.
UnaryOperatorColumn:Account.Rollup
Step9:
Process the Dimension again and you would see the following structure which you can use it in your BI reports.
Scenario:
As you see below we have a simple table which you can build using SSIS and map the corresponding Parent and Child values.
Account:
Primary Column With unique IDs.
ParentAccount:
Account 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: 101010 has Parent 109999 and 109999 has Parent as 139999
Step1:
You can create a New SSAS Project and create the DataSourceView.
After importing the table in the DataSource View we start creating the Dimension.
Right Click "Dimension" folder in the Project in Visual Studio and Click "New Dimension".
The Dimesnion wizard opens up as shown below.
Select Account Column as Key Column and for display we show the ID+Name combination which is already there in the Description Column.
Step2:
Select Account and ParentAccount columns. You can add more columns like Name later on.
Step3:
Name it "Accounts".
Step4:
Rename "Account" attribute to "Account Flat" and "Parent Account" to Account.
So going forward Account attribute is "Parent Account".
The reason we do is because I want to name my hierarchy attribute as "Account".
Its a lot User friendly for the EndUser.
This is how it should look like:
Step5:
For the Account(orginally which was Parent Account) attribute set the following Properties:
usage:Parent.
Step6:
In the attribute relationships Windows delete the first relation shown below.
This is how it should look like:
Step7:
Set the "Account Flat" attribute as Key Attribute.
Step7:
Process the Dimension so we see how the hierarchy looks like now.
Step8:
Set following Properties:
MembersWithData:NonLeafDataHidden.
UnaryOperatorColumn:Account.Rollup
Step9:
Process the Dimension again and you would see the following structure which you can use it in your BI reports.
No comments:
Post a Comment