Optimizing Cell Writeback in Microsoft SQL Server 2000 Analysis Services
Summary: Learn about improvements in cell writeback, a key feature of SQL Server 2000 Analysis Services, and learn how to optimize cell writeback performance. (6 printed pages)
A primary purpose of business intelligence is to provide useful, business-centric information for strategic planning. Microsoft® SQL Server™ 2000 Analysis Services supports strategic planning in many ways, one of which is by providing the capability to temporarily or permanently write values back to a set of cells within a cube. This capability, called cell writeback, supports strategic planning by facilitating interactive "what if" analyses. You can temporarily write values to a cube and examine the resulting aggregations locally, without actually changing data on the Analysis server. Once you have completed your planning activity and have a useful set of values, you can permanently write the values to the cube.
Permanently writing a value to a leaf cell in a cube requires an individual writeback operation, which writes a single record to a writeback table. However, permanently writing a value to a non-leaf cell can require hundreds of thousands of individual writeback operations, each of which writes a single record to a relational database. The value for a non-leaf cell must be allocated across all of the leaf cells that are subordinate to the non-leaf cell, and then the values of the leaf cells are aggregated to provide the value of the non-leaf cell. Because this process can require a high volume of individual transactions, permanent cell writeback operations can significantly affect the performance of the underlying relational database.
SQL Server 2000 Service Pack 3 (SP3) improves the performance of cell writeback in Analysis Services by taking advantage of the bulk insert feature in SQL Server 2000 when performing permanent cell writeback operations on non-leaf cells. This white paper includes technical information and usage guidelines for this updated feature.
This paper assumes that you have a basic knowledge of relational database concepts, a working knowledge of Analysis Services, and that you have installed SP3.
The Multidimensional Expressions (MDX) UPDATE CUBE statement is used when values are permanently written to non-leaf cells in Analysis Services. Because the value of a non-leaf cell is based on the values of its subordinate leaf cells, the UPDATE CUBE statement uses the value and allocation method supplied for the non-leaf cell to write one record representing the allocated value of each subordinate leaf cell in the writeback table.
Before SP3, each record was written to the underlying relational data source individually, using the IRowsetChange interface of the OLE DB provider for the data source. While this was efficient enough for writeback operations involving a change to a single leaf cell, for writeback operations involving changes to non-leaf cells, the interface was potentially called hundreds of thousands of times before a single non-leaf cell was updated.
With the release of SP3, Analysis Services can now use the IRowsetFastLoad interface, implemented by the Microsoft OLE DB Provider for SQL Server, to perform bulk insert operations. This interface greatly improves the performance of cell writeback operations on non-leaf cells by inserting all of the information necessary to update the subordinate leaf cells in one transaction. The functionality of the IRowsetFastLoad interface is identical to that provided by the bcp utility in SQL Server 2000 for bulk insert operations.
You use the
SSFastLoadOptions registry key to enable and configure this feature. The registry key, located at
accepts a comma-delimited string value.
Note To use the improved cell writeback functionality, you must use a Microsoft SQL Server 2000 data source to store the writeback table and the Microsoft OLE DB Provider for SQL Server to access the data source. Otherwise, the
SSFastLoadOptionsregistry key is ignored.
To enable the feature, supply any value other than <disabled>. Setting the registry key to an empty string ("") enables the feature and uses the default functionality specified for each option.
The following table describes the options that are available with the SSFastLoadOptions registry key.
|<disabled>||Disables bulk insert capability for cell writeback and specifies that the server use the IRowsetChange interface instead of the IRowsetFastLoad interface to perform cell writeback operations. This is the default value.|
|ORDER (column [ASC | DESC] [, . . . n])||Specifies the sort order of the data in the rowset. Bulk copy performance is improved if the data being loaded is sorted according to the clustered index on the table. If the rowset is sorted in a different order, or if there is no clustered index on the table, the ORDER hint is ignored. The names of the columns must be valid columns in the destination table. By default, the cell writeback operation assumes the rowset is unordered.
Note Use of this parameter is not recommended for cell writeback, because the SSFastLoadOptions registry key applies cell writeback operations for all databases, and corresponding SQL Server 2000 data sources, on the Analysis server.
|ROWS_PER_BATCH = bb||Specifies the number of rows of data for each batch (as bb). Using this value results in the entire cell writeback rowset being sent to the server as a single transaction. The server optimizes the cell writeback operation load according to the value bb. By default, ROWS_PER_BATCH is unknown.|
|KILOBYTES_PER_BATCH = cc||Specifies the approximate number of kilobytes (KB) of data for each batch (as cc). By default, KILOBYTES_PER_BATCH is unknown.|
|TABLOCK||Applies a table-level lock for the duration of the cell writeback operation. Using this value significantly improves performance because holding a lock only for the duration of the cell writeback operation reduces lock contention on the table. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load.|
|CHECK_CONSTRAINTS||Specifies whether constraints on the destination table are checked during the bulk copy operation. By default, constraints are ignored.|
|FIRE_TRIGGERS||Specifies that insert triggers defined on the destination table execute during the bulk copy operation. If FIRE_TRIGGERS is not specified, insert triggers do not execute.|
It is recommended that you use one of the following string values for the
SSFastLoadOptions registry key:
- TABLOCK, FIRE_TRIGGERS
- An empty string ("")
If you use the TABLOCK value, you can get optimal performance during cell writeback but triggers placed on the writeback table are not executed. If triggers are necessary, use the value TABLOCK, FIRE_TRIGGERS instead.
If you use the FIRE_TRIGGERS option, INSERT and INSTEAD OF triggers on the writeback table execute for all rows that are inserted by the cell writeback operation. In addition, the row-insert operations performed during the cell writeback operation are fully logged to the transaction log, potentially filling the transaction log and causing unexpected results. Use the FIRE_TRIGGERS option only if triggers are necessary to ensure the consistency of your data.
When you use the bulk insert feature with cell writeback, most of the performance benefits occur during the commit phase. During the commit phase, the data in the caches maintained by Analysis Services is submitted to the SQL Server 2000 data source.
To further optimize cell writeback performance using SQL Server 2000 databases, configure the SQL Server 2000 database and writeback table to enable a minimally logged bulk copy operation. This configuration prevents the transaction log from filling up because of the high volume of SQL Server row-insert operations that can be performed during an Analysis Services cell writeback operation.
For more information about minimally logged bulk copy operations in SQL Server 2000, see "Logged and Minimally Logged Bulk Copy Operations" in SQL Server 2000 Books Online.
The following list details additional steps you can take to ensure optimal performance of cell writeback operations:
- Configure the database to use the simple or bulk-logged recovery model.
SQL Server 2000 can use either the simple or bulk-logged recovery model for minimally logged bulk copy operations, but the bulk-logged recovery model can still write enough information during very large cell writeback operations to fill the transaction log. Use the simple recovery model to ensure that the cell writeback operation does not inadvertently fill the transaction log.
You can use the ALTER DATABASE statement, as shown in the following examples, to change the recovery model:
ALTER DATABASE database SET RECOVERY SIMPLE
ALTER DATABASE database SET RECOVERY BULK_LOGGED
For more information about how to set the recovery model in Enterprise Manager, see "How to set the recovery model for a database (Enterprise Manager)" in SQL Server 2000 Books Online.
Important If you configure the database to use the simple recovery model, you must also set the select into/bulkcopy database option to true.
You can use the sp_dboption system stored procedure to set the select into/bulkcopy database option, as shown in the following example:
sp_dboption @db_name = 'database', @optname = 'select into/bulkcopy', @optvalue = true
- Configure the database to ignore torn pages.
Torn page detection, a useful feature in SQL Server 2000 for ensuring data integrity, increases the amount of disk and logging activity that is required to perform a cell writeback operation and can affect performance.
You can use the ALTER DATABASE statement, as shown in the following example, to turn off torn page detection:
ALTER DATABASE database SET TORN_PAGE_DETECTION OFF
For more information about how to set torn page detection in Enterprise Manager, see "How to change the configuration settings for a database (Enterprise Manager)" in SQL Server 2000 Books Online.
- Do not configure the writeback table for replication.
Depending on the type of replication you use, the table may be required to support features that prevent minimally logged bulk copy operations. For example, transactional replication depends on transaction log entries when replicating data, and merge replication changes the structure of the table and requires triggers to replicate data.
- Do not create triggers or indexes on the writeback table.
Although an index on the writeback table can improve performance during read operations, write operations are considerably slower because the row-insert operations are logged. To determine whether an index is appropriate for the writeback table, see "Optimizing Bulk Copy Performance" in SQL Server 2000 Books Online.
SQL Server Books Online contains more information about Analysis Services. For additional information, see the following resources: