Source Values and Conformed Values in the Dimension table

View previous topic View next topic Go down

Source Values and Conformed Values in the Dimension table

Post  TheDarkKnight on 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!!!
avatar
TheDarkKnight

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

View user profile

Back to top Go down

Re: Source Values and Conformed Values in the Dimension table

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Source Values and Conformed Values in the Dimension table

Post  TheDarkKnight on 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 <=====?
avatar
TheDarkKnight

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

View user profile

Back to top Go down

Re: Source Values and Conformed Values in the Dimension table

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Source Values and Conformed Values in the Dimension table

Post  TheDarkKnight on 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?
avatar
TheDarkKnight

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

View user profile

Back to top Go down

Re: Source Values and Conformed Values in the Dimension table

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