Doing Balance Calculations in a Microsoft Analysis Services Database
By Antonina Semenkova, Team Lead in BI solutions for Galaktikasoft.
Balance calculation is one of the most complex and difficult issues we had to deal with while working with Microsoft Analysis Services. There are several approaches to it, but we usually use two of them:
- Calculating balance on DSV level (MS SQL tables or views)
- Calculating balance in the multidimensional data warehouse based on product movement operations
When the first approach is used, daily balance values are stored in the relational database. The multidimensional warehouse pulls data from the database, and the measure is aggregated with a LastNonEmpty function for the Period dimension (month, quarter, and year). It requires more time to process the multidimensional warehouse, but less time to retrieve the data from the OLAP store, because the data has already been pre-calculated. It also allows slicing the data into partitions and using predefined aggregations. Calculating balance on the DSV level enables the correct working of the Drill-Through function.
When using the second approach, there is no need to store daily balance values because they are calculated when the multidimensional warehouse data is viewed. It is better to use this approach when write-back measures are used; for example, when the user inputs data that affects balance calculation in the multidimensional warehouse. However, such an approach requires more time to retrieve the data when the query is run. Besides, in such cases, the Drill-Through function does not work correctly because there are no source data rows to return and balance values are calculated at query time.
Let's see how both approaches work.
There is a table in the AdventureWorksDW2012 database that stores data about daily product balance and movement of products. Let's design a cube that will calculate product balance—Product Balance—in which we'll create measure groups for a balance using both approaches.
In this cube, we'll use the existing Date dimension, and we'll create a Product Balance dimension for products because the existing Product dimension doesn't contain all products that have balance. Let's create a Product Balance dimension by analogy with the Product dimension, but only with Product, Product Subcategory, and Product Category hierarchy attributes. These hierarchy attributes can be joined into the Category-Subcategory-Product hierarchy:
Figure 1: Category-Subcategory-Product hierarchy in Product Balance cube
Let's create a view for balance factBalance1:
SELECT ProductKey, MovementDate, UnitsBalance, DateKey FROM FactProductInventory
Based on this view, let's create a Balance 1 measure group that will have Balance 1 measure based on the factBalance1.UnitsBalance column with a LastNonEmpty aggregation function.
Let's create a view for product movement factMovementProduct:
SELECT ProductKey, MovementDate, UnitsIn, UnitsOut, DateKey FROM FactProductInventory WHERE (UnitsIn <> 0) OR (UnitsOut <> 0)
Based on this view, let's create a Movement measure group that will have Units In and Units Out measures based on the factMovementProduct.UnitsIn and factMovementProduct.UnitsOut columns, respectively, with a Sum aggregation function. These measures represent product income and expenditure.
Let's create a view to show opening balance factOpeningBalance:
SELECT ProductKey, MovementDate, UnitsBalance AS OpeningBalance, DateKey, CAST(NULL AS INT) AS UnitsBalance FROM FactProductInventory WHERE (UnitsBalance <> 0) AND (MovementDate = (SELECT MIN(MovementDate) AS MinDate FROM FactProductInventory AS FactProductInventory_MinDate))
Based on this view, let's create a Balance 2 measure group that will have an Opening Balance measure based on the factOpeningBalance.OpeningBalance column with a Sum aggregation function. This measure represents the product opening balance.
In the Balance 2 measure group, let's create a Balance 2 measure based on the factOpeningBalance.UnitsBalance field with a LastNonEmpty aggregation. As you can see, the factOpeningBalance.UnitsBalance field is empty; it is a "stub" measure for balance calculation. Thus, in the cube calculation we have to redefine this measure calculation as follows:
SCOPE([Date].[Date].[Date], [Measures].[Balance 2]); THIS = SUM(NULL:[Date].[Date].CURRENTMEMBER, [Measures].[Opening Balance] + [Measures].[Units In] - [Measures].[Units Out]); END SCOPE;
Now, the Product Balance cube contains two measures that show product balance: Balance 1 and Balance 2>, calculated by different approaches.
Let's run MDX queries to analyze their performance speed:
Figure 2: Using Balance 1 and Balance 2 measures to view product balance for the Date dimension
Figure 3: Using Balance 1 and Balance 2 measures to view product balance for the Product dimension
As you can see, the measure created using the first approach returns results significantly faster when a query is run for the first time. However, when queries are run for the second time, their speed is similar because the data is already cashed:
Figure 4: Using Balance 2 measure to view product balance for Product dimension for a second time
When choosing an approach to balance calculation, you should consider various aspects, such as required speed of receiving data, necessity use Drill Through operation, or opportunity to change the data right in the cube (write back).
The first approach allows the correct work of the Drill-Through operation and generates a quick MDX query. However, balance values have to be calculated in the relational database after any change in product movement, and they require a large amount of space in the database. Besides, it requires a long time to process the multidimensional warehouse.
When balance values are calculated at query time, cube processing goes much faster, and there is no need to store balance values in the relational database. It also allows us to use write back functionality. The disadvantages are: the MDX query time is longer than when using the first variant, and Drill-Through doesn't work.
About the Author
|Antonina Semenkova is team lead in the BI solutions department at Galaktikasoft and an expert in Windows BI tools.|
This article was contributed for exclusive use on Developer.com. All rights reserved.