Introducing Dimension Writeback

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.

Up to this point, we’ve focused exclusively on the update of measures using cell writeback. When we need to insert, update or delete dimension records, a different form of writeback, i.e. dimension writeback, is employed. Far less frequently used than cell writeback, dimension writeback is a very useful technique when we need to give analysts control over dimension data.

NOTE As a reminder, dimension writeback is not available in SQL Server Standard edition.

Like cell writeback, dimension writeback is performed by establishing an (ADO or ADO.NET using the MSOLAP provider) connection to Analysis Services and submitting statements to affect the writeback operation. However, there is no writeback cache with dimension writeback. 

Instead, the operation is performed immediately through Analysis Services and against the associated table (or updateable view) in the relational database. There is no separate step to discard (rollback) or publish (commit) the changes. In addition there is no inherent audit trail retained for the data changes and the operation must meet all the constraints imposed on the relational database table, e.g. provide values for non-nullable fields, if the operation is to succeed. In this regard, Analysis Services simply provides a consistent point of interaction for the writeback application.

NOTE Permission to perform dimension writeback is often limited to a small subset of the application’s users. In addition to meeting the security requirements of Analysis Services, the connection through which interactions with the underlying dimension table take place must have the permissions required to perform the associated DML operations within the relational database. Without these permissions, an error will be generated in the relational database and surfaced through Analysis Services.

From a structural perspective, a dimension supporting writeback must be mapped to a single table in the data source view. That table should map to a single table or updateable view in the associated relational data source. The dimension must also have its WriteEnabled property set to True, and users without full control of the Analysis Services database must be assigned Read/Write permission to the dimension through a role. Then, depending on the type of dimension being written to, i.e. a parent-child or regular dimension, the application must submit either the ALTER CUBE MDX statement or one of three XMLA statements, respectively. Writeback to parent-child and regular dimensions are addressed separately in the next two blog entries.

Finally, on the topic of security, end-users you wish to perform dimension writeback must be granted Read/Write permissions on the dimension(s) to which you wish to allow them to write. This can be assigned to the dimension at the database level or to cube dimensions (at the cube level), though database-level rights make the most sense to me personally. Users with Full Control Permission on a database and AS admins also have permission to write data (assuming the dimension other wise meets the requirements for this feature).