Skip to content

How to retrieve Cell Properties from a SSAS MDX query using SSIS

July 2, 2016

Scenario

This is a short article where I want to share something I discovered recently. I worked on an SSIS package where I had to query a SSAS cube using MDX and pass cell properties such as the VALUE and FORMATTED_VALUE down the data path to an output/destination file.

Problem

Typically a MDX query would have both dimension properties and cell properties and in my case, the required dimension properties were duly set using the following expression;

DIMENSION PROPERTIES MEMBER_KEY, MEMBER_CAPTION ON COLUMNS

As a result of using this expression, the values returned from the dimension properties were as expected. The challenge for me was getting those for the cell properties. For instance, a look at the column tab of the data source editor, shows the required dimension properties

(i.e. MEMBER_CAPTION and MEMBER_KEY) were successfully retrieved. Please note I have obfuscated the dimension and measures using the blue highlight just for security reasons 🙂

Figure2

The problem, however, was how to get or retrieve the cell properties, VALUE and FORMATTED_VALUE. As can be seen from the picture above the three measure values where these are required, are not being retrieved and this was an issue.

Solution

Dear reader, I am please to say that the solution to this issue is quite simple and it lays in the connection string that point to the analysis services cube.

The current connection string is defined as;

Data Source=MyServerName;Initial Catalog=MyDatabase;Provider=MSOLAP.4;Integrated Security=SSPI;

The solution is simply adding the “ReturnCellProperties=True” property to the connection like so;

Data Source=MyServerName;Initial Catalog=MyDatabase;Provider=MSOLAP.4;Integrated Security=SSPI;ReturnCellProperties=True;

After adding the property, the cell properties (i.e. VALUE and FORMATTED_VALUE) were successfully retrieved as shown below

Figure3.

Conclusion

From the above demonstration, it is clear that the “ReturnCellProperties” property is not available by default or initially when the connection string is defined. If you have a requirement to retrieve the cell properties then this property must be explicitly set in order to show this.

Hope this has been a useful read and as always any comments, additions or suggestions are welcome. Cheers

 

Advertisements

From → SQL

Leave a Comment

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: