Editing data in the Pivot Grid

The document includes the following sections:
  • Setting up the Writeback partition for the database Adventure Works
  • Setting up the MDX query and the UPDATE SCRIPT
  • Editing data in the Pivot Grid
  • Examples of the code

Use the version Ranet OLAP 2.5 for Silverlight.

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.
RUI_writeback_bids01.png
  • Connect to database Adventure Works (Adventure Works DW 2008R2 in the example) and create a project.
RUI_writeback_bids02.png
  • 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
RUI_writeback_bids03.png
and create table WriteTableSalesTargets
RUI_writeback_bids04.png
  • Save the changes and recalculate the cube by running command Process for the cube in project tree.
RUI_writeback_bids05.png
  • Table WriteTableSalesTargets 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.
RUI_writeback_ssms01.png

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.
RUI_writeback_swa01.png
  • After the page was loaded, start Sample Silverlight Application by clicking the respective link.
RUI_writeback_swa02.png
  • After starting the application, configure the connection string for the demo database. Write the following in the OLAPConnectionString field:
    1. Database server (and its Instance, if necessary)
    2. 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.
RUI_writeback_swa03.png
  • 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 NON EMPTY in the toolbar and run the MDX query using command Execute MDX Query.
RUI_writeback_swa04.png

_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.
RUI_writeback_swa05.png

Switch to the Mdx Query tab in the demo application and paste the text of the MDX query from the clipboard into this area.
RUI_writeback_swa05_1.png

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 http://technet.microsoft.com/en-us/library/ms145488(v=sql.105).aspx

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.
RUI_writeback_swa06.png

Switch to tab Tuple in the Cell Properties window and copy the contents of the tuple to the clipboard.
RUI_writeback_swa07.png

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:
  1. 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.
  2. 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).
RUI_writeback_swa08.png
  • 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.
RUI_writeback_swa08_1.png

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.
RUI_writeback_swa08_2.png
  • 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.
RUI_writeback_swa08_3.png

You can find more details at:
http://www.galantis.com/ranet/writeback.html
http://www.galantis.com/ranet/pivot-grid-commands.html

Part 4. Examples of the code

The Ranet OLAP Installer includes a Demo Solution with Silverlight Demo Application.
RUI_writeback_vs01.png

You can open the project in Visual Studio 2010/2012 and study the examples, how visual elements of Ranet OLAP library are used.
RUI_writeback_vs02.png

Last edited Apr 3, 2013 at 4:11 PM by Leschenok, version 19