hi ,everyone.
The situation is like this:
In the date source,fact table have three columns(time,product_no,amount).
and the dimention table have columns(time,product_no,product_status)
I want to join these two table together with join condition time and product_no and then make product_status another dimention to form this structure:
dimention: time product_no,product_status.
meature: amount
what should i do to design transformer model like this?
How do cognos deal with SCD like this?
any help will be appreciated
In the transformer, we can not phisically join like dim.prod_no=fact.prod_no.
In the transformer model, we have to take care while naming of the joining columns.
Assume that you have 2 tables ( one is DIM and second is FACT table.)
as you mentioned the DIM has 3 columns( time,product_no,product_status) and FACT has 3 columns( TIME,PRODUCT_NO,Amount).
Now you have to rename the DIM column name as TIME and PRODUCT_NO instead of time and product_no( its case sensetive).
and while you design the dimention the last level/ leaf level item property should be 'uniqe'
By doing this we are informing to the transformer to consider the TIME and PRODUCT_NO columns for the relationship.
Transformer internally cosider these columns for the joining purpose by looking at the last level in the dimention property and the NAME of the columns between the DIm and FACt tables.