Optimizing Microsoft SQL Server Analysis Services: Optimize Query Performance with a Derived Measure
22. Click OK to begin processing.
Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Figure 11.
Figure 11: Indication of Successful Processing Appears (Compact View)
23. Click Close to dismiss the viewer.
24. Click the Data tab in the Cube Editor, if necessary.
Cube data is retrieved, and we are able to see the values that appear for all measures, including the new calculated measure, in the Data view.
25. Drag the Store Type dimension in the upper half of the Data view to the top of the left-most column in the lower half – in my case, I am dragging the Store Type dimension bar over the Country column heading, but your row axis may be different, depending upon previous activities with the cube.
Store Type appears in the place of the previously existing dimension in the row axis.
26. Drag the Promotions dimension to the immediate right of the newly placed Store Type column, to effect a “crossjoin.” When the cursor appears as shown in Figure 12, drop the Promotions dimension.
Figure 12: Cursor Indicates Appropriate Drop Point
The final arrangement should appear as depicted in Figure 13.
Figure 13: Arrangement in the Data View – Partial Row Axis
We can now see the calculated member exactly as it was constructed by the newly appointed MSAS Administrator. We will leave the calculated measure in place until after constructing and testing its replacement, for comparison purposes.
Procedure – Creating a Derived Measure
We will next create a derived measure, whose role will be to improve performance from the perspective of querying, and thus from that of the information consumers. The derived measure will be directly based upon columns in the FoodMart database, upon which we will use SQL to perform the required logic, as we shall see. The key reason, we will recall, for creating the derived measure is that the measure is calculated as a part of cube processing, and is stored completely in MSAS for rapid retrieval, versus being generated at runtime like the calculated measure.
We will name our derived measure DM_Cost with Promo Alloc, and, once we verify that it stores the desired values, will replace the MSAS Admin’s calculated member that we recreated in the last section.
1. In the tree pane of the Cube Editor, right click the Measures folder.
2. Select New Measure from the context menu that appears, as shown in Figure 14.
Figure 14: Select New Measure …
The Insert Measure dialog box appears, offering us the appropriate columns in the designated fact table for the cube, sales_fact_1997.