Home > MDX Challenges > MDX #5 – A Case for Pass-Through MDX Queries using a MSOLAP Linked Server

MDX #5 – A Case for Pass-Through MDX Queries using a MSOLAP Linked Server

If cube processing allows errors and missing keys, automatic notification process should be in place to check the data counts in the cube against the data counts in the fact table.

Although I’ve known that we can create MSOLAP linked server, and send pass-through MDX queries to cubes using OpenQuery through the MSOLAP linked server, I’ve never found a compelling business case where I absolutely must use it. That is until recently when I started to think about comparing data counts in the cube to the data counts in the fact table in automatic mode.If the cube processing allows errors and missing keys, the comparison becomes very crucial.See my previous blog SSAS 26# – “Ignore Error” when processing cube. An periodic automatic notification process should be in place to check the data counts in the cube against the data counts in the fact table.

The MSOLAP linked server allows pass-through MDX queries sent to cube.

I had an opportunity of a MDX training recently. Our trainer Mr. Chester (http://www.virsolutions.com/) showed some examples of the pass-through MDX queries.

image

This query is simple enough, but it opens a door to an automatic process that I’ve been looking for.

Enable the "Allow inprocess" flag in MSOLAP Provider Options

Creating a linked server is pretty simple. Here is the sp_addlinkedserver procedure I used:

image

However, the first try of the OpenQuery failed with a message like this:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "Cube_Link" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLAP" for linked server "Cube_Link".

Again Mr. Chester came to the rescue and sent me this link:

http://sqlblog.com/blogs/marco_russo/archive/2006/10/05/using-msolap-as-a-linked-server.aspx

In the above blog, the author pointed out that the “Allow inprocess” must be enabled.

To enable the “Allow inprocess” flag, follow this path in SSMS:

SQL Server Management Studio/Server Objects / Linked Servers / Providers / MSOLAP, right-click and edit Properties:

image

image

Pass-through MDX queries return column names as [Dimension].[hierarchy].[level].[property]

The column names returned back from the pass-through MED queries are long and fully qualified. There are many ways to change them into cleaner name. To change them into shorter names that are the same as the column names on the fact table in a fast way, I used WITH statement to define column names.

image

Now data validation between source, staging, fact and cube can be automated in TSQL

Now we are back to the TSQL land, and anything is possible.

Categories: MDX Challenges
  1. No comments yet.
  1. No trackbacks yet.

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: