Reporting Services became very popular right after they were released. Most of users start with SQL Server data sources. As we know, RS are not limited to relational data only; OLAP cubes and Data Mining models are just as valid sources as relational sources. In this blog I collected couple of quite frequently asked questions about using Analysis Services for the reports.
Let’s start with common problem: can a MDX query use query parameters? The answer is, unfortunately, no for the current (2000) version. Still, there are many possible solutions. We can use filters on the dataset instead. Another possibility is to create the query as a dynamic expression, i.e. to create a string that concatenates MDX query with report parameters in the Where clause. Finally, we could create a T-SQL query, using SQL Server as a data source, and the Openrowset function in the From clause of the query. Additionally, we can establish a linked server to the Analysis Services and then use the Openquery function in the From clause. The three solutions mentioned (filter, dynamic query and linked server) are already implemented in the “Reporting Services 2000 – OLAP Sample Reports” download.
Can we use Reporting Services to access Data Mining models? The answer here is yes. We have to connect to the Analysis Services and use a DMX query. There is a nice example how to create a customers credit card membership prediction using standard FoodMart 2000 AS demo database and the Decision Trees demo model in the “Reporting Services using Data Mining” download. Note that you will have to register at the site to get the download.
Finally, here is another advice. Reporting Services can’t replace common OLAP client tools like Excel and others that allow dynamic drilling on the cube. I find RS very useful as an OLAP client tool for one specific problem. In this version, Distinct Count is a very problematic aggregate for OLAP. It works very slowly, so a common advice is to create a separate cube for the Distinct Count aggregate. Well, with RS, we can use the RS CountDistinct aggregate function. As it works on the dataset read from the OLAP cube, the aggregate is processed on the fly, so it works very efficiently. I already have some implementations in couple of reports, and end users seem to be quite happy with the solution.
