No DRILLTHROUGH queries in Reporting Services

June 30, 2006

I was needing to do an mdx DRILLTHROUGH report in Reporting Services and got this strange error: “Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)”.  Went looking and came across Nick Barclay’s blog entry that talks about how you can’t do mdx DRILLDOWN queries in RS and that Darren Gosbell had written a bug report about it.  Microsoft responded that they decided not to support it since you could easily perform the same query in 2005 w/o using a drilldown statement.

It took me a little while to work out all the syntax of the different queries and thought I’d share it here as well as some performance differences that I found.

First of all, here is the DRILLTHROUGH query that I wrote.

DRILLTHROUGH
SELECT NON EMPTY { [Measures].[Internet Sales Amount]} ON 0
FROM [Adventure Works]
WHERE ( [Date].[Calendar Year].&[2003])
RETURN
     [$Customer].[Customer]
     ,[$Date].[Calendar Year]
     ,[$Product].[Category]
     ,[$Product].[Model Name]
     ,[$Product].[Product]
     ,[$Product].[Product Line]
     ,[$Product].[Subcategory]
     ,[$Promotion].[Promotion]
     ,[$Sales Reason].[Sales Reason]
     ,[$Sales Territory].[Sales Territory Region]
     ,[$Sales Territory].[Sales Territory Group]
     ,[$Ship Date].[Date]
     ,[Internet Sales].[Internet Sales Amount]
     ,[Internet Sales].[Internet Order Quantity]

Here is the equivalent query written in a way that will work in Reporting Services 2005.

SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY {
     [Customer].[Customer].[Customer].ALLMEMBERS *
      [Date].[Calendar].[Date].&[550] : [Date].[Calendar].[Date].&[914] *
      [Product].[Category].[Category].ALLMEMBERS *
      [Product].[Model Name].[Model Name].ALLMEMBERS *
      [Product].[Product].[Product].ALLMEMBERS *
      [Product].[Product Line].[Product Line].ALLMEMBERS *
      [Product].[Subcategory].[Subcategory].ALLMEMBERS *
      [Promotion].[Promotion].[Promotion].ALLMEMBERS *
      [Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
      [Sales Territory].[Sales Territory Region].[Sales Territory Region].ALLMEMBERS *
      [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS *
      [Ship Date].[Date].[Date].ALLMEMBERS
} ON ROWS
FROM [Adventure Works]

I have found (very unscientifically) that on my server, there is a significant difference in performance.  The DRILLTHROUGH query ran in 14-16 seconds on average and the CROSSJOIN query ran in 23-25 seconds.  When the queries were smaller and included fewer dimensions and fewer rows because of the WHERE clause, they converged and there wasn’t a significant difference in performance – although on a small query I was finding that the CROSSJOIN query took 3 seconds and the DRILLTHROUGH took 1 second – a 200% difference.

Now I’m not an expert in how the query engine works, but it isn’t a surprise to me that the CROSSJOIN query would be slower than the DRILLTHROUGH query.  Maybe someone out there can detail why the DRILLTHROUGH is faster or if maybe there is another way to write the query to get the same results into RS without using a CROSSJOIN.

This seems to me that we have another instance of the integration of Reporting Services and Analysis Services getting shortshrifted again (read here).  Hopefully Microsoft will see this as an opportunity to improve the integration of OLAP into the Reporting Services product.


Query bound SSAS partitions

June 27, 2006

Been doing some thinking about the best way to design query bound partiton queries.  In the wizard in BI Studio, when you switch the partition to query bound instead of table bound (so you can put a WHERE clause as a row constraint) it lists out each column.  That means that if you need to add a new dimension to your star/cube, you will need to edit the query in each partition before you can load and process the cube.

Would it be better to do a select * in the query?  I’m not sure and I’m curious what anyone else thinks about it.

Leave a comment.

Mark


Role Playing Dimensions Materialized and Aggregated? Yes.

June 27, 2006

Ok. We have an official word and it is good news.  Role playing dimension are materialized and aggregated and there was just an error in the Project REAL docs. You can read about it in the comments to this post and also on Chris Webb’s blog.

I have to admit that I probably should have thought twice about this statement and had a little more doubt.  Not being able to aggregate on a role-playing dimension would be a big blow to the product.

Thanks,

Mark

Role playing dimensions are dimensions that are defined once and then used multiple times in a cube.  One example might be a date dimenion.  You create one date dimension on top of one relational table and add that to your dimensions folder in your project.  In your cube you have two roles for this dimension to play, sales date and ship date.  You add the dimension to your cube and for each one, you select the key that it will join to in the fact table.

There is one thing that should be noted when doing this.  If you create your dimensions this way, they are not materialized.  This means that no aggregations will be stored for that dimension.  Since the dimension plays two roles, we can’t store two sets of aggregates for it.  This does save space, but at a performance cost.

One way to materialze your dimensions is to create a named query in the data source view.  You can select * from your table and then rename the table and the key column so that it matches your fact table.  This will also create the primary key on the named query.  Then go through and re-create your dimensions but create one dimension for each new named query.

Mark


Creating a lot of partitions

June 27, 2006

I have a cube that has about 200 million rows and I decided to partition by month.  The data is spread out over about 10 years.  That means that per measure group, I have around 120 partitions to create.  In addition to that, I have 3 measure groups.  That means a total of around 360 partitions.

I could have created all of these parititons by hand, but that would have taken forever. Read the rest of this entry »


Table Difference

June 12, 2006

Saw something on Marco Russo's blog that I thought was really cool…

It is an SSIS component for doing table comparisions.

I have encountered this same problem and wrote an entire SSIS package to do the same thing.  Really nice to be able to do this in one step.

Have fun.


Dimension Attribute Keys

June 8, 2006

A collection of keys must be defined for each dimension attribute.  This key collection must uniquely define each member of that attribute.

If we take a date dimension for example, the key for the [Year] level can be defined as the [Year] column in the database.  Every member with the same year will be aggregated together.  The story is different once we get to the [Quarter] level.  We have many members named [Q1] and they become unique in conjunction with the [Year] member.  That means that the key for the [Quarter] level will be [Year] and then [Quarter].  The same goes for the [Month] level as well.  The key would be [Year] and then [Month].  You could use the month name or the month number – whichever you like.  At the day level, you have a couple of options.  First of all, you can use the identity ID of your dimension table, and this is more desirable when you are using a smart date key or you can choose to use [Year], [Month], and [Day] as the key.

Now every member of each attribute and consequently any hierarchies built using these attributes has a unique way to reference each member.

After you are finished, you should make sure that your attribute hierarchies are set up correctly or SSAS won’t design [can’t calculate] aggregates at any level other than the [All] level and the leaf level.  More on that here.


MDX Solutions

June 6, 2006

I have just gotten done with reading (most) of the book MDX Solutions by Spofford, et al – Second Ed.
 
What a great book.  If you have questions about how to get something done in MDX, the answer is probably there.
 
Good job guys.
 
Mark