Jedox Percentage on Percentage on Percentage Split with “instant” preview

Usecase

Imagine you have a customer who wants to split revenue by multiple dimensions not based upon total figures nor on percentual splits. E.g. you plan 10k and split it to 3 Segments by

  • 20% Seg A
  • 40% Seg B
  • 40% Seg C

And after this by ProductGroup and Product which are in one Dimension

  • 20% ProdGroup I
    • 50% Prod A
    • 50% Prod B
  • 80% ProdGroup II

Design

Important: I struggled by trying to solve this with a “one cube solution” as two many “unknown” levels are existing to identify the storage of the percentage value. So to get a stable an performant solution, you need to stick to the following steps:

  1. Identify the Split Points, in our example for the segments (which are the first split level) the percentages will be stored on the base elements for Seg A, B, C
  2. Then we need to create a helper Cube which will store the percentages for this first level, as we will have different percentages by Year, Version and Szenario, they need to be added to seperate the values later

Cube Layout within Integrator Constant Table Extract:

Definition of split cube step 1

From this extrace we go straight to the cube load:

Cube load – will create the cube on the first execution

So in your modeler a new cube should have appeared:

The “storage” cube for the factors on level 1 – segments

So within the worksheet (report designer) to keep it manageble I will add a named range to store the cube name:

Named Range within worksheet

So for the entry of the factors we create a DynaRange for the Segments:

And allow the user to enter the values on the base elements. Conditional Format on the ELEVEL information.

Check for Baseelements

=PALO.ELEVEL(RNG_CONNECTION,”Segments”,$D42)=0

The calculation of the “temp result” will be done in the Split 1 cube by using the formula

Rule within result cube

=[‘_Sales_Budget_Top_Down’:’_split_faktor_Segments’] = B:[‘_Sales_Budget_Top_Down’:’_faktor_Segments’] * PALO.DATA(“”,”SALES_BUDGET_LIC_REV_TOP_DOWN_REDUX”,!’Years’,”Total_Year”,!’Versions’,!’Szenario’,”Reporting_Currency”,”Total_Segments”,”Sales Hierachy”,”Total_TD_Account_Business_Type”,”Total_TD_Products”,”_input”) / 100

The next step is tricky, as we have the product group and the product in one dimension, so we aren’t able to store the value for the percentage on a base element as needed. to solve this issue we create a helper dimension for the product which will allow us to store the percentage on a base element. To do this, we first extract all values from the dimension that we wanna “split” to give it even more complexity, here we have a structure of different depths…

For our first helper, we actually focus on the values of level2. To do so, we need to get to an TreeFH Transformation step which will only be possible with a “in between step” using a tableview:


And after this the treeFH where we only use the first 2 levels:

Improvements

As the tree fh will hold “no distinct” values, you can improve by adding like a aggregation inbetween, as we don’t have that many rows, I will step a head an create the dimension diretly
The big picture for the product level 1 helper dimension

So as we did for the segments, we will create a helper cube to store the factors, this time we need to take care that the factors are although related to the segments:

From here we proceed as above and the cube should look like this:

Then we need to add the following rule to this cube which looks like:

Faktor Segment * Faktor ProductGroup / 100 /  100

[‘_Sales_Budget_Top_Down’:’_split_faktor_TD_Products’] = B:[‘_Sales_Budget_Top_Down’:’_faktor_TD_Products’] *
PALO.DATA(“”,”SPLIT_BY_STEP1_SEGMENT”,!’Years’,!’Versions’,!’Szenario’,!’Segments’,”_faktor_Segments”)*
PALO.DATA(“”,”SALES_BUDGET_LIC_REV_TOP_DOWN_REDUX”,!’Years’,”Total_Year”,!’Versions’,!’Szenario’,”Reporting_Currency”,”Total_Segments”,”Sales Hierachy”,”Total_TD_Account_Business_Type”,”Total_TD_Products”,”_input”) / 10000

After this you can again adopt the values in the worksheet to show the correct figures from within the 2nd helper cube.

For the products itself, it will become easy, as we can use the product dimension “as-is”. Which will mean we create a cube design as followed:

And here the cube:

Split By Step3 Product Level 2 (Base Elements)

Important, this time (as we stay in the same dimension) we only exchange the helper dimension of the products with the full product dimension and let it rock!

And pls. add the following rule

Faktor Segment * Faktor ProductGroup * Faktor Product / 100 /  100 / 100

[‘_Sales_Budget_Top_Down’:’_split_faktor_TD_Products’] = B:[‘_Sales_Budget_Top_Down’:’_faktor_TD_Products’] * PALO.DATA(“”,”SPLIT_BY_STEP1_SEGMENT”,!’Years’,!’Versions’,!’Szenario’,!’Segments’,”_faktor_Segments”) * PALO.DATA(“”,”SPLIT_BY_STEP2_PRODUCT_L1″,!’Years’,!’Versions’,!’Szenario’,!’Segments’,PALO.EPARENT(“”,”TD_Products”,!’TD_Products’,1),”_faktor_TD_Products”) * PALO.DATA(“”,”SALES_BUDGET_LIC_REV_TOP_DOWN_REDUX”,!’Years’,”Total_Year”,!’Versions’,!’Szenario’,”Reporting_Currency”,”Total_Segments”,”Sales Hierachy”,”Total_TD_Account_Business_Type”,”Total_TD_Products”,”_input_ABR”) / 1000000

Again we add the named range:

So like this you will be able to have like a report where you can show values by each “split” and written back to all base elements by helper cube.