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.