Database Reference
In-Depth Information
The Building Blocks
The second step in slaying bad data is “Identify and load missing dimension data.” The ODI Packages PKG_hMissingDim-
Members and PKG_LoopAndLoad are the structure for performing that discovery and dynamic dimension building. Within
that framework, three key components must be created and executed:
1. Finding the missing members.
2. Building a parent/child table to load those missing members to a suspense parent.
3. Creating an Essbase Load rule to read the parent/child table.
Finding Those Missing Members
A simple SQL query can test the fact data against the extracted Dimensions table for the Years dimension as shown
below. In parameterized form, this code is the “Find Non Matching Members” step of PKG_hMissingDimMember's
Procedure PROC_DataNotInMetaData.
-- Purpose: Write to DataNotInMetaData all members in the fact table
-- not in the Dimensions table
-- Modified: 11 November 2011, initial write
-- Written by: Cameron Lackpour
-- Notes: This sample code is for the Years dimension only.
-- The LEFT OUTER JOIN of FactData to Dimensions where the
-- Dimensions result set is Null gives us ALL of the records
-- in FactData that don't match Dimensions.
-- Clear out the target table
TRUNCATE TABLE DataNotInMetadata
--
Find those missing members
INSERT
INTO DataNotInMetadata
SELECT DISTINCT
'Years' AS Dimension,
F.Years AS 'Years'
FROM FactData F
LEFT OUTER JOIN Dimensions D
ON F.Years = D.MemberName
WHERE D.MemberName IS NULL
Given that there are rows in the fact data that have in the [Years] column “1911” and “Next Year” in addition to the
expected [Curr Year] and [Prev Year] Years dimension members, the query output looks like Table 2.5.
This SELECT result describes the members that are not in Essbase, but are in the data. This is a start, but not enough
to load the dimension to Essbase. While 1911 and Next Year could be loaded to the Years dimension as is without specify-
ing a parent, they would automatically go in at generation one. In a dimension like Years, that is fine. But, it is clearer to
put them under a parent that highlights that they have been added, such as [Suspend]. Furthermore, if the dimension had
alternate hierarchies, then Suspend would simply be the top of a new alternate hierarchy. Therefore, we must give as a
grandparent of [Suspend] either the dimension name (if no alternates) or the name of the Primary hierarchy. Taking care
of this detail would require a hard coded value for each dimension. But, that is easily accomplished and it is suggested to
the reader as an exercise.
For now, just focus on the base case where there is only a primary hierarchy in each dimension. What is needed is a
query that creates a complete parent/child or recursive table with a predetermined parent.
Build That Parent/Child Table
There are three parts to this query: the header, the parent record, and finally those missing members found above. To get
this requires only a little string manipulation and a sort order field to get everything in the right order as shown in the
segment of code below.
--
Purpose:
Create the content of a dimension load rule from
--
missing metadata
Table 2.5
Missing Members
Dimension
MemberName
ye a r s
1911
ye a r s
next year
