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 percent. 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 percent.
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 percent, require aggregated data to obtain the results. These calculations can't be easily represented with SQL.
For example, in this simple form, we've manually calculated gross margin and gross sales after returns with SQL and used those values to determine the gross margin percent. 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, 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. If we want to affect all models in the application, we can write MDX directly into the definition of a dimension member as a member formula. We can add an MDX script to a single model to affect just that model, or we can edit the native MDX tab of an individual 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 a model can override a member formula, and the native MDX tab can override both.
As gross margin percent 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 percent in the account hierarchy and select the gear icon to define a formula.
Here, we can define it simply as gross margin divided by gross sales after returns, then format it as a percent.
Let's deploy the application.
In the P and 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 percent.
This concludes part four of the series. In the final part, we'll go over some more administrative options and examine users, security, and workflow settings.
[/transcript]