Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

MDX Cell Properties Supported By Excel

with 5 comments

I was wondering the other day (as you do) which of the MDX cell properties Excel PivotTables actually support. This page has all the details on the cell properties that are available in an MDX query but most client tools don’t bother retrieving all of them and Excel is no different. Of course it retrieves the most important properties and it retrieves one or two others, but I couldn’t resist doing a bit of research to find out the exact situation with Excel 2013.

The first thing to note is that you can control which cell properties Excel retrieves for a given connection in the connection properties dialog, in the OLAP Server Formatting section:

image

If you can live with not getting all of the cell properties back from SSAS there are some scenarios where unchecking all of the boxes in the OLAP Server Formatting section can improve performance:

  • When you have a large number of databases and cubes on your server, and/or complex security, because a side effect of the way Excel retrieves properties is that it causes all cubes in all databases to be loaded, their MDX Scripts executed and security evaluated. See here for more details.
  • When your queries return a lot of data and your network is slow. See page 61 of the SQLCAT Guide to BI and Analytics for more details.

You can also quite easily see which cell properties Excel is retrieving by looking at the MDX queries it generates (thank you OLAP PivotTable Extensions). Here’s an example of a simple PivotTable query run on a connection which has all of the boxes checked in the OLAP Server Formatted section:

SELECT 

{[Measures].[D1],[Measures].[D2]} 

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 

ON COLUMNS  

FROM 

[Adventure Works DW2008] 

CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

 

In the cell properties clause of this query you can see the six properties returned. Here’s a breakdown of each of them.

VALUE and FORMAT_STRING

Excel doesn’t actually retrieve the FORMATTED_VALUE cell property, which gives you the measure value with formatting applied by SSAS. Instead, assuming you have the Number Format box checked in the Connection Properties dialog, it retrieves the VALUE property from SSAS (which contains the raw, unformatted measure value) and the FORMAT_STRING property (which contains the format string you defined on the server). It then tries to convert the format string into an Excel format for the PivotTable. Unfortunately it can’t always do the conversion successfully – I blogged about a problem with the Percent format some time ago and this is still a problem with Excel 2013. Excel also doesn’t support formats defined in the fourth section (see here for more details) of the FORMAT_STRING property for null values, and there are probably lots of other relatively obscure types of formatting it doesn’t support too.

LANGUAGE

The language property of a cell controls things like the currency symbol displayed when you are using the “Currency” built-in format string and the thousands and decimal separators used. Whether Excel returns the language property or not is also linked to the Number Format checkbox in the Connection Properties dialog. I strongly recommend that you do not use the “Currency” format string and the Language property if you are working with multiple currencies, for reasons I outlined here.

BACK_COLOR and FORE_COLOR

I’ve never particularly liked using the BACK_COLOR and FORE_COLOR properties on a cell to do traffic light-style reporting, to be honest, although I know some people love it. However I do use these properties a lot when debugging scoped assignments as seen here. BACK_COLOR is retrieved if you have the Fill Color box checked on the connection properties dialog; FORE_COLOR is retrieved if you have the Text Color box checked on the connection properties dialog.

FONT_FLAGS

Probably the only surprise of this whole exercise was the fact that Excel retrieved the FONT_FLAGS property if you have the Font Style box checked on the connection properties dialog; alas it doesn’t support FONT_SIZE or FONT_NAME. For example if you put the following calculated members on your cube:

CREATE MEMBER CURRENTCUBE.MEASURES.BOLD as "This is BOLD", FONT_FLAGS=1;

 

CREATE MEMBER CURRENTCUBE.MEASURES.ITALIC as "This is ITALIC", FONT_FLAGS=2;

 

CREATE MEMBER CURRENTCUBE.MEASURES.UNDERLINE as "This is UNDERLINE", FONT_FLAGS=4;

 

CREATE MEMBER CURRENTCUBE.MEASURES.STRIKEOUT as "This is STRIKEOUT", FONT_FLAGS=8;

If you drop them into a PivotTable, you will see the following returned:

image

Written by Chris Webb

April 25, 2014 at 11:02 am

5 Responses

Subscribe to comments with RSS.

  1. Hi Chris
    like your blog
    I have a large CSV file that has too many rows to be handled with powerpivot and my system. My solution was to split the table into smaller chunks. The challenge I have is that after bringing the separate tables together into Powerpivot, how do I now analyze all the data as a whole…thank you for your consideration

    Rick Stine

    April 27, 2014 at 12:36 am

    • Hi Rick, do you mean that when you tried to load the data as a single csv file that you ran out of memory – so you split the file and loaded it into separate Excel workbooks?

      Chris Webb

      April 27, 2014 at 12:12 pm

      • Hi Chris
        thank you for getting back to me
        The computer I have at work is a 64 bit system with limited memory. they have provided me with excel 2010 for a 32 bit system
        The data is a string of test data in a CSV format that grows by about 50,000 lines per day. Currently I have around 2,000,000 lines @ 9 columns,
        Each row has a date time stamp and a lot and batch identifier. So if I split the data by month (which I did) i can have part of the information in one month and part in the next. each month is a separate sheet.
        I would like to run a pivot table so I can compare lot to lot, or batch to batch when the data is in different tables (sheets)
        Even if I use access to split the tables, I would still like to compare the data between the sheets and that is where I get stuck

        I hope I explained myself
        thank you
        Rick

        Rick Stine

        April 27, 2014 at 11:18 pm

      • I’m still a bit confused – are you saying that you tried loading the data as a single file into Power Pivot and it failed, but when you split it up and loaded it to separate worksheets you were able to load it into PowerPivot using linked tables? Did you try loading the data from the original csv file direct into Power Pivot, or did you try loading that data into the worksheet first?

        Chris Webb

        April 28, 2014 at 4:05 pm

  2. SutoCom

    April 29, 2014 at 11:29 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,144 other followers

%d bloggers like this: