Combining measures from two fact tables in Dynamic cube

[DeeEss]

Cognos Gurus,

I have a virtual cube measure related query( please note i have been using Cube designer for a month now-but its my first time)

We have two fact tables in datawarehouse
1. F_TP- grain is sale amount for a T at level P.
    Total amount for a T can be paid in multiple P Types .data sample below
         row 1- T1   P1
         row 2- T1   P2
         row 3- T2   P2
         row 4- T2   P3
         row 5- T3   P1
This fact table is joined to Dimension Table D_P with a key Key_P and also to other few dimensions with respective keys.
I can get total amount for a P_type or T. There is a Key_T also

2. F_TL- grain is sale amount for a T at level I.
    Total amount for a T can be sum of various T-I combination. data sample below
         row 1- T1   I1
         row 2- T1   I2
         row 3- T2   I1
         row 4- T2   I3
         row 5- T3   I3
This fact table is joined to Dimension table D_I  with a key Key_I and also other few dimensions with respective keys.
All the other dimensions are common between F_TP and F_TL except D_P for F_TP and D_I for F_TL. This table also has a Key_T

I created one dynamic cube for each fact table. Then i created a virtual Cube(VC) with these both. I get all common dimensions in the VC,the extra dimensions D_P and D_I also, and two measures ( sales_amount_TP, sales amount_TL)
QUESTION:
1.   Is it possible to have only one sales amount measure in Virtual cube instead of two and based on the dimensions I pull in report, Cognos should understand if TP sales amount or TL sales amount should be used.
How can it be done- if it can be.
2.   The client wants to pull a column from D_P dimension in report and sales amount from TL. This shows no data currently( obviously because there is no join between F_TP and D_P as there is no column column)
Is there a way this can be done with any join in the cube level?

Please Help.

[MFGF]

Cognos Gurus,

I have a virtual cube measure related query( please note i have been using Cube designer for a month now-but its my first time)

We have two fact tables in datawarehouse
1. F_TP- grain is sale amount for a T at level P.
    Total amount for a T can be paid in multiple P Types .data sample below
         row 1- T1   P1
         row 2- T1   P2
         row 3- T2   P2
         row 4- T2   P3
         row 5- T3   P1
This fact table is joined to Dimension Table D_P with a key Key_P and also to other few dimensions with respective keys.
I can get total amount for a P_type or T. There is a Key_T also

2. F_TL- grain is sale amount for a T at level I.
    Total amount for a T can be sum of various T-I combination. data sample below
         row 1- T1   I1
         row 2- T1   I2
         row 3- T2   I1
         row 4- T2   I3
         row 5- T3   I3
This fact table is joined to Dimension table D_I  with a key Key_I and also other few dimensions with respective keys.
All the other dimensions are common between F_TP and F_TL except D_P for F_TP and D_I for F_TL. This table also has a Key_T

I created one dynamic cube for each fact table. Then i created a virtual Cube(VC) with these both. I get all common dimensions in the VC,the extra dimensions D_P and D_I also, and two measures ( sales_amount_TP, sales amount_TL)
QUESTION:
1.   Is it possible to have only one sales amount measure in Virtual cube instead of two and based on the dimensions I pull in report, Cognos should understand if TP sales amount or TL sales amount should be used.
How can it be done- if it can be.
2.   The client wants to pull a column from D_P dimension in report and sales amount from TL. This shows no data currently( obviously because there is no join between F_TP and D_P as there is no column column)
Is there a way this can be done with any join in the cube level?

Please Help.

Hi,

Are dimensions D_P and D_I separate dimensions, or are they different levels of a single conformed dimension?

If they are different dimensions, then the measure values are different in each fact - driven by the design of your data warehouse. You can't treat these as a single measure - your design does not support this.
If they are different levels in the same dimension, then it sounds like one fact is an aggregated version of the other? If this is the case, rather than modelling them as separate cubes, you could model the most granular fact in your cube, then add the second fact as an aggregate. From your description I don't think this is the case, though.

Cheers!

MF.

[DeeEss]

Hi MF

Thanks for the reply.

D_P and D_I are separate dimensions.

Yes fact T_P is and aggregated version of fact T_L but there is a catch. Its not a simple aggregate.
One T can have multiple I's hence there can be multiple rows of a T in T_L say 3 rows. Total amount of 1 T in T_L can be paid in many P's.
so in T_P also there can be multiple rows for the same T e.g. 2 rows for the same T.

so if we design a single cube based on T_L, i still wonder how can the same measure be used against all dimensions.
Can cognos be able to figure out how to split the amount of 3 Items against 2 payments from T_F. I think this is design issue of DW but need to be sure if I am missing somethings that cognos can do.

Regards
Deepina

[CognosGeek]

So basically we need to have common dimensions for both fact tables in transformer else it will not reflect the correct data, Is that right ??

Is there any KB from IBM on this ??

Any leads would be appreciated !!

Thanks
CG

[MFGF]

So basically we need to have common dimensions for both fact tables in transformer else it will not reflect the correct data, Is that right ??

Is there any KB from IBM on this ??

Any leads would be appreciated !!

Thanks
CG

Hi,

This isn't related to Transformer - it's in Cube Designer for Dynamic Cubes.

MF.