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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 6, 2005

MDX Essentials: Basic Set Functions: The TopCount() Function, Part II - Page 5

By William Pearson

The calculated member we create with this MDX is a "busy place." It accomplishes the TopCount(), providing as the << Set >> argument a Descendants() function. We saw the TopCount() / Descendants combination in very similar action in the first exercise of the series, and so I won't go into that any further here, other than to point out that the function is structured to return the set of the members of the Warehouse dimension that belong to the level it specifies, the Warehouse Name level. It is from this level that we are able to obtain the string representing the Warehouse Name.

The TopCount() function is provided a << Count >> argument of "1" , obviously, as we seek to return the top Warehouse with regard to the Units Shipped measure, the final argument of the function, << Numeric Expression >>. The interim result of the TopCount() function, therefore, is to return a single-member for each month, which enables us to leverage the .Item function. .Item, in turn allows us to exploit its capability to return the member in a tuple, based upon its position. By using .Item(0), we select the first single-member (the product of the TopCount() of 1) tuple in each Month set. (.Item contains a zero-based specification of position, hence the zero as the position argument.) We then use .Item(0) again, immediately after the first .Item function, to select the first (and only) member in the single-member tuple.

Finally, we use the .Name function to return the name of the member that we have so precisely isolated. This function, combined with the functions just described, result in a calculated member that returns the name of the Warehouse with the highest shipping quantity in a given Month in 1998, which is handily identified by the TopCount() / Descendants() combination we discussed earlier.

NOTE: For an introduction to the .Item function, see my article Basic Member Functions: The .Item Function, another member of the MDX Essentials series at Database Journal.

9.  Modify the ON COLUMNS line of the query, which already contains the Units Shipped measure, to contain the new Largest Shipper calculated member / measure we defined above, as follows:


{[Measures].[Units Shipped], [Measures].[Largest Shipper]} ON COLUMNS,

10.  Leave the remainder of the query in its original state.

The Query pane appears as depicted in Illustration 8, with our modifications marked.


Illustration 8: The Query with Added Calculated Member -

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

The Results pane is populated, and the resulting dataset, partially shown in Illustration 9, appears.


Illustration 9: Result Dataset - New Calculated Member Appears (Partial View)

We note that the Warehouse Names of the locations with the highest shipping volumes appear. The presence of the months of 1998, which populate the rows already, result in the evaluation of the Largest Shipper calculated measure for each month respectively, giving us an outcome that meets the expressed requirements of the information consumers.

12.  Re-save the file as MDX032-2-2.

13.  Leave the query open for the next step.

Using the results from our latest query execution, we can easily verify that the calculated member is producing the results that its name purports. An example of such verification appears in Illustration 10, where we see that the top shipper Warehouse Name returned by our calculated member reflects the name of the Warehouse that displays the highest value in the monthly Units Shipped measure at its left.


Illustration 10: Verifying the Largest Shipper Name Returned as Correct (Partial View)

All that remains, to align the results dataset with the presentation requirements specified by the information consumers, is to remove the Units Shipped measure. Recall that we added this measure earlier simply to allow us to verify the correct operation of the Largest Shipper calculated member.

14.  Within the query we have saved as MDX032-2-2, replace the top comment line of the query with the following:


-- MDX032-2-3, Alignment of Results Dataset to the Business Requirements

15.  Save the query as MDX032-2-3, to keep MDX032-2-2 intact as a working sample.

16.  Remove the Units Shipped measure ([Measures].[Units Shipped]), from the existing ON ROWS line within the SELECT statement of the query, so that the line appears simply as:

  { [Measures].[Largest Shipper]} ON COLUMNS,

17.  Remove NONEMPTYCROSSJOIN, and the left parenthesis to its right, on the line below the row we modified in the last step (part of the ON ROWS specification). The item to be removed appears as:

NONEMPTYCROSSJOIN(

18.  Remove the following, the second Descendants() function, from the ON ROWS definition, as well:

{DESCENDANTS( 

      [Warehouse].[All Warehouses].[USA], [Warehouse].

            [Warehouse Name] )})

19.  Remove the comma (",") that appears to the right of this remaining portion of the ON ROWS line:

[Time].[1998], [Time].[Month] )},

To summarize the final set of modifications, the query appears, before our modifications, as follows, with syntax to be deleted enclosed in rectangles:

-- MDX032-2, Addition of Calculated Member to Produce Name of Warehouse with 

      Highest 1998 Volume Monthly Shipping

WITH 

MEMBER

     [Measures].[Largest Shipper]

AS
 
    'TOPCOUNT( DESCENDANTS( 

            [Warehouse].[All Warehouses].[USA], [Warehouse].[Warehouse Name] ), 

                1,  (Time.CURRENTMEMBER, [Measures].[Units Shipped]) 
            ).ITEM(0).ITEM(0).NAME'

SELECT 

  { [Measures].[Units Shipped], [Measures].[Largest Shipper]} ON COLUMNS,

  NONEMPTYCROSSJOIN(

{DESCENDANTS( [Time].[1998], [Time].[Month] )} , {DESCENDANTS( [Warehouse].[All Warehouses].[USA], [Warehouse].[Warehouse Name] )})
ON ROWS FROM [WAREHOUSE]

20.  Leave the remainder of the query in its original state.

The Query pane appears as depicted in Illustration 11, with deletions having been made.


Illustration 11: The Query, after Final Modifications

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

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


Illustration 12: Result Dataset - Reflecting the Business Requirement

22.  Re-save the file as MDX032-2-3.

23.  Exit the MDX Sample Application when ready.

Summary ...

This article served as an extension of our previous introductory article, Basic Set Functions: The TopCount() Function, Part I. We briefly reviewed the operation of the TopCount() function, and then extended our examination of the TopCount() function to include somewhat more sophisticated uses.

We defined illustrative business needs as posed to us by hypothetical groups of information consumers, and, after discussing the needs in general, with regard to challenges inherent to meeting them with the TopCount() function in particular, we constructed queries to meet the expressed requirements using combinations of TopCount() and other MDX functions we have explored in articles of the MDX Essentials series. In both practice examples, we discussed the syntax contained within the solutions we constructed for the information consumers, as well as the datasets we obtained in employing TopCount() and other functions, 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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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