MDX in Analysis Services: Named Sets in MDX: An Introduction - Page 3

March 22, 2004


Static Named Set

To reinforce our understanding of the basics we have covered so far, we will use the WITH clause to create a static named set to meet an illustrative business need. Let's say that information consumers from the Accounting Department wish to be provided with a simple report, reflecting the balances in their Income Statement line items, from the Budget cube, for the year 1997.

Let's use the WITH statement to create a named set to handle this requirement. First, we will open the MDX Sample Application, the usual platform from which we perform our practice exercises.

1.  Start the MDX Sample Application.

We are initially greeted by the Connect dialog, shown in Illustration 3.

Click for larger image

Illustration 3: The Connect Dialog for the MDX Sample

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2.  Click OK.

The MDX Sample Application window appears.

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

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

5.  Select the Budget cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that depicted in Illustration 4, complete with the information from the Budget cube displaying in the Metadata tree (left section of the Metadata pane).

Illustration 4: The MDX Sample Application Window (Compressed View)

Let's add a query that includes the creation of a named set via the WITH clause.

6.  Type the following new query into the Query pane:

-- MXAS13-1  Static Named Set
    Set [Income Statement L2]

    '{[Account].[All Account].[Net Income].[Net Sales],
    [Account].[All Account].[Net Income].[Total Expense].Children,
    [Account].[All Account].[Net Income]}'
    {[Measures].[Amount]} ON COLUMNS,
    {[Income Statement L2]} ON ROWS
WHERE [Time].[1997]

7.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 5.

Illustration 5: Results Dataset - Static Named Set

8.  Save the query as MXAS13-1.

(Keep in mind that the "life" of the named set is only as long as the query in which it resides.)

We see that the constituent line items for the Income Statement appear, generating, in effect, a mini-"report" unto itself. Indeed, full reports can be written in this manner, and, if created in Analysis Manager, together with the underlying objects to support drilldown, could mean powerful capabilities indeed - at a fraction of the time and effort costs of setting it up in MDX on an ad hoc query basis. (While our purpose here is to assimilate concepts, we can never consider too much any enterprise-level opportunities to add value!)

Let's take a look at a dynamic named set at this juncture, within another practice example.