nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

OBIEE - Multi level hierarchy into single column

User_1ZD2F

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_SKEY

ACCT_LEVEL1

ACCT_LEVEL2

…….

ACCT_LEVEL20

Fact Table:

ACCT_SKEY

Fact1

Thanks in advance. Nag

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

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

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

Thanks Christian, Robert for your quick reply.

My Account table structure:

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

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

Thanks,

Nag

[Deleted User]

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

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

Hello gurus any suggestions?

[Deleted User]

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

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

User_1ZD2F

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

Unfortunately that is the requirement.