Relational Writeback

Relational writeback in XLCubed allows writeback operations to call SQL Stored Procedures - these procedures can be used to store additional data (i.e. text commentary) or perform advanced transformation on the written values before applying them to the cube.

Analysis Services Cube Configuration

• The cube needs to be enabled for writeback, using the standard approach,
• An additional table (henceforth RWritetable, mirroring the fact table structure, is created in the source SQL database, and added into the datasource view,
• An additional ROLAP Partition is added to the AS database, using the RWritetable,
• The standard writeback table needs to remain in place although is essentially unused.

Analysis Services 2008 R2

In Analysis Services 2008 R2, the additional table RWritetable is an optional step. Instead, it is possible to change the storage mode of the Writeback table to be ROLAP, it is then possible for the Stored Procedure to make updates directly to the standard Writeback table (NB. if using this approach then the SQL updates cannot update the same slice/measure being written back by the user, but can update other measures/slices. This is useful, for example, in a Workflow situation to move data from one slice to another).

Stored Procedure Config

A stored procedure must be created within the source SQL database, configured to the rules outlined below. It is only this stored procedure which will be called from XLCubed when a relational writeback is invoked, though it can of course call other procedures as required. The fundamental job of the procedure will be to populate the RWritetable.

Procedure Name

• [dbo].[XL3_DatabaseName_CubeName_Writeback]
  • Replace any spaces in the database or cube name with ‘_’

Parameter Names

• @Dimension_Hierarchy
  • (Hierarchy unique name without the [ ], replace “.” With “_”)
  • The values for members will be the member keys, passed as NVarChar(max)
• @Value
  • Values will be passed as T-SQL float (.Net double).
  • For measures the measure caption will be passed as there is no row key.

Example

A very simple example procedure follows. (This is the simplest case, anticipating the entered value as Budget, rather than handling the possibility of several measures being available for entry):

AS Database Name

Retail

AS Cube Name

Retail Forecast

Created Relational Writeback table

RetailRelWriteback

CREATE PROCEDURE [dbo].[XL3_Retail_Retail_Forecast_Writeback]
	@Accounts_ChartOfAccounts nvarchar(50),
	@Measures nvarchar(50),
	@Period_Period nvarchar(50),
	@Unit_Category1 nvarchar(50),
	@Unit_Category2 nvarchar(50),
	@Unit_Category3 nvarchar(50),
	@Unit_Category4 nvarchar(50),
	@Unit_Category5 nvarchar(50),
	@Unit_Organisation nvarchar(50),
	@Value double precision
AS
BEGIN
	
	SET NOCOUNT ON;

	insert into [dbo].[RetailRelWriteback](
		[Account],
		[Unit],
		[Period],
		[Budget],
		[Forecast],
		[Actual],
		[Phasing],
		[cCurrency],
		[nEntity]
	)
	
	values(
		@Accounts_ChartOfAccounts,
		@Unit_Organisation,
		@Period_Period,
		@Value,
		NULL,
		NULL,
		NULL,
		NULL,
		1
	)
	
END

GO

XLCubed Client Settings

By default, the writeback setting will be standard Analysis Services. To switch to relational:

• Go to XLCubed –> Options -> Workbook Options, and then to the writeback tab. Here the method to be used can be varied at a connection level as shown below:

• Then click on the Configure button for the connection to specify the relational database source

Once relational is specified, the writeback process will be unchanged from a user perspective, but will call the stored procedure specified in steps 1 and 2.

It's worth noting that the change is now applicable for the connection, so relational writeback will now be used for grids as well as the writeback formula (XL3LookupRW and XL3DoWriteback)