Overview
In this session, we’ll use MDX to calculate the Gross Margin %. First, we’ll take an in depth look into the differences between SQL and MDX, then we’ll look at the different areas of the application where we can apply MDX. And lastly, we’ll use a Member Formula to define the Gross Margin %.
Script
[Gross Margin] / [Gross Sales After Returns]
Transcript
[transcript]
In this session, we’ll use MDX to calculate the Gross Margin %.
First, we’ll take an in depth look into the differences between SQL and MDX, and the particular use-cases for each, then we’ll look at the different areas of the application where we can apply MDX. And lastly, we’ll use a Member Formula to define the Gross Margin %.
In the Application, tables exist in a SQL Database, for example the Dimension Tables or Fact Tables. Cubes are the aggregations of table data in an OLAP Database.
When we want to manipulate table data, we use SQL. When we want to manipulate data in the Cube we use MDX.
Some calculations, like our Gross Margin %, require aggregated data to obtain the results. These calculations can’t be easily represented with SQL.
For example, in this sample Form, we’ve manually calculated Gross Margin and Gross Sales After Returns with SQL, and used those values to determine the Gross Margin %. While each individual calculation is correct, the aggregation of these values is not.
In the second Form, we’ve defined the same calculation using MDX. Notice how as we change the filter selections, the dynamic aggregation is always calculated correctly. To get this value for all possible aggregations in the Form, we need to use MDX.
There are three areas in the Application that we can apply MDX. We can write MDX directly into the definition of a Dimension Member as a Member Formula, we can add MDX scripts to a Model, or we can edit the Native MDX tab within a Form.
It’s important to note that this is also the order in which the MDX will be executed in the Application. This means that MDX scripts on the Model can override the Member Formulas, and the Native MDX tab can override both.
As Gross Margin % is always defined as the ratio of Gross Margin over Gross Sales After Returns, let’s add the MDX calculation to the Dimension Member.
In the Account Dimension, let’s turn on Member Formulas by checking the box here. Then we can locate Gross Margin % in the Account Hierarchy, and select the gear icon to define a formula. Here, we can define it simply as Gross Margin / Gross Sales After Returns.
Then format it as a percent. Let’s Deploy the Application. In the P&L Report, we can confirm that it’s working. Now all the calculations in our Application are complete.
In this session, we examined the differences between SQL and MDX, and the particular use-cases for each, then we looked at the areas of the application where we can apply MDX. And lastly, we used a Member Formula to define the Gross Margin %.
This concludes Part IV of the series. In the final part, we’ll go over some more administrative options and examine users, security, and workflow settings.
[/transcript]