Database Reference
In-Depth Information
Basic and Raw Syntax
The basic syntax of an MDX select statement starts like a standard SQL select statement, but soon the differences
are quite apparent. Listing 14-2 outlines the basic MDX select syntax.
Listing 14-2.
Basic MDX Select Syntax
Select
{ <a set of attributes> } On Columns,
{ <a set of attributes> } On Rows
From<cube name>
Where ( <member> );
The following are some key differences:
FROM
clause, MDX uses the cube name. This
makes sense, because the cube is equivalent to a set of one or more tables in a relational
database.
•
Instead of using the table name in the
•
Curly braces and semicolons are not commonly used in SQL programming but are used
often in MDX programming.
ON COLUMNS
and
ON ROWS
to indicate how the results should
be displayed within an Analysis Server client application. Analysis Server applications
can use 128 different axis positions to return results. Most applications, like SQL Server
Management Studio, are designed only to handle the first two axis (columns and rows).
In custom applications, you may see more axis used that indicate the next two positions
after columns and rows, such as pages and chapters.
•
MDX uses the keywords
Listing 14-3 shows an example of a raw MDX statement that queries the CubePubsSales cube.
Listing 14-3.
A Raw MDX Statement
Select
{ [Measures].[SalesQuantity] } On Columns, -- Axis 0
{ [DimTitles].[Title].[Is Anger the Enemy?] } On Rows -- Axis 1
From [CubePubsSales]
Where ( [DimStores].[Store].[Eric the Read Books] );
■
we recommend typing each code sample, but for your convenience we have included a script file in our
downloadable content called
Chapter14 All MDX Listings.mdx
that contains all of the MDX code used in this
chapter. we have also included a processed backup of the PubsBiCubes database for ssAs so that if you did not
complete your cube in previous chapters, you would be able to restore the database and cube, allowing you to run
the code in this chapter. The instructions on how to restore this type of database can be found in the readme file
along with the backup file. You will find both items within
C:\_BookFiles\Chapter14Files\
.
Note
Running Your MDX Code
To run your MDX code from SQL Server Management Studio, you must either create a new MDX script or open
an existing one. If you have opened an existing script with code in it, highlight the MDX statement to run and
click the “! Execute” button at the top of the toolbar. To make a new MDX script, click the New MDX Query button
indicated in Figure
14-3
.
