Prologika Forums
Making sense of data
When (null) is not NULL

Blogs

Prologika (Teo Lachev's Weblog)

Books

Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling)Learn PowerPivot and Analysis Services-Tabular at your own pace with our latest book Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling). It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Training

Applied Microsoft SQL Server 2008 Reporting Services

We offer onsite and online Business Intelligence classes!  Contact us about in-person training for groups of five or more students.

For more information or to register click here! 

Syndication

Archives

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


Posted Wed, Feb 8 2006 8:11 AM by tlachev

Comments

furmangg wrote re: When (null) is not NULL
on Thu, Mar 2 2006 12:17 PM
Just in case you hadn't seen the response from Microsoft on this issue. Sounds like you may have already talked to them, though.

The MDX I posted in that example was (the AdventureWorks equivalent of) a real query from a real report... so my opinion is that doing this type of query is more common than it may first appear. I've used that trick several times before in SSAS2000.
furmangg wrote re: When (null) is not NULL
on Thu, Mar 2 2006 12:20 PM
Prologika (Teo Lachev's Weblog) wrote Aggregate rows and SP2
on Sun, Jun 10 2007 11:02 AM

Back to my When (null) is not Null post , there is a undocumented breaking change in SQL Server 2005

Prologika (Teo Lachev's Weblog) wrote Intelligencia Query
on Wed, May 14 2008 10:37 PM

Chris Webb, a fellow MVP and MDX guru, who's blog is a must-read for all SSAS junkies, was kind enough