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.
- Replace any spaces in the database or cube name with ‘_’
- (Hierarchy unique name without the [ ], replace “.” With “_”)
- The values for members will be the member keys, passed as NVarChar(max)
- 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
- AS Cube Name
- Retail Forecast
- Created Relational Writeback table
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.