SSRS & SSAS #61 – SSRS and SSAS Integration: how bad it is and our hope with two types of data providers
A few months ago, I had a post, SSAS #21 – Steps to create a SSRS report with MDX accessing a Cube. If you have worked on creating SSRS reports accessing cubes for a little while, you already knew that my post really doesn’t help you much in terms of solving real problems in your work.
The issue of integration between Reporting Services and Analysis Services is a never ending source of pain and frustration for many BI developers. Before I talk about the pain and frustration I’ve experienced, I want to give you some hope first by showing you two types of data providers we can use in SSRS to access your cubes.
Data Provider 1 – Microsoft SQL Server Analysis Services
Many blogs, such as my blog above, showed you the steps to create SSRS reports accessing cubes in Analysis Services database without telling you a very important piece of information. That is they are using the Microsoft SQL Server Analysis Services as the data provider for the integration between SSRS and SSAS.
This simple omission can be very beneficial to you, or can create frustration for you as well, depending on the nature of your project. If your cubes are designed well and your data navigation flows naturally as you would want, the drag-and-drop nature of this type of integration can be very beneficial to you. The well-liked SSAS query designer can be a life-saver for many developers who are so much experienced in creating manual MDX queries yet. On the other hand, when you start to experience serious performance issue with your SSRS reports, you would start to struggle with the un-optimized MDX queries that the integration has generated for you.
1) This is because this types of direct integration between SSRS and SSAS assumes that CROSSJOIN works for all data. Even without much experience in MDX, we all know that CROSSJOIN is absolutely not the best candidate for optimal performance in either TSQL or MDX.
2) It also assumes that we need help to create MDX queries for every report parameter. Any customized MDX queries you managed to create for your report parameters will be overwritten without any notification or your consent. It also assumes that we do not have interest in seeing the datasets it has created for our parameters by hiding them from us. This can create serious performance problems for us when many unused datasets are hidden from us.
Coupling the above two “helps” from this type of direct integration with the serious performance issue with your reports, there is nothing but pain and frustration.
(To be fair, this type of direct integration provides lots of benefits too. This post intends to focus on the pain and frustration, so I’ll ignore the benefits for now.)
Data Provider 2 – Microsoft OLE DB Provider for Analysis Services 9.0 (9.0 fro AS 2005 or 10.0 for AS 2008)
The OLE DB provider is not exactly better. What it interests me is that now I can put a little more customization/optimization to the MDX queries. With more customization, my hope for performance improvement (without any modifications to many shared/existing dimensions in the OLAP database) has increased a notch (if not soared).