Budget data with different dimensionality and granularity
Hi,
how can we budget data for measures having different dimensionality and granularity?
Consider some commercial and distribution cost calculated based on % or mileage
Commission cost, for example, are a % defined by customer (or by product or by aggregate entity like channel or brand) and data entry form should refer to this dimensionality/granularity.
Among the following solutions:
-use an ad-hoc data model with multiple cubes that fit the required dimensionality (and some lookup function to calculate the cost amount)
-use the sales budget cube and add some logic to write to a dummy element for each non-referenced entity (and some refer-to funtion to calculate cost amount)
-use the sales budget cube and add some logic to splat the unit value to each element for each non-referenced entity
-others
which is the best approach with Board in terms of performance and scalability?
Many thanks in advance.
Sincerely
Alberto
Answers
-
Hi Alberto Piazza,
Depending on the requirements, you can achieve this result in different ways. The best practice is not to create dummy members in entities as it's not really necessary given the flexibility of having separate measures with different granularity - one of BOARD main strenghts.
The most common way of developing a budget process similar to the one you describe is via different cubes (measures) in data-entry and a procedure to calculate the result and populate the main P&L cube via dataflows.
Simple example with:
- A sales commissions % cube by Year and Area (father of Customer in a tree)
- A cube with a simple P&L entity, dimensioned also by Product and Customer - with a rule applied to calculate specific lines
You can enter the percentages and the Sales Commissions line is calculated via dataflow.
The procedure can be triggered via button or on data-entry save. If you need to show the user a simulation of how the cost would look like, you can add the formula in the data-entry layout so that it displays the result before committing it.
The procedure just has a step to calculate the PL line Sales Commissions by multiplying the Gross Revenues with the Sales Commission % by area.
This is the simplest way if you already have a cube with P&L lines / KPIs. Ideally you would want to have the main measures as separate cubes (Gross Revenues as a Cube, Sales Commissions as a cube) so they can be used in reporting, data-entry can be achieved in different ways, and then everything is consolidated together in a single PL view afterwards.
Michele
3
