# How to simulate calculation (production quantity and cost-roll up)

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # How to simulate calculation (production quantity and cost-roll up)

Hi,

I am looking solution for my
ScoolProject to solve calculations topic (production quantity and cost-roll up) witch i have create in MS Excel (i like to port this logic into palo cube). I allready create sales cube (in way
Best Biker template), Like i said I have create excel model to solve MRP planing and
roll up cost, but I do not know yet how to transform this model into
Palo. Perhaps somebody do this allready in his model. Can enyboty pleas help me?

I tray to solve my Chalange whitch is How
to prepare modet to calculate production quantity (MRP planing) and
production prices (cost roll up).

1. Imput data:

-sales plan (Qth);
-BOM and routing (in my attached case there I have only BOM data, allso whitout Capacity);

-Purchase price (activity prices for Routings)

2.
Calculation Model (I use it in excel with index,match and IF formula)- I
attached it in Excel).
• Whitch dimension to create?

-company,
-year and month,
-parent matirial code

-production version

-child
material code
-child QTH_normativ
-capacity,
-child QTH_normativ

-chart of account (to make later GuW cube)

-version (Simulation)

• Join Table:

-stock Qth (parent and child item),

-sales Qth for parent material code

-activity prices (routing),
-row material prices,

• Transform Table Roules:

-production quantity for parent material code
-production Qth for child material code (butom up, button down)
-production cost Roll UP (standard cost)

-needed purchase quantity (MRP-stock Qth)

I
would be very very greatful someboy can shere his Ideas. I like to hear Your view of my idea. How would you solve this

Best Regards
Boris
Files
• BOM planning is a bit tricky when it comes to cubes,
its a good idea of exploiting alias for product(BOM) dimension, since duplicate member names are not permitted.
Twin dimension modeling for parent child is not a good way to design.
for example, if Product A is output product constituting of B, C and Product B is constituting of C & E
Then your product(BOM) dimension would structure as
A (Alias: Product A)
| -- A_B (Alias: Product B)
| -- A_C (Alias: Product C)

B (Alias: Product B)
| -- B_C ( Alias: Product C)
| -- B_E (Alias: Product E)
In this case you are creating an alias member and populating with real product names. this aliases will be stored in system alias cube.
By this you can use Alias cube references to create calculations, alias names in reports & will also get the benefit of consolidation.

If the manufacturing process consist of different phases with different product in and out , then you would need to create separate cubes for each phases and link them as BOM dimension will change in each phase.

If your BOM dimension is huge(I would say excess of 10,000 elements), although cube will still be able to handle them, its better to explore custom relational solution, as cube solution will over time become more complex.

Best regards,
Noel.
• Noel than you for your replay. How would You solve this your way?

Like I can see this calculations are hard to manage in Palo BI. I find an German article obaut this topic which cover a Production cost in General but I can not find out if this Solution cover my Topic. Perhaps Volks know this and anibody can help. If somebody interesting in this topic pleas google this "kostenanalytik mit der jedox BI Suite". This pdf file from mr. Stefan Müller with printscreen and others howtos, can be allso downloaded. I hope somebody is interesting in this Topic

Best regards
Boris
• If my understanding is correct of your cost rollup.

simple example of 2 dimensions - Product & Measures

Product Hierarchy:
- MBS01
|---------- MBS01_MBL01

- MBS02
|--------- MBS02_MBS01

- MBB03
|--------- MBB03_MBS02

Define Product dimension Alias as Product Name & Produced
MBS01 ( Product Name: MBS01, Produced: TRUE)
MBS01_MBL01 (Product Name: MBL01, Produced: FALSE)
MBS02 (Product Name: MBS02, Produced: TRUE)
MBS02_MBS01 (Product Name: MBS01, Produced:TRUE)
MBB03 (Product Name: MBB03, Produced:TRUE)
MBB03_MBS02 (Product Name: MBB03_MBS02, Produced: TRUE)

and have Measures as:
N Qty per
N Scrap %
N Effective Qty
N Purchased Price
N Cost Rollup

Have a Enterprise Rule as
['Effective Qty'] = B: ['Qty per'] + ['Qty per'] * ['Scrap %']

and on Cost Rollup
['Cost Rollup'] = B: IF(EQUALS(PALO.DATA("DatabaseName","#_Product","Produced",!'Product'),"TRUE"),IF(PALO.EISCHILD("Database","Product","All Products",PALO.DATA("Database","#_Product","Product Name", !'Product')), PALO.DATA("Database","CostRollupCube", PALO.DATA("Database","#_Product","Product Name", !'Product'),"Cost Rollup") * ['Effective Qty'], ['Purchased Price'] * ['Effective Quantity']), ['Purchased Price'] * ['Effective Quantity']);

The Produced/Sourced Attribute is whether the raw material is produced in-house or sourced from vendor.

Its a bit ugly as currently Palo.ISERROR doesn't work to handle errors on PALO functions.
idea is to check if the child element product Name exists in parent element product Name.
For that reason we have additional Alias Produced, and starting IF clause.

B: signifies the calculation will happen only to base elements, so if a Finished Product has one or more raw materials then cost would be calculated and then aggregated to parent product.

ideally the second ELSE clause will never be reached.

Hope it helps!
Noel.
• Yes Noel it helps me a lot and yes you understand my cost roll-up which is my 1 step. In second step i will try to make a transform to get sales quantity from orders file. This sales Qth will be a starting point to calculete production/purchased Qth . Noel, I am very grateful for you help. I am traying to get a full Picture.

I am afraid I did not get (see pleas my example-is this correct?):

-How to build product hierarcy (rule)
-How t put data into Alias Product Name (rule) & Produced (rule).
_______________________________________________________________________________________________________________________
Starting point - FreeTH Product hierarchy (All Product/Parent/Child)

Product Hierarchy: Product Name Produced ---alias

-MBS04 MBS04 TRUE
-MBS04_FMS04 FMS04 FALSE
-MBS04_CSM05 CSM05 FALSE
-MBS04_ST200 ST200 FALSE
-MBS04_HT550 HT550 FALSE
-MBS04_CSA06 CSA06 FALSE
-MBM04 MBM04 TRUE
-MBM04_FMM04 FMM04 FALSE
-MBM04_CSM05 CSM05 FALSE
-MBM04_ST200 ST200 FALSE
-MBM04_MBS04 MBS04 TRUE
_________________________________________________________________________________________________________________________

I get a Enterprise Rule for ['Effective Qty'], but not for ['Cost Rollup']. Can you pleas explain me this formula (i did not find a second ELSE claus)?

Is it with this logic possible to make calculation for 2 production version (example: MBS04 ver 1= 40%,MBS04 ver 2= 60%)? This will be allso measure topic.

Probably I can make in area of measurement allso number of simulation version.

Best regards
boris
• Hello Boris,

If you are using TREEFH to create Product Hierarchy then, you will have to structure your source columns like:
ParentID,Parent_Name, Parent_Produced, ChildID,Child_Name,Child_Produced

In this example, Parent_Name is not needed as its same as parent id.

for:
- MBS01
|---------- MBS01_MBL01

source as:
MBS01,TRUE,MBS01_MBL01,MBL01,FALSE

Have 2 aliases defined in TREEFH as - Product Name and Produced
Then select Alias Product Name
and define for Level ParentID , Product Name as column ParentID
and for level ChildID, Product Name as column Child_Name
Similarly do for Produced alias, ParentID alias maps to column Parent_Produced, and childID maps to Child_Produced.

For formula Cost Rollup, I have considered alias cube which gets created if you define alias for Product dimension
Alias cube will have name as "#_DimensionName"

['Cost Rollup'] = B: IF(EQUALS(PALO.DATA("DatabaseName","#_Product","Produced",!'Product'),"TRUE"),IF(PALO.EISCHILD("Database","Product","All Products",PALO.DATA("Database","#_Product","Product Name", !'Product')), PALO.DATA("Database","CostRollupCube", PALO.DATA("Database","#_Product","Product Name", !'Product'),"Cost Rollup") * ['Effective Qty'], ['Purchased Price'] * ['Effective Quantity']), ['Purchased Price'] * ['Effective Quantity']);

Algorithm of current Cost rollup formula:
IF Product is Produced ?
THEN:
IF ProductName for the Product ID is in Parent Level as Product ID( child of "All Products")?
THEN : Parent Product's Cost Rollup multiplied by Effective Price
ELSE: (2nd Else clause) Purchased Price * Effective Quantity
END-IF
ELSE
Purchased Price * Effective Quantity
END-IF
I feel it would be better to omit the second(inner) if condition, by just putting the inner THEN condition
because Produced = TRUE product will have a parent level member with ProductName as ProductID in hierarchy.
does this make sense?

Best regards,
Noel.
• Hi Noel

Sorry for late response. I am a litle clumsy and I have again aditional questions.

1. The roule for alias Produced =TRUE or FALSE. If childName have a calculation in previus run, Child_Produced=TRUE else FALSE. Parent_Produced by defoult=TRUE.

2. Algorithm of current Cost rollup formula:
I agree with your proposal to omit the second ELSE and formula in this case would be?

3. Could this logic make calculation for 2 production version (example: MBS04 ver 1= 60%,MBS04 ver 2= 40%)? This will be allso measures in transform and cost roll up formula mast be changed, correct (how)?.

________________________________________________________________________________
ParentID ChildID Product Name -alias Produced -alias ProductioID MixVersion%

-MBS04 -MBS04_FMS04 FMS04 FALSE 1 60
-MBS04 -MBS04_CSM05 CSM05 FALSE 1 60
-MBS04 -MBS04_ST200 ST200 FALSE 1 60
-MBS04 -MBS04_HT550 HT550 FALSE 1 60
-MBS04 -MBS04_CSA06 CSA06 FALSE 1 60

-MBS04 -MBS04_FMS04 FMS04 FALSE 2 40
-MBS04 -MBS04_CSM05 CSM05 FALSE 2 40
-MBS04 -MBS04_ST200 ST200 FALSE 2 40
-MBS04 -MBS04_HT550 HT550 FALSE 2 40
-MBS04 -MBS04_CSA06 CSA06 FALSE 2 40

-MBM04 -MBM04_FMM04 FMM04 FALSE 1 100
-MBM04 -MBM04_CSM05 CSM05 FALSE 1 100
-MBM04 -MBM04_ST200 ST200 FALSE 1 100
-MBM04 -MBM04_MBS04 MBS04 TRUE 1 100
___________________________________________________________________________________

Best regards,
Boris
• for 1. You would be the best judge of that with your applied business knowledge. From what I see if a child alias is present in Parent level then its Produced else its sourced.

2. IF Product is Produced ?
THEN:
// note that MBS01_MBL01 alias is MBL01 and if its Produced then parent Product name: MBL01
Parent Product's Cost Rollup multiplied by Effective Price
ELSE
Purchased Price * Effective Quantity
END-IF

3. You can either have a Version dimension and define ways of doing this calculation, or
have more aliases for Product i.e Alias : "Version 1 factor" "Version 2 factor"
and put the percentage values in those columns.

and multiply by the result with the factor for example: PALO.DATA("Database","#_Product","Version1 factor", !'Product') for version 1.

Jedox is versatile and its possible to create most kind of calculation one way or other, there is a neat programming api in php and java to do more. But BOM planning is not a strong point of OLAP in general.

Best regards,
Noel.
• Hi Noel

1. OK
2. Is this Business Roule correct:

['Cost Rollup'] = B: IF(EQUALS(PALO.DATA("DatabaseName","#_Product","Produced",!'Product'),"TRUE"),IF(PALO.EISCHILD("Database","Product","All Products",PALO.DATA("Database","#_Product","Product Name", !'Product')), PALO.DATA("Database","CostRollupCube", PALO.DATA("Database","#_Product","Product Name", !'Product'),"Cost Rollup") * ['Effective Qty']), ['Purchased Price'] * ['Effective Quantity']);

3.I preffer one new Alias Version. N % values could be in measurments. So the bussines roule for Cost Rollup must be corrected (how).

Best Regards

Boris
• I have provided you a feasible direction, it remains unto you to model it to your needs, implement and test it.
We would be happy to help if you face real technical challenges on the way, but at least first give it a honest try and struggle.

What better way to test if a calculation works by compiling it yourself ?

regards,
Noel.
• Yes Noel You have help me a lot and I am wery thankfull for this. I am still learning palo and I will try my best. I will report my progress.

Best Regards

Boris