Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures - Page 2
August 16, 2004
Create a Derived Measure
Objective and Business Scenario
In this article, we will examine the use of a derived measure, as a substitute for, and in contrast with, an existing calculated measure that represents a performance bottleneck. For purposes of our practice procedure, we will assume that we have been contacted by a client to investigate slowdowns in query response time when accessing an MSAS cube. We have been informed that reporting response times, in general, have disappointed information consumers throughout the organization. They specifically indicate that using some of the calculated members in the current cube structures lead to report performance issues, although they have no idea as to the root of the problem.
Discussion of the issues with various consumers has revealed that the client, as is often the case these days, failed to become involved in the implementation while it was underway. As the "go live" commitments of the responsible department loomed, the attention of its manager was suddenly "redirected" by upper management. The manager allowed a very brief turnover from the implementation team to a designated employee, whose original position had been recently off shored. Having assigned the employee the administration of the MSAS cubes, the manager dismissed the implementation team without further ado.
While the new support selectee had some experience in a competitor OLAP application, she had spent only a little "cram" time with MSAS, barely progressing from a popular "Baby Steps" book that was once popular on the market. She was confident that she could "figure it out," however, and expressed optimistic confidence that managing the MSAS applications would be a "piece of cake." For that matter, she allowed management to assume that she was an MSAS "guru," and promised to take over where the implementation team left off, and meet the information consumers' evolving needs as they arose.
The "Baby Steps" book, while an excellent introduction to MSAS functionality, failed to equip the new MSAS Administrator to deal with the involved troubleshooting, processing and performance tuning considerations of the real world. Understandably, its focus was teaching MSAS quickly, and in a way that would appeal to non-technical people of varied backgrounds. After the manner of most "populist" technical publications, general concepts, versus optimization and best practices, was, unsurprisingly, the objective.
Upon initially examining the cubes in place, we have noticed many things that could be changed, including the use of lengthy description fields versus (surrogate) id's for member key columns within the cubes, among a host of other items that could be adjusted to optimize both processing and querying performance. One specific item that we have identified, and which is the focus of this article, was the use of various calculated measures when a derived measure might have been constructed. While we will take a single example of this for purposes of our practice exercise, we have actually found several instances where the same procedures might be applied to build and substitute derived measures within the MSAS cubes we have examined at the client location.
The calculated measure upon which we will concentrate in our present article was constructed within the Sales cube to generate a store cost value, with a fixed allocation of marketing costs added. The adjusted cost value, constructed using the calculated measure under consideration, applied an added fifteen percent to the store cost for products that were subjected to given marketing promotions. Products that were not marketed under specific promotions were assigned only the original store cost amounts to their adjusted cost values. Again, this calculated measure is only one example of several such constructions that, as we shall see, can be managed in a way that promotes more efficient query processing.
Considerations and Comments
For purposes of this exercise, we will create the calculated measure as constructed by the erstwhile guru, using the Sales sample cube that accompanies the installation of MSAS. We will then construct a derived measure that will generate the same values, but with enhanced query processing time.
Unlike calculated members (including, of course, calculated measures), whose values are created at runtime, based upon the MDX expression(s) they contain, a derived measure, just as any other cube measure, is calculated during the cube processing cycle, and is stored within Analysis Services. In the case of the calculated member, only its definition is stored, and the values it renders are calculated in memory when specified in a query. Because only the definition is stored, cube processing time ("build time") is not materially increased, and, indeed, depending upon the environment, this might be an appropriate tradeoff. In our present scenario, however, query processing is the more important concern, so we lose the primary benefit behind the choice of a calculated measure to provide the needed values.
We are limited to creating derived measures from the columns of the fact table, since MSAS essentially only offers these columns as options in the measure creation process. However, as many of us have found, a view can be created to contain columns that lie outside the physical fact table, making this limitation a bit less restrictive than it might appear at first blush. Derived measures can extend well beyond simple math, as we shall see in our example, and can apply conditional logic and other nuances in derivation of the measure. The syntax obviously has to fit the database - we will note in our practice example that we use MS Access - friendly syntax, because the FoodMart 2000 data source ships as an MS Access .mdb.
If the sample FoodMart MSAS database, or for that matter the FoodMart .mdb that underlies it, was not installed, or was removed prior to your beginning this article, please see the MSAS documentation, including the Books Online, for the straightforward procedure to restore the database from the archive (.cab) file containing the samples. As of this writing, a copy of the archive can be obtained from the installation CD or via download from the appropriate Microsoft site(s).