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
