Business users are going to be interested in some calculations of the values in the measure to compare some values. So at the end, they will need valuable information from the existing fact tables that contain measures. For example, in our case they may be interested in comparing the units ordered with the units actually shipped. The business solution of this scenario is to add new calculations into the logical fact table. There can be another solution such as adding these calculations into the data warehouse and making the calculations at the database level. But this solution will take more time. So that's the reason that we're going to add new calculations into the BMM layer.
There are three types of methods that we can use:
Creating calculations based on logical columns
Creating calculations based on physical columns
Creating calculations using the Calculation Wizard (based on logical columns)
Besides these calculations, we're going to cover time-based calculations in Chapter 3, Using Aggregates and the Time Series Functions.
First, we're going to use the first method, which is about calculations based on logical column sources. We're going to expand the logical fact table and see the list of existing columns, then right-click on the table and navigate to New Object | Logical Column.... It's going to open the Logical Column properties.
By default, this Logical Column is not mapped to any physical column. When you click on the Column Source tab in the same window, you can see that it's not mapped yet. We're going to select the Derived from existing columns using an expression option box from the Column Source Type section. You'll see the Edit Expression icon.
When you click on the Edit Expression icon, it's going to open Expression Builder where we're going to write the formula and click on the OK button.
"Sales"."Fact_Sales"."Units Ordered" – "Sales"."Fact_Sales"."Units Shipped"
By using this method, we can see the differences between the two measures such as
Units OrderedandUnits Shipped. The most important point is how the calculations and aggregations are done in this method. When you use the logical columns in the calculations, it means that the aggregation rule is going to be applied before the calculation. So the formula is as follows:SUM (UNITS ORDERED) – SUM (UNITS SHIPPED)
The formula will not be:
SUM (UNITS ORDERED – UNITS SHIPPED)
When it comes to the second method, that is when the calculations will be based on physical columns, we're going to again create the Logical Column in the logical fact table. But this time we're not going to change anything in the Column Source tab, because we've already discussed that physical column mapping can be only accessed from Logical Table Source.
And when we open the Logical Table Source window, we'll see that the new column is not mapped yet.
Again we're going to open Expression Builder and write the formula, but this time physical columns are going to be used in the formula.
As a result you'll have another measure that calculates the difference between the units ordered and the units shipped. Now the aggregation rule is going to be applied after the calculation as shown:
SUM (UNITS ORDERED – UNITS SHIPPED)
As an extra step, you'll need to configure the aggregation rule that will be used for this new Logical Column. Otherwise, the BI server is not going to be aware of the aggregation rule that is going to be applied.
In the previous method, we could also use the wizard. It's easier to use it. You just have to right-click on the logical column that you want to make a calculation in and go to the Calculation Wizard menu item; it'll ask you the other values for calculations.
At the end of the wizard you'll notice that the result is exactly the same as the first method, creating calculations based on logical columns, and the aggregation rule is going to be applied before the calculations.
We learned how to create new calculations in this section and also covered how the aggregation rules are applied based on the columns. These calculation techniques may produce different results when it comes to the usage of multiplication or division.
Although it's possible to add new calculation measures into the logical fact table easily, it's better to implement them in the data warehouse, because whenever a query is executed, these new measures are going to be calculated every time during runtime. This is going to negatively impact the query performance. The recommended way is implementing these measures at the database level and handling them in the Extraction, Transformation, and Loading (ETL) process. As a result, when the query is executed, the calculated values will be retrieved from the database itself instead of making any calculation during runtime. Adding new calculations to the business model should be a short-term solution. Obviously creating these calculated measures at the database level has a cost. Data warehouse design and the ETL process are all going to be affected, and it'll take some time to implement them.