Don't forget the OLE DB connection in Reporting Services 2005

So I hear everyone complaining about the poor support for Analysis Services in Reporting Services 2005.

 

What I’ve found is that for better performance you have to keep in mind the following :

 

1) The Analysis Services connection doesn’t generate the best MDX. It’s somewhat like the HTML that Word generates ( ok , maybe not that bad ).

2) In Reporting Services 2000 , we didn’t have it. So we created an OLE DB connection to Analysis Services, wrote our own MDX and the speed was quite good. Of course you still have the resultset "converted" into a SQL style resultset, you have to work around that.

 

Select an OLE DB connection , then click edit to get to the screen below.

Use the OLE DB provider for Analysis Services 9.0. You can then create a dataset the way you would normally create a SQL dataset , and then insert your MDX , as shown below.

You then get a normal reporting services dataset which you can use in a table or matrix. To use Paramaters , you need to create the MDX as a string , then spilt it where the parameter goes. see below :

This will customize the MDX depending on the selection the user has made. So you get the following result :

So if you want better performance connecting to AS from RS 2005 , don’t forget that this option is still available.It’s something to consider if you’re having performance issues using the standard connection

 

About these ads