Part 1. Setting up the Writeback partition for the database
Start application SQL Server Business Intelligence Development Studio (BIDS) from the Windows menu. In order to connect to database Adventure Works, choose command: File -> Open -> Analysis Services Database… in the menu.
Connect to database Adventure Works (Adventure Works DW 2008R2 in the example) and create a project.
Open the Solution Explorer tree and select folder Cubes in it. Open the folder and double-click cube [Adventure Works] to select it.
Then you need to create a Writeback partition for the cube. Switch to tab [Partitions] and select a measure group, for example, [SalesTargets].
Run command Writeback Settings… for the selected measure group from the local menu
and create table WriteTable_Sales_Targets
Save the changes and recalculate the cube by running command [Process] for the cube in project tree.
Table WriteTable_Sales_Targets will be created in the database as a result of the recalculation. This table will hold the changes of the cube data made in the Writeback mode.
Thus we have set up the Writeback partition for demo database Adventure Works.
Part 2. Setting up the MDX query and the update script
Let's continue the example of setting up and using the pivot grid from library Ranet OLAP in the Writeback mode.
Launch Sample Web Application from the Windows menu.
After the page was loaded, start Sample Silverlight Application by clicking the respective link.
After starting the application, configure the connection string for the demo database. Write the following in the OLAPConnectionString field:
- Database server (and its Instance, if necessary)
- Catalog
Check the settings by clicking button [Set OLAPConnectionString and check it]. If the check is successful, save the settings by clicking button [Save current values].
Now run the MDX Designer by clicking on the respective tab. Initialize the Mdx Designer control and create the pivot grid settings for the same measure group as we created the Writeback partition earlier. In this example, it is the [Sales Targets] measure group. For example, select measure [Sales Amount Quota] in the cube metadata tree and drag it to design area [Data]. Select hierarchy [Sales Territory Country] and drag it to the [Rows] area. Put the [Employee] hierarchy here too. Then select hierarchy [Date].[Calendar] and put it to area [Columns]. Deactivate buttons [NONEMPTY] in the toolbar and run the MDX query using command [Execute MDX Query].
The MDX query was generated automatically in accordance with the pivot grid design settings. You can see the text of the query in area [MDX Query]. Later we will use this MDX query in an example demonstrating the operation of the Writeback mode.
The current version of Ranet OLAP does not allow generating the Update Script automatically based on the design settings. It has to be configured manually. Because of this, it is not possible to use the pivot grid in the editing mode directly in the MDX Designer. This shortcoming makes the example somewhat complicated, and it restricts the possible use of the components. It will be eliminated in the next coming build of the Ranet OLAP. They are going to implement functionality similar to What-If Analysis in Microsoft Excel.
Select the text of the MDX query and copy it to the clipboard.
Switch to the [Mdx Query] tab in the demo application and paste the text of the MDX query from the clipboard into this area.
Now switch to the [Mdx Update Script] tab of in the demo application and create a template for the UPDATE CUBE command using the MDX language syntax
UPDATE CUBE [Adventure Works]SET(
)
= <%newValue%>
Where <%newValue%> is the updated (new) cell value. Use <%oldValue%> if you need to refer to the old cell value in the script.
The update script will refer to the cube hierarchies that are used in the visible part of the pivot grid, in its rows and columns. You need to copy them to the script.
Return to tab [Mdx Designer] and select [Properties] in the local menu for the data cell in the pivot table in area [Query result].
Switch to tab [Tuple] in the Cell Properties window and copy the contents of the tuple to the clipboard.
The copied details of the hierarchies comprising the tuple will be used for programming the update script.
Switch to tab [Mdx Update Script] and paste the copied text to area SET.
UPDATE CUBE [Adventure Works]SET(
[Date].[Calendar].[All Periods], [Employee].[Employee].[All Employees], [Sales Territory].[Sales Territory Country].[All Sales Territories]
)
= <%newValue%>
Now modify the pasted text: substitute the value of the hierarchy, to which the element belongs, enclosed in <%%>, for the full key of the measure element. For example, substitute <%[Date].[Calendar]%> for [Date].[Calendar].[All Periods], and so on.
After the modification the update script will look like this:
UPDATE CUBE [Adventure Works]
SET
(
<%[Date].[Calendar]%>
,<%[Employee].[Employee]%>
,<%[Sales Territory].[Sales Territory Country]%>
)
= <%newValue%>
Now we need to add measure [Measures].[Sales Amount Quota] to the template, as it was not present in the Tuple.
As a result, the update script will look like this:
UPDATE CUBE [Adventure Works]SET(
<%[Date].[Calendar]%>
,<%[Employee].[Employee]%>
,<%[Sales Territory].[Sales Territory Country]%>
,[Measures].[Sales Amount Quota]
)
= <%newValue%>
All settings have been configured. Now we can run the pivot grid in the editing mode.
Important things to address when programming the update script:
- Consider the amount of data in the measures of the measure group for which Writeback is configured. The UPDATE CUBE command can freeze the SQL Server if the measure contains too many elements and writing rules are not specified in the update script.
- If data filters are used in the MDX query, then the filter values must be specified explicitly in the update script. You can't use filters returning multiple elements. The element must be defined uniquely, or it can be (All).
Part 3. Editing data in the pivot grid
Switch to tab [Pivot Grid] in the demo application and click the [Initialize PivotGrid] button to initialize the pivot grid.
To simplify the example, open the folders for tree elements in the rows and columns of the pivot grid to show empty fields in the data input area (so that the input area looks similar to the figure below).
Select a cell (for example, France, A.Scott Wright, H2 CY 2001) and enter data (for example, 1000). If all settings are correct, the pivot grid will look similar to the figure below. Congratulations.
If you get an error when running the command, you need to analyze it. Run SQL Server Profiler and enter the data once again. Then inspect the query execution log.
Now you can test various data editing modes in the pivot grid:
- Postpone calculation
Enables or disables the use of cache memory when editing cells. The changes are accumulated in the cache memory until they are sent to the server using the Save Changes command. Otherwise, the data are saved automatically after each change.
- To cancel the change, click the [Rollback Transaction] button.
- To save changes to the server, click the [Commit Transaction] button.
- To copy data from one slice to another, use command [Copy value] in the local menu.
- To propagate the cell value (for example, to child elements), use command [Deliver value] in the local menu.
You can find more details at:
