Microsoft KB Archive/299926

= INF: Writebacks That Use Multi-Hierarchies Must Specify All Hierarchies =

Article ID: 299926

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q299926



SUMMARY
If a cube contains multi-hierarchy dimensions, when you perform a writeback operation to cells by using the UPDATE CUBE statement, all the hierarchies of the dimensions must be specified in the SET clause. Specifying only one side of a multi-hierarchy will not implicitly specify the other sides of the hierarchy.



MORE INFORMATION
A multi-hierarchy dimension is in fact two or more separate and independent dimensions. Multi-hierarchy dimensions are not virtual dimensions. Analysis Services treats the multi-hierarchy dimensions as separate for the purpose of the UPDATE CUBE statement.

To demonstrate that Analysis Services treats the multi-hierarchy dimensions separately, use the following FoodMart 2000 database example:
 * 1) To generate a new multi-hierarchy dimension, copy the [Time] Shared Dimension to [MHTime].[MH1] and [MHTime].[MH2].
 * 2) Edit both of these new dimensions and set the All Level property to Yes.
 * 3) Create a new cube named [MHCube] by using sales_fact_dec_1998 as the fact table, which contains [store_sales] as a measure and include the two hierarchies of the [MHTime] dimension, [MH1] and [MH2].
 * 4) Change the data source to use the Microsoft Jet 4.0 OLE DB Provider. Otherwise, writebacks cannot be enabled.
 * 5) Right-click the [MHCube] cube, and then click Write-Enable to create the default writeback table.

You now have a write-enabled cube that contains a multi-hierarchy dimension. Browse the data and you can see that the only fact data is: Store Sales for [1998].[Q4].[12] = 120,160.84 Use the MDX Sample Application to connect to the FoodMart 2000 database, and issue the following MDX: update cube [MHCube] set ([MHTime].[MH1].[1998].[Q4].[12]) = 1000 USE_EQUAL_ALLOCATION Use the cube browser to browse the [MHCube] cube. You can see that with Store Sales on columns and [MHTime].[MH1] on rows, the cube has been updated as expected with the fact table and shows: Store Sales for [1998].[Q4].[12] = 1,000 However, with Store Sales on columns and [MHTime].[MH2] on rows, the update has been allocated across the complete [MHTime].[MH2] dimension and shows: Store Sales for each month = 41.67