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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 22, 2004

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

By William Pearson


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.

MS SQL Archives

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