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

-- Modified: 11 November 2011, initial write

-- Written by: Cameron Lackpour

-- Notes: This sample code is for the Years dimension only.

SELECT

A.Parent,

A.Member,

A.Alias

FROM

(

-- Header record

-- PARENT0, CHILD0, and ALIAS0 are hardcoded

-- The concatenated dimension name completes the header

SELECT

'900' AS 'Order',

'PARENT0,' + P.DimName AS 'Parent',

'CHILD0,' + P.DimName AS 'Member',

'ALIAS0,' + P.DimName AS 'Alias'

FROM SuspendParent P

WHERE P.DimName = 'Years'

-- Join header and body

UNION

-- Parent record

-- The dimension name, member name, and alias are all

-- in the SuspendParent table

SELECT

'800' AS 'Order',

P.DimName AS 'Parent',

P.SuspendParent AS 'Member',

P.SuspendAlias AS 'Alias'

FROM SuspendParent P

WHERE P.DimName = 'Years'

UNION

-- Missing members, the result of an INSERT SELECT statement

SELECT

'700' AS 'Order',

S.SuspendParent AS 'Parent',

D.MemberName AS 'Member',

'' AS Alias

FROM

DataNotInMetadata D

INNER JOIN SuspendParent S

ON D.Dimension = S.DimName

) AS A
ORDER BY [Order] DESC
Given the above output from the table DataNotInMetadata, Table 2.6 reflects this query's output.
The above is a dimension source ready for loading in every detail and completely data-driven. In parameterized form,
this SQL code is the “Create ParentChildLoadFormat” step in PROC_DataNotInMetaData.
Note that the [Suspend] member name is actually generated as “Suspend” + DimensionName with DimensionName
coming from the SuspendParent table in the middle SELECT. If I had not done that then there would be duplicate members
if more than one dimension needed “Suspend” members. In some cases, the users may want the suspend members to be
at a specific place in the outline. That can be handled when you enhance this solution with the “Suspend Grandparent”
needed to deal with Alternate hierarchies as mentioned above.

Table 2.6 Missing Members with Dimension and Suspense Parent
Parent Child Alias

PArEnt0,years ChILD0,years ALIAS0,years

ye a r s Suspendyears Suspend years

Suspendyears 1911

Suspendyears next year