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