dcsimg

MDX Essentials: Set and String Functions: The GENERATE() Function - Page 3

March 7, 2005

Practice

Preparation

To reinforce our understanding of the basics we have covered so far, we will use the GENERATE() function in a manner that illustrates its operation within a couple of examples. In both scenarios, we will create a dataset containing information that might be useful in a business scenario. In each case, we will outline a brief business requirement as we might receive it from a hypothetical group of information consumers. This will perhaps help to reinforce the concepts in our minds, where they can be triggered upon meeting a similar situation in our respective business environments.

We will call upon the MDX Sample Application again, as our tool to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Start the MDX Sample Application.

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

3.  Select the WAREHOUSE cube in the Cube drop-down list box.

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

Procedure - GENERATE(): Set Version

Let's assume, for our initial practice example, that we have received a request from a group of information consumers in the Finance department of the FoodMart organization. The consumers have asked for support in the presentation of some data, housed within the Warehouse sample cube, regarding Warehouse Sales for 1997. The consumers wish to know, for each U. S. State in which FoodMart operates Stores (California, Oregon and Washington), to which five Products we can attribute the greatest Warehouse Sales.

We immediately recognize the opportunity to employ the set version of the GENERATE() function, and compose a simple query to return the desired dataset.

5.  Type the following query into the Query pane:


-- MDX29-01:  Use of GENERATE() Function - SET VERSION
SELECT
    {[Measures].[Warehouse Sales]} ON COLUMNS,
    GENERATE([All Stores].[USA].Children, CROSSJOIN({[Store].CurrentMember}, 
        TOPCOUNT( [Product].[Product Name].Members, 5, [Store].CurrentMember) 
            )) ON ROWS
FROM 
    [Warehouse]
WHERE
    ([Time].[Year].[1997])

The reasoning behind the query is as follows: The set version of the GENERATE() function will evaluate the secondary set of the function (represented by TOPCOUNT( [Product].[Product Name].Members, 5, [Store].CurrentMember, or the top five Products with regard to 1997 Warehouse Sales) separately for each member of the primary set (represented by [All Stores].[USA].Children, the set of the U. S. States in which FoodMart operates). We can appreciate the fact that the independent evaluation of "top five" for each State is important, because the top selling products will likely vary between States.

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

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


Illustration 1: Results Dataset, GENERATE() Function, Set Version

We see there are a total of three Store States (California, Oregon and Washington), each of which appears crossjoined with its five top selling Products. In effect, GENERATE() has performed a union between each State and the independent results of its respective TOPCOUNT() evaluation.

7.  Select File -> Save As... from the main menu.

8.  Name the file MDX29-01, and place it in a meaningful location.

Now let's get some practice with the string version of the GENERATE() function.

Procedure - GENERATE(): String Version

Let's assume for our second practice example that we have received a request from a group of information consumers in the Marketing unit of the FoodMart organization. The group needs to meet a basic presentation requirement, with the data under consideration once again being housed within the Warehouse sample cube. The consumers wish to present a simple matrix dataset, and display total FoodMart Warehouse Sales for the children of the Drink and Food Product Families only (the children of the Product Family level of the Product dimension, we recall, are themselves Product Departments) .

The information consumers also inform us that they would like to make a "cosmetic addition" to the core presentation. They would like to see a column called "Macro Class" (their internal term for groups of Product Families) added to the layout, between the row axis containing the Product Family names, and the Warehouse Sales measure. Within this column, they would like text displayed to reinforce the fact that the Sales information in the dataset relates to "Drink or Food" families only, so that no confusion can erupt among the intended audience: The intent of the report is to present focused data about consumable products only. Finally, the information consumers want the data restricted to Warehouse Sales for 1997.

We recognize an opportunity to use the string version of the GENERATE() function, and compose a simple query to return the desired dataset.

9.  Select File -> New from the main menu.

10.  Type the following query into the Query pane:


-- MDX29-02:  Use of GENERATE() Function - STRING VERSION
WITH 
MEMBER
    [Measures].[Macro Class]
AS
    'GENERATE( {[Product].[Product Family].[Drink], 
      [Product].[Product Family].[Food]}, Product.CurrentMember.Name, " or ")'
SELECT
    { [Measures].[Macro Class], [Measures].[Warehouse Sales] } ON COLUMNS,

    { [Product].[Product Family].[Drink].Children, 
          [Product].[Product Family].[Food].Children} ON ROWS
FROM
    [WAREHOUSE]
WHERE
     ([Time].[Year].[1997])

The reasoning behind the query is as follows: The string version of the GENERATE() function will evaluate the specified <<String Expression>>, represented by Product.CurrentMember.Name in the above query, against the members of the primary set, {Product.[Product Family].[Drink], Product.[Product Family].[Food]}. We employ the optional delimiter of "or," to place it between the Drink and Food family names, which will evaluate into the string we intend to create to nest against the Product Families in the row axis, as we shall see.

11.  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 2 appears.


Illustration 2: Results Dataset using the String Version of GENERATE()

Our dataset displays the row axis as required, and the new string, with which the Product Family children are concatenated, reveals their membership in the "Drink or Food" group, in accordance with the request of the information consumers. Again, the "or" delimiter (optional) provides the separation of the two components in the concatenated string that is returned.

We thus meet the business needs of the Marketing information consumers through the use of the string version of the GENERATE() function.

12.  Select File -> Save As... from the main menu.

13.  Name the file MDX29-02, and place it in a meaningful location.

14.  Exit the MDX Sample Application when ready

Summary ...

In this lesson, we will introduced the GENERATE() function, which can be properly classed, depending upon the syntax we employ within it, as either a set or a string function. We focused primarily upon the set verision, as this is the more powerful use, and because it is more prevalent in the business environment, but we performed a practice example with each version to gain some hands-on exposure to the possibilities. We prepared for our practice sessions with a discussion of the purposes and operation of the function, focusing upon the syntax involved in reaching our objectives from within both set and string contexts.

In addition to discussing the purpose and operation of the GENERATE() function, we mentioned the elimination of duplicates by the function, as well as the option to bypass that process, if need be. We then practiced the uses of the function within a multi-step exercise, using both set and string versions to meet the expressed business needs of two groups of hypothetical information consumers. Throughout the steps of the practice exercise, we discussed the results we obtained with each step's execution, remarking on the distinguishing characteristics of each.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers