Data Warehousing and Business Intelligence

23 July 2010

Parent Child Dimension

Filed under: Analysis Services — Vincent Rainardi @ 9:22 pm
Tags:

In this post I’d like to point out 3 things:

  1. The column contains ParentID, not the ChildID
  2. MembersWithData property
  3. 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.

4. Unknown

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.

4 Comments »

  1. Way to Go !!!. Excellent BI site.

    Comment by Salman — 1 March 2011 @ 10:16 pm | Reply

  2. Simple but precise explanation! Thanks!

    Comment by jpedroalmeida — 25 October 2011 @ 9:18 pm | Reply

  3. Reblogged this on K4TIMINI and commented:
    Créer une dimension à partir d’une table comportant une “self-referencing” association

    Comment by k4timini — 16 April 2014 @ 9:44 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: