Incorrect data may be written to the writeback table when you use the USE_WEIGHTED_ALLOCATION allocation method to update a cube in Analysis Services (822718)

SYMPTOMS

With Microsoft SQL Server 2000 Analysis Server, you can write-enable a cube. This allows the client applications to record changes to the cube's data. These changes are known as writeback data. The writeback data is stored in the writeback table. The writeback table resides on the cube's data source. The writeback table is treated as a relational OLAP (ROLAP) partition that is separate from the cube and separate from the cube's underlying data. In some rare cases, an UPDATE CUBE statement that includes a USE_WEIGHTED_ALLOCATION allocation method with a default weight_expression can write incorrect data (-1.#IND) to the writeback table.

CAUSE

This problem occurs when both of the following conditions are true:

The multidimensional OLAP (MOLAP) partition and the relational OLAP (ROLAP) partition have different precisions for the float data type.
The SELECT statement and the UPDATE statement are run in a different order.

The following example helps explain the problem.

The MOLAP partition is the fact table. The MOLAP partition has the following data:

2000_w1 5.95100024813888E-10
2000_w2 6.73600009104547E-10
2000_w3 7.16399994882266E-10
2000_w4 7.54299955829651E-10
2000_w5 8.57700022116603E-10

The ROLAP partition is the writeback table. The ROLAP partition has the following data:

2000_w1 -5.9510085748115671E-10
2000_w2 -6.7360000910454687E-10
2000_w3 -7.1639999488226636E-10
2000_w4 -7.5429995582965148E-10
2000_w5 -8.5770002211660312E-10

How the aggregration calculation works

The aggregation calculation has the following two orders.

Order 1

Rounds up the writeback table data to digit 15, and then adds the rounded values to the fact table values. The calculated aggregation is 0.0.

Order 2

Sums up each partition, and then adds the two sums. The calculated aggregation is 4.99E-16.

The default weight_expression of the USE_WEIGHTED_ALLOCATION allocation method is the "original cell value" divided by the "original aggregate value" ([Original cell value]/[Original aggregate value]). If you use the method that is shown in Order 1 to calculate the "original aggregate value", a "division by zero" error occurs, and "-1.#IND" is written to the writeback table.

WORKAROUND

To work around this problem, use one of the following methods.

Method 1

If you can, set up a business rule that will check for a "division by zero" error. For example, use a business rule that is similar to the following:

if [Original aggregate value] = 0 or < 1.0E-15

If the statement evaluates as true, you can roll back the transaction to prevent incorrect data from being written to the writeback table.

Method 2

Check the value of the updated cell before you commit the transaction. When you update a cube, the changes stay on the local computer until you commit the transaction. Therefore, you should only commit the final result. This helps avoid the unpredictable problems that are caused by changing the values in the same cells too many times and causing many small values to be written to the writeback table.

Method 3

Either periodically or on demand, use a filter to save the writeback table data that you want to a partition, and then write-enable the cube again. This helps avoid the problems that are caused by having unnecessary data in the writeback table. This also improves the performance of the corresponding queries.