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.

Continues…



Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |