Intro To MDX Decathlon–The Basic MDX Query (2/10)

By Mark on August 6, 2012 • ( 2 Comments )

In the previous post in this series, Intro to MDX Decathlon – Cube Space: The Final Frontier (1/10), we learned the terminology we use when talking about cubes and cube space. We also gave a little primer on a helpful way to imagine the cube space while working with MDX.

In this post, we will start looking at the basic structure and syntax of MDX queries. But first, let’s set the stage by showing how to get to the MDX Query Designer in SQL Server Management Studio (SSMS).

Note: Since the source code formatting plug-in I use with Live Writer does not have a specific setting for MDX (there is one that does, but it has other behavior I really don’t like), you will notice that it will seem to make strange choices in what gets highlighted in different colors. Essentially, it will put T-SQL keywords in blue, even if they are in the middle of an identifier. Just be aware that the highlighting of keywords in SSMS MDX query editor will differ from my source code snippets for MDX.

Opening The MDX Query Editor in SQL Server Management Studio

The sample queries in this series will use the cube I created for my MDX Trek: First Contact presentation. You can find a link to it in the Introduction post for this series, Intro To MDX Decathlon – Introduction.

To open the MDX query editor in SSMS, just follow these steps.

Open SSMS.
Connect to your Analysis Services instance.
Right-click on the SSAS database containing the cube you want to query.
Choose New Query.
Choose MDX, as shown in Figure 1.

Figure 1

This will take you to the MDX query designer, which should resemble Figure 2.

Figure 2

The first pane on the left, marked with the 1 in Figure 2, is the Object Explorer for the SSAS instance. You can see that my StarTrekDW SSAS database has been expanded to see the cubes it contains. It has just the one, called Borg. What else could I call my cube in a Star Trek themed presentation? The Borg cube has one Measure Group, Episode, and three Dimensions: Date, Season, Series.

The second pane, marked with the 2 (clever, don’t you think?), shows the metadata of my cube. You can see once again the the Episode Measure Group, this time along with its single Measure, Episode Count. You can also see my Dimensions again.

The third pane, brilliantly marked with a 3, is the query window where you write your MDX.

You will notice a lot of brackets in MDX you see out in the wild. Essentially, the same rules apply to MDX that apply to T-SQL regarding when brackets are required. A key here is the use of spaces in object names. I, for one, can’t stand having spaces in the names of objects in the core database engine. I don’t ever create objects with spaces in their names. But cubes are designed for users, not DBAs and developers. As such, objects in cubes will very often have spaces in their names as it makes them easier to read. It is common practice, therefore, to use brackets as a matter of course so that you don’t have to worry about it.

There are three different styles to use when referencing dimension members in your MDX. Sometimes the situation dictates which of the options are available.

Attribute Hierarchy Member (Most Complete)

[Dimension].[Attribute Hierarchy Name].[Member]

Example from Borg cube:

[Series].[Series Name].[Star Trek: The Original Series]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Attribute Hierarchy Member (by UniqueName)

[Dimension].[Attribute Hierarchy Name].&[Member UniqueName]

Note that the UniqueName of a member corresponds to it ID. For example, in DimSeries in my data warehouse, the Star Trek: The Original Series row has an ID value of 1. In the cube, that translates into the UniqueName of the member in the Series dimension. So, the following example will produce the same result as the example above.

Example from Borg cube:

[Series].[Series Name].&[1]

Attribute Hierarchy Member in Dimension With Only One Attribute Hierarchy

[Dimension].[Member]

If there is only one Attribute Hierarchy in a Dimension, it is not necessary to specify the Attribute Hierarchy.

Example from Borg cube:

[Series].[Star Trek: The Original Series]

User Hierarchy Member

[Dimension].[User Hierarchy Name].[Hierarchy Level].[Member]

Example from Borg cube:

[Date].[Date Hierarchy].[Calendar Year].[1966]

Bring on the MDX

It’s time to dive into MDX query syntax with Figure 3.

Figure 3

You will notice in Figure 3 that the basic MDX query has some similarities to the basic T-SQL query. You see it starts with a SELECT clause, includes a FROM clause and also has an optional WHERE clause. You my be tempted to start thinking about MDX as kind of a dialect of T-SQL. I implore you not to do that. It will only lead to pain and frustration. The behavior of MDX, while sharing some keywords with T-SQL, is a very different animal.

MDX queries will feature a SELECT clause that will determine what cube cells will be returned. Within the SELECT clause, there are query axes. I show two query axes in Figure 3, Columns and Rows. The vast majority of MDX queries you will encounter in the wild will have these two axes and no more. In fact, SQL Server management studio supports only these two query axes. Wait. More axes? Yes. MDX supports up to… wait for it… <pause for effect> 256 query axes. Two hundred fifty-six. Yup. I can’t even fathom such a thing. Believe me, from my perspective, two axes are plenty for now.

The 256 query axes are numbered from 0 to 255. The first five axes also each have an alias you can use in place of the number. For this intro, we’ll just stick with the first two axes. Axis 0 can be referenced using the alias Columns. Axis 1 can be referenced using the alias Rows. Just for clarity, my samples will use the aliases Columns and Rows rather than 0 and 1.

Columns Axis

The first axis we will look at is the Columns axis. That is noted in Figure 3 as the <<expression>> ON COLUMNS. That tells the MDX engine to put the information specified in that expression across the top of our result set, rather like the columns one might see in an Excel spreadsheet. If your query has only one axis, that axis will be Columns. You cannot use any other axis until you have specified the Columns axis.

SELECT

      [Measures].[Episode Count] ON COLUMNS

FROM [Borg]

Result:

Since we only specified the Columns axis, we don’t have anything in the rows to provide context for the Episode Count value returned. What we end up with is the total Episode Count across the entire cube. I’ll explain in the next post in this series how that is working behind the scenes.

Rows Axis

The Rows axis is specified using <<expression>> ON ROWS, as shown in Figure 3. This corresponds to the rows of your result set, just like the rows one might see in an Excel spreadsheet. This is the second possible axis. If you will have two axes in your query, you will have Columns and Rows. You cannot specify anything for Rows until you have specified Columns.

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , [Series].[Series Name].[Star Trek: The Original Series] ON ROWS

FROM [Borg]

Result:

In this result, we place the [Star Trek: The Original Series] member on the rows, resulting the cell that contains Episode Count for just that series.

WHERE Clause (Slicer Axis)

The WHERE clause is specified in Figure 3 using the [ ] notation common to SQL Server documentation, marking it as optional. Your MDX query need not have a WHERE clause at all.

The WHERE clause in MDX is often referred to as the Slicer Axis. Since other key parts of the MDX query are axes, I suppose it only makes sense to a lot of people to refer to this as one. What is important to remember here is that the WHERE clause is not a true axis like Columns and Rows are. An axis in MDX has a direct impact on the structure of the result set that is returned. The Slicer does not. Rather, it affects the contents of the cells that are returned.

Before we implement the Slicer, let’s start by returning more rows so that the demonstration is clearer.

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , [Series].[Series Name].members ON ROWS

FROM [Borg]

Result 1:

In this result, we specified that we wanted every member of the [Series Name] attribute hierarchy on the rows by using the .members function (which will be explained in a later post in this series). Notice that we have a total of 6 rows and 1 column. Now let’s add the Slicer.

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , [Series].[Series Name].members ON ROWS

FROM [Borg]

WHERE [Date].[Date Hierarchy].[Calendar Year].[1966]

Result 2:

Notice now that we still have 6 rows and 1 column. The structure is exactly the same, just the contents of the cells are different. This is very different from the WHERE clause in T-SQL that can have a direct effect on the rows returned in the result.

Wrapping Up

In this post, we showed how to get into the MDX query editor in SSMS and went over the basic syntax and structure of MDX queries. In the next post, we’ll dig into a very important concept for working with MDX: the tuple.

Categories: Decathlon, MDX

Tagged as: Decathlon, MDX

Mark Vaillancourt

I drink coffee and know things

Categories

Archives

Intro To MDX Decathlon–The Basic MDX Query (2/10)

Opening The MDX Query Editor in SQL Server Management Studio

Attribute Hierarchy Member (Most Complete)

Attribute Hierarchy Member (by UniqueName)

Attribute Hierarchy Member in Dimension With Only One Attribute Hierarchy

User Hierarchy Member

Bring on the MDX

Columns Axis

Rows Axis

WHERE Clause (Slicer Axis)

Wrapping Up

2 replies »

Categories

Archives

Intro To MDX Decathlon–The Basic MDX Query (2/10)

Opening The MDX Query Editor in SQL Server Management Studio

Attribute Hierarchy Member (Most Complete)

Attribute Hierarchy Member (by UniqueName)

Attribute Hierarchy Member in Dimension With Only One Attribute Hierarchy

User Hierarchy Member

Bring on the MDX

Columns Axis

Rows Axis

WHERE Clause (Slicer Axis)

Wrapping Up

Share this:

Like this:

Related

2 replies »