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

OBIEE - Multi level hierarchy into single column

Received Response
155
Views
13
Comments
User_1ZD2F
User_1ZD2F Rank 2 - Community Beginner
edited Aug 13, 2024 5:56PM in Oracle Analytics Cloud and Server

Hi,

I have a multi level tree account table with say parent level at level1 and leaf level in any of the child levels say level20. My fact table has data only for leaf level. My requirement is to aggregate all the levels into single column to show in report. For example, below is my table structure is

Acct Dim Table:

ACCT_SKEYACCT_LEVEL1ACCT_LEVEL2…….ACCT_LEVEL20

Fact Table:

ACCT_SKEYFact1

Thanks in advance. Nag

«1

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner 
    3459307 wrote: My requirement is to aggregate all the levels into single column to show in report.

    What do you mean? A hierarchical column where you can drill into the data?

    Please be precise about your requirements so proper help can be provided.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    I concur with Christian, more detail please.

    OBIEE does not provide rollup aggregation, if your data source is a database table / view then you would need to add the additional levels as explicit column(s) in that table, then default sum aggregation behaviour on those columns will do the rest for you, and if you can put them together in a data hierarchy then you will also get a drill behaviour similar in principal to MOLAP.

    Alternatively but less performant you can also join to your fact table's base level members to a hierarchy table, base to base, and make the table self join in tree walking fashion, not as fast as the above option which flattens your hierarchy, but if you don't have the luxury of changing your ETL and can live with the performance then it is an option.

  • User_1ZD2F
    User_1ZD2F Rank 2 - Community Beginner

    Thanks Christian, Robert for your quick reply.

    My Account table structure:

    pastedImage_0.png

    My Fact table has data at leaf level. I am trying to create a report looks as below

    pastedImage_1.png

    I hope I gave the requirement , let me know if it is not clear.

    Thanks,

    Nag

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

    So you just have a hierarchy that's ragged and skip-level. Still it's just a normal hierarchy object in the RPD:

    https://www.rittmanmead.com/blog/2010/07/obiee-11gr1-support-for-ragged-skip-level-value-based-hierarchies/

  • User_1ZD2F
    User_1ZD2F Rank 2 - Community Beginner

    Thanks again Christian. I looked into this, my requirement is little different, i.e., I don't want hierarchal view and then click + symbol to expand to see further. When I place acct column with measure, it should aggregate to all the levels and also we are going to create filters using this column.

    Thanks Again for you time and inputs.

    Regards,

    Naga

  • User_1ZD2F
    User_1ZD2F Rank 2 - Community Beginner

    Hello gurus any suggestions?

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

    What you "want" is flattening the hierarchy...

    Then again I put "want" into quotes sicne that already smells like you're already fixed on HOW you want to do things (compared to WHAT you want to do - i.e. fulfilling the requirement / business need).

    Also your "example" with the "measures" doesn't help at all since your made up numbers look like they are non-additive and just show the value for a member in the level.

    tl;dr - if you can't describe your requirement precisely and correctly we will have a hard time helping you with any pertinent solution. Be precise.

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    only 1 measure value for all levels? as it joins like that

  • User_1ZD2F
    User_1ZD2F Rank 2 - Community Beginner

    Hi Christian,

    Apologies for the confusion and delayed response.  I don't have the solution yet.  Attached are my table structures and the expectation is to show all the Accts (child's and then respective parents). Hope it gives better understanding of requirement.

    Acct requirement.png

    Thanks again,

    Nag

  • User_1ZD2F
    User_1ZD2F Rank 2 - Community Beginner

    Unfortunately that is the requirement.