Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Source Values and Conformed Values in the Dimension table

2 posters

Go down

Source Values and Conformed Values in the Dimension table Empty Source Values and Conformed Values in the Dimension table

Post  TheDarkKnight Mon Jun 03, 2013 10:11 am

Hello,

I Need to design my facts and dimensions in a way that i can report my facts on both the conformed and the source values.Especially since we have a requirement where we need to show that for a specific conformed value there are no facts received from the source,in other words; total sales = 0 for conformed value "ABC".

What should be the best possible way to handle this dual requirement for reporting.It would be great to give use case in form of tables.

Thanks in advance!!!
TheDarkKnight
TheDarkKnight

Posts : 10
Join date : 2013-03-22
Age : 39
Location : NJ

Back to top Go down

Source Values and Conformed Values in the Dimension table Empty Re: Source Values and Conformed Values in the Dimension table

Post  ngalemmo Mon Jun 03, 2013 12:33 pm

Just have two columns in the dimension, one hold the raw value and the other the conformed value.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Source Values and Conformed Values in the Dimension table Empty Re: Source Values and Conformed Values in the Dimension table

Post  TheDarkKnight Mon Jun 03, 2013 12:58 pm

Thanks !!!

So if in the case we have no source values for some of the conformed values from the DW,do we create a seperate rows for them/and maintain the mapping changes throughout the dimension table ?

SInce our users like to see the conformed values which have no associate facts as well.

id Source val DW Val
1 Uk United Kingdom
2. uk1 united kingdom
3. DE Germany
4. --- United States <=====?
TheDarkKnight
TheDarkKnight

Posts : 10
Join date : 2013-03-22
Age : 39
Location : NJ

Back to top Go down

Source Values and Conformed Values in the Dimension table Empty Re: Source Values and Conformed Values in the Dimension table

Post  ngalemmo Mon Jun 03, 2013 2:42 pm

Rows in the dimension tables are going to be driven by the natural key. If you are receiving raw values as the natural key from facts, then the dimension should be built accordingly and that is going to drive the number of rows. You then have additional columns with the conformed values. The same conformed values may appear on many rows.

You should avoid using conformed values in the natural key as it would cause problems in the future should the conformance rules change. If you have raw natural keys, a change in conformance rules is simply a dimension table update to adjust the existing conformed values.

As far as queries reporting facts that did not happen, it is a matter of using outer joins between the fact and dimension.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Source Values and Conformed Values in the Dimension table Empty Re: Source Values and Conformed Values in the Dimension table

Post  TheDarkKnight Mon Jun 03, 2013 4:09 pm

so in my last post,we should have United states ,if we want to show it in the outer join with facts.

But then we wont have the surrogate key driven by the Source Natural key.

How can i get around that?
TheDarkKnight
TheDarkKnight

Posts : 10
Join date : 2013-03-22
Age : 39
Location : NJ

Back to top Go down

Source Values and Conformed Values in the Dimension table Empty Re: Source Values and Conformed Values in the Dimension table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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