SSRS – Connect to an OLAP cube using XML/A

While there is native way in Report Builder 3.0 to connect to an MS SQL OLAP Cube.

I wan’t to demonstrate an alternative way to connect to ANY kind of OLAP cube that supports XML/A.

For MS SQL you first need to configure your OLAP Analisys Services to be able to connect using XML/A protocol.

For Windows Server 2003 look here for Windows Server 2008 look here

Make sure you have the IIS Application Pool settings set to be 32Bit compatible.

image

Once you have it running you can connect SSRS using the msmdpump.dll

Like this :

http://YourServer/olap/msmdpump.dll

You can use multiple XML/A compatible clients like for example Kettle Spoon.

image

Where you can enter your MDX query and test it.

As well as just plain MS Excel to connect to the OLAP Cube using XML/A connection

image

Or even PowerPivot as a client to connect.

image

But back to SSRS, it goes like this. Choose SQL Analysis Server, and point the to the XML/A URL as connection string.

image

And you can access the Dimensions using regular MDX Queries.

image

Bonus:

Apart from the fact that you access multiple OLAP Cube brands like for example the In-Memory IcCube

image

image

And using Multiple Clients – see above.

It comes also is also is not blocked by the firewall since XML/A uses the HTML (80) or SHTML (443) port.

Enjoy !

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: