When it comes to Microsoft Business Intelligence implementations, it is surprising to see that very few utilize precalculated closing balances and semi-additive measures in SQL Server. This may be due to a lack of good examples available. In this article, we will explore this issue and provide a step-by-step guide on how to precalculate closing balances and build a cube for a balance sheet.

Background

Let’s start by understanding the concept of semi-additive measures. A semi-additive measure behaves differently in different dimensions. For example, when tracking inventory in an ERP system, the management is interested in the quantity available at the end of each day, rather than the net changes. This quantity available is known as the closing balance, which is a semi-additive measure because it behaves differently in the time dimension compared to other dimensions.

Another scenario where opening and closing balances are important is in balance sheets. When extracting general ledger data from an ERP system, you typically get the net transactions. However, financial professionals are accustomed to seeing opening balances, net changes, and closing balances. In this example, we will focus on calculating closing balances, but opening balances can be easily produced with slight modifications.

Step-by-Step Example – A Balance Sheet

Let’s consider a star-schema for describing a balance sheet. The fact table contains transactional data, and the dimension tables provide additional information about the accounts and time periods. The goal is to calculate the closing balances for each account on a daily basis.

To precalculate the closing balances in the relational database, we create a new table called Fact_GL_CB. This table is similar to the previous fact table but includes an additional field called Amount_CB. We then fill this table with the closing balances by summing all amounts with a date less than or equal to the current day. This can be achieved using a recursive SQL query.

Once the closing balances are precalculated in the relational database, we can proceed to build the cube in Analysis Services. We create a data source view and then use the New Cube wizard to create the cube and dimensions. We select both the original fact table and the precalculated closing balances table as measure group tables. We also make some adjustments to the dimensions and measures to ensure they are displayed correctly.

Testing and Conclusion

To evaluate the results, we can use MDX Studio to run MDX queries against the cube. We can compare the performance of dynamically calculated closing balances versus precalculated closing balances. The precalculated closing balances are significantly faster, especially when not cached. Additionally, dynamically calculated closing balances may have limitations such as problems with solve-order and lack of options like display folders.

In conclusion, utilizing precalculated closing balances and semi-additive measures in SQL Server offers several advantages. Not only does it improve performance, but it also avoids potential issues with dynamic calculations in the cube. If you already have closing balances or opening balances from the data source, it is highly recommended to use precalculations. By following the step-by-step example provided in this article, you can easily implement precalculated closing balances in your SQL Server solution.