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 May 2, 2005

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

By William Pearson

Practice

To reinforce our understanding of the basics we have covered so far, we will first use the TopCount() function in a simple scenario to illustrate its operation as the primary focus. We will do so in a backdrop that places TopCount() within the context of meeting a business need for a group of hypothetical information consumers.

Let's return to the MDX Sample Application as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

Let's assume, for our practice example, that we have received a call from the Finance department of the FoodMart organization, requesting some information surrounding general profitability among the organization's Warehouse Cities. The Finance information consumers specifically wish to know the Warehouse Profit figures attributed to each of the top ten performing cities for operating year 1998.

To rephrase, the objective will be to present a single measure, Warehouse Profit, for 1998 within the context of the FoodMart Warehouse cube. (For our exercise, the cube can be assumed to represent the prior year's activity of the organization.) We wish to sort the Warehouse Cities by the total Warehouse Profit of each, and then to return data for the highest ten Warehouse Cities based upon the profit measure.

Let's construct a simple query, therefore, to return the top ten performers, as requested.

5.  Type the following query into the Query pane:


-- MDX031-1,Simple use of TopCount():  "Top Ten 1998 Profit Producers"
SELECT
  {[Measures].[Warehouse Profit]}  ON COLUMNS,
{ TOPCOUNT([Warehouse].[City].Members, 10, 
      ([Measures].[Warehouse Profit]))}  ON ROWS
FROM
   [WAREHOUSE]
WHERE
([Time].[Year].[1998])  

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

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


Illustration 1: Result Dataset - Simple Use of the TopCount() Function

In a manner similar to that in the Syntax section illustration above, the top ten Warehouse Cities are returned (this time specifically for operating year 1998). The operation of TopCount() has again been to sort by Warehouse Profit, and then to return the top ten values from that sort, as we requested in the function.

7.  Select File -> Save As, name the file MDX031-1, and place it in a meaningful location.

8.  Leave the query open for the next section.

Next, let's say that the Finance information consumers are provided with the simple "top ten" data we have generated. They state that they need to add the Warehouse Sales and a Margin % to the presentation, keeping the TopCount() based upon the Warehouse Profit measure, as before. This, they feel, will "round out" the presentation to provide more utility from an analysis perspective, presenting "more information at a single glance."

9.  Within the query we have saved as MDX031-1, replace the top comment line of the query with the following:

-- MDX031-2, "Top Ten 1998 Producers":  Add Sales and Margin Calc Member

10.  Save the query as MDX031-2, to keep MDX031-1 intact as a working sample.

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


WITH
MEMBER
    [Measures].[Margin %]
AS
   ' [Measures].[Warehouse Profit]/[Measures].[Warehouse Sales]'

This will create the calculated member Margin %, which we will present alongside the Warehouse Sales and Warehouse Profit measures in the following steps.

12.  Modify the ON COLUMNS line of the query to contain the Warehouse Sales measure, together with the new Margin % calculated measure defined above, as follows:


{[Measures].[Warehouse Sales], [Measures].[Warehouse Profit],   
     [Measures].[Margin %]}  ON COLUMNS,

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

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


Illustration 2: The Query with Added Measure and Calculated Measure

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

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


Illustration 3: Result Dataset - With Our Modifications

We note that, although the order of the "top ten" has not changed (the TopCount() function continues, after all, to be based upon the Warehouse Profit measure), the Margin % calculated measure is not sorted. (We will focus on this calculated measure as an "additional perspective" next.)

15.  Re-save the file as MDX031-2.



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