dcsimg

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

May 2, 2005

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers