Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Considering DISTINCT COUNT


11. Click OK to accept the selection.

The Insert Measure dialog closes, and we see the new measure appear (default name of Product_Id) in the Measures folder, as depicted in Figure 7.





Figure 7: Product_Id Appears in the Measures Folder (Circled)


12. Click-select Product_Id in the Measures folder, if required.


13. If necessary, click the downward arrow beneath the Cube Tree to open the Properties pane.


14. Click the Basic tab.


15. Modify the default Name of Product Id to the following:


Product Count


16. Type the following into the empty Description box, just below the Name box:


Distinct Count – Products


17. Click the box to the right of the Aggregate Function property label (at the bottom of the Basic tab), to enable the selector.


18. Select Distinct Count in the Aggregate Function selector.

The Basic tab of the Properties pane appears, with our modifications, as shown in Figure 8.




Figure 8: Product Count Measure – Properties Pane – Basic Tab



19. Click the Data tab as if going to the Data View to perform a routine browse.

A warning briefly appears, indicating that sample data is being generated, and that the cube requires processing, as a result of our modifications. The sample data then appears, along with a static warning below it, as partially depicted in Figure 9. The warning ensures that we are aware that the data is not what it might appear to be, and that the cube must be processed to make updated, actual data available.





Figure 9: Data View (Partial and Compressed) – With “Staleness” Warning at its Foot



Let’s process the cube to activate our changes.

20. Select File —> Save to save the cube in its modified state.


21. Select Tools —> Process Cube to initialize the processing steps.

A message box appears, stating that the cube has no aggregations, and asking if we wish to design them at this time, as shown in Figure 10.





Figure 10: Aggregations Message Box – Just Say “No”



NOTE: The message box may not appear, if the cube has been altered with regard to aggregations since its installation as an MSAS sample. If not, the next box will appear instead, skipping this one.

Continues…

Pages: 1 2 3 4 5 6




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 |