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

Veralteter Haftungsausschluss für KB-Inhalte

Dieser Artikel wurde für Produkte geschrieben, für die Microsoft keinen Support mehr anbietet. Deshalb wird dieser Artikel im vorliegenden Zustand bereitgestellt und nicht mehr aktualisiert.


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 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


For more information about the UPDATE CUBE statement, see the "UPDATE CUBE Statement" topic in SQL Server Books Online.

Artikelnummer: 299926 – Letzte Überarbeitung: 21.11.2006 – Revision: 1