Super Fast Saves with Relational Writeback for IBM Clarity

When Is It Useful?

Clarity is a budgeting and planning tool. As a budget deadline approaches, system usage skyrockets. Everyone has left their budget entry until the last possible moment. For that one day, when everyone is furiously trying to finish updating and approving their budgets, some systems grind to a half. Heavy save activity on a Clarity system can result in locking. This is when one user needs to wait for another user to finish saving before their changes can be saved. If there are only a few users on the system, it's not a big deal. When you have 20 people in the system all at the same time, where seconds seem like hours, this can be very frustrating and costly. Relational writeback options try to address this by optimizing the way data is saved back to the cube.

How Do I Do It?

There are some configuration steps to be accomplished before using relational writeback. A special writeback stored procedure is required too. This is something tailored to each cube to save the correct data back to the correct place in the fastest way. At the bottom is a sample writeback stored procedure for SQL 2008.

What Are The Different Writeback Types?

Writeback is a common term for the process of sending updates back to a cube. If a value in the is currently 100 and we want to update it 150, only the change (or delta) of 50 is sent back. We already have a row of data for the 100. We add a new row for 50, and that gives us the correct value of 150. Below is a list showing the various writeback methods. For anyone still using Clarity 6.x, there is a relational writeback for that version too, but it is not as reliable or fast as the one in Clarity 7.x.

• 0 = Native Analysis Services Writeback: This lets SQL Server Analysis Services do all the work. It doesn't require any additional stored procedures, but is the slowest option.
• 1 = Clarity custom write-back - cell by cell method: This method inserts the deltas to a relational table directly, one at a time. This is faster than the native method, but still slow because it loops through all the cells that changed, one at a time.
• 2 = Clarity custom write-back - SQL 2005: For folks using SQL 2005, one method is known...but SQL 2005 is not officially supported in Clarity 7. This option should never be used, but it's an option, so I list it for reference.
• 3 = Clarity custom write-back - SQL 2008: Last, but not least, this is the option most folks should select. This performs a fast writeback by doing an update in place. It also processes the changes as a set in one operation. This should always be the fastest option.

How Do I Know What Fields To Put In The Writeback Table, In What Order?

Clarity runs the following SQL Query to get the list of fields to be saved. The 'Amount' field should be first, and then each of the dimension fields returned by this query. The ordering of the dimension fields isn't important, but the names must match exactly. Also, if there are alternate hierarchies, they need to have a fake dimension column too for this writeback.

Select DimensionName
FROM Dimensions
WHERE CubeID IN (
  SELECT CubeID FROM Cubes
  WHERE lower(Alias) = '<cube alias from olap manager>'
 )
ORDER BY 
DimensionID

What are the different approaches?

Everything can be handled using a SQL stored procedure, but another approach is to setup two views that make the stored procedure much simpler. By setting up one view unioning all non-ROLAP partitions together and one view onto the writeback table, all the grouping stuff can be encapsulated into the view. This makes it a bit easier when building the stored procedure as you don't need to group everything in each part.

What If I Need Help?

It is very common to need a little help implementing this feature. I recommend contacting Clear Insight ( http://www.clearinsight.ca ) for help. They can help implement this for you and have you on your way quickly.

--Sample Writeback Stored Procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[stp_ClarityWB_2008]

/*This parameter must be named @WB_Amount*/

@WB_Amount <cube name>_WBTableType READONLY

AS

BEGIN

DECLARE @Message NVARCHAR(4000);

DECLARE @MemberName NVARCHAR(100);

DECLARE @Measure nvarchar(100);

/*CONFIGURABLE TRANSACTION ISOLATION LEVEL*/

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

begin transaction

--superset WB table for all measure groups

CREATE TABLE #WB (

[Amount] [float] NULL,

[Account] [varchar](80) null,

[Scenario] [varchar](80) null,

[Measure] [varchar](80) null

)

CREATE UNIQUE CLUSTERED INDEX [IX_#WB] ON [dbo].[#WB]

(

[Account] ASC,

[Scenario] ASC,

[Measure] ASC

)

--insert transaction data into temporary table

insert into #WB([Amount],[Account],[Scenario],[Measure])

select SUM(coalesce([Amount],0.0)),[Account],[Scenario],[Measure]

from @WBTYPE

group by [Account],[Scenario],[Measure]

--determine measure group

select top 1 @Measure=[Measure] from #WB

if (@Measure='Amount') begin

--Perform writeback for Measure Group Amount

--ensure only one measure from measure group is being written to

SET @MemberName=''

select TOP 1 @MemberName=measure from #WB where Measure<>'Amount'

if (@@ROWCOUNT>0) BEGIN

SELECT @Message = N'Incorrect Measure - ' + @MemberName + ';Cannot write to

multiple Measures from same dimension';

ROLLBACK TRANSACTION

RAISERROR (@Message,16,1);

RETURN;

END

--Validate account dimension to ensure it exists in dim table and is a leaf

member

SET @MemberName=''

SELECT @MemberName=WB.[account] FROM #WB WB where WB.[Account] not in (select

[Level0_MemberId] from tbldimaccount)

-- check for Unknown member values

if (@@ROWCOUNT>0) BEGIN

SELECT @Message = N'Cannot write data to an Undefined, All or Parent Account

(' + @MemberName + ')';

ROLLBACK TRANSACTION

RAISERROR (@Message,16,1);

RETURN;

END

--Validate scenario dimension to ensure it exists in dim table and is a leaf

member

SET @MemberName=''

SELECT @MemberName=WB.[scenario] FROM #WB WB where WB.[scenario] not in

(select [Level0_MemberId] from tbldimscenario)

-- check for unknown member values

if (@@ROWCOUNT>0) BEGIN

SELECT @Message = N'Cannot write data to an Undefined, All or Parent Scenario

(' + @MemberName + ')';

ROLLBACK TRANSACTION

RAISERROR (@Message,16,1);

RETURN;

END

--Delete records from writeback table that will be updated by Clarity

DELETE

FROM dbo.Rolap_Budget

WHERE EXISTS(

SELECT *

FROM #WB WB

WHERE dbo.Rolap_Budget.[Account]=WB.[Account] and dbo.Rolap_

Budget.[Scenario]=WB.[Scenario])

--Insert deltas for Clarity update into WB table

insert into Rolap_Budget ([Amount],[Account],[Scenario],sourcedetail,dtstamp)

select sum(WB.[Amount])-

sum(coalesce(FactGroup.[Amount],0.0)),WB.[Account],WB.[Scenario], system_

user,GETDATE()

from #WB WB left outer join

(

select fact.[Account],fact.[Scenario],SUM(coalesce(fact.amount,0.0)) as

Amount

from

(

select * from tblfactactual UNION ALL

select * from tblfactbudget

) fact

group by fact.[Account],fact.[Scenario]

) factGroup

ON WB.[Account] = factgroup.[Account] and WB.[Scenario] =

factgroup.[Scenario]

group by WB.[Account],WB.[Scenario]

having sum(WB.[Amount])-sum(coalesce(FactGroup.[Amount],0.0))<>0

end

ELSE IF (@Measure='Count') BEGIN

--Perform writeback for Measure Group Count

--ensure only one measure from measure group is being written to

SET @MemberName=''

select TOP 1 @MemberName=measure from #WB where Measure<>'Count'

if (@@ROWCOUNT>0) BEGIN

SELECT @Message = N'Incorrect Measure - ' + @MemberName + ';Cannot write to

multiple Measures from same dimension';

ROLLBACK TRANSACTION

RAISERROR (@Message,16,1);

RETURN;

END

--Validate account dimension to ensure it exists in dim table and is a leaf

member

SET @MemberName=''

SELECT @MemberName=WB.[account] FROM #WB WB where WB.[Account] not in (select

[Level0_MemberId] from tbldimaccount)

-- check for Unknown member values

if (@@ROWCOUNT>0) BEGIN

SELECT @Message = N'Cannot write data to an Undefined, All or Parent Account

(' + @MemberName + ')';

ROLLBACK TRANSACTION

RAISERROR (@Message,16,1);

RETURN;

END

--Delete records from writeback table that will be updated by Clarity

DELETE

FROM dbo.Rolap_Budget

WHERE EXISTS(

SELECT * from #WB WB

where dbo.Rolap_Budget.[Account]=WB.[Account])

--Insert deltas for Clarity update into WB table

insert into Rolap_Budget_Count ([Amount],[Account],sourcedetail,dtstamp)

select sum(WB.[Amount])-sum(coalesce(FactGroup.[Amount],0.0)),WB.[Account],

system_user,GETDATE()

from #WB WB left outer join

(

select fact.[Account],SUM(coalesce(fact.amount,0.0)) as Amount

from

(

select * from tblfactactualcount

) fact

group by fact.[Account]

) factGroup

on WB.[Account] = factgroup.[Account]

group by WB.[Account]

having sum(WB.[Amount])-sum(coalesce(FactGroup.[Amount],0.0))<>0

END

ELSE BEGIN

SELECT @Message = N'Measure "' + @Measure + '" not defined.';

RAISERROR (@Message,16,1);

RETURN;

END

--drop stored proc temp table

DROP TABLE #WB

commit transaction

END