You can use the graphical environment in SQL Server Management
Studio 2005 (SSMS) to build MDX queries against cubes. This
example shows how to build the following query:
Select [Ship Date].[Calendar Year].MEMBERS on
Columns,
[Product].[Dim Product].CHILDREN on Rows
From [AdventureWorksDW]
Where [Measures].[Sales Amount]
After you’ve set up a cube in Microsoft Visual Studio 2005,
deploy it to your local SQL Server database. When the cube is on
your local database, you
can build a query against
it. In SSMS 2005, go
to the upper-left side of
the toolbar and click the
Analysis Services MDX
Query button (the third
button from the left). A
prompt asks you to define
your database connection.
After you’ve referenced
your local server, the query
pane opens, as shown in
Figure A.
Next, look for the Template
Explorer pane to the
right of the query pane.
If it isn’t visible, click
View, Template Explorer to
display it. The default view
in Template Explorer is a series of templates for creating T-SQL
commands. However, by clicking the Analysis Services MDX Query
button, you’ve switched this view to display Analysis Services Templates,
as Figure A shows.
In the Template Explorer, click MDX template, Queries. As you
can see in Figure A, the first item in the Query list is the Basic
Query. Drag the Basic Query from the Template Explorer into the
query pane to display the Basic MDX Query definition. Update this
definition by using the metadata for your sample cube; this is displayed
in the Metadata tab to the left of the query pane. First you’ll
create columns and then you’ll create rows.
Use the metadata in the list to define the columns based on the
years dimension. Click Ship Date, and then Ship Date. Calendar
Year. To add this definition to the Basic MDX Query, click the location
in the template place holder where you want it to go and then
drag the Ship Date. Calendar Year attribute to that location in the
query. In Figure A you can see the text [Ship Date]. [Calendar Year]
at the top of the query. While this code would build, it probably
wouldn’t produce the specific results you want. For more precise
results, you need to further define what you want from this attribute.
For this example, I’ll introduce two keywords, the first of which is
MEMBERS. The MEMBERS designation tells the query engine that
you want a column for the top summation for this attribute as well as
for each of the items that make up this attribute. In this case, you’ll
get a summation column for all of the years and a column for each
year. After [Ship Date]. [Calendar Year], append .MEMBERS prior to
the on Columns text in the query as shown in Figure A.
Next, follow the same steps to create the rows. I’ve entered the
Product dimension into the query and used the Dim Product attribute.
I’ve also specified that I want only the children of the Rows
attribute. The only difference
between the CHILDREN
and MEMBERS
keywords is the summation.
Because I’m
looking only at the child
attributes that make
up this dimension, the
query engine won’t
return a row representing
the top-level query.
The remaining portions
of the query are
straightforward. The
From clause names
the location the data
is retrieved from; in an
MDX query it’s usually
the name of the cube. In
this case, you’ll populate
the From clause by dragging AdventureWorksDW from the Cube
Metadata pane and dropping it into the query pane next to the
From clause. Next, you’ll populate the Where clause, which defines
the data that will be applied. The Where clause needs a data element
that’s measured by dimensions. In the Cube Metatdata pane,
the only measure defined for this sample cube is Sales Amount, so
you’ll drag and drop Sales Amount to populate the Where clause.
Here’s the final query:
Select [Ship Date].[Calendar Year].MEMBERS on
Columns,
[Product].[Dim Product].CHILDREN on Rows
From [AdventureWorksDW]
Where [Measures].[Sales Amount]
Click the Execute button on the SSMS toolbar to test this query.
The results are displayed in the bottom half of the Query pane, as
Figure A shows.
End of Article
