Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 2, 2005

MDX Essentials: Basic Set Functions: The TopCount() Function, Part I - Page 4

By William Pearson

16.  Leave the query open for the next step.

We present the results to the information consumers, who are quite happy with the outcome. At this point, we propose to extend the requirement once again, and to modify the query to return the top ten Warehouse Cities based upon the new Margin % calculated measure. This will provide another analytical perspective, we say; we also mention our confidence, based upon our experience within the realm of business intelligence, that this analytical "view" may act to broaden the perspective of Warehouse Profit alone.

We have only to make one simple change to the function to deliver this additional perspective. We will do so, and verify the operation of the modified TopCount() function, by taking the following steps:

17.  Within the query we have saved as MDX031-2, replace the top comment line with the following:

-- MDX031-3, Top Ten 1998 Producers":  
   Calc Member is the TopCount() Basis

18.  Save the query as MDX031-3, to keep MDX031-2 intact as a working sample.

19.  Replace [Measures].[Warehouse Profit] within the Topcount() function (in the ON ROWS line) with [Measures].[Margn %], the new calculated member we added above.

The ON ROWS line appears as follows after the change:

{ TOPCOUNT([Warehouse].[City].Members, 10, 
      ([Measures].[Margin %]))}  ON ROWS

The Query pane appears as depicted in Illustration 4, with our modifications marked, once again.

Illustration 4: The Query with Our Modifications Marked

20.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset shown in Illustration 5 appears.

Illustration 5: Result Dataset - Presenting a New View, Indeed

It quickly becomes obvious that we have derived a new view of the business, indeed, with this small modification. The operation of the TopCount() function, based in this instance upon the Margin % attained by each of the Warehouse Cities, results in a sort of the Warehouse Cities accordingly. The subsequent selection of the top ten from this sort presents Warehouse Cities that did not appear earlier, because their Profit levels were not among the highest in the organization. The substitution of the Margin %, however, as the basis for the TopCount() function has revealed a higher margin of return in Warehouse Cities that simply do not "register "in the former query, where their relative size obscures their performance.

This revelation is welcomed by the information consumers, who can now enhance their analysis capabilities by taking into consideration both perspectives (Warehouse Profit and Margin Percent) to isolate top performers. The study of the top groups from the perspective of Margin Percentage will likely add valuable lessons that can be extrapolated to other Warehouse operations, regardless of size. This is an excellent illustration of the power of multidimensional analysis, courtesy of the pairing of the TopCount() function with a meaningful calculated member to delve deeper in the analysis of top organizational performers.

21.  Re-save the file as MDX031-3.

22.  Exit the MDX Sample Application when ready.

Summary ...

This article served as the first of a pair of articles surrounding the potentially powerful TopCount() function. In this introductory session, we examined the TopCount() function, noting its obvious value in equipping us with a means of isolating high performers from among hundreds or thousands of fellow members. We noted that this ranking capability is often critical in data analysis and decision support scenarios, and then discussed how TopCount() facilitates our performing such ranking, first discussing the general operation of TopCount(), and then examining the syntax surrounding the function.

We then undertook exercises where we practiced using the function in meeting the business requirements of a hypothetical group of information consumers. We focused on a simple use of the function, to allow for minimal distraction while grounding ourselves in the basics, before providing a straightforward, yet meaningful, example of how we might leverage our core query with the addition of a calculated member to achieve a revealing additional perspective in our analysis of the performance of operational units. Throughout the practice examples, we briefly discussed the results datasets we obtained with regard to the TopCount() function, together with other surrounding considerations.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM