Using Unbalanced Hierarchy and standard entity together

Navan Mehra · 2023-08-02T12:38:55+00:00

There was an error rendering this rich post.

I have an unbalanced hierarchy ( P&L Reporting) which has multi-levels

Level 1

— Level2

— — Level 3

Another entity is Account which has all the base-level accounts in it. Level 3 of the unbalanced hierarchy is linked to the Account entity where multiple accounts have a relationship with a single Level 3 member.

I have a data view where both P&L Reporting and Account entities are placed on Rows. With such a structure, Level 2 and Level 1 members are not displayed on the data view. If I enable "Show All" on rows, they display as blank.

Is there a way to display the members of both the unbalanced hierarchy and Account on the data view?

Find more posts tagged with

Dataview

Unbalanced Hierarchy

Accepted answers

All comments

Giles Corner

@Navan Mehra are you sure your relationships are mapped correctly without issues? From my understanding based on what you've explained it should work. Might be helpful to include some screenshots of the relevant relationships and layout you're trying to create.

Navan Mehra

Thanks @Giles Corner for coming. Sure, I am sharing some screenshots of how the hierarchy looks

When I keep only the unbalanced hierarchy in rows on the layout, I can see the parents of the unbalanced hierarchy showing numbers. The purple lines (Revenue, Direct Costs, etc) and the bluish-green lines (Gross Profit, EBITDA, etc) are parents of an unbalanced hierarchy. They all show up with numbers on the report.
The lines which I have highlighted in the red box are the leaf members of the unbalanced hierarchy which are related to the Account entity. The cube is structured by Account entity and the requirement is to display the P&L tree to the base account level

3. When I add the Account entity to the layout on the rows, I lose this structure. On enabling "Show All" on rows in the layout setting, it shows the parents of an unbalanced hierarchy with no data

Tommaso Riva

Hi @Navan Mehra,

It seems you are having problem with the relationship definition between Account and the UBH entity P&L Report.

Is that correctly defined?

Which version is currently being used?

With B2023 Summer version when connecting a child entity to an unbalanced hierarchy has been enhanced.

Please advise.

Tommaso

Navan Mehra

@Tommaso Riva Thanks for your feedback. The members of the Account entity are connected to the base members of the unbalanced hierarchy. On checking I can see there are around 8 account members which are not mapped to any members of the unbalanced hierarchy. Do you think this is causing the parent members to not show data? Rest all accounts are properly mapped to base members of the unbalanced hierarchy.

The version in use is 12.2. Let me test this scenario in version 12.6

Bart Scott

I have encountered some differences in functionality depending on how Unbalanced Hierarchies are setup. I.e if you do the Unbalanced entity first, then do relationship entities from there many things work well. If you do flat entities first, then attach the flat entity members to the leafs of the unbalaced entity, there is some functionality differences with how the relationship is evaluated (because you have the Unbalanced Relationship and the relationship to the root entity which seems to take precedence).

For example, on that Unbalanced Entity that is a parent relationship to flat entity, you can no longer just select the parent level from a pager, or doing a member select only, you must use Descendants (or leaves) of that parent level. Because that parent tries to evaluate the Relationship and what is connected directly to it, not what is coming thru the unbalanced relationship.

I suspect that is why you are getting no values on your report, because the unbalanced is a parent connected by leafs, so only the leaf members have a connection to where the members in the main entity in order to display the results.

If you switched the order, did Unbalanced entity first, then built your flat entities as relationships to it, your report would probably work correctly.

I have not tested this with the latest release mentioned.

Bart Scott

CFO Solutions LLC