How to make multiple relationships coexist in the same hierarchy
1. Abstract
Establishing and managing relationships effectively is crucial for the optimal performance of a Board implementation.
In numerous instances, it becomes necessary to accommodate multiple relationships within the same hierarchy.
Therefore, it is vital to make informed decisions when constructing the multidimensional data model in Board right from the outset.
2. Context
In certain situations, there is a need to handle two distinct relational hierarchies simultaneously within a process, as seen in planning processes with various work scenarios (Actual, Budget, Forecast).
In such scenarios, it is probable that the Forecast scenario will align with the Actual hierarchies, given its focus on planning in the current year.
In contrast, the Budget scenario is likely to feature different hierarchies, as it involves planning for the subsequent year.
3. Content
When dealing with the challenge of overseeing multiple hierarchies within a single process, various solutions come into play.
At a higher level, three distinct approaches can be identified:
- Utilizing two independent data models;
- Employing a single data model with multiple parents;
- Employing a single data model with a technical key (essentially a concatenated entity) as a leaf node;
3.1 Two independent data models
In adopting this approach, the choice is made to replicate the data models, where one data model is dedicated to overseeing the forecast process, and the other is dedicated to managing the budget process.
3.1.1 Security
In this scenario, as they constitute two distinct data models, it becomes feasible to implement standardized security measures that allow for differentiation between the two scenarios.
3.2 Single data model with multiple parents
In opting for this approach, we choose to uphold a singular Data Model while replicating the aggregating entities of the leaf entity.
This results in a distinct set of entities for the forecasting process and another set for the budgeting process.
3.2.1 Security
In this scenario, managing security for aggregated entities, even if they differ between scenarios, becomes more straightforward.
This allows for a user assigned a security profile to view specific salespersons (Salesperson_FCS) in the forecast, which may differ from those in the budget (Salesperson_BDG).
To achieve this, the "Select Entity based on Cube" feature in the database security area can be employed, with one cube structured by Salesperson_FCS/Scenario and another by Salesperson_BDG/Scenario.
On the contrary, if security is applied at the leaf entity level, it becomes challenging to differentiate between the two scenarios.
In such cases, it is advisable to consider one of the other two solutions.
3.3 Single data model with a technical key as a leaf entity
This solution allows the management of two distinct hierarchies within the same relationship.
In this approach, the elements of the leaf entity are duplicated, with one set of codes for the current year and another for the next year, both linking to the same aggregating entities (e.g., customer "001" duplicates into "001_CY" for the current year and "001_NY" for the next year).
To distinguish between forecast (current year) codes and budget (next year) codes, it is essential to establish a technical entity that selects the corresponding codes based on the working scenario (illustrated as "BDG/FCS TYPE" in the figure below).
It is crucial to ensure that both BDG and FCS keys are loaded with actual data.
Moreover, to prevent the display of concatenated codes from the leaf entity to the end user, an additional parent of the leaf entity can be created, excluding code concatenation (depicted as "Customer" in the figure below).
The Customer entity will then be used to compare scenarios.
3.3.1 Security
Managing security at aggregated entities or directly at the leaf level becomes simpler.
However, handling different scenarios complicates security management, presenting two potential solutions:
- Utilize the new "Select based on cube" feature at the security profile level;
- Introduce duplicates of aggregated entities (one per scenario) and implement security directly on these entities (a combination of cases 2 and 3);
4. CONCLUSION
For the identical process used both in Forecast and Budget, all the aforementioned solutions are applicable, and the selection depends on the nature of the existing process (combining them is also an option).
The approach 3 provides more flexibility for potential changes and is easier to maintain over time.
Comments
-
0
-
Thanks @Daniele Santandrea for the insights on this article!
5
