In this post I’d like to point out 3 things:
- The column contains ParentID, not the ChildID
- MembersWithData property
- What happen to node without parent
1. The column contains the ParentID, not the ChildID
When we crate a Parent Child Dimension, we have ID and name column, then we have a third column that points to the ID column. This third column should contains the parent ID, not the child ID:
On the table we specify the parent node rather than the child node because the hierachy is in pyramid format, where by a node can only have 1 parent, but a node can have several children. Hence we can’t set the third column to be the ChildID, because a node can have several children.
Note that in the table:
a) The top node has no parent, and
b) The ChildID (NodeID) is the primary key of the table. Meaning that we can’t have duplicate node numbers, because it means that a node can have more than one parent.
When we create the SSAS dimension, we make the ChildID (NodeID) as the key of the dimension. The “self referencing column”* contains ParentID, not ChildID. Like this:
*a “self referencing column” is a column that points to another column on the same own table.
Let’s create the table first:
create table PC
( ID nvarchar(10) null,
Name nvarchar(10) null,
ParentID nvarchar(10) null
insert into PC values (1, ‘Node1’, NULL)
insert into PC values (2, ‘Node2’, 1)
insert into PC values (3, ‘Node3’, 2)
insert into PC values (4, ‘Node3’, 2)
select * from PC
Then create the DSV table: (click the ParentID column and drop it onto the ID column)
On the DSV, the relationship is: Source = ParentID, Destination = ID:
Then create the dim (from existing table):
No need to define the “Usage” property of ParentID attribute as “parent”. BIDS automatically does this for us.
Process the dim, then browse:
2. MembersWithData property
In the above picture, the nodes are repeating because of a property called “MembersWithData”. This property is currently set to NonLeafDataVisible:
PS. This is the property of the ParentID column, not the property of the dimension.
If we set it to NonLeafDataHidden, process the dim, browse the dim, and reconnect, the repeating nodes disappear:
3. What happen to node without parent (orphan nodes)
For this let’s add a row with ParentID = null to the table:
insert into PC values (5, ‘Node5’, NULL)
select * from PC
Process the dimension, browse:
We see that a node without parent will become a top level node.
I know I said 3 points, but oh well, 1 more:
In the above picture the Unknown member is there because the UnknownMember property is set to Visible. Set to Hidden and the Unknown member disappears:
(This is the property of the dimension, not the property of the attribute)
Note that making the unknown member hidden is not recommended because fact rows mapped to the unknown member of this dim will “disappear from the cube”, causing the total of the measure to be incorrect (less than what it should be).
Yes, the usual counter argument is: “but we have set the dim key to 0 on the ETL, so there would be no rows on the fact table with dim key = null”.
Well in Data Warehousing there’s an old saying: “If it is not enforced, it will happen” 🙂 Meaning: things like RI, dup, and nullability, if they are not physically enforced they will happen. May be not on the first month of going live, but eventually they will happen.
PS. RI is enforced using FK, duplication is prevented using PK and nullability is enforced using “Allow Nulls” column property.
“But the FKs would slow down the ETL”. I know, I know, … classic.