Excel Services

We had a recent request to combine data from 2 different cubes on 2 different Analysis Services instances on 2 different servers.

You can see where I am going here.
There are a few ways to do this, but what we eventually came up with was using good old Excel.

We created 2 data connections to the different cubes and were then able to use the CUBEMEMBER and CUBEVALUE functions to build up a table of data.

As we were using Excel we could make use of VLOOKUPs to match up data using a mapping range.

All in all a pretty elegant solution and we were able to then publish via PerformancePoint to SharePoint and make use of an MDX member filter to allow users to interact with the data.

I can see myself using this kind of solution again in the future.


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