Chris Webb tells us that the MDX Solutions book, which we co-wrote with other MDX gurus, is soon to be released. Based on the resources available on the publisher’s website, the book looks like a very promising read and I am looking forward to get my hands on it. Unfortunately, during my Applied Microsoft Analysis Services 2005 book project, I didn’t have the time to fully research the vast MDX changes in SSAS 2005 and devote the space they deserve. This is where MDX Solutions comes in. I enjoyed the previous edition by George Spofford. I am sure that I will enjoy the sequel as well.
Archive for month: February, 2006
I’ve just learned that I’ve been selected to present at TechEd 2006 US in Boston! I’ve been honored to present Reporting Services for three consecutive years at TechEd. This time, I will change things a bit and instead of focusing on the development area, I will talk about integrating Reporting Services and Analysis Services. As of now, the main topics I am planning to include are:
1. SSAS report actions
2. MDX Query Designer
3. Server aggregates
4. Building data mining reports
5. Ad-hoc reporting
6. CLR integration
See you in the “hub”!
Nick Barclay from Australia has good things to say about my book “Applied Microsoft Analysis Services in Action”. No, I didn’t sweat blood, but I have to admit that writing this one wasn’t easy! It took me almost twice as long to write it as my previous “Microsoft Reporting Services in Action” book. For the most part, that was because Analysis Services is a focal point of many technologies, including OLAP, data mining, data integration, reporting, performance management, etc. Not to mention that, as Nick said it, it was like trying to hit a moving target because the product was constantly evolving on the way to RTM.
The other main factor for taking so long is that I just can’t write short. I am a firm believer that knowing a product is knowing its limitations. I usually put myself in my reader’s shoes and try to address all possible questions the reader may ask while reading the book. I am committed to point out not only the tool strengths but also its weaknesses (nothing is perfect, right). Of course, this takes time, lots of, lots of time …
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).
DevX has just published Part 1 of my article Building Report-enabled Applications with the Windows Forms ReportViewer. Part 2 (ASP.NET ReportViewer) should be out by the end of February.
Reporting is an integral part of every complete application. The Report Viewer controls greatly reduce the development effort required to report-enable .NET applications. Part one of this two-part article shows you how you can leverage the Windows Forms ReportViewer to integrate your Windows Forms .NET applications with Reporting Services 2005.