Decoding rwo different column table with the same decode string needed to group different dimension in the same table

[marcello]

Hi Everybody,

i'm facing the following problem:

I have two fact table like this:

T1:

Qty1, Status1

T2:

Qty2, Status2

The Status1 and 2 columns must be decoded in the same main status to get a table  with the decode status and two column with the different dimension (Qty1, Qty2).

I figured out i need a table like this:

DecodeT:

MainStatus, Status1, Status2 (where one of the two status could be null) but doing so i get a circular reference.

It would be kind if someone could help me.

Thanks a lot, Marcello

[edwin · Accepted solution]

status1 and status2 do not need to be on the same row - rt?  its just that values for status 1 and 2 share the same decoded status.

one way is to concat the 2 fact tables and just have a new column that indicates whether they are from table 1 or 2 - but more relevant field name and value of course.  and you will need just 1 status field.  your decode table would just be  main status and status.  or it could be a combination of the new field + status links to the main status  

or create a link table between all the dimensions and the two fact tables.  i think the concat will be simpler.

then your