Joining fact tables with non-confimed dimension

Neethaa

Hi,

Please tell me how to design RPD for the following report requirement

HTOPS fact table and HFPAY fact table have one dimension in common that is Program_Loc_Dim

HFPAY is have one more dimension called F_TYPE_Dim

Report columns

1. Program office code => Program_Loc_dim.program_code

2. Col2 => count( HTOPS.pos)

3. Col3 => count( HTOPS.vac)

4. Col4 => SUM( case when F_TYPE_Dim.fcode = 'ED' then HFPAY.Fval else 0 end)

Please tell me how should I join two facts and two dimension tables in RPD.

Do I have to create a view joining F_TYPE_DIM and HFPAY ? Any solution in obiee rep ?

Thanks,

Neeta

[Deleted User]

a) "conformed" dimension: http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techn…

b) So you just join both dimensions to their facts over their respective keys to once have those dimensions as...well dimensions with hierarchies.

c) You additionally include your type dimension in the LTS of HFPAY so you can do the physical calculation you mention above

d) You set the content levels for the fact LTSs to Detail for Program_Loc_Dim in both and for the Type dimension to detail for HFPAY and Grand Total for HPOS

e) PLEASE use correct and nicely readable business naming and not garbled technical names with underscores, ALL UPPER-CASE etc. Analytics is a *business* topic. People will accept and use it a lot more if you actually produce nice output rather than just lazily thrown together "stuff"