Microsoft KB Archive/299797

= Sample: Analysis Services Writeback Sample =

Article ID: 299797

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q299797



SUMMARY
This article presents a sample Appliance Mart database along with sample code that illustrates how to write back programmatically to a write-enabled OLAP cube. This sample uses both leaf level and cell allocation writebacks. The sample also shows an error message that can occur when you attempt writebacks that specify members of a virtual dimension below the All level.



MORE INFORMATION
The following file is available for download from the Microsoft Download Center:

Olapwriteback.exe

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How To Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

The Olapwriteback.exe file contains the following files:

A cell level writeback sets the value of a leaf level member directly, using the transaction methods of the Connection object. Cell allocation sets the value of a nonleaf member and specifies how the change should be distributed among the children of the member. For this method, it is not necessary to use the transaction methods of the Connection object.

The WriteBack Visual Basic sample application requires Microsoft Visual Basic 6.0 and uses Microsoft ActiveX Data Objects (ADO) and ActiveX Data Objects (Multidimensional) (ADO MD).

To install the sample files, extract the files that Olapwriteback.exe contains to a folder named C:\Writeback. After you extract the files, launch Analysis Server Manager and restore the DEMO OLAP database from the Demo.cab file. Verify that the Sales cube of the DEMO database is write-enabled. You should also write-enable the Warehouse cube of the FoodMart 2000 OLAP database.

Open the WriteBack Visual Basic sample application and open WriteBackForm. Double-click Execute Cell Level Write-Back. The CmdCellWriteBack_Click method calls the RunQuery method. In this sample, parameters are passed for the ROWS, COLUMNS, FROM, and WHERE clauses of an MDX statement. Two parameters that contain the updated values for the cells are also passed. When the RunQuery method is called, an MDX query is built from the passed parameters. In this example, a slicer is used to ensure that the query reaches the leaf level. After the application creates an ADO connection to the server, sends a query, and obtains an ADOMD Cellset, a transaction is initiated. The two new cell values are written back to the server and the transaction is committed. Run this code, then browse the writeback table of the DEMO Sales cube.

Double-click Execute Update Cube Using Equal Allocation. The CmdUpdateEqualAllocation_Click method builds an UPDATE CUBE command with USE_EQUAL_ALLOCATION, which is then passed as a parameter to the DoUpdate method. In this example, every atomic cell that contributes to the update is assigned an equal value. Existing values are overwritten by the new value. Run this code, then browse the writeback table of the DEMO Sales cube.

Double-click Execute Update Cube Using Equal Increment. The CmdUpdateEqualIncrement_Click method builds an UPDATE CUBE command with USE_EQUAL_INCREMENT, which is then passed as a parameter to the DoUpdate method. In this example, every atomic cell that contributes to the updated cell is updated. The updated value is additive and does not discount the existing value. Run this code, then browse the writeback table of the DEMO Sales cube.

Double-click Execute Update Cube Using Weighted Allocation. The CmdUpdateWeightedAllocation_Click method builds an UPDATE CUBE command with USE_WEIGHTED_ALLOCATION, which is then passed as a parameter to the DoUpdate method. In this example, every atomic cell that contributes to the updated cell is assigned an equal value that is determined by the ratio that is returned by the BY clause of the MDX statement. In this particular case, the ratio is the number of dryers sold in Texas divided by the number of washers sold in Texas. The new value effectively overwrites the existing value. Run this code, then browse the writeback table of the DEMO Sales cube.

Double-click Execute Update Cube Using Weighted Increment. The CmdWeightedIncrement_Click method builds an UPDATE CUBE command with USE_WEIGHTED_INCREMENT, which is then passed as a parameter to the DoUpdate method. In this example, every atomic cell that contributes to the updated cell is updated to reflect the existing value plus a value that is determined by the ratio that is returned by the BY clause of the MDX statement. In this particular case, the ratio is the number of cook tops sold in North Carolina divided by the number of ovens sold in North Carolina. Run this code, then browse the writeback table of the DEMO Sales cube.

Double-click Execute Update Cube From All Levels. The CmdUpdateFromAllLevel_Click method builds an UPDATE CUBE command with USE_EQUAL_ALLOCATION, which is then passed as a parameter to the DoUpdate method. In this example, none of the dimensions that are used in the query reach the leaf level and every atomic cell that contributes to the updated cell is assigned an equal value. Run this code, then browse the writeback table of the DEMO Sales cube.

Double-click Attempt Update Cube against Warehouse. The CmdUpdateVirtualDimension1_Click method builds an UPDATE CUBE command with USE_EQUAL_ALLOCATION, which is then passed as a parameter to the DoUpdate method. In this example, the lowest levels of the [Store Type] and [Store Size in SQFT] virtual dimensions are referenced, and as a result the query fails. The reason for the failure is that the references to levels in the two virtual dimensions below the All level cannot be resolved. Run this code and note the error message that is displayed.

Double-click Execute Update Cube against Warehouse. The CmdUpdateVirtualDimension2_Click method builds an UPDATE CUBE command with USE_EQUAL_ALLOCATION, which is then passed as a parameter to the DoUpdate method. In this example, the No levels of the [Store Type] and [Store Size in SQFT] virtual dimensions below the All level are referenced. Run this code, then browse the writeback table of the FoodMart 2000 Warehouse cube.

Additional query words: OLAP Sample Writeback Write back

Keywords: kbdownload kbdownload kbfile KB299797

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.