MDX

N/A For Subtotals

Adventure Works bashing is quite a fun sport. It’s unfortunate Adventure Works makes such an easy target. In this post I want to pick on how the Adventure Works cube shows N/A for the Statistical Account subtotal.

Obviously it makes no sense to sum together Headcount and Units and Average Unit Price. Setting the Statistical Accounts subtotal to NA does make sense. Adventure Works uses the following code to do this:

// Assignment of constant for Statistical Accounts account member.
// This will allow parent member to be visible by default.
( [Account].[Accounts].&[95], [Measures].[Amount] ) = "NA" ;

Unfortunately, this is not the best way for several reasons.


Strings Break Aggregations

First, assigning a string will null out any subtotals and grand totals above where you assigned the string. This problem isn’t visible in Adventure Works since IsAggregatable is set to false on the [Account].[Accounts] hierarchy which removes the grand total. But if we do the following assignment on a hierarchy with a grand total, we see the problem:

( [Product].[Product Categories].[Category].[Accessories], [Measures].[Internet Sales Amount] ) = "NA";

      before assignment

       after assignment

You can see that when we assigned a string to the Accessories subtotal, it nulled out the grand total making it disappear. And null grand totals are a major usability problem, as I’ll explain in a second.


Preserving Sparsity

The second reason the Adventure Works NA approach is flawed is that it eliminates the sparsity in the cube for the Statistical Accounts subtotal. For example, let’s say we put [Date].[Date] on columns, note a bunch of dates with no data except for NA show up. This hurts performance tremendously and makes it more difficult to find meaningful data.

 

Typically, fixing this problem involves a conditional assignment which ensures empty cells stay empty:

( [Account].[Accounts].&[95], [Measures].[Amount] ) = IIf(IsEmpty([Measures].[Amount]),null,"NA");

But in Adventure Works, all the Statistical Accounts have a ~ unary operator which means that Statistical Accounts subtotal is always null. So to preserve sparsity and fill in the Statistical Accounts subtotal with NA only where any statistical accounts have data, we would have to do something like:

( [Account].[Accounts].&[95], [Measures].[Amount] ) =
IIf(
 IsEmpty(
Sum(
 [Account].[Accounts].&[95].Children
 ,[Measures].[Amount]
)
 )
 ,null
 ,"NA"
);


Strings Are Less Efficient

The third reason the Adventure Works NA approach is flawed is the degradation in performance due to the use of strings. In talking with the SSAS team, Marius Dumitru summarized this problem by saying, “strings are larger than other data types, harder to manipulate, introduce data structure indirections and memory copies/allocations.”

Mosha Pasumansky mentioned how strings and sparsity impact performance in the discussion of the Ratio calculation in this old blog post.


Null Grand Totals

Recently some co-workers were building a PivotTable and no matter what they did, they could never get anything more than a blank PivotTable. After a bit of head scratching, I realized what was happening was that the grand total was nulled out, so they were never even able to start the PivotTable properly.

To demonstrate this problem, let’s pretend we want to null out the grand total on the Product Categories hierarchy:

( [Product].[Product Categories].[All Products], [Measures].[Internet Sales Amount] ) = null;

Now if we start a PivotTable and put something other than the Product Categories hierarchy on rows, you’ll see this null grand total prevents us from starting the PivotTable since no rows even show up:

       before assignment

      after assignment

Incidentally, you can tell from the “this will allow parent member to be visible” comment in the code at the top of this blog post that the developers of Adventure Works had this very thing in mind. Since the ~ unary operator caused Statistical Accounts to be null always, it made it impossible to expand that subtotal since it would never appear.

Of course, your users could check the “Show items with no data on rows” option under PivotTable Options… Display. But you can’t expect your users to know to do that. PivotTables are designed for top down analysis. And if the top is null, your PivotTable is not user friendly.


My Solution

So how do we avoid all three Adventure Works problems created by assigning strings? And how do we avoid the problem of null subtotals and null grand totals?

You will note Mosha Pasumansky’s approach in the blog post above was to use NULL and a four-part format string like '#.#;;;NA'. The problem with that approach is that it doesn’t work in Excel PivotTables. Excel retrieves the VALUE and the FORMAT_STRING cell properties then does the formatting itself. But the formatting code in Excel ignores the fourth part, unfortunately. (As a reminder, format strings are semicolon separated. The first part is for positive numbers, the second part is for negative numbers, the third part is for zero, and the fourth part is for nulls.) So our solution will have to limit itself to three-part format strings which work in Excel.

My approach of choice looks like the following:

scope( [Account].[Accounts].&[95], [Measures].[Amount] );
 this = IIf(IsEmpty(Sum([Account].[Accounts].&[95].Children, [Measures].[Amount])),null,0);
 Format_String(this) = ";;"; //format zeros as blank
end scope;

It is assigning a zero to a cell if any statistical accounts have data in this slice, otherwise it’s assigning null. Then it’s using a custom format string just for the Statistical Accounts subtotal. The format string is just two semicolons. That three-part format string says to format zero as blank. Since the format string is inside the scope statement, we’re only customizing the format string for the Statistical Accounts subtotal.

If you prefer NA, then you would use the following format string code inside the scope statement:

 Format_String(this) = ";;\N\A";

And as discussed above, typically IsEmpty(Sum([Account].[Accounts].&[95].Children, [Measures].[Amount])) would just be IsEmpty([Measures].[Amount]) but we had to use the more complex expression here because of the ~ unary operators in play.

This approach solves all the problems we were trying to avoid. It doesn’t null out the grand total. It preserves sparsity in the cube. It avoids using expensive strings. It avoids null subtotals and null grand totals. And it is usable in Excel.

Excel Writeback – Security and Leveraging the UPDATEABLE Cell Property

Most of my blog posts chronicle some lesson learned in the heat of battle at a client site. This blog post is a bit different since the lesson learned came from an Artis internal project we started and completed last year. At Artis, our time entry system feeds into an Artis data warehouse and an Analysis Services cube for reporting. We also support writeback against the cube for project managers to enter forecasts for projects. That way, at the weekly staffing meeting, it’s pretty simple to see that Greg is forecasted to work 95 hours between four projects next week. At that point, preventative measures can be taken… like alerting local emergency rooms that I may be making a visit.

Seriously though, Artis is a great place to work since I get to work with people like Drew Jones and Cindy Liang (who actually implemented this resource planning cube), and I’ve never once gotten close to working 95 hours. I’ve got an MDX query to prove it! But I digress…

Writeback Cube Security

Since forecasting and resource planning is the goal, we use role-based security in the cube to prevent project managers from setting a forecast for a prior week (since it’s already too late to plan resources at that point). A typical scenario in industry where write security would be needed would be making sure each regional manager can only set the budget for his region. Setting up this security was a bit tricky, so I’ll describe it step-by-step.

The first step is to create a ReadAll role. In this case, the ReadAll role grants members access to read the whole cube. I’ve definitely seen a number of clients make mistakes here and grant extra permissions that are not required. Nothing needs to be checked on the General tab (not even Read definition which grants them access to see the metadata in tools like SSMS). On the Cubes tab you need Read and optionally Drillthrough access. If you want a user to be able to read the whole cube from a tool like Excel, that’s all you need to do.

Next, build a separate Writeback role. On the Cubes tab, grant Read/Write, check Process, and (optionally) Drillthrough permissions. I’m frankly not sure why you need Process permissions to perform ROLAP writeback, but it appears you need Process permissions for ROLAP or MOLAP writeback:

In the Data Sources tab, make sure to grant Read permissions on the data source used for writeback. For example, when I created my writeback partition, I used the Greg Test data source to direct that writeback table to live in another database (such as a database with Full recovery model):

Finally, if you want to only let them writeback to certain cells, control this on the Cell Data tab. Since I was prototyping against Adventure Works data, the expression below returns true when the current year is later than 2003. In your cube, the expression might look at a current week flag in the Date dimension or something like that.

 

Am I Seriously Recommending Cell Security?!?

Wait! Cell security! Won’t that kill performance? Definitely Analysis Services best practices call to avoid cell security in favor of dimension data security because cell security is a performance killer. The reason I use it here is because I don’t know of a way to accomplish the writeback security without cell security. The question is whether this particular combination of cell security designed to be write security impacts read performance. I verified this security only impacts the performance of writes with a simple trick. In the read/write permissions box above, I put in the following expression to prove this:

ASSP.FireTraceEventAndReturnValue(99) < 100

Let me explain what that’s doing. This FireTraceEventAndReturnValue function was designed for the very purpose of being able to detect via Profiler when Analysis Services evaluates it. So it simply fires a “User Defined” event in Profiler and then returns the integer you pass into the function. That way, you can watch Profiler and verify cell security isn’t being evaluated during read-only queries. It only appears in Profiler when you query the UPDATEABLE property or when you perform a write. So just building a PivotTable in Excel isn’t slowed down by the above cell security since Excel currently doesn’t query the UPDATEABLE cell property. But I’m getting ahead of myself as I’ll describe the UPDATEABLE cell property below.

Why is this the case that cell security didn’t impact read performance? Note that we used two roles. One role grants read permissions which let you read the whole cube. Another role grants write permissions but then says you can only write to recent years. If we had just created the Writeback role, users would not have been able to read 2003 data and cell security would impact read performance, too. By creating two roles, the read and write permissions are managed separately.

As a side note, we have a support case open to fix a bug where attempting to write data to 2003 sometimes causes SSAS to crash. It appears this may happen when the user is in two roles, as described above. Hopefully that bug will be fixed soon. (Update:It looks like it will be fixed in SQL 2008 R2 SP1 CU6.) Coincidentally, the VBA described below provides a workaround to the bug.


Testing Out Writeback in Excel 2010

Now that we’ve setup our role-based security, let’s see what the user experience is in Excel 2010. We start a PivotTable as usual. But since I’m an administrator on my Analysis Services dev server, I need to tweak the connection string to pretend to be a lower privileged user. Go to the Data tab, click Connections, and click Properties for the connection. Then append either of the following snippets to the end of the connection string:

;Roles=ReadAll,Writeback

;EffectiveUserName=DOMAIN\UserWithReadWritePermissions

The first pretends to be a member of the ReadAll and the Writeback roles. This works fine as long as the security is hardcoded (i.e. not dynamic security which uses the UserName() function in MDX expressions in the role). The EffectiveUserName option is another option which impersonates a specific user, and I’ve blogged about it before.

Now I’ll build a simple PivotTable then enable What-If analysis. What happens when I edit a 2003 cell and publish my changes? I get this error message:

The error message is at least helpful in describing what went wrong. But it’s not helpful in describing which cell was secured. Also, it’s not helpful that it waited until I was finished entering my data before giving me an error during the Publish. Some immediate feedback would have been nice.

So how do you tell in advance which cells are secured and which cells are updateable. Fortunately, there’s a handy cell property called UPDATEABLE which does just that. Unfortunately, Excel 2010 what-if analysis doesn’t leverage it, so I have proposed this feature. For now we can use a little VBA to roll our own.


Using VBA to Query the UPDATEABLE Cell Property

The following VBA code catches the Worksheet’s PivotTableAfterValueChange event. I could have taken a number of approaches, and I’ll discuss the pros and cons of this approach below, but I chose this approach for this prototype because it was the most straightforward. When you type in a new value for a cell in a PivotTable, this event fires. This code borrows the ADO connection from the PivotTable and runs a simple query that retrieves the UPDATEABLE cell property for that cell’s coordinates in the cube. I definitely haven’t explored all the possible values of the UPDATEABLE cell property, so you’ll need to verify this code with your cube. But for the simple example in this blog post, I’ve seen two possible values. If UPDATEABLE is CELL_UPDATE_ENABLED then writeback is allowed and the cell is not secured. If UPDATEABLE is CELL_UPDATE_NOT_ENABLED_SECURE then the cell is secured and will produce an error upon writeback, so we discard that change and popup the error message:

In my mind, that’s a better user experience. You get immediate feedback that tells you which cell is the problem. So without further ado, here’s the code:

Private Sub Worksheet_PivotTableAfterValueChange(ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)
 
    On Error GoTo ErrHandler
 
    Application.ScreenUpdating = False ' turns off screen updating
 
    'from http://msdn.microsoft.com/en-us/library/ms145573.aspx
    Const MD_MASK_ENABLED = &H0
    Const MD_MASK_NOT_ENABLED = &H10000000
    Const CELL_UPDATE_ENABLED = &H1
    Const CELL_UPDATE_ENABLED_WITH_UPDATE = &H2
    Const CELL_UPDATE_NOT_ENABLED_FORMULA = &H10000001
    Const CELL_UPDATE_NOT_ENABLED_NONSUM_MEASURE = &H10000002
    Const CELL_UPDATE_NOT_ENABLED_NACELL_VIRTUALCUBE = &H10000003
    Const CELL_UPDATE_NOT_ENABLED_SECURE = &H10000005
    Const CELL_UPDATE_NOT_ENABLED_CALCLEVEL = &H10000006
    Const CELL_UPDATE_NOT_ENABLED_CANNOTUPDATE = &H10000007
    Const CELL_UPDATE_NOT_ENABLED_INVALIDDIMENSIONTYPE = &H10000009
 
    Dim pcache As PivotCache
    Set pcache = TargetPivotTable.PivotCache
 
    If Not pcache.IsConnected Then
        pcache.MakeConnection
    End If
 
    Dim oCellset As New ADOMD.cellset
    oCellset.ActiveConnection = pcache.ADOConnection
 
    Dim sError As String
    Dim oPivotTableCell As Range
    For Each oPivotTableCell In TargetRange.Cells
        Dim oPivotCell As PivotCell
        Set oPivotCell = oPivotTableCell.PivotCell
 
        Dim sMDX As String
        sMDX = "select from [" & pcache.CommandText & "] WHERE " _  
         & oPivotCell.MDX & " CELL PROPERTIES UPDATEABLE"
 
        oCellset.Source = sMDX
        oCellset.Open
 
        Dim iUPDATEABLE As Long
        iUPDATEABLE = oCellset(0).Properties("UPDATEABLE")
 
        If iUPDATEABLE = CELL_UPDATE_ENABLED Then
            'update allowed
        ElseIf iUPDATEABLE = CELL_UPDATE_NOT_ENABLED_SECURE Then
            sError = sError & "Cell " & Replace(oPivotTableCell.Address, "$", "") _ 
             & " is secured." & vbCrLf
            oPivotCell.DiscardChange
        End If
 
        oCellset.Close
 
    Next
 
    If sError <> "" Then
        sError = "The following cells do not allow writeback, " _ 
         & "so their values were discarded." & vbCrLf & vbCrLf & sError
        MsgBox(sError)
    End If
 
ErrHandler:
    Application.ScreenUpdating = True ' turns on screen updating
 
End Sub

There is one main downside to this approach. It can slow down the data entry process since after each cell is changed, we execute one MDX query per cell to retrieve the UPDATEABLE cell property. This is generally fairly snappy as running 100 of these MDX queries only took a second against the local network and several seconds over a slower VPN connection. But when there are a bunch of secured cells you attempt to update, discarding each change one cell at a time takes a couple of seconds, which adds up. Another downside to consider is that if you enter a change on Monday then save the workbook without publishing those changes, when you open the workbook on Tuesday your writeback permissions in the cube may have changed yet this VBA code doesn’t rerun for previous changes you’ve entered but not published.

Another approach would be to catch the PivotTableUpdate event and grab the coordinates of all the cells and run one MDX query to retrieve the UPDATEABLE property for all cells in the PivotTable, caching this information for later when the user updates cells. The reason this approach would be more complex is because the .MDX property of a subtotal PivotCell returns a tuple with one less member than a PivotCell that’s a detail level cell since the All member isn’t mentioned in the subtotal’s MDX. And you can’t easily combine two tuples with different dimensionality. So I went with the more straightforward approach for this prototype.

Note in the VBA above the use of the ADOMD.Cellset object. This requires a reference to any version of Microsoft ActiveX Data Objects (Multi-dimensional), and you can add that reference from within the VBA editor from the Tools… References menu:

 

What’s He Not Telling Me?

This post focused on role-based security for writeback and how to improve that user experience. I didn’t talk about setting up a writeback partition or whether to choose MOLAP or ROLAP writeback. I didn’t discuss how allocation method impacts writeback performance. I didn’t discuss how to writeback to non-leaf cells. I didn’t discuss the necessity of periodically moving data from the writeback table into the regular fact table. Maybe I’ll blog about those in the future.

I also didn’t discuss the pros and cons of the different architectures we could have chosen. This project lent itself to extending our existing time entry reporting cube with forecast writeback, but we could have also chosen to implement a good budgeting and planning tool like deFacto. That decision process is a blog post for another time.

 

Conclusion

To summarize, be sure to grant read permissions and write permissions in separate roles if the user should be able to read more of the cube than they can write to. The UPDATEABLE cell property can come in handy if you want to know in advance which cells a user has permissions to update. With a little VBA, it’s pretty straightforward to improve the writeback experience where writeback security is involved by querying the UPDATEABLE cell property.

 

Updates

1. Fixing some VBA formatting/syntax issues that happened when I pasted my the code into my blog
2. Updating the status of a hotfix for the SSAS bug I mentioned which will be fixed in SQL 2008 R2 SP1 CU6.

 

 

 

LastNonEmpty Gotchas

I have been collecting a few advanced tidbits about semi-additive measures over the last few months, and it's time to share them.

Role-Playing Time Dimensions

Semi-additive aggregation functions like LastNonEmpty work just like Sum on all dimensions except for the Time dimension. How does it determine which is "the Time dimension"? It looks for the dimension marked Type=Time:

But what happens when, like most cubes, you have multiple role-playing versions of that dimension in your cube? Which does it choose? Flip to the Dimension Usage tab in BIDS. For the column representing that measure group with the semi-additive measure, find the role-playing date dimensions. I've circled them in green below. Then find the topmost one that's connected to the measure group. I've circled that in orange below. That's the semi-additive dimension for this measure group. A LastNonEmpty measure in this measure group will look for the most recent day with data in that Date dimension.


If you discover that the wrong dimension is the semi-additive dimension, it's very easy to fix. You just need to make the correct dimension the topmost connected role-playing date dimension by reordering the cube dimensions. To reorder the cube dimensions, flip to the Cube Structure tab, look at the list of cube dimensions in the bottom left, then drag and drop them into the correct order, similar to the following:

I suggest you arrange the cube so that the semi-additive dimension be the same for every measure group. This is probably a topic for a post another time, but I'm a big proponent for having a dimension simply called Date which is connected to every single measure group as the most common meaning of date. This dimension should be the semi-additive dimension.

I was warned that there have been some code changes in the Analysis Services product in this area at some point. So I tested this behavior in AS2005 RTM, SP1, SP2, SP3, and AS2008 RTM. I found it worked as I described above in all those versions. But test your cube thoroughly.

On a related note, the BIDS Helper Validate Aggs feature understands semi-additive measures and role-playing dimensions correctly. If you’ve got a measure group with only semi-additive measures, and if there are any aggs that don’t include the granularity attribute of the correct semi-additive dimension, then it warns you that those aggs will not be used in most situations.

Performance of LastNonEmpty vs. LastChild

On my largest cube, we decided to go with LastChild over LastNonEmpty. The driving reason was that LastNonEmpty hit more partitions than it needed to. To be specific, if we asked for a LastNonEmpty measure for 2008, it would hit every partition in 2008, not just the latest. LastChild only hits the latest partition. I would like to credit Robert Skoglund of Microsoft Consulting Services with making this observation and suggesting the solution.

The only hurdle was that the current month/quarter/year weren't complete, so the LastChild didn't have any data. That was easy to fix with an addition to the MDX script like the following:

// the most recent day with data 
CREATE HIDDEN SET [Yesterday] 
AS Exists([Date].[Calendar].[Date].Members, [Date].[Yesterday Flag].&[True]);

// The Inventory measures are AggregateFunction=LastChild. 
// When we are in a current period, use the most recent day to 
// get the inventory counts. 
SCOPE(MeasureGroupMeasures("Inventory")); 

  SCOPE(Exists([Date].[Calendar].[Month].Members, [Date].[Yesterday Flag].&[True])); 
    this = [Yesterday].Item(0).Item(0); 
  END SCOPE; 
  
  SCOPE(Exists([Date].[Calendar].[Quarter].Members, [Date].[Yesterday Flag].&[True])); 
    this = [Yesterday].Item(0).Item(0); 
  END SCOPE; 
  
  SCOPE(Exists([Date].[Calendar].[Year].Members, [Date].[Yesterday Flag].&[True])); 
    this = [Yesterday].Item(0).Item(0); 
  END SCOPE; 
  
  [Date].[Calendar].[All] = [Yesterday].Item(0).Item(0); 
  
END SCOPE;


Another reason for going with LastChild over LastNonEmpty is that the results are often more intuitive in situations where not every single item is snapshotted every single period. Teo Lachev explained this problem very well in his post Last Non Empty Affairs. Depending on the business requirements, you could workaround the problem as he did with MDX that looks at each Customer, or you could change the ETL to snapshot every single item every period, or you could use LastChild to ensure that your results come from a consistent time period. The LastChild trick won't work in every situation, but it is a handy trick to have in your bag.

Long Navigation Parameters

I was shocked recently when my nice report that was executing in 2 seconds suddenly took 22 seconds to finish after I enabled navigation (i.e. drillthrough links) in every textbox in the report. The point of this post is to explain what’s going on and suggest workarounds to improve performance.


The Symptoms

This particular report was an MDX report in Reporting Services 2005. If your MDX reports are anything like mine, you end up having an obscene number of multi-select parameters to give the end user maximum flexibility. This report was no exception. In SQL reports, the parameter values are often integers. However, in MDX reports, the parameter values are usually MDX unique member names such as [Product].[Category].&[Bikes]. You can already see the problem. If you have many large multi-select parameters, the concatenated list of selected parameter values becomes large. In fact, for this report, that list ended up being about 30KB long. Multiply that times 3,000 textboxes in the report, and your speedy report is now a 90MB beast.

Another interesting thing I noticed is that of the TimeDataRetrieval, TimeProcessing, and TimeRendering columns in ReportServer.dbo.ExecutionLog, the only one that changed when I turned on navigation was the TimeProcessing column.

Also, you might guess shortening the name your “Geography” report parameter to “G” would help, but in fact, the length of the report parameter name does not matter (just the length of the selected parameter values).

The final interesting thing I noticed is that the Drillthrough chunk in ReportServerTempDB was massive. Clearly, part of the expense of lots of huge drillthrough links was in constructing and saving that chunk to ReportServerTempDB. The following query shows you the chunks and their sizes:

select sd.SnapshotDataID
,sd.CreatedDate
,sd.ExpirationDate
,s.ReportPath
,s.Expiration
,s.CreationTime
,c.ChunkName
,datalength(c.content) as ChunkBytes
from ReportServerTempDB.dbo.SnapshotData sd
join ReportServerTempDB.dbo.SessionData s
on s.SnapshotDataID = sd.SnapshotDataID
join ReportServerTempDB.dbo.ChunkData c
on c.SnapshotDataID = sd.SnapshotDataID
order by sd.CreatedDate, c.ChunkName

I did a little research into how drillthrough works in SSRS 2005. Here is my understanding based solely on observation of the HTML in Report Manager and watching SQL profiler events against the ReportServer database. When a report is rendered in Report Manager, it does not pass the entire URL for each drillthrough link to the web browser. All it does is capture the cell coordinates you clicked when you click a drillthrough link. Then it posts that event back to the server. Then the server runs a few SQL queries against ReportServerTempDB to lookup info from that ChunkData table. I was rather impressed with how efficient this lookup was as it did not require reading the entire chunk. From what I can tell, that drillthrough chunk is some sort of binary structure that is internally indexed somehow. When the server goes to lookup the full drillthrough URL for the link you just clicked, it grabs the first few segments of that drillthrough chunk (which I’m guessing is loading the “index” for the entire chunk) and then it skips right to the segment of that chunk which has the info it needs. So my conclusion is that in SSRS 2005, they have done a good job optimizing the click of a drillthrough link. However, the up-front processing required to optimized that click is rather expensive.

Indications so far are that SSRS 2008 will improve this scenario. I tried this in the November CTP of SSRS 2008 and the report with the navigation rendered the first page 2x faster than on SSRS 2005 SP2. Interestingly enough, the time difference between a report with navigation and a report without navigation now shows up in SSRS 2008 under the TimeRendering column, not the TimeProcessing column. Also, the chunks are stored differently in SSRS 2008. Hopefully someone from Microsoft will fill us in on how they were able to achieve this performance improvement. (Update: Robert Bruckner from Microsoft responded here.)


Workaround #1

Short of waiting for SSRS 2008, what is the workaround? I wrote an MDX sproc called ASSP.KeysStrToSet. You can refer to all the technical details, get some usage examples, and download some sample reports at that link. But let me explain the workaround at a high level. There are two parts.

First, you need to find a way to shorten the parameter values. In general, this is pretty easy as you can just use the surrogate key (i.e. use “1” as the key instead of “[Product].[Product].&[1]”). For optimal performance, change the parameter datatype to Integer if possible. However, there are a couple of more advanced scenarios such as composite keys or nullable keys which add some complexity. The link to the Analysis Services Stored Procedure project above explains all these advanced scenarios and the solution.

Second, you need to figure out a way to let MDX accept those shortened parameter values. That’s where the ASSP.KeysStrToSet function comes into play. Here’s an example. The following two expressions return the same set:

StrToSet("{ [Product].[Product].&[1],[Product].[Product].&[2] }")
ASSP.KeysStrToSet("[Product].[Product]", "1,2")

How much improvement should you expect from this workaround? Our tests on reports with lots of navigation-enabled textboxes have shown anywhere from a 2x to an 8x performance improvement in the time it takes to render to the browser in Report Manager. Of course, that depends upon how many textboxes have navigation enabled and on how large the drillthrough links are for each textbox.

I suspect utilizing this workaround with SSRS 2008 will make the performance improvement even greater. (Unfortunately, I can’t test that theory on the November CTP at the moment because of a bug.)

If you agree with this approach, please vote for this feature suggestion so that KeysStrToSet functionality becomes available in a built-in MDX function.


Workaround #2

Another possible workaround is to add a “Show Drillthrough?” parameter that controls whether or not to enable drillthrough on the textboxes. Then in the “Jump to Report” property, put the following expression:

=IIf(Parameters!ShowDrill.Value,"MyDrillReportName",Nothing)

In SSRS 2005 (it’s not necessary in SSRS 2008 apparently), you also have to wrap each parameter you hook up to the drill report in that way:

=IIf(Parameters!ShowDrill.Value,Parameters!City.Value,Nothing)

You should probably use workaround #2 in conjunction with workaround #1 to achieve good performance when they flip the “Show Drillthrough?” parameter to True.

Note you may have to make that “Show Drillthrough?” parameter a string to workaround a bug.


Conclusions

Enabling drillthrough on every cell in a report can drastically slow report rendering if the drillthrough parameters are very long. However, you can adequately workaround this problem by shortening the parameter values and dynamically disabling drillthrough when it is not needed.

Retrieving Cell Properties

The point of this post is to discuss how to retrieve cell properties. Specifically, I want to call attention to the ReturnCellProperties setting on an Analysis Services connection and command. I will also talk about how you can use cell properties in Reporting Services reports.

It must be said that Teo Lachev beat me on publishing this tip. I had already drafted most of this post and had a couple of extra things to say on the topic, so I’m publishing it anyway.

In an MDX query, you’ve got dimension properties and cell properties. Dimension properties are intrinsic member properties such as MEMBER_CAPTION in addition to member properties you define in your dimension (such as the Customer attribute having a member property called Email). Cell properties are properties such as VALUE, FORMATTED_VALUE, and FORMAT_STRING which are simply extra information about a cell.

In most cubes, an effort has been made to properly set the FORMAT_STRING for all measures so that percents are formatted as percents and currency is formatted as currency. (Sometimes, the calc script even customizes the FORMAT_STRING for particular slices of the cube.) Therefore, it’s important to know how to retrieve the FORMATTED_VALUE cell property so that you can fully leverage all the cube provides you.

When executing an MDX query, you can retrieve the results in two-dimensional (DataReader/DataSet) or multi-dimensional (CellSet) format. The CellSet object lets you easily access any cell properties the query returns. On the other hand, it is not obvious how it is possible to retrieve any cell property other than VALUE in a DataReader/DataSet.

Putting the ReturnCellProperties=True property on the connection string changes the columns you get in a DataReader/DataSet:

AdomdConnection conn = new AdomdConnection();
conn.ConnectionString =
"Data Source=(local);Initial Catalog=YourDB;ReturnCellProperties=True";

Similarly, setting the ReturnCellProperties property on a command object will produce the same effect:

AdomdCommand cmd = new AdomdCommand();
cmd.Properties.Add("ReturnCellProperties", true);

This is obviously relevant if you’ve got a custom .NET application that executes MDX queries because it allows you to retrieve cell properties. It’s also relevant if you’re using MDX queries in Reporting Services. If you’re using the OLE DB provider to connect to Analysis Services (which is less common in SSRS2005), you can retrieve cell properties by adding ReturnCellProperties=True to the connection string which causes the extra columns to show up. However, a “Microsoft SQL Server Analysis Services” data source in Reporting Services automatically sets the ReturnCellProperties property (and the DbpropMsmdFlattened2 property, which I won’t discuss) to true on the underlying command object. That data source also hides the extra columns from you when you’re previewing your dataset, so you just have to trust that they are there.

select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].Members on 1
from [Adventure Works]

If your MDX query for your report does not specify a CELL PROPERTIES clause (as in the query above), then you get the VALUE and FORMATTED_VALUE properties by default in Reporting Services. Obviously, =Fields!FieldName.Value retrieves the VALUE cell property. You can retrieve the FORMATTED_VALUE cell property with =Fields!FieldName.FormattedValue expression. More information on all the field properties you can use in SSRS expressions is available here.

select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].Members on 1
from [Adventure Works]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

If your MDX query for your report does specify a CELL PROPERTIES clause (as in the query above), you override the defaults. This is useful for instructing Reporting Services to retrieve fewer properties for performance reasons. (For instance, a best practice is to specify “CELL PROPERTIES VALUE” so you don’t retrieve FORMATTED_VALUE if you’re not going to use the FormattedValue expression in your report.) Overriding the default CELL PROPERTIES is also useful to pull back extra properties such as FORMAT_STRING. Then in a report you can retrieve the FORMAT_STRING for a cell by using the =Fields!FieldName("FORMAT_STRING") expression.

There is a known limitation such that you cannot use the SSRS Aggregate expression and FormattedValue together. This limits the usefulness of the FormattedValue property. A workaround is to format the textbox in Reporting Services instead of using the FormattedValue property. This formatting could be hardcoded, but sometimes measures have different format strings for different slices of the cube. So if the format string is not constant, you can make the SSRS formatting dynamic by putting a =Fields!FieldName("FORMAT_STRING") expression on the Format property of a textbox. For footers, it would need to be =First(Fields!FieldName("FORMAT_STRING")). Note that .NET format strings (which SSRS uses) aren’t quite the same as SSAS format strings, so you may need to write a small function in the Code tab of your report and do replacements such as changing Currency to c0.

As a side note, Darren Gosbell blogged about an easy way to see the connection string properties for SSAS, but unfortunately, ReturnCellProperties is one of several properties that don’t appear in that list. You have to look at this page to see all the available properties. Mosha says that you can use all the XMLA properties on the connection string.