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

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

.

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