INF: Writebacks That Use Multi-Hierarchies Must Specify All Hierarchies (299926)


The information in this article 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

REFERENCES


For more information about the UPDATE CUBE statement, see the "UPDATE CUBE Statement" topic in SQL Server Books Online.
Modification Type:MinorLast Reviewed:4/24/2003
Keywords:kbinfo KB299926 kbAudDeveloper
©2002 Microsoft Corporation. All rights reserved.