Archive for year: 2006

Here is an interesting SSRS-SSAS integration gotcha that got me recently. Suppose that you have the following MDX query.

Needless to say, this query is most likely wrong to start with. It brings the members of three dimensions even if they don’t exist with each other. The correct version should probably be to cross-join the attribute hierarchies which is what the MDX Query Designer does behind the scenes when you place attribute hierarchies side by side. As you could see, as it stands, this MDX query brings 12 rows. However, much to your surprise, when you bind this dataset to a table region, you will see only three rows (the ones that don’t have (null)). So, what’s going on?

It turns out that the rows with (null) are server aggregates. The (null) in this case in this case indicates that this is a subtotal row. Since such a row is not a detail row, the SSRS Analysis Services data extension drops the row when you bind the dataset. If you add groupings on the table (group1: AgeGroup, group2: Year, group3: Month), and use the Aggregate() function accordingly in the group headers/footers, the table will show the appropriate aggregated measure values from the AS flattened rowset in the correct scopes.

What if you want to show all 12 dataset rows as detail rows on your report? The only way to do this is to switch the data source from “Analysis Services” to “OleDB” and select the MSOLAP.3 (OleDb for OLAP 9.0) or MSOLAP.2 (OleDB for OLAP 8.0) provider. However, once you switch to the OleDB provider, new features such as the graphical MDX query designer, query parameters for MDX queries, etc. are no longer available. You have to use the text-based generic query designer. You could design the query with the graphical designer as usual and then copy & paste the non-parameterized MDX query string to use it with the old OleDB provider.

One cautionary note: As I noted, most likely the original MDX query is wrong. While changing the data extension will give you all the rows, make sure that this is what you want because the report now will show duplicate rows that don’t exists within the cube space. This will probably skew your report results and confuse the end users.

The SSRS team is considering extending the RDL schema to so that server aggregate rows (currently determined by if a data extension implements the IDataReaderExtension interface) can be explicitly interpreted as real server aggregates (behavior of the AS 2005 AdoMd data extension) or as detail rows (behavior of the AS 2000 OleDB provider).

Microsoft has just published an excerpt from my book “Applied Microsoft Analysis Services 2005” on TechNet. Learn how to integrate SSRS and SSAS to build smart reports using data mining and CLR stored procedures.

Recently, I’ve come across a question on the RS public newsgroup about how to use the new RS 2005 XML extension to report off relative nodes in an XML document. The attached sample demonstrates this scenario. For example, given this document:

<bookstore xmlns=”http://example.books.com“>
  <book genre=”autobiography” publicationdate=”1981″ ISBN=”1-861003-11-0″>
    <title>The Autobiography of Benjamin Franklin</title>
    <author>
      <first-name>Benjamin</first-name>
      <last-name>Franklin</last-name>
    </author>
    <date>09-06-1956</date>
    <samplechapters>1 3 4</samplechapters>
    <price alternative=”discount”>5.99</price>
    <price>8.99</price>
  </book>
  <book genre=”autobiography” publicationdate=”1972″ ISBN=”0399105573″>
    <title>The Moon’s a Balloon</title>
    <author>
      <first-name>David</first-name>
      <last-name>Niven</last-name>
    </author>
    <date>09-06-1974</date>
    <samplechapters>4 5</samplechapters>
    <price alternative=”discount”>1.94</price>
    <price>2.57</price>
  </book>
</bookstore>

The following query fetches the book/title, book/author/first-name, and book/date elements, as well as book/@publication date.

bookstore/book {title, author{first-name}, @publicationdate, date}

In my opinion, it could have been nice if you were able to use XPATHs to bring the elements needed instead of this pseudo syntax that relies on curly brackets. Another potential area of improvement is supporting XML schemas to cast the column types automatically according to the defined schema types. As it stands, the XML extension returns all columns as of string date type. However, you can explicitly cast the column type to another type. For example, assuming that the publication attribute returns a valid date:

bookstore/book {title, author{first-name}, @publicationdate(date), date}

will cast the publicationdate column to a date data type.