ERP Business Intelligence: Using Calculated Measures in Microsoft Dynamics

In the previous Turnkey Technologies BI Blog, we defined BVDs (BI Value Differentiators) and identified one Dynamics ERP BVD, Analysis Cubes for Excel (ACE).

Today we are not covering BI geo-spatial analysis, natural language queries or BI dashboard eye candy. The banner ads on the internet show us enough of that for the moment. Today we are covering a key underlying component of making great BI happen, which involves Calculated Measures.

Calculated Measures are a good example of an important differentiating feature of BI and ACE, which provide an efficient way to analyze otherwise hidden parts of your historical/projected results in unique ways for predictive What-If analysis, and a variety of dashboard and BI reporting/trending analysis use cases.

Calculated Measures are dynamic pre-defined calculations that become a portable analysis formula for use against any combination of your business dimensions. They’re a dynamic smart-formula constructed to run against a BI cube for a variety of analysis scenarios, which is precisely what makes them a BI Value Differentiator (BVD).

The differentiator is how the results become insight that would typically not be identified via a two-dimensional pivot table, SmartList or other transactional list report for-- in the case of predictive analysis-- knowing what's coming, not merely what's happened. Calculated Measures are one method for sourcing a dashboard Key Performance Indicator, time-based calculations for a predictive What-If model, or one-touch slicers for a Cube-based Pivot Analysis.

In the diagram to the right, Calculated Measures would typically be constructed as part of the BI cube Rules Engine of a traditional BI solution, and can also live within the reporting tool in some cases, as with PowerPivot when Excel or PowerView is used.

So today’s blog, in summary, tells us…

“Dynamics GP/AX owners who implement and use ACE (Analysis BI cubes for Excel) can then identify/refine calculated measures that are re-usable to analyze and discover unique BVD decision information within their data”.

BI puts the right information in the hands of the people who use it for decision and business action, but you first need to know the key feature differentiators to do that. PowerPivot is a free way to explore BI BVDs in a prototype scenario when you already own MS Office 2010 & 2013, so don’t overlook the value and power of properly designed Calculated Measures when planning your BI initiatives.

If you have any questions, or would like to learn more:

By Turnkey Technologies, Inc. – Microsoft Gold Certified Dynamics ERP and Dynamics CRM Partner of Missouri, Illinois, Oklahoma and North Texas.