Self-Documenting Cubes in Excel PivotTables

If I were the supreme leader of the Excel team (and I've had trouble convincing them of that), one of the first things I would do is add tooltips in the PivotTable field list so that when you mouse over a field, it would describe that field. I've asked for that feature, but so far, it doesn't look like it will make Excel 2010.

So... plan B. But let me first give some background on self-documenting cubes.


Self-Documenting Cubes

I'm big on self-documenting code because I've had too many experiences where separate documentation gets stale and irrelevant. Plus, I'm lazy. Thus, anything that makes it easier for me to keep documentation up-to-date and relevant is a good thing in my book.

Having descriptive names for measures and dimensions is one aspect of self-documenting cubes. You'll see descriptive calculation names like "Internet Sales Amount Variance to Annual Quota" in my cubes. But what I really want to focus on in this post is the Description property which appears on measures, dimensions, and all other objects in Analysis Services.

A few years ago at a user group, I was speaking with one prominent member of the Analysis Services team (who shall remain unnamed), and when I mentioned the Description property, he asked incredulously whether anybody ever filled those in. If you're wondering the same thing, the reason I fill in every single one is because it's a cinch to generate documentation that's relevant and up-to-date. You can use various tools that are out there. Or you can easily write a simple custom .NET app which loops through measures and dimension attributes and writes the descriptions to an HTML document. Or you can make use of descriptions with the following idea.


Plan B

Since we don't get tooltips on the field list in PivotTables for now, what's plan B? I'll credit my co-worker Drew Jones with this idea. He suggested using actions in cubes to approximate this as much as possible. Here's what we came up with.

Right-click on any numeric cell in a PivotTable, and you can see the first 100 characters of the description property of that measure under Additional Actions...

Here's how you do that. First of all, you'll need the latest version of the ASSP assembly which includes the DiscoverSingleValue functions. Then you'll need to create an action in your cube with the following setup:

 

Target type: Cells

Target object: All cells

Condition:

Len(
ASSP.DiscoverSingleValue(
  "Description"
  , "MDSCHEMA_MEASURES"
  , "<MEASURE_NAME>" + [Measures].CurrentMember.Name + "</MEASURE_NAME>"
    + "<CUBE_NAME>" + [Measures].CurrentMember.Properties("CUBE_NAME") + "</CUBE_NAME>"
)
)>0

Action Content Type: URL

Action Expression: "http://intranet/Documentation/CubeDocumentation.html"

Caption:

"Definition: "
+ Left(
   ASSP.DiscoverSingleValue(
    "Description"
    , "MDSCHEMA_MEASURES"
    , "<MEASURE_NAME>" + [Measures].CurrentMember.Name + "</MEASURE_NAME>"
      + "<CUBE_NAME>" + [Measures].CurrentMember.Properties("CUBE_NAME") + "</CUBE_NAME>"
   )
  ,100
  )
+ iif(
   Len(
    ASSP.DiscoverSingleValue(
     "Description"
     , "MDSCHEMA_MEASURES"
     , "<MEASURE_NAME>" + [Measures].CurrentMember.Name + "</MEASURE_NAME>"
       + "<CUBE_NAME>" + [Measures].CurrentMember.Properties("CUBE_NAME") + "</CUBE_NAME>"
    )
   )>100
   ,"..."
   ,""
  )

Caption is MDX: True


The action looks like the following in BIDS when you're done:

 

The only downside is that Excel 2007 doesn't even ask for actions on cells any time there is a multi-select in a PivotTable filter. But other than that limitation, it's a pretty good workaround.