Home > Computers and Internet > Don’t forget the OLE DB connection in Reporting Services 2005

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
  1. No comments yet.
  1. September 6, 2011 at 1:11 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: