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 3

By William Pearson

As we might have expected, this simple approach hardly achieves our ultimate objectives: it returns the top three Warehouses from the perspective of Warehouse Profit for 1998 - but the top three from the entire FoodMart organization. We will next advance beyond our previous basic queries by employing another MDX function, Descendants(), to supply the <<Set>> argument for the TopCount() function, but have a bit more to do to reach our goal of presenting the top performer Warehouses by U.S. Warehouse State.

NOTE: For information surrounding the Descendants() function, see my article MDX Member Functions: The "Family" Functions, a member of the MDX Essentials series at Database Journal. For an introduction to the .CurrentMember function, see MDX Member Functions: "Relative" Member Functions, an article in the same series.

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

8.  Leave the query open for the next section.

Next, we need to add logic to generate the same "top three" data, but for an ancestor of Warehouse (a level in the Warehouse hierarchy), the State it inhabits. To do so, we will introduce more MDX functions, as we see in the next step.

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


-- MDX032-1-2, TopCount(), combined with Ancestor() and .Name  
--   in a calculated member, to provide Warehouse State with each of "Top 3 
--        Most Profitable Warehouses in FoodMart Organization in 1998"

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

11.  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].[State]
AS 
     'ANCESTOR([Warehouse].CURRENTMEMBER, 
          [Warehouse].[State Province]).NAME'

This will create the calculated member State, which will allow us to present the States alongside their respective member Warehouses.

NOTE: For an introduction to the Ancestor() function, see my article MDX Member Functions: The "Family" Functions, a member of the MDX Essentials series at Database Journal.

12.  Modify the ON COLUMNS line of the query, which already contains the Warehouse Profit measure, to contain the new State calculated member we defined above, as follows:

{[Measures].[State], [Measures].[Warehouse Profit]}  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 Calculated Member

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: Results Dataset - With Our Modifications

We note that the State to which each of the "top three" warehouses (still the top three for the entire FoodMart organization) belongs appears in the results. Our calculated member has taken us a step closer to realization of the information consumers' requirements.

15.  Re-save the file as MDX032-1-2.

16.  Leave the query open for the next step.

At this point, all that remains is to generate the "top three" data for each of the Warehouse States, limiting the States to the three American States within which FoodMart operates Warehouses. The word "generate" is particularly apt here: we will use the Generate() function in our efforts to return the "top three" from each of the three U.S. States.

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


-- MDX032-1-3, Addition of Generate() to retrieve 
--   "3 Most Profitable Warehouses in Each U. S. State in 1998"

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

19.  Substitute the following for the entire existing ON ROWS line within the SELECT statement of the query:


{GENERATE([Warehouse].[USA].CHILDREN, 
    TOPCOUNT(
         DESCENDANTS(
              [Warehouse].CURRENTMEMBER, [Warehouse].[Warehouse Name]
                    ),3,[Measures].[Warehouse Profit] ) )} ON ROWS

(In actuality, we are only adding the Generate() statement to the "front end" of the ON ROWS specification, as it already existed from earlier steps, with a closing right parenthesis - ")" - just before the ON ROWS keyword).

The Generate() statement builds a set based upon the top performer Warehouses, as retrieved by the TopCount() function (with Warehouse Profit as the <<Numeric Expression>> argument, and the Descendants() function, once again, specifying the <<Set>> argument as the individual Warehouses), limited to U.S. States (the "children" of the USA level of the Warehouse dimensional hierarchy.

NOTE: For information surrounding the Generate() function, see my MDX Essentials article Set and String Functions: The GENERATE() Function, (where we perform a combination of Generate() and TopCount(), as well). For an introduction to the .Children function, see MDX Member Functions: The "Family" Functions, within the same series.

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

The Query pane appears as depicted in Illustration 4, with our addition marked.


Illustration 4: The Query with Substituted ON ROWS Line

21.  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: Results Dataset - After Final Modifications

Finally, we see the presentation as requested by the information consumers: 1998 Warehouse Profit for the three Warehouses with the highest Warehouse Profit for each of the U. S. States within which FoodMart conducts Warehouse operations. (We note that the State of Oregon shows only two Warehouses, but can easily verify that this is because only two Warehouse locations exist within the State, by expanding the Warehouse dimension at the OR level in the tree pane below the Cube selector in the MDX Sample Application, as depicted in Illustration 6.


Illustration 6: Verifying that Only Two Warehouses Exist in Oregon ...

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



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