Relational writeback in Anaplan XL allows writeback operations to call SQL stored procedures. These steps can be used to store more data, like text comments, or make more changes to the written values before using them in the cube.

Analysis Services Cube Configuration

Enable the cube for writeback, using the standard approach.

An additional table,‌ 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 it is essentially unused.

Analysis Services 2008 R2

In Analysis Services 2008 R2, the additional table RWritetable is an optional step. Instead, you can change the Writeback table's storage mode to be ROLAP. This will permit the stored procedure to make changes directly to the standard Writeback table.

If using this approach, then the SQL updates can't 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 configuration

Create a stored procedure  within the source SQL database, configured to the rules outlined below. Anaplan XL will only call this stored procedure when a relational writeback is called. It can also call other stored procedures as needed. The basic 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

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

Anaplan XL client settings

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

Go to Anaplan XL > 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:

Anaplan XL Client settings

Now, select on Configure for the connection to specify the relational database source.

Once you select relational, the writeback process will be the same for the user. But it'll call the stored procedure you set in steps one and two.

The change is now available for the connection. Relational writeback will now be used for grids and the writeback formula (XL3LookupRW and XL3DoWriteback).