Database Reference
In-Depth Information
ON N."#VAR_Object" = D.MemberName
WHERE D.MemberName IS NULL
Command on Target
INSERT INTO <%=odiRef.getObjectName("DataNotInMetadata")%>
VALUES(:DimensionName, :Member)
Truncate hParentChild
Command on Target
TRUNCATE TABLE <%=odiRef.getObjectName("hParentChild")%>
Create ParentChild load format
Command on Source
SELECT
A.Parent,
A.Member,
A.Alias
FROM
(
-- Header record
SELECT
'900' AS 'Order',
'PARENT0,' + P.DimName AS 'Parent',
'CHILD0,' + P.DimName AS 'Member',
'ALIAS0,' + P.DimName AS 'Alias'
FROM <%=odiRef.getObjectName("SuspendParent")%> P
WHERE P.DimName = '#VAR_Object'
-- Join header and body
UNION
-- Parent record
SELECT
'800' AS 'Order',
P.DimName AS 'Parent',
P.SuspendParent AS 'Member',
P.SuspendAlias AS 'Alias'
FROM <%=odiRef.getObjectName("SuspendParent")%> P
WHERE P.DimName = '#VAR_Object'
UNION
-- Missing members
SELECT
'700' AS 'Order',
S.SuspendParent AS 'Parent',
D.MemberName AS 'Member',
'' AS Alias
FROM
<%=odiRef.getObjectName("DataNotInMetadata")%> D
INNER JOIN <%=odiRef.getObjectName("SuspendParent")%> S
ON D.Dimension = S.DimName
) AS A
ORDER BY [Order] DESC
