Building a Write-Enabled Cube
NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.
To build a writeback application, you need a write-enabled cube and an application with which to interact with it. I’ll cover the application in my next post, but for now I’ll focus on the cube.
The design of the cube depends on the model you wish to employ. The key thing to keep in mind is that the concerns about data volumes, scalability, maintenance and processing cycles we typically weigh when designing a cube do not come into play as much with a write-enabled cube. The write-enabled cube is often of limited scope, receives smaller and less regular data updates, and is used by smaller numbers of more specialized (often expert) users. These cubes are sometimes mapped to a data warehouse but more frequently are built against a specialized data mart. There are always exceptions but the point I want to drive home is that you are not typically injecting writeback into the middle of a traditionally built OLAP cube.
As a cube designer, the key thing to concern yourself with are the limitations imposed on the measures and measure groups of a write-enabled cube. Any measure to which you wish to writeback data must be mapped to values in the data source view, i.e. not calculated members defined in the cube script. These measures must employ signed numeric data types and use the SUM aggregation function. And, the measure group housing a writeback measure must employ regular relationships only.
Thinking back to the cube in the simple demonstration, you may be curious how these restrictions were overcome. The cube seemed to employ a many-to-many relationship between the measure group housing the Score measure and the Scenario dimension and the Score measure itself employed a weighted average for aggregate values. All this was handled with a little sleight-of-hand in the cube script. While not a requirement, write-enabled cubes tend to make very heavy use of the cube script to both work around the limitations imposed on the cube and implement the model’s calculation logic.
Returning to the writeback measure, if you intend to commit (publish) data the limitations on the writeback measure apply to all measures in its measure group. To clarify, if you only intend to have users perform writeback on a measure and then discard their changes, measures in the same measure group as the measure to which the user is writing do not have to follow the rules above. However, if you wish to commit the data (which is the much more typical case) ever measure in the group must adhere to these rules, regardless of whether they receive writeback modification.
To house the committed writeback data for the measure group, you must setup a writeback partition per these instructions. This creates a writeback table in a relational database for which you have established a data source connection. (This is typically the same source to which the measure group is mapped, but you have flexibility should you need it.) Keep in mind that the account used with the data source must have the permissions required to create this table when the cube is published and read and write permissions to it once it is deployed.
On the topic of security, any end-users you wish to perform writeback to the cube must be granted Read/Write permissions at the cube level. By setting read/write permissions on the cell data, you can more narrowly define to which cells a user can write. Users with Full Control Permission on the database and Analysis Services administrators also have read and write permissions across the cube.