Self Referencing Dimension

View previous topic View next topic Go down

Self Referencing Dimension

Post  tim_goodsell on Tue Mar 18, 2014 12:59 am

Hi

I will have an account dimension which contains an account id and a parent account id (self referencing).

For those members that have no parent account (top level) do I leave it Null or should there always be a value (day -1 for N/A)

Regards


Tim

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Self Referencing Dimension

Post  nick_white on Tue Mar 18, 2014 6:18 am

This is not a modelling issue - rather a reporting issue. If you are reporting using SQL then you can do whatever you want in order to make the SQL as easy to build and as performant as possible. However if you are using a BI app (OBIEE, BO, Cognos, etc.) then there is likely to be a required set up in order for the BI app to understand the hierarchy correctly. Whether that is parent = null or parent = child (or something else) differs from app to app

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Self Referencing Dimension

Post  cmp66 on Tue Mar 18, 2014 9:35 am

According to Kimball articles you should have a "Not Applicable" as opposed to null:

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/null-dimension-attribute/

http://www.kimballgroup.com/2010/10/06/design-tip-128-selecting-default-values-for-nulls/

On another note if you want to see alternatives to modeling the self referencing relationship, this article discusses various options:

http://www.kimballgroup.com/2009/08/17/five-alternatives-for-better-employee-dimension-modeling/

cmp66

Posts : 6
Join date : 2014-03-12

View user profile

Back to top Go down

Re: Self Referencing Dimension

Post  nick_white on Tue Mar 18, 2014 10:01 am

I'm not sure that the "Not Applicable" design is applicable in this scenario - if I have understood this correctly then this is logically an FK on the Dimension rather than an attribute of the Dimension, in which case you wouldn't want to put "Not Applicable" in this column as there is no record in the Dim with this as its PK

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Self Referencing Dimension

Post  cmp66 on Tue Mar 18, 2014 10:25 am

I guess I didn't state this before, I'm not a pro at this kind of stuff (yet) but from my reading and research, Null is always bad because it can mess counts up and you can't even "compare nulls" if that make any sense.

But you definitely bring up a good point which is it's a foreign key and not another attribute. I believe what I stated still holds true. To deal with this situation one should create a record in the account dimension that represents "I do not have a parent". So whenever a null value does arises that embodies this concept that row id should be use as the FK for the parent account id.

I recommended the last link in that post because I didn't think the self referencing was the best approach and wanted to present alternatives.

Again, I could be totally wrong, I'm simply stating how I would deal with the issue but again, I've only been doing dimensional modeling for the past two months. So please take everything I say with a grain of salt.

cmp66

Posts : 6
Join date : 2014-03-12

View user profile

Back to top Go down

Re: Self Referencing Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum