Relational Writeback

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

Analysis Services Cube Config

• 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 to 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.

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