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.
Make sure you have the IIS Application Pool settings set to be 32Bit compatible.
Once you have it running you can connect SSRS using the msmdpump.dll
Like this :
You can use multiple XML/A compatible clients like for example Kettle Spoon.
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
Or even PowerPivot as a client to connect.
But back to SSRS, it goes like this. Choose SQL Analysis Server, and point the to the XML/A URL as connection string.
And you can access the Dimensions using regular MDX Queries.
Apart from the fact that you access multiple OLAP Cube brands like for example the In-Memory IcCube
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.