Connecting to Analysis Services in Another Domain

October 6, 2014

There are several tools available on the next to query an SSAS OLAP instance using MDX queries.

But most of the time you have the OLEDB drivers on your machine.

image

But some tools lack the authentication mechanism to poke into an OLAP cube that is not in your domain.

Like this MDX Query generation for QlikView

image

It allows you to connect but you can’t send across any authentication to connect remotely.

Solution: 

image

runas.exe /user:YourDomain\User /netonly “C:\Program Files (x86)\MDX Query Generator for Qlikview\QlikviewMDX.exe”

And it will just do fine ! Smile


QlikView – How to run MDX queries against OLAP Cubes

September 29, 2014

QlikView does not support natively accessing OLAP Cubes running MDX Queries.

But with a little creativity you can get it going.

1. First of all I setup an IcCube OLAP In Memory Cube to run the MDX against in the IDE.

image

IcCube supports the XML/A protocol natively Smile

Of course you can use your existing SSAS cube(s) as well as a data source, if you have setup an XML/A endpoint config.

I just used IcCube to get a quick testing environment.

2. Next I have used MS SQL as a middleware to grab the OLAP Cube data.

This MS SQL Express (Free) Edition is just there to connect to from the QV client.

3. On the QV client I made an ODBC connection to the MS SQL Express.

Next I run the OPENROWSET Query against the OLAP Cube using XML/A protocol.

image

As you can see the OPENROWSET command is executed locally on the MS SQL Server.

See : DATA SOURCE=http://localhost:8282/icCube/xmla?msrs

This makes it super easy to access the CUBE data.

Put in there your MDX Query and go.

4. When finished, reload and and look at the result.

image

This is realy fun stuff you don’t learn at school Winking smile

Enjoy !