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 4

By William Pearson

Perform a Topcount() within the Context of Another Dimension, and Return a Related String for Presentation Purposes

Our next exercise comes in the form of a request from the same group of information consumers (the Accounting and Finance department at FoodMart), who have expressed satisfaction with our first solution. This request also involves "top counts" of Warehouses, and on the surface may sound simple to many of us. The consumers want a query that will produce a plain list containing the top individual Warehouse, for each of the twelve Months of 1998 (denominated simply in numbers one to twelve, as they appear in the Warehouse cube), this time in terms of number of Units Shipped within that month.

We again draft a sketch of the proposed report layout, to confirm our understanding and to agree fully on the requirement. The sketch appears as illustrated in Table 2.

Largest Shipper

Units Shipped

1

Warehouse Location shipping largest no of units in Month 1

Total No. Units

2

'' '' '' '' '' '' '' '' '' 2

'' '' "

3

'' '' '' '' '' '' '' '' '' 3

'' '' "

4

'' '' '' '' '' '' '' '' '' 4

'' '' "

5

'' '' '' '' '' '' '' '' '' 5

'' '' "

6

'' '' '' '' '' '' '' '' '' 6

'' '' "

7

'' '' '' '' '' '' '' '' '' 7

'' '' "

8

'' '' '' '' '' '' '' '' '' 8

'' '' "

9

'' '' '' '' '' '' '' '' '' 9

'' '' "

10

'' '' '' '' '' '' '' '' '' 10

'' '' "

11

'' '' '' '' '' '' '' '' '' 11

'' '' "

12

'' '' '' '' '' '' '' '' '' 12

'' '' "


Table 2: Results Dataset, with TopCount() Defining Columns

We agree to pursue a solution, and consider the requirement in bite-size pieces. One challenge that stands out immediately is the apparent need to return the Warehouse Name, a text string, from the lowest level of the Warehouse dimension. TopCount() is still at the heart of our contemplated approach, however, with the Units Shipped the measure upon which it is based.

Let's construct part of the query to get started. First, we will construct a query that lists all Warehouses, and respective quantities shipped, for each of the Months in 1998. This serves to build the core ON ROWS specification, the "months" part of which we will use within our final solution. It also serves to present the quantities for each Warehouse, each Month, allowing us to "proof" the accuracy of the results of the next step, where we generate the name of the top Warehouse from the perspective of shipping, for each Month.

1.  Select File --> New to open a blank Query pane.

2.  Type the following query into the Query pane:


-- MDX032-2-1, Shipping Volume Qty by Month, 1998, for Individual U.S.    
    Warehouses"
SELECT 
  {[Measures].[Units Shipped]} ON COLUMNS,
  NONEMPTYCROSSJOIN(
      {DESCENDANTS([Time].[1998], [Time].[Month])},
            {DESCENDANTS( [Warehouse].[All Warehouses].[USA], 
                  [Warehouse].[Warehouse Name] )})
  ON ROWS
FROM 
              [WAREHOUSE]

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

The Results pane is populated by Analysis Services, and the dataset partially shown in Illustration 7 appears.


Illustration 7: Result Dataset - Quantities for All U.S. Warehouses, by Month in 1998

4.  Select File -> Save As, name the file MDX032-2-1, and place it in a meaningful location.

5.  Leave the query open for the next section.

At this stage, we can easily discern the largest number for each month within the measure column, which identifies at a glance, of course, the top shipping Warehouse for each month. Next, we will add the MDX required to generate the name of the top shipper for each month, initially placing the name next to the column displaying shipping volumes for each Warehouse, for easy verification of the accuracy of the name produced.

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


-- MDX032-2-2, Addition of Calculated Member to Produce Name of Warehouse with 
--   Highest 1998 Volume Monthly Shipping

7.  Save the query as MDX032-2-2, to keep MDX032-2-1 intact as a working sample.

8.  Add the following lines to the query, between the top comment line we just replaced, and the SELECT keyword that begins the query:


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'

This will create the calculated member Largest Shipper, which will allow us to present a string containing the name of the Warehouse shipping the largest volumes for each month, alongside the member Warehouses' respective volumes.



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


















Thanks for your registration, follow us on our social networks to keep up-to-date