Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Joining fact tables with non-confimed dimension

Received Response
2
Views
1
Comments
Neethaa
Neethaa Rank 1 - Community Starter
edited Aug 13, 2024 6:00PM in Oracle Analytics Cloud and Server

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

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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"