Sidebar: The MS IT Pattern for Custom Writeback Applications
NOTE This is part of a series of entries on the topic of Building a Writeback Application with Analysis Services.
Writeback to cubes through Excel applications is used extensively within Microsoft to support a number of our internal processes, including our mid-year business review. Sergei Gundorov and Pablo Trejo Montemayor with MS IT outline how writeback fits into this process within this white paper. It’s well worth a read.
As you might imagine, supporting a custom application written in Excel within a company the size of Microsoft can be challenging. To address this, Sergei and Pablo have focused on standardizing the implementation of the writeback code and extending the capabilities of Excel Services in SharePoint to manage the code base. And they do this in a manner that retains the flexibility that drives most analysts to employ Excel. Check out these white papers to explore the details of what they are doing:
- Enabling Write-back to an OLAP Cube at Cell Level in Excel 2010
- Creating Business Applications by Using Excel Services and Office Open XML Formats
Focusing on the Excel application aspects of what Sergei and Pablo are doing, the process in a nutshell is to use the Excel cube functions to retrieve data from a cube, have users enter data in associated Excel cells, use a custom function to evaluate the user’s input, and then pull all the valid data input together to form an UPDATE CUBE statement that is then submitted and committed to Analysis Services. If you are just getting started with Excel cube functions, you might find that starting with a PivotTable containing the data you want and then converting the PivotTable to formulas (based on cube functions) is the easiest way to go. If you’d like to review a working demo that incorporates Sergei and Pablo’s code and follows the basic Excel pattern, please check out the sample macro-enabled workbook associated with this entry.