Month: August 2012

Intro To MDX Decathlon–.members Function (5/10)

By Mark on August 27, 2012 • ( 4 Comments )

In the previous post in this series, Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10), we defined a Set and also discussed the rules that valid sets must follow. In this post, we will introduce a function that can provide an easy way to form a set: .members.

The .members function returns a set of the members of the dimension, hierarchy, or hierarchy level provided. We will cover each of those three instances.

[<<Dimension>>].members

You can use the .members function directly on a dimension when that dimension has one and only hierarchy. When you do, you get a set that comprises all of the members of that hierarchy, in order according to the hierarchy’s configured sort order in the cube. Query 1 shows an example of calling the .members function directly on a dimension with only one hierarchy in my Borg cube. The [Series] dimension has only one hierarchy, [Series Name].

Query 1

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , [Series].members ON ROWS

FROM [Borg]

.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; }

Result 1

Notice that the result of the .members function on my [Series].[Series Name] hierarchy includes the [All] member. In a later post in this series, we will discuss a way to exclude the [All] member and just get the [Series Name] values themselves.

[<<Dimension>>].[<<Hierarchy>>].members

Attribute Hierarchy

The only difference between Query 2 below and Query 1 is that we specify the name of the hierarchy whose members we want in our set. Although leaving off the hierarchy name, as in Query 1, is technically allowed when the dimension has only one hierarchy, I prefer specifying the hierarchy name anyway, as in Query 2. To me, this makes my MDX more complete and easier for another person to follow.

Query 2

SELECT

      [Measures].[Episode Count] ON COLUMNS

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

FROM [Borg]

Result 2

Notice the Result 2 is identical to Result 1. They both return the exact same set of members. You notice, also, that they both dealt with the same Attribute Hierarchy. Since you have been basing your life around the release of each new post in this series, you will recall from the first post, Intro To MDX Decathlon – Cube Space: The Final Frontier (1/10), that an Attribute Hierarchy is one that contains only a single Attribute. The [Series].[Series Name] hierarchy contains only the [Series Name] attribute.

User Hierarchy

If you call the .members function on a User Hierarchy, which is composed of more than one Attribute, you still get all of the members of the hierarchy in order, but now you have more levels to deal with. Query 3 shows an example of calling the .members function on my [Date].[Date Hierarchy] user hierarchy from my Borg cube. You will note the Query 3 also has a WHERE clause as well as the NON EMPTY keyword (which we get to later in this series). I did this so that the results returned by the .members function would fit into a tighter space so that you can notice the pattern of the returned members.

Query 3

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , NON EMPTY [Date].[Date Hierarchy].members ON ROWS

FROM [Borg]

WHERE [Series].[Series Name].[Star Trek: The Next Generation]

Result 3

Just as a reminder, my [Date].[Date Hierarchy] user hierarchy has the following structure:

Level	Attribute Hierarchy
0	[All]
1	[Calendar Year]
2	[Calendar Quarter]
3	[Calendar Month]
4	[Full Date]

Result 3 still starts with the All member. The next member you see is [Calendar Year].[1987]. This is the FIRST member immediately below the All member that that meets the criteria. We then proceed through all of the members in 1987 before getting to 1988, which would make sense since my [Date Hierarchy] is in ascending Full Date order. Each path in the hierarchy is exhausted before going to the next path. So, every member in [Calendar Quarter].[1987–Q3] appears before anything in [Calendar Quarter].[1987–Q4]. Likewise, every member in [Calendar Month].[1987-09] appears before anything in [Calendar Month].[1987-10]. And so on. You can see that the results of using .members on a User Hierarchy will travel up and down the levels in the hierarchy in order to show every member of every level while still keeping everything in the sort order configured for this hierarchy in the cube. This behavior can be fantastic for some reporting scenarios. Just keep in mind that for very deep or populous hierarchies, this can return a lot of members. Use it wisely.

[<<Dimension>>].[<<Hierarchy>>].[<<Level>>].members

With User Hierarchies, sometimes you just want to work with the members of a particular level. When you use .members to do this, you just get the members of that level. Query 4 shows an example of doing this. The only difference between Query 4 below and Query 3 is that we are adding in the [Calendar Year] level before using the .members function.

Query 4

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , NON EMPTY [Date].[Date Hierarchy].[Calendar Year].members ON ROWS

FROM [Borg]

WHERE [Series].[Series Name].[Star Trek: The Next Generation]

Result 4

You will notice that the [All] members is not returned. This is because the [All] member is not at the [Calendar Year] level of my [Date Hierarchy]. It is the level above.

Making that one, tiny change to the query from Query 3 produced vastly different results in Query 4. That is something to keep in mind when dealing with hierarchies in MDX. Little things can have a big impact.

In this post, we looked at the behavior of the .members function in different scenarios. An important thing to remember is that .members outputs a Set. So, you can pass a .members expression to another function that takes a Set as input in order to nest functions. Doing so can allow you to implement some very sophisticated logic in your MDX queries.

PASS Regional Mentor–Canada

By Mark on August 22, 2012

I had mentioned in my most recent PD post, Professional Development Plans: Turning Your Job Into a Career, Part 2, that I wanted to jump back into SQL Community involvement and let PASS know of my desire to become a PASS Regional Mentor (RM). I was on the Executive Board for the Minnesota chapter of PASS, PASSMN, for three years and had a great time. After taking a few years off, I am ready and anxious to get back into the game. The RM program is a huge asset to both PASS local chapters and the PASS community as a whole. The roles of an RM are, as PASS Community Evangelist Karl Landrum (b|t) noted in her post on the PASS Blog, the following:

I heard from PASS Director Allen Kinsel (b|t) that PASS would like to have me on board as a Regional Mentor. Hazzah!!!! It is now official.

I will be helping Chris Shaw (b|t) with the mighty Canada region. I am really excited to get this opportunity to work with Chris and serve this community I care so much about.

To my Canadian friends:

1. Having grown up in New Hampshire before moving to Minnesota, I have never lived in a US State that did not border Canada.

2. Canada happens to be the only country outside the US to which I have traveled.

3. When I was growing up, my Dad would often watch Les Habitants on the French language channel.

4. I think Mike Myers is hysterical.

5. I studied French in school for 12 years. It was mostly written, though, so my conversational French is pretty poor. But I can tell you all about pencils, pens, dogs, cats, cows, pigs, coats, boys, girls, and colors. Oh, and skirts. I don’t wear them, myself, but I remember how to say it in French. Boats, too.

6. I remember watching the Winter Olympic Games in Calgary, thinking it looked pretty cool. I was only thirteen at the time.

7. My wife and I watched a whole big lot of the Winter Olympic Games in Vancouver and it was just amazing. When I told her that being a Regional Mentor for Canada was a possibility, she immediately told me, “If you end up going to Vancouver, I AM COMING. Period.” 🙂

8. Like The Kids in the Hall, I was in a TheatreSports improvised comedy troupe for three years.

Words can’t really explain how excited I am for this new role. I am constantly telling people about the vibrant, fantastic SQL Community. It’s going to be great to be an ambassador for PASS in a more formal capacity.

Intro To MDX Decathlon–Sets: A Shirt of a Different Color (4/10)

By Mark on August 20, 2012 • ( 5 Comments )

In the previous post in this series, Intro To MDX Decathlon – The Trouble With Tuples (3/10), we discussed how to reference a particular cell in a cube by using a Tuple. Now it’s time to group them together using a concept we haven’t discussed yet: the Set. Its basic definition is deceptively simple:

Set: A collection of tuples.

The basic syntax of a set is essentially a list of Tuples, separated by commas, surrounded by curly braces {}. You can see this in Figure 1.

Figure 1

As a concept, we can liken the concept of a Set to the uniforms within Starfleet in the Star Trek franchise. The members of Starfleet wore uniforms of a certain color depending on their role. In Figure 2, an excellent Lego depiction of the main characters of Star Trek: The Next Generation, you see Red, Blue, and Yellow uniforms.

Figure 2

Image URL: http://www.flickr.com/photos/dunechaser/163959411/sizes/z/in/photostream/

The characters are wearing shirts that are designed to group them together by their job function.

Essentially, the colors follow this pattern:

Red: Command

Yellow: Tactical, Security, Engineering (possibly considered Operations)

Blue: Science, Medical

Light Blue: Well… Wesley.

This grouping only works when there are properties shared amongst members of the group. Sets in the cube space also have to have some shared properties to be valid.

Prime Directives of Sets

Sets in MDX must follow the following rules to be valid.

Hierarchality: All Tuples in a set must reference the same hierarchies. This is essentially the Apples to Apples rule. Figure 3 shows an example of a valid Set. “Hierarchality” is also a great word to throw around at parties.

Figure 3

The Set in Figure 3 is valid because the members are both from [Hierarchy Y]. This Set is a good example of Hierarchality.

Figure 4 shows an example of an invalid Set.

Figure 4

The Set in Figure 4 is invalid because one member comes from [Hierarchy Y] and the other comes from [Hierarchy X]. These members do not have Hierarchality.

The code in Query 1, below, shows a set in my Borg cube.

Query 1

SELECT

      [Measures].[Episode Count] ON COLUMNS

, {

          [Series].[Series Name].[Star Trek: The Original Series] -- Tuple A

        , [Series].[Series Name].[Star Trek: The Next Generation] -- Tuple B

      } ON ROWS

FROM [Borg]

Result 1

Dimensionality: All Tuples in the Set must reference the same Dimensions in the same order. This rule comes into play when you are declaring multi-part Tuples as members of the set.

Figure 5 shows an example of a valid Set.

Figure 5

The Set in Figure 5 is valid because the first tuple references [Hierarchy Y] and then [Hierarchy Z]. Likewise, the second tuple references [Hierarchy Y] and then [Hierarchy Z]. These tuples have the same Dimensionality.

Figure 6 shows an example of an invalid Set.

Figure 6

This Set is not valid because the first Tuple references [Hierarchy Y] and then [Hierarchy Z] while the second Tuple reverses the order of the hierarchies, referencing [Hierarchy Z] and then [Hierarchy Y]. These tuples do not have the same Dimensionality.

Query 2 shows an example of a Set created from multi-part tuples in my Borg cube.

Query 2

SELECT

      [Measures].[Episode Count] ON COLUMNS

, {

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

            , [Season].[Season Name].[Season 01]) -- Tuple A

        , ([Series].[Series Name].[Star Trek: The Next Generation]

            , [Season].[Season Name].[Season 01]) -- Tuple B

      } ON ROWS

FROM [Borg]

Result 2

The Named Set

For readability and organization, you can also give your sets names. You declare a Named Set using the WITH keyword. Figure 7 shows the basic syntax.

Figure 7

Query 3, below, will return the same result as Query 1, but is a bit more organized by using a Named Set.

Query 3

WITH SET MyFavorites AS

      [Series].[Series Name].[Star Trek: The Original Series] -- Tuple A

    , [Series].[Series Name].[Star Trek: The Next Generation] -- Tuple B

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , MyFavorites ON ROWS

FROM [Borg]

Result 3

Result 3 is identical to Result 1.

The Range Operator “:”

The Range Operator is immensely helpful in creating Sets when dealing with ordered hierarchies, which should be just about all of them. The Range Operator takes two arguments, the Starting Member and the Ending Member.

[Starting Member]:[Ending Member]

It returns an ordered set of all member from the Starting Member to the Ending Member, inclusive of both. So, given a list of members [A],[B],[C],[D], the expression [A]:[D] would return the entire list of members.

Query 4 shows an example of a Named Set from my Borg cube made up of all of the members of the [Calendar Year] hierarchy in the 1980s decade.

Query 4

WITH SET TheEighties AS

      [Date].[Date Hierarchy].[Calendar Year].[1980]

      ,[Date].[Date Hierarchy].[Calendar Year].[1981]

      ,[Date].[Date Hierarchy].[Calendar Year].[1982]

      ,[Date].[Date Hierarchy].[Calendar Year].[1983]

      ,[Date].[Date Hierarchy].[Calendar Year].[1984]

      ,[Date].[Date Hierarchy].[Calendar Year].[1985]

      ,[Date].[Date Hierarchy].[Calendar Year].[1986]

      ,[Date].[Date Hierarchy].[Calendar Year].[1987]

      ,[Date].[Date Hierarchy].[Calendar Year].[1988]

      ,[Date].[Date Hierarchy].[Calendar Year].[1989]

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , TheEighties ON ROWS

FROM [Borg]

Result 4

Since the [Calendar Year] hierarchy is ordered by the Year values, you can also use the Range Operator instead of specifying all ten years individually. Query 5 shows the same query logic as Query 4, but uses the Range Operator, resulting in much cleaner code.

Query 5

WITH SET TheEighties AS

      [Date].[Date Hierarchy].[Calendar Year].[1980] -- Start

      :[Date].[Date Hierarchy].[Calendar Year].[1989] -- End

SELECT

      [Measures].[Episode Count] ON COLUMNS

    , TheEighties ON ROWS

FROM [Borg]

Result 5

Result 4 and Result 5 are identical, even though Query 5 is much easier to read and is much shorter.

In this post, we introduced the concept of the Set and explained the rules around their use. We also discussed how to create a Named Set to make your MDX a bit more organized. We closed by showing how the Range Operator can be of great use in creating sets from within ordered hierarchies.

Intro To MDX Decathlon–The Trouble With Tuples (3/10)

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

In this third installment of my Intro to MDX Decathlon, we cover the hugely important Tuple.

Before we get into the details, I want to make a quick note about pronunciation. There is some controversy over how to properly say the “tup” in Tuple. In one camp, there are the folks that say it must rhyme with “cup.” The other camp insists it must rhyme with “loop.” I poked around a bit online concerning this topic and found roughly equal numbers of people in each camp declaring with absolute certainty that the pronunciation they prefer is the right one. I, personally, rhyme with “cup,” but make no claim of correctness. It just feels right to me and was the first pronunciation I heard. I don’t think it really matters. In a world in which we have television shows that start with “The Real Housewives of” ANYTHING, we have much bigger problems than how to pronounce this word.

When it comes to Tuples, a key concept to cover first is that of a cube Cell.

Cell: The logical intersection of a member of the Measures dimension with a member of each attribute hierarchy in the cube

Now that we have defined a cell, let’s look at one from my first post in this series, Intro To MDX Decathlon–Cube Space: The Final Frontier (1/10). Figure 1 shows four dimensions, each named with a letter, A thru D.

Figure 1

The circle in the center, highlighted in blue and a bit larger than the others, can be considered a cell. It is the intersection of all of the dimensions in the figure. We can refer to this cell according to its coordinates; the member of each dimension present at that location. You can see this in Figure 2.

Figure 2

We can use the coordinates of this cell as its name. This is just how it works in the cube as well. We reference a particular cell in the cube by specifying the members that intersect at that location. The coordinate name for that location is a Tuple. That brings us to the definition below.

Tuple: The unique coordinates of a cell within the cube space

One key thing to keep in mind about a tuple is that it cannot include more than one member of the same hierarchy. That makes perfect sense. Think of a Calendar Year attribute hierarchy. Can there be any date that is simultaneously in both 2010 and 2011? (Hint: No.) In addition, you can only reference each dimension once in the tuple.

To specify a tuple in MDX, you separate dimension references with commas and surround the entire structure with parentheses. So, a tuple of the cell in Figure 2 would look like this:

       [A].[3]

     , [B].[5]

     , [C].[7]

     , [D].[4]

NOTE: The particular style of having the comma at the front of each line, the line breaks, and the indentation in this series is just my preference. I find it the most organized and readable. You need not follow my exact formatting. As long as you have the commas and parentheses in the proper location relative to the dimension references, you can format it how you like.

Now, let’s look at a tuple from my Borg cube.

Query 1

SELECT

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

        , [Season].[Season Name].[All]

        , [Date].[Date Hierarchy].[All]

        , [Measures].[Episode Count]

      ) ON COLUMNS

FROM [Borg]

Result 1

Notice in the result above that all of the labels are returned on the Columns axis since that is the only one specified in the query.

Since you have been following this series with such rapt joy, you will have noticed that the Result above, 80, has the same value as one from the previous post in this series, Intro To MDX Decathlon–The Basic MDX Query (2/10).

That previous post included this query.

Query 2

SELECT

      [Measures].[Episode Count] ON COLUMNS

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

FROM [Borg]

Result 2

You will notice that both queries return a cell with the value of 80. It is actually the same cell that is just reached using a slightly different syntax.

What we see in Query 2 is an example of what is called a Partial Tuple. A partial tuple is a feature of Analysis Services that allows you to specify the parts of the tuple you wish to while letting SSAS take care of the rest. Note that when you are using a Partial Tuple and specifying only one hierarchy, as in Query 2, you need not use parentheses to surround the Tuple.

When we just specify just [Series].[Series Name].[Star Trek: The Original Series] on the Rows axis in Query 2, SSAS follows the decision tree in Figure 3 to determine how to properly identify the cell.

Figure 3

When I chose not to specify either the Season or the Date dimension in the partial tuple on the Rows axis in Query 2, SSAS looked to see if either of those had a Default Member. My Season Name attribute hierarchy has a Default Member specified in my Borg cube: The [All] member. So that member is used. My Date Hierarchy does not have a Default Member. But it does an [All] member. So that member is used. If neither a Default Member nor an [All] Member are present, then the first member found (according to the hierarchy sort order defined in the cube) is used.

The benefit of Partial Tuples is that it allows for cleaner code and less typing in many cases. This allows the following two queries return the exact same cell.

Query 3 (Full Tuple)

SELECT

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

        , [Season].[Season Name].[All]

        , [Date].[Date Hierarchy].[All]

        , [Measures].[Episode Count]

      ) ON COLUMNS

FROM [Borg]

Result 3 (Full Tuple)

Query 4 (Partial Tuple)

SELECT

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

        , [Measures].[Episode Count]

      ) ON COLUMNS

FROM [Borg]

Result 4 (Partial Tuple)

In this post we introduced the concept of the Tuple, including defining a cube Cell. We also showed the power of Partial Tuples. In the next post, we really start having some fun with Sets.

Upcoming Presentation: MDX Trek: First Contact on August 21st

By Mark on August 9, 2012 • ( 1 Comment )

I will be giving my MDX Trek: First Contact presentation to the mighty Minnesota Chapter of PASS (PASSMN) on Tuesday, August 21st. You can come live and in person to the Microsoft office in Edina or attend via Live Meeting. Get more details of register here. Happy days.

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]

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.

Mark Vaillancourt

I drink coffee and know things

Month: August 2012

Intro To MDX Decathlon–.members Function (5/10)

[<<Dimension>>].members

[<<Dimension>>].[<<Hierarchy>>].members

[<<Dimension>>].[<<Hierarchy>>].[<<Level>>].members

PASS Regional Mentor–Canada

Intro To MDX Decathlon–Sets: A Shirt of a Different Color (4/10)

Prime Directives of Sets

The Named Set

The Range Operator “:”

Intro To MDX Decathlon–The Trouble With Tuples (3/10)

Upcoming Presentation: MDX Trek: First Contact on August 21st

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