Enabling Write-back to an OLAP Cube at Cell Level in Excel 2010
Summary: Microsoft Excel 2010 introduces the What-If Analysis feature that is a codeless solution for capturing manually-entered user data using PivotTables connected to OLAP providers that support UPDATE CUBE statement. This article describes how the methodology used in the PivotTable write-back can be extended to individual spreadsheet cells that contain OLAP functions.
Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA
In this article
Overview
Leveraging OLAP Data Sources for Write-back
Exploring the MDX Property of the Range Object
Creating a Basic UDF for Data Write-back
Creating a Directive-based OLAP Data Source Write-back UDF
Building Directive Based UPDATE CUBE Statement
Adding an Allocation Rule Parameter to the UDF
Points to Consider When Planning to Use a Trigger Cell-based Write-back Approach
Conclusion
Additional Resources
Published: December 2010
Provided by: Sergei Gundorov, Microsoft | Pablo Trejo Montemayor, Microsoft
Contents
Overview
This article describes how to:
Leverage OLAP data sources for data write-back from Excel
Use MDX cell property introduced in Excel 2007
Create basic user-defined function (UDF) that triggers data update process
Create a UDF for complex data write-back scenarios
The write-back feature of Microsoft Excel 2010 addresses scenarios for "What-If" analysis with support for real time data update in the OLAP cubes. Microsoft SQL Server Analysis Services dynamic dimension security provides read-write user security at the dimension member level. Data entered and submitted at the lower levels of the reporting hierarchy by authorized users is instantly aggregated and processed through a set of rules that are defined in the Analysis Services cube. This allows the data updates to be immediately available to the decision makers, which makes the combination of Microsoft Excel connected to an OLAP cube into a real-time analytical decision support system.
The Microsoft Information Technology Business Intelligence Engineering team supports the Corporate Finance group where Microsoft Excel spreadsheets with PivotTables and OLAP CUBE functions are used for business process analysis and decision support. There are many instances where data that is used in the business models must be entered manually by authorized users, when that data is not available through a service or some other timely and easily available mechanism. For example, there is no data source that tracks articles about Xbox 360 games worldwide in a particular month. However, the leadership team may need this information for decision making on the first day of a month. This is where the Excel 2010 PivotTable write-back feature provides a codeless solution to data entry. Nonetheless, not all business models created in Microsoft Excel spreadsheets fit into the fixed tabular structure of the PivotTables. In many cases, users create asymmetric views and layouts using built into Microsoft Excel OLAP CUBE functions. Because of the heavy reliance on the CUBE functions, the development team had to take PivotTable write-back feature to the next level and develop a way to write data back to Analysis Services cube from any spreadsheet cell.
Since this approach is based on the CUBE functions that were introduced in Excel 2007 (even though the built-in PivotTable write-back functionality is available only in Office 2010), the concepts described in this article also work with the Office 2007 object model. This article assumes that the reader has the knowledge of Microsoft Office automation fundamentals and is familiar with the basics of the SQL Server Analysis Services and MDX.
Leveraging OLAP Data Sources for Write-back
An OLAP data source that supports UPDATE CUBE statement (for example, Microsoft SQL Server Analysis Services) may be configured to accept user inputs and can be used for data write-back from Microsoft Excel 2010 PivotTables without any custom code. For more information about OLAP data source setup, see the Additional Resources section. The remainder of this article describes free-form spreadsheet layout cell level write-back scenarios made possible with a small amount of custom code.
Exploring the MDX Property of the Range Object
In Microsoft Excel 2007, it became possible to pull data into Excel spreadsheets using OLAP functions. Any cell that contains an OLAP function (for example CUBEMEMBER, CUBEVALUE, or CUBERANKEDMEMBER) and does not evaluate to an error has a programmatically accessible property that contains Microsoft Excel validated MDX statement that the calculation engine uses to pull the data into the cell from the OLAP data source. To access this property, click a cell with a CUBE function and in the VBA Editor run the following statement in the "Immediate Window".
?ActiveCell.MDX
The returned MDX property is not a complete SELECT statement, but a tuple expression and will appear similar to the following code example.
([Geography].[Geography Hierarchy].&[779], [Time].[Time].[Fiscal Month].&[7], [KPI].[Scorecards].[KPI].&[1]&[101], [Measures].[Actual])
This property value is ideal for write-back scenarios because the same cell MDX statement that was used for data retrieval can also be used for data write-back without modification.
Important
While the data retrieval does not require that the user include all cube dimensions in the formula for getting the data it is critical to have all dimensions for data write-back if you want to avoid default allocation by the OLAP engine. For example, if you were to construct an UPDATE CUBE statement using the tuple expression above and removed the Geography dimension from it, the value entered would be allocated evenly among all the members of the Geography dimension according to Analysis Services default behavior.
Note
For simplicity, the examples that follow assume that the workbook contains a single connection used for both data retrieval and write-back.
Creating a Basic UDF for Data Write-back
The UPDATE CUBE statement for a single OLAP cube cell looks like the following code example.
UPDATE CUBE [ManualCube] SET
(
[Geography].[Geography Hierarchy].[United States],
[Time].[Time].[Fiscal Month].[July],
[KPI].[Scorecards].[KPI].&[1]&[101],
[Measures].[Actual]
)=1000;
You can use User Defined Function in the write-back trigger cell to construct, validate, and either execute the update statement during calculation or return an update trigger directive (for more information, see the following directive based scenario).
The recommended way to set up an OLAP write-back UDF that demonstrates the concept may have only two parameters and should return success or failure of the UPDATE CUBE statement:
Reference to a source cell that contains the CUBEVALUE function.
Reference to a manual data entry cell.
For example: =CubeWriteBack(H20,E20)
The code behind the UDF will verify the MDX property in the referenced source cell (H20 in the previous example), validate that manual entry cell (E20 in the example) is not empty, evaluate the values of both referenced cells for equality and if they are not equal it will construct and execute the UPDATE CUBE statement. Be aware that the updates are not permanent until they are followed by a COMMIT TRANSACTION statement executed in the same user session by using the same open connection instance.
The UDF will get an OLAP data source response regarding the outcome of the attempted update. This response should be evaluated in the UDF code and the result added in the trigger cell to notify the user of the success or failure of the cell value update operation.
Although this UDF example clearly shows the concept of the trigger cell-based write-back, it may not be the most efficient approach for some scenarios.
Creating a Directive-based OLAP Data Source Write-back UDF
It is more practical and efficient to execute the update statements in bulk. To support the bulk update scenario the UDF, instead of executing the update statement during calculation, should evaluate to a directive that will be used by another process. The directive contains instructions that instruct the bulk update process to use the information that is contained in the trigger cells to build an update statement programmatically. The user should have a user interface option to request the bulk update process when he or she finishes making manual data entries and decides to submit them. Figure 1 shows Microsoft Office Fluent ribbon example of custom user interface that contains an assigned to bulk update programmatic call button.
Figure 1. Sample User Interface
.jpg "Sample User Interface")
A directive-based OLAP write-back UDF still takes two cell references as parameters, but instead of issuing an update statement during cell calculation it returns a write-back directive:
Reference to a source cell that contains a CUBEVALUE function.
Reference to a manual data entry cell.
The "in trigger cell directive" version of the write-back function =CubeWriteBack(H20,E20) may return the following values that represent processing directives:
"UPDATE|MDXRetention!$H$20|MDXRetention!$E$20" is a pipe-delimited directive that contains tree parts. The first part of the directive: UPDATE, instructs the bulk update process that it has to issue an UPDATE CUBE statement. The second part contains cell reference that serves as a source of the MDX statement. The third part is a cell reference that contains the new value that the user wants to update in the data source.
"No Update from: MDXRetention!$H$20" where cell H20 is the cell with CUBE function, an MDX property and its cell value is equal to the user input cell value (E20 in our example).
"Input Range is Not Numeric!" will be returned if manual input cell E20 is empty or holds some other non-numeric value.
"Value Range does not contain valid MDX!" will be returned if cell H20 does not have valid MDX property.
In all cases except the first one the bulk update method code will not include trigger cell into the update statement building process.
The following code example is sample VBA code for a trigger cell UDF that returns a directive.
'**********************************************************************
'CubeWriteBack User Defined Function
'**********************************************************************
Function CubeWriteBack(valTestRange As Variant,inputRange As Variant) _
As String
Dim InputCell As Range
Dim ValueCell As Range
'Testing for valid input range input
On Error GoTo ICellRangeErrorHandler
Set InputCell = inputRange
'Testing for valid test value range input
On Error GoTo VCellRangeErrorHandler
Set ValueCell = valTestRange
'Ranges are valid
'Validate MDX in test value cell (used in Cube Update statement)
On Error GoTo ErrorHandler
'validating that both ranges contain only one cell
If InputCell.Cells.Count > 1 Then
CubeWriteBack = "Input Range contains multiple cells!"
Exit Function
End If
If ValueCell.Cells.Count > 1 Then
CubeWriteBack = "Value Range contains multiple cells!"
Exit Function
End If
'validating user input as numeric
If Not (IsNumeric(InputCell.Value)) Or _
IsEmpty(InputCell) Then
CubeWriteBack = "Input Range is Not Numeric!"
Exit Function
End If
'validating existence of MDX in the value test range
If Len(ValueCell.MDX) = 0 Then
CubeWriteBack = "Value Range doesn't contain valid MDX!"
Exit Function
End If
'TODO: move this first for performance? Assess!
If InputCell = ValueCell Then
CubeWriteBack = "No Update from: " & _
InputCell.Worksheet.Name & "!" & _
InputCell.Address
Else
'modified 8/4/09 to prevent possible returned MDX attack
CubeWriteBack = "UPDATE|" & _
InputCell.Worksheet.Name & "!" & _
InputCell.Address & "|" & _
ValueCell.Worksheet.Name & "!" & _
ValueCell.Address
End If
Exit Function
'Error Handlers
ICellRangeErrorHandler:
CubeWriteBack = inputRange & " is not a valid range!"
Exit Function
VCellRangeErrorHandler:
CubeWriteBack = valTestRange & " is not a valid range!"
Exit Function
ErrorHandler:
CubeWriteBack = "#N/A"
End Function
Building Directive Based UPDATE CUBE Statement
The directive based update statement is constructed by scanning all cells in the used range on each sheet. If the process finds that the cell contains CubeWriteBack function, it verifies that the cell value contains an update directive and builds the cumulative update statement. The following code example is the sample VBA code.
'**********************************************************************
'Function that builds update statement
'**********************************************************************
Function GetAllUpdateStatements() As String
Dim strMDX As String
Dim rangeAddress As String
Dim rangeWithUpdate As Range
Dim wks As Worksheet
Dim cubeName As String
For Each wks In ThisWorkbook.Worksheets
Set rangeWithUpdate = GetRangeForUpdate(wks)
If Not rangeWithUpdate Is Nothing Then
rangeAddress = rangeWithUpdate.Address
strMDX = strMDX & ConstructUpdates(rangeWithUpdate)
End If
Next
'Aborting update if there are no UPDATE statements to send
If Len(strMDX) = 0 Then Exit Function
cubeName = Range("CUBE_NAME")
GetAllUpdateStatements = "UPDATE CUBE [" & cubeName & "] SET "
GetAllUpdateStatements = GetAllUpdateStatements & strMDX
'performing clean up
If Right(GetAllUpdateStatements, 3) = "," & vbCr & vbCr Then
GetAllUpdateStatements = Left(GetAllUpdateStatements, _
Len(GetAllUpdateStatements) - 3) & ";"
End If
End Function
'**********************************************************************
'Function to retrieve the range of cells with values for OLAP update
'**********************************************************************
Function GetRangeForUpdate(wks As Worksheet) As Range
Dim rangeWithCWBFunc As Range
Dim rCell As Range
Dim i As Integer
'iterating through every cell to get the list of all cells that
'contain CWB OLAP update function
For Each rCell In wks.UsedRange.Cells
'collecting all cells that contain UDFs
If InStr(rCell.Formula, "CubeWriteBack") > 0 Then
'Testing to see if the range contains UPDATE directive
If Not Left(rCell.Value, 7) = "UPDATE|" Then GoTo SkipRange
'Add to the collection cells to be updated
If Not rangeWithCWBFunc Is Nothing Then
Set rangeWithCWBFunc = _
Application.Union(rangeWithCWBFunc, rCell)
Else
Set rangeWithCWBFunc = rCell
End If
End If
SkipRange:
Next
'Return the range
Set GetRangeForUpdate = rangeWithCWBFunc
End Function
'**********************************************************************
'Function to construct update statement string
'**********************************************************************
Function ConstructUpdates(updateRange As Range)
Dim rCell As Range
Dim updateStatement As String
Dim params() As String
For Each rCell In updateRange
'split and process using GetUpdateStatement
'add commit transact here?
params = Split(rCell.Value, "|")
updateStatement = updateStatement & _
GetUpdateStatement(Range(params(1)), Range(params(2))) & _
"," & vbCr & vbCr
Next
ConstructUpdates = updateStatement
End Function
'**********************************************************************
'Function to build individual OLAP Update Statement
'**********************************************************************
Function GetUpdateStatement(valueRange As Range, mdxRange As Range) _
As String
GetUpdateStatement = mdxRange.MDX
'modified 12/18/09: address non-US locale decimal separator issues
'valueRange is already confirmed to be numeric by the UDF,
'so straight replace of comma is valid
GetUpdateStatement = GetUpdateStatement & "=" & _
Replace(valueRange.Value, ",", ".")
End Function
The following code example is the resulting statement to update values entered in two cells.
UPDATE CUBE [ManualCube] SET
([Geography].[Geography Hierarchy].&[779],[Time].[Time].[Fiscal Month].&[7],[Measures].[Actual],[KPI].[KPI Name].&[1]&[101])=9280,
([Geography].[Geography Hierarchy].&[779],[Time].[Time].[Fiscal Month].&[7],[Measures].[Actual],[KPI].[KPI Name].&[1]&[102])=9681;
Important
-
Be aware of how the individual value update segments are separated. In MDX, individual value update segments are comma-separated.
-
To commit changes and make the data value updates from this cumulative update statement visible to other users, immediately execute the COMMIT TRANSACTION statement in the same user session (that is, on the same open connection).
Adding an Allocation Rule Parameter to the UDF
The UPDATE CUBE statement supports allocation rules. You can add another level of flexibility (and complexity) for the end-users by including an allocation rule parameter in the UDF. For more information about the exact syntax of the allocations rules, see your OLAP data source documentation. The following code example is based on the Microsoft SQL Server Analysis Services syntax.
The UDF that supports allocations can reference a named range that will contain allocation rule string.
For example: =CubeWriteBack(J1102,E1102,USE_EQUAL_ALLOCATION)
USE_EQUAL_ALLOCATION named range contains string USE_EQUAL_ALLOCATION. The reason for the named range reference recommendation is to enable support for more complex (non-constant) allocation rules like USE_WEIGHTED_ALLOCATION BY and USE_WEIGHTED_INCREMENT BY where complex allocation rule can be defined once in a single cell and used in many data write-back trigger cells by referencing that cell using descriptive named range.
The following code example is the resulting update statement with a simple equal allocation rule from the previous example.
UPDATE CUBE [ManualCube] SET
(
[Geography].[Geography Hierarchy].[United States],
[Time].[Time].[Fiscal Quarter].[Q1-2011],
[KPI].[Scorecards].[KPI].&[1]&[101],
[Measures].[Actual]
)=8228 USE_EQUAL_ALLOCATION;
In this case, the value of 8228 will be allocated evenly among all the months that have Q1-2011 as their parent.
Points to Consider When Planning to Use a Trigger Cell-based Write-back Approach
Consider the following when planning to use a trigger cell-based write-back approach:
The same MDX statement that is used for data retrieval is used for data write-back making spreadsheet setup less error prone.
Besides supporting free form spreadsheet layout the cell level based OLAP write-back approach also allows the authors of the spreadsheet to construct complex MDX statements for advanced data retrieval and write-back scenarios. For example, the user can construct CUBEVALUE functions to write-back directly to the .DATAMEMBER for scenarios where the dimension is modeled as a parent-child hierarchy and you want to avoid allocation to the children. This scenario can be performed by entering the following CUBEVALUE function into the source cell.
=CUBEVALUE(ConnectionFile,"[Geography].[Geography Hierarchy].&["&GeoID&"].DATAMEMBER,E$9,$C11,Time)The MDX property of this cell will resemble the following code example.
([Geography].[Geography Hierarchy].&[779].DATAMEMBER, [Measures].[Actual], [KPI].[KPI Name].&[1]&[101], [Time].[Time].[Fiscal Month].&[7])The update statement will write the cell value directly for the United States (parent member with ID 779) and will not allocate among its children (in this case, states).
Greater control over layout with cell referencing. Our business users decided to have a dedicated manual data entry sheet which is easy to achieve using cell referencing. Any cell with CUBE function (for example, on the sheet used for presentation view) referenced with an equal sign (=) in another cell (for example, on the manual data entry sheet) inherits MDX property of the parent cell.
Free form layout formula based spreadsheet generally will require more time to set up and maintain than a PivotTable.
The write-back to an OLAP data source should not be used for applications requiring a large amount of manual data entry. However, it does provide some of the best user experiences for the cases in which manual entries are used in complex calculations and the data should be available in real time. Based on the results of conducted tests the target should be to have 2000 or fewer manual cell updates per batch. On average, 2000 updates complete in 30-35 seconds per request from client computers located 11 time zones away from the OLAP data source (assuming adequate network connection speed). Application usage logs show that the users rarely have more than 200 cell updates per "Submit Manual Data" button click in their regular course of work. The majority of manual data submissions contain fewer than 10 cells per user click and complete in approximately 10-15 seconds from most of the remote locations.
Conclusion
This article shows that if you use data retrieval functions capable of maintaining original record dimensions (such as CUBE functions with MDX property in Excel), the trigger cell UDF approach will enable you to write back to the data source and make the updates available in real time to the users of that data source. More specifically, this article shows how Microsoft Excel CUBE functions together with the trigger cell UDFs and an OLAP data source that supports UPDATE CUBE statement can make Microsoft Excel spreadsheets with complex formula driven business rules into a data entry tool that supports real-time collaboration. This article also highlighted the fact that this approach can be extended to support more complex business scenarios: for example, using allocation rules and modifying values for specific members of the parent-child hierarchy.
Additional Resources
For more information, see the following resources: