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:

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:

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:

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!