Advanced Gross Margin Percent Calculation using Data Map

The following post contains the data pipeline configurations to calculate Gross Margin Percent using the Planning application in the 7.5 Appshell.

The algebra that supports this calculation is the following:
Gross Margin/Total Revenue = Gross Margin Percent

What makes the Gross Margin Percent calculation challenging, is the fact that both the Total Revenue and the Gross Margin accounts that are used to calculate the percentage are non base level. This means the numbers behind these accounts don't readily exist in the native fact tables and need to be derived first before the percentage is calculated.

This example will illustrate the following BPMC best practice concepts with the following advanced conditions applied:

• Non base level calculations using the Data Map only
• No use of remote staging
• Minimal custom SQL code
• Calculation performed on unoptimized non-distinct data
• Calculation performed on Multi-level data

Remote staging is available in BPMC, however this does not provide optimal performance, does not scale, and requires a lot of custom code.  Use of the data map is recommended wherever possible.  Data Map will work on unoptimized data if the proper techniques are applied.  This example demonstrates these techniques.

The following prerequisites are required for this example:

• A base level target Account called  GMarginPct needs to be created in the Appshell planning account dimension
• A new feed needs to be created called GrossMarginPercentCalc
• The surface area of this feed needs to be configured.  Both source and destination of the surface area need to point to the 7.5 Appshell planning application 
• BPMC Config Builder 2.0.1 or greater.

Once the GrossMarginPercentCalc feed has been created and the surface area is set up, the following spread sheet can be copied and pasted into the data pipeline.  There are three (3) configuration  tabs, one for each respective area of the data pipeline:

• Raw Data
• Data Map
• Optimize and Filter

These principles and concepts depicted here can  used for ANY non based level calculation.  A critical aspect of getting the Data map to work on unoptimized, multi-level data is understanding how to use the RTOC capability.