Edit

Share via

Partitions - Write-Enabled Partitions

  • Article

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

The data in a cube is generally read-only. However, for certain scenarios, you may want to write-enable a partition. Write-enabled partitions are used to enable business users to explore scenarios by changing cell values and analyzing the effects of the changes on cube data. When you write-enable a partition, client applications can record changes to the data in the partition. These changes, known as writeback data, are stored in a separate table and do not overwrite any existing data in a measure group. However, they are incorporated into query results as if they are part of the cube data.

You can write-enable an entire cube or only certain partitions in the cube. Write-enabled dimensions are different but complementary. A write-enabled partition lets users update partition cells, whereas a write-enabled dimension lets users update dimension members. You can also use these two features in combination. For example, a write-enabled cube or a write-enabled partition does not have to include any write-enabled dimensions. Related topic:Write-Enabled Dimensions.

Note

If you want to write-enable a cube that has a Microsoft Access database as a data source, do not use Microsoft OLE DB Provider for ODBC Drivers in the data source definitions for the cube, its partitions, or its dimensions. Instead, you can use Microsoft Jet 4.0 OLE DB Provider, or any version of the Jet Service Pack that includes Jet 4.0 OLE. For more information, see the Microsoft Knowledge Base article How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine.

A cube can be write-enabled only if all its measures use the Sum aggregate function. Linked measure groups and local cubes cannot be write-enabled.

Writeback Storage

Any change made by the business user is stored in the writeback table as a difference from the currently displayed value. For example, if an end user changes a cell value from 90 to 100, the value +10 is stored in the writeback table, together with the time of the change and information about the business user who made it. The net effect of accumulated changes is displayed to client applications. The original value in the cube is preserved, and an audit trail of changes is recorded in the writeback table.

Changes to leaf and nonleaf cells are handled differently. A leaf cell represents an intersection of a measure and a leaf member from every dimension referenced by the measure group. The value of a leaf cell is taken directly from the fact table, and cannot be divided further by drilling down. If a cube or any partition is write-enabled, changes can be made to a leaf cell. Changes can be made to a nonleaf cell only if the client application provides a way of distributing the changes among the leaf cells that make up the nonleaf cell. This process, called allocation, is managed through the UPDATE CUBE statement in Multidimensional Expressions (MDX). Business intelligence developers can use the UPDATE CUBE statement to include allocation functionality. For more information, see UPDATE CUBE Statement (MDX).

Important

When updated cells do not overlap, the Update Isolation Level connection string property can be used to enhance performance for UPDATE CUBE. For more information, see ConnectionString.

Regardless of whether a client application distributes changes that were made to nonleaf cells, whenever queries are evaluated, changes in the writeback table are applied to both leaf and nonleaf cells so that business users can view the effects of the changes throughout the cube.

Changes that were made by the business user are kept in a separate writeback table that you can work with as follows:

  • Convert to a partition to permanently incorporate changes into the cube. This action makes the measure group read-only. You can specify a filter expression to select the changes you want to convert.

  • Discard to return the partition to its original state. This action makes the partition read-only.

Security

A business user is permitted to record changes in a cube's writeback table only if the business user belongs to a role that has read/write permission to the cube's cells. For each role, you can control which cube cells can and cannot be updated. For more information, see Grant cube or model permissions (Analysis Services).

See Also

Write-Enabled Dimensions
Aggregations and Aggregation Designs
Partitions (Analysis Services - Multidimensional Data)
Write-Enabled Dimensions