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

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.

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: Use the MDX Sample Application to connect to the FoodMart 2000 database, and issue the following MDX: 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: However, with Store Sales on columns and [MHTime].[MH2] on rows, the update has been allocated across the complete [MHTime].[MH2] dimension and shows:

REFERENCES

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