Practice
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.
The illustration above
depicts the name of my server, MOTHER1, and properly indicates that we
will be connecting via the MSOLAP provider (the default).
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
WITH
Set [Income Statement L2]
AS
'{[Account].[All Account].[Net Income].[Net Sales],
[Account].[All Account].[Net Income].[Total Expense].Children,
[Account].[All Account].[Net Income]}'
SELECT
{[Measures].[Amount]} ON COLUMNS,
{[Income Statement L2]} ON ROWS
FROM
[Budget]
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.
» 
Comment and Contribute
