Category: MDX

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

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.

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

Figure 1

image

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

Figure 2

image

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

.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]

.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 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]

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

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

User Hierarchy Member

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

Example from Borg cube:

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

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

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

 

Bring on the MDX

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

Figure 3

image

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]

.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:

image

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]

.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:

image

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]

.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:

image

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]

.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 2:

image

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.

Intro To MDX Decathlon–Cube Space: The Final Frontier (1/10)

Since this series is an introduction to MDX (Multidimensional Expressions), let’s start off with a few definitions. The following terms form the foundation of our discussion of MDX and Cube Space.

Measure: A value used to asses a business process or event. Measures are typically numeric and often map to fields in your fact table. These values are frequently aggregated in your queries. Examples are Sales Amount and Order Count.

Measure Group: A container into which related Measures are organized. These typically map to fact tables. Examples are Internet Sales or Reseller Sales.

Dimension: A collection of information used to categorize Measure values. These form the BY or the FOR EACH of the questions your queries are meant to answer. An example would be a Publisher dimension. Dimensions usually map to a Dim table.

Attribute: A row or field in a Dimension. Attributes are organized into hierarchies. Examples include a Publisher Name in a Publisher dimension.

Member: A distinct value within an attribute’s domain. An example would be the [Random House] in a Publisher Name attribute.

Hierarchy: A structure in which dimension attributes/members are organized at different levels relating to each other. An example would be a Date hierarchy in which Dates roll up to Months, Months to Quarters, and Quarters to Years.

Attribute Hierarchy: A hierarchy composed of a single attribute. Figure 1 shows an example.

Figure 1

image

User Hierarchy: A hierarchy composed of more than one attribute. Figure 2 shows an example.

Figure 2

image

Cube Space

The Cube Space is a very different place from your relational database environment. It requires a different mindset to navigate. Rather than Boolean logic of whether field values meet the criteria within various filters, the Cube Space is all about intersections of dimensions in the N-Dimensional space. The “N” in this term just stands in for the number of dimensions you have, including the Measures dimension itself. I find keeping this in mind to be hugely helpful when I am dealing with MDX queries and scripts.

Figure 3 shows a one-dimensional space; essentially, a number line. This is Dimension A.

Figure 3

image

If we want to refer to a particular location within Dimension A, we can just use the name of that location. For example, in Figure 3, location (A3) is highlighted.

We can add another dimension to our space, creating the 2-dimensional space shown in Figure 4.

Figure 4

image

In this space, we can reference a particular location using the coordinates (A3,B5). This names a particular intersection of Dimension A and Dimension B.

Figure 5 shows the effect of adding a third dimension, Dimension C.

Figure 5

image

In this space, we reference locations using a 3-part coordinate, (A3,B5,C7). So far so good.

I have seen explanations of the cube space using the familiar X,Y and Z dimensions you may recall from Geometry class. You may recall that the X Axis is the Horizontal Axis. Y is the Vertical. Z then comes into the picture referring to Depth, adding that third dimension. All 3 are necessary for picturing a physical Cube. Going down this path can help you picture an Analysis Services cube with 3 dimensions as a physical object you can hold in your hand. The problem I have with this model is that SSAS Cubes will typically have more than 3 dimensions. Now, I challenge you to picture a physical object that has 4 dimensions that you can hold in your hand. How about 5? You can see pretty quickly that this model of picturing a physical object breaks down REALLY quickly. Thus, to me, it is not a good way to go.

I prefer to leave physics out of the whole thing. I don’t picture an object I can hold or than can exist within the bounds of Physics. Rather, I picture an SSAS Cube as just a collection of intersecting number lines, like in the figures I have shown so far, with each number line being a Dimension. By using this more linear style way of thinking, picturing the cube space with more than 3 dimensions is easy. Figure 6 demonstrates this pretty well.

Figure 6

image

You can see in Figure 6 that adding the fourth dimension, Dimension D, is no big deal at all. It works just fine. Figure 7 takes it even further.

Figure 7

image

Here we have six dimensions and it still works.

So, in this first post of my MDX Decathlon series, my main point is that by picturing the Cube Space as just a collection of intersecting number lines, rather than the physical cube that is often suggested, it helps keep things from getting too complicated too quickly. It was when I figured this out that MDX started to click for me. I got it. Believe me, I still have a lot to learn about MDX, but this foundation is really important.

Intro To MDX Decathlon–Introduction

For quite some time now, I have had an idea in the back of my head right next to some useless trivia about Sherlock Holmes. A blog series with ten parts, called a Decathlon, would be really cool. Particularly with the Olympics Games in London having just started, now is a great time to get this going. I have ideas for more Decathlons, but I figured I would start with one on MDX.

I have taken content from my MDX Trek: First Contact presentation and broken it up into 10 easily digestible chunks. The source data for this can be found here. The list below will serve as the map to each post. I will update it as each new post is released.

1. Intro To MDX Decathlon – Cube Space: The Final Frontier (1/10)
2. Intro To MDX Decathlon – The Basic MDX Query (2/10)
3. Intro To MDX Decathlon – The Trouble With Tuples (3/10)
4. Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10)
5. Intro To MDX Decathlon – .members Function (5/10)
6. Intro To MDX Decathlon – Hierarchy Navigation Basics (6/10)
7. Intro To MDX Decathlon – NON EMPTY and CROSSJOIN (7/10)
8. Intro To MDX Decathlon – LEAD() and LAG() Functions (8/10)
9. Intro To MDX Decathlon – PARALLELPERIOD() Function (9/10)
10. Intro To MDX Decathlon – PREIODSTODATE() Function (10/10)

Upcoming Presentation: PASS DW/BI Virtual Chapter May 9th

I am elated to announce that I will be delivering my MDX Trek: First Contact presentation on May 9th (at 4PM Central Time) to the PASS Data Warehouse and Business Intelligence Virtual Chapter. I have presented to local groups and at SQL Saturdays before (with a few more coming in the next two months) but never for such a geographically diverse audience. I am really excited for this opportunity and hope it will be a great step in establishing myself as a quality presenter in the SQL community.

Using Bogus Measures in SSRS Parameter Available Values Lists

I am currently working on a project involving many reports against an Analysis Services 2008 R2 cube that we implemented for the client. The available values lists for the report parameters are largely just lists of members of various dimension attribute hierarchies from the cube. Since we are not going to actually use any measures in our available values lists, we decided it was best if we didn’t even hit any measure groups at all. Why waste resources retrieving facts we don’t even want, right?

Let’s take our example from the Adventure Work DW 2008 R2 SSAS database, which contains the Adventure Works cube.

Suppose we have a report parameter that allows the user to select from the list of Product Categories.

We could start by just putting the Product Categories on the COLUMNS axis, thereby specifying only the items we actually want. Since all queries must include the COLUMNS axis, we have to start there.

-- Query 1
SELECT [Product].[Product Categories].children ON COLUMNS 
FROM [Adventure Works]

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

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

Query 1 produces the following results:

image

With this query, we are hitting a measure group even though we didn’t specify one, in this case the Reseller Sales measure group, to retrieve the default measure, Reseller Sales Amount. Not only that, but we have the Product Categories pivoted and would have to un-pivot them to use them in an available values list. That’s not the best way, for sure.

We could just go ahead and resign ourselves to retrieving a measure and specify the Reseller Sales Amount measure since we’re going to get it anyway.

-- Query 2
SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Product].[Product Categories].children ON ROWS
FROM [Adventure Works]

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

Query 2 produces the following results:

image

This is less than spectacular as well. While we actually have our Product Categories on the Rows where we want them, we are still hitting the measure group for no reason. We have already seen that we will get the default measure even if we don’t specify one. So, let’s specify one that isn’t in a measure group at all.

In the following query, we declare a new measure called Bogus that is just a constant, “1” and nothing else.

-- Query 3
WITH MEMBER [Measures].[Bogus] AS "1"

SELECT [Measures].[Bogus] ON COLUMNS,
[Product].[Product Categories].children ON ROWS
FROM [Adventure Works]

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

Query 3 produces the following results:

image

This query still puts the Product Categories on the rows where we want them. Hazzah. And, since we specified a Measure, even a Bogus one, we didn’t need to hit any measure groups, not even for the default measure. Double Hazzah.

So, when constructing available values lists, follow the lead of modern political discourse in the United States: Don’t muddy things up with facts. Oh snap!