Source Values and Conformed Values in the Dimension table

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!!!

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

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 <=====?

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.

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?