Slay Bad Data in Essbase - Developing Essbase Applications - page 63

Years = 'Curr Year' AND
[Time] = 'Jul'
GROUP BY Years, [Time]
--Retrieve the values exported from Essbase
INSERT INTO @Validate
SELECT
Years,
TimePeriod AS 'Time',
2,
OriginalPrice AS 'Original Price',
PricePaid AS 'Price Paid',
Rtns AS 'Returns',
Units,
Transactions
FROM <%=odiRef.getObjectName("ValidateASOsamp")%>
--Do the variance
TRUNCATE TABLE <%=odiRef.getObjectName("ValidationVariance")%>
INSERT INTO <%=odiRef.getObjectName("ValidationVariance")%>
SELECT
F.Years,
F.[Time],
F.[Original Price] - E.[Original Price] AS 'Fact vs. Essbase
OP Var',
F.[Price Paid] - E.[Price Paid] AS 'Fact vs. Essbase PP Var',
F.[Returns] - E.[Returns] AS 'Fact vs. Essbase Rtns Var',
F.Units - E.Units AS 'Fact vs. Essbase Units Var',
F.Transactions - E.Transactions AS 'Fact vs. Essbase Trans Var'
FROM
@Validate F
INNER JOIN @Validate E
ON F.ID != E.ID AND F.Years = E.Years AND F.[Time] = E.[Time]
WHERE F.ID = 1
2.6.1.4.4 PROC_TruncateVariance Purpose— Clear out the table validationvariance
when the maxL script validate.msh fails.
Code Steps
Truncate variance table ValidationVariance
Command on Target
TRUNCATE TABLE <%=odiRef.getObjectName("ValidationVariance")%>
2.6.1.4.5 PROC_LogFailureStatus Purpose— Write out Essbase application and data-
base, maxL script name, Dimension, Failure flag, and date and time to the LoadStatus table.
Code Steps
Write Status
Command on Target
INSERT INTO <%=odiRef.getObjectName("LoadStatus")%> VALUES('#VAR_
AppName.#VAR_DbName', '#VAR_MaxLScriptName','#VAR_
Object','Failure', SYSDATETIME() )
2.6.1.4.6 PROC_LogNoActionStatus Purpose— Write out Essbase application and
database, maxL script name, Dimension, no Action flag, and date and time to the
LoadStatus table.