Introduction
Some allocation planning Forms also allow manual input, as shown below.
Example
The Distribution Method drop-down decides whether to perform top-down allocation or bottom-up aggregation. Here is the definition of the stored procedure:
ALTER PROCEDURE [dbo].[USR_DISTRIBUTE_TRADESPEND]
@EntityID SMALLINT,
@ProductID SMALLINT,
@ScenarioID TINYINT,
@TimeID INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DISTRIBUTE TABLE
(
AccountID INT,
[Total Amount] FLOAT,
[Distribution Method] NVARCHAR(20)
)
INSERT INTO @DISTRIBUTE
SELECT AccountID
,[Total Amount]
,DD.MemberLabel [Distribution Method]
FROM
(
SELECT AccountID
,DM.MemberLabel [METRIC]
,F.Value
FROM [dbo].[F_Trade Spend_CoreMG_Writeback] F
INNER JOIN D_Time DT
ON F.TimeID = DT.MemberId
INNER JOIN D_Metric DM
ON F.MetricID = DM.MemberId
WHERE DT.FiscalYear = 20140000
AND ScenarioID = 2
AND EntityID = 1
AND ProductID = 42
) F
PIVOT
(
MAX(VALUE) FOR [METRIC] IN
(
[Total Amount]
,[Distribution Method]
)
) AS PVT
INNER JOIN D_Dropdown DD
ON PVT.[Distribution Method] = DD.MemberId
DECLARE @AmountID SMALLINT = (SELECT MemberId FROM D_Metric WHERE MemberLabel = N'Amount')
--DELETE MONTHLY AMOUNT ON NON-MANUAL SPREAD ROWS
DELETE F
FROM [dbo].[F_Trade Spend_CoreMG_Writeback] F
INNER JOIN D_Time DT
ON F.TimeID = DT.MemberId
INNER JOIN @DISTRIBUTE D
ON F.AccountID = D.AccountID
WHERE DT.FiscalYear = @TimeID
AND EntityID = @EntityID
AND ProductID = @ProductID
AND ScenarioID = @ScenarioID
AND MetricID = @AmountID
AND D.[Distribution Method] <> N'Manual'
--INSERT MONTHLY AMOUNT INTO ROWS WITH DISTRIBUTION METHOD SET TO 'BY MONTH'
INSERT INTO [dbo].[F_Trade Spend_CoreMG_Writeback]
([AccountID]
,[TimeID]
,[EntityID]
,[LineItemID]
,[MetricID]
,[ProductID]
,[ScenarioID]
,[Value]
,[Status])
SELECT
[AccountID]
,DT.MemberId
,@EntityID
,1 [LineItemID]
,@AmountID [MetricID]
,@ProductID
,@ScenarioID
,D.[Total Amount] / 12
,1
FROM @DISTRIBUTE D
INNER JOIN D_Time DT
ON 1 = 1
WHERE D.[Distribution Method] = N'By Month'
AND DT.Granularity = N'Month'
AND DT.FiscalYear = @TimeID
--INSERT MONTHLY AMOUNT INTO ROWS WITH DISTRIBUTION METHOD SET TO 'BY QUARTER'
INSERT INTO [dbo].[F_Trade Spend_CoreMG_Writeback]
([AccountID]
,[TimeID]
,[EntityID]
,[LineItemID]
,[MetricID]
,[ProductID]
,[ScenarioID]
,[Value]
,[Status])
SELECT
[AccountID]
,DT.FiscalMonth
,@EntityID
,1 [LineItemID]
,@AmountID [MetricID]
,@ProductID
,@ScenarioID
,D.[Total Amount] / 4
,1
FROM @DISTRIBUTE D
INNER JOIN D_Time DT
ON 1 = 1
WHERE D.[Distribution Method] = N'By Quarter'
AND DT.Granularity = N'Quarter'
AND DT.FiscalYear = @TimeID
DECLARE @TotalAmountID SMALLINT = (SELECT MemberId FROM D_Metric WHERE MemberLabel = N'Total Amount')
DECLARE @DefaultMonthID INT = (SELECT MIN(MemberId) [TimeID]
FROM D_Time DT
WHERE DT.Granularity = N'Month'
AND DT.FiscalYear = @TimeID)
--DELETE TOTAL AMOUNT ON MANUAL SPREAD ROWS
DELETE F
FROM [dbo].[F_Trade Spend_CoreMG_Writeback] F
INNER JOIN @DISTRIBUTE D
ON F.AccountID = D.AccountID
WHERE @TimeID = @DefaultMonthID
AND EntityID = @EntityID
AND ProductID = @ProductID
AND ScenarioID = @ScenarioID
AND MetricID = @TotalAmountID
AND D.[Distribution Method] = N'Manual'
--INSERT TOTAL AMOUNT INTO ROWS WITH DISTRIBUTION METHOD SET TO 'MANUAL'
INSERT INTO [dbo].[F_Trade Spend_CoreMG_Writeback]
([AccountID]
,[TimeID]
,[EntityID]
,[LineItemID]
,[MetricID]
,[ProductID]
,[ScenarioID]
,[Value]
,[Status])
SELECT
F.[AccountID]
,@DefaultMonthID
,@EntityID
,1 [LineItemID]
,@TotalAmountID [MetricID]
,@ProductID
,@ScenarioID
,SUM(F.Value)
,1
FROM @DISTRIBUTE D
INNER JOIN [dbo].[F_Trade Spend_CoreMG_Writeback] F
ON F.AccountID = D.AccountID
INNER JOIN D_Time DT
ON F.TimeID = DT.MemberId
WHERE D.[Distribution Method] = N'Manual'
AND DT.FiscalYear = @TimeID
AND F.EntityID = @EntityID
AND F.ProductID = @ProductID
AND F.ScenarioID = @ScenarioID
AND F.MetricID = @AmountID
GROUP BY F.AccountID
END
If a user requests to perform bottom-up aggregation, whenever a user touches the monthly amount (without first setting the Distribution Method to Manual), we will need to modify the stored procedure.
In this scenario we would need to store the calculated top-down allocation data into the Calculated partition. In the beginning of the calculation, we scan the Writeback partition to see if there is any data in the Monthly amount region. If yes, we know we should apply bottom-up aggregation on these rows, otherwise we can go with the Distribution Method selections.