articles >> business intelligence >> Optimizing Microsoft SQL Server Analysis Services: Optimize ...

Optimizing Microsoft SQL Server Analysis Services: Optimize Query Performance with a Derived Measure

By : William E. Pearson, III Apr 24, 2004

Page 2 / 8


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, but, 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).

 

 

 

Hands-On Procedure

Preparation – Retracing the Steps of the New MSAS Administrator

Let’s first create a copy of the original calculated measure, so as to provide a basis of comparison for the derived measure, which we will next create to take its place. To repeat the original requirement, we need to generate an adjusted store cost value, which contains an additional, fixed allocation of marketing costs. Specifically, we will add fifteen percent to the store cost value in any case where a specific marketing promotion was involved.

We will be able to “flag” the use of promotions through the use of the promotion_id in the sales_fact_1997 table, which we can see within the Schema view of the FoodMart Sales cube. It appears, with the promotion_id column circled in red, as shown in Figure 1.
 

Figure 1: The Promotion_Id Column in the Fact Table

A quick browse of the promotion table (which is joined to the sales_fact_1997 table via the promotion_id column), the results of which are partially depicted in Figure 2, reveals that a promotion_id of 0, and a corresponding promotion name of “No Promotion,” indicates the absence of a promotion.

Figure 2: Partial Browse of the Promotion Table – Promotion_Id of 0

The MSAS administrator reasoned that this field could serve as the basis of a conditional expression, to the effect of “if the promotion_id equals zero, we need not apply any percentage increase to the existing store_cost value (and can accept its current value in our new measure),” as no additional promotion was performed for any associated items. If, however, the promotion_id equals anything except zero, she could apply the mandated fifteen percent upward adjustment to store_cost. The operation could then be handled using a relatively straightforward “IF-THEN” construct, as we shall see in the following steps, where we recreate the original calculated measure.

1. Open Analysis Manager.

2. Expand the Analysis Servers folder in the management console.

3. Expand the Analysis Server with which you are working by clicking the “+” sign to its left.

4. Expand the FoodMart 2000 database.

5. Expand the Cubes folder inside the FoodMart 2000 database.

6. Right-click the Sales cube.

7. Select Edit … from the context menu that appears, as depicted in Figure 3.

Figure 3: Select Edit from the Context Menu

The Cube Editor opens.