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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

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

Featured Database Articles


Posted Jul 12, 2004

MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function - Page 3

By William Pearson



To reinforce our understanding of the basics we have covered so far, we will use the Subset() function in a manner that illustrates its operation. We will do so in a simple scenario that places Subset() within the context of meeting a business need.

To begin, we will construct a SELECT query with a clearly defined set, then put Subset() to use in limiting that set to meet an illustrative need for a group of hypothetical information consumers. The intent is, of course, to demonstrate the operation of the Subset() function in a straightforward manner.

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 Sales cube in the Cube drop-down list box.

Let's assume, for our practice example, that we have received a call from the Marketing department of the FoodMart organization, requesting some information surrounding sales promotions that have been conducted. The Marketing information consumers specifically wish to know the Unit Sales figures attributed to each of the promotions, broken out by gender of the purchasers, from which to derive a recurring report that is more filtered.

To rephrase, the objective will be to present a single measure, Units Sales, for "all time" within the context of the FoodMart Sales cube. (For our exercise, the cube can be assumed to represent the current year-plus activity of the organization.) We wish to return data showing Unit Sales broken out by male and female purchasers, for each of the promotions that we have conducted within the time frame represented by the Sales cube. It is from the results dataset that is returned that the consumers want to narrow their request, once they get a look at overall figures, to a compact, recurring report.

Let's construct a simple query, therefore, to return the Unit Sales information, presented by gender (as columns) and the promotion name (as rows).

5.  Type the following query into the Query pane:

-- MDX021-1, Preparation for Use of Subset() Function in a Basic Query

{[Gender].Members} ON COLUMNS,
{[Promotions].[Promotion Name].Members} ON ROWS
WHERE ([Measures].[Unit Sales])

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 Preparation for Use of Subset() Function

We see Male, Female, and All Gender populating the columns across, and the Promotion Name (from the Promotions dimension) appearing on the row axis.

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

8.  Leave the query open for the next section.

Next, let's say that our information consumers are provided with the somewhat raw Promotion-by-Gender metrics we have generated. They state that they need the data in a slightly different presentation, before determining the thresholds for the ultimate recurring report.

The department has recently decided to emphasize its focus on the purchasing activities of female purchasers, while perusing the corresponding activities of male purchasers, in an attempt to identify patterns. More specifically, they want the same information that we have provided, but sorted by Unit Sales values, from highest sales promotion to lowest, from the perspective of female shoppers.

We can accomplish this re-sort using the Order() function that we explored in Basic Set Functions: The Order() Function, as we shall see in the following steps.

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

-- MDX021-2, Preparation for Use of Subset() Function -Ordered Query

10.  Save the query as MDX021-2, to prevent damaging MDX021-1.

11.  Change the following line of the query (the rows axis definition):

{[Promotions].[Promotion Name].Members} ON ROWS

to the following

{ORDER([Promotions].[Promotion Name].Members, ([Gender].[All Gender].[F], 
		   [Measures].[Unit Sales]), BDESC)} ON ROWS

12.  Remove the following line (the slicer at the bottom) from the MDX query:

WHERE ([Measures].[Unit Sales])

The Query pane appears as shown in Illustration 2.

Illustration 2: The Query with Ordering Enhancement

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