Scenario and Best Practice Inquiry: Entity Granularity Adjustment in Board Reporting

Putra

I have developed a report using a data model with structured entity relationships. The core entities include:ID, Level 1, Level 2, Chart of Accounts (CoA), Level 3, and Currency.

In the initial design:

• ID was defined as a child of Level 1, Level 2, and CoA.
• Level 3 and Currency were not linked via relationships, as they were not required in any reporting set at the time of development.

However, a new reporting requirement has emerged that necessitates the inclusion of Level 3 and Currency as dimensions.When these entities are added without proper relationships, the report values become inaccurate—often inflated due to unintended multiplication across all members of the newly added entities.

Question: What is the best practice to handle this situation so that Level 3 and Currency can be integrated as ready-to-use dimensions without compromising data precision?

Hamza Mesbahi

Hi @Putra ,

Thanks for the detailed explanation. From what you described, it sounds like your data isn’t currently stored at the Level 3 or Currency level in the reporting cube. If that’s the case, adding those dimensions to the layout will just duplicate values, since Board can’t associate the data to specific Level 3 or Currency members unless it exists at that level of granularity.

To address this, you’ll need a reporting cube that includes ID, Level 3, and Currency. You can either load the data directly at those levels (if it already exists externally) or create a new cube and populate it through a procedure using data flows. If there are only a few ID–Currency or ID–Level 3 combinations, you could use Selections to push the data from your existing cube into the new one. If there are many combinations there, you're probably better off with a more dynamic approach, using mapping cubes to link IDs to Currencies and Level 3s.

In that setup, you would have two mapping cubes: one mapping ID (or ID parent) to Currency, and another mapping ID (or ID parent) to Level 3. Your procedure would include two data flows, with a temporary cube to store the intermediate result. The logic would look something like this:
Cube B (temp cube, original dimensions + Currency) = Cube A (source) * Mapping Cube 1 (ID to Currency)
followed by a second data flow:
Cube C (final, includes ID, Level 3, and Currency) = Cube B * Mapping Cube 2 (ID to Level 3).

Also, just to confirm, can Level 3 be integrated into your existing CoA hierarchy? If so, that would simplify things, since you’d only need to handle one additional dimension instead of two.

Hope this helps.

Kind regards,

Hamza