Self Referencing Dimension
3 posters
Page 1 of 1
Self Referencing Dimension
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
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
Re: Self Referencing Dimension
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 : 364
Join date : 2014-01-06
Location : London
Re: Self Referencing Dimension
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/
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
Re: Self Referencing Dimension
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 : 364
Join date : 2014-01-06
Location : London
Re: Self Referencing Dimension
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.
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
Similar topics
» bridge table and junk dimension on customer dimension (bank/credit union)
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|