Database Reference
In-Depth Information
Command on Target
INSERT INTO <%=odiRef.getObjectName("hParentChild")%>
VALUES(:Parent, :Member, :Alias)
2.6.1.4.2 PROC_LogHeaderFooter
Purpose—
Write “Start” and “End” to the
LoadStatus table.
Code Steps
Write Status
Command on Target
INSERT INTO <%=odiRef.getObjectName("LoadStatus")%> VALUES('#VAR_
AppName.#VAR_DbName', 'LoadDim','N/A','#VAR_Status', SYSDATETIME() )
2.6.1.4.3 PROC_FactVsEssbase
Purpose—
Compare the data values in Essbase via a
report Script output and the summed fact data.
Code Steps
Compare data in Fact versus data from Essbase
Command on Target
-- Purpose: Compare the data values in the Fact table to the
-- upper level Essbase export
-- Modified: 11 November 2011, initial write
-- Written by: Cameron Lackpour
-- Notes:
-- Declare temporary table to hold results of SUMmed Fact
-- and Essbase extract
DECLARE @Validate TABLE(
Years NVARCHAR(80),
[Time] NVARCHAR(80),
ID INT,
[Original Price] MONEY,
[Price Paid] MONEY,
[Returns] MONEY,
[Units] MONEY,
[Transactions] MONEY
)
-- Aggregate the Fact table
INSERT INTO @Validate
SELECT
Years,
[Time],
1,
SUM([Original_Price]) AS 'Original Price',
SUM([Price_Paid]) AS 'Price Paid',
SUM([Rtns]) AS 'Returns',
SUM([Units]) AS 'Units',
SUM([Transactions]) AS 'Transactions'
FROM
<%=odiRef.getObjectName("FactData")%>
WHERE
-- I leave it to the reader the simple ODI exercise of adding and
-- valuing variables to drive Year and Time.
