2.4.3.1 Compare the Fact Data to the Dimension Just to be clear, we are comparing the meta-data references in the fact meta-data to the metameta-data in the dimension (again checking that all data has a home) and loading the missing dimensionality (creating homes for the homeless).
The package, Pkg_hmissingDimmembers, in Figure 2.10, loads missing dimension-ality through the following objects and steps:
1. vAr_Appname, vAr_Dbname, and vAr_maxLScriptname are Declare variables that receive their values through the calling Package Pkg_ LoopAndLoad.
2. vAr_object queries the dimension list in the vDimList relational view to get the dimension name based on the value of #vAr_kounter (not visible in this Package, but in the Pkg_LoopAndLoad calling code).
3. ProC_DatanotInmetaData compares the extracted Essbase level-0 dimension members for a single dimension against the fact table FactData and writes the results to the table hParentChild.
4. If hParentChild has only the default two records, a “no Action” message is piped to the LoadStatus table.
5. If hParentChild is more than two records long (these header records are always created), the maxL script LoadDim.msh is launched.
Table 2.4 Variable Types
Type Purpose
Set variable Assign a value at design time
Declare variable receive a value from another process as a parameter Evaluate variable test a variable value
refresh variable value a variable from code within the variable itself via SQL or other code
6. If LoadDim.msh’s StDErr produces a LoadDim.err file with a file length greater than zero bytes (the file is always generated regardless of error condi-tion), a failure notice is written to the LoadStatus table, else a success message is written.
2.4.3.2 Loop the Dimensions Every dimension represented in the fact data can potentially have orphan members not represented in the target Essbase database ASosamp. While the above Package is suitable for one dimension, this is no more functional than a single dimension Interface and, in fact, is quite a bit more compli-cated. going through this much trouble only makes sense if the Package’s logic can be encapsulated and called as a subroutine. Compiling the Package to the Scenario SCn_Pkg_hmISSIngDImmEmBErS makes Pkg_hmissingDimmembers an object that can referenced as needed and driven through a simple dimension loop for n dimensions instead of creating multiple Interfaces. That has been done in Pkg_
LoopAndLoad. And, for you oDI purists out there, please note that this reuse is exactly what would not have been possible without the use of parameterized SQL procedures.
Pkg_LoopAndLoad in Figure 2.11 loops the dimensions in the view vDimList and loads missing dimension members through the following objects and steps:
1. The Set variable counter vAr_kounter is set to 1. note: variables can be Set at design time and then refresh their value through code as shown in this Package.
2. vAr_Appname and vAr_Dbname are Declare variables set by the calling Load Plan PLAn_ASosamp. They pass their values to the compiled Pkg_hmissing-Dimmembers through the Scenario SCn_Pkg_hmISSIngDImmEmBErS.
3. vAr_Status is set to an initial value of “Start” for writing to the LoadStatus table.
4. ProC_LogheaderFooter writes a start status message to the table LoadStatus.
5. The refresh variable vAr_maxDimCount performs and stores a row count of the view vDimList.
6. The refresh variable vAr_LoopDimname gets the dimension name based on vAr_kounter’s value.
7. SCn_Pkg_hmISSIngDImmEmBErS tests the dimension value of vAr_
LoopDimname and loads missing metadata if necessary.
Figure 2.10 Package PKG_hMissingDimMembers.
8. vAr_kounter increments itself by 1, and tests to see if it is equal to vAr_
maxDimCount. If the values match, the flow branches to vAr_Status, if not, vAr_LoopDimname refreshes again.
9. When all of the dimensions in vDimList have been looped, ProC_
LogheaderFooter writes an end message to LoadStatus.
using the examples of data orphans in the years dimension, Figure 2.12 shows the years dimension before Pkg_LoopAndLoad is run.
Figure 2.13 shows the years dimension after Pkg_LoopAndLoad is executed with orphan members loaded under the parent Suspendyears, thus ensuring that the fact data rows that contain years 1911 and next year can be loaded to ASosamp.
Figure 2.11 Package PKG_LoopAndLoad.
Figure 2.12 Years dimension before PKG_LoopAndLoad.
Figure 2.13 Years dimension after PKG_LoopAndLoad.
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
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
years 1911
years next year
-- Modified: 11 November 2011, initial write -- Written by: Cameron Lackpour
-- Notes: This sample code is for the Years dimension only.
SELECT
-- PARENT0, CHILD0, and ALIAS0 are hardcoded
-- The concatenated dimension name completes the header SELECT
-- The dimension name, member name, and alias are all -- in the SuspendParent table
-- Missing members, the result of an INSERT SELECT statement SELECT
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
years Suspendyears Suspend years
Suspendyears 1911
Suspendyears next year
A Really Dynamic Essbase Dimension Load rule
It also would be nice if this dimension build table could be used with a single dimension Load rule; lazy programmers hate to build more Load Rules than absolutely required and if there is one thing I am, deep in my heart of hearts, it is that I am lazy. As an example, I think I am still working on mowing the back yard from my list of chores, circa 11th grade. As that was during the Reagan administration, you begin to understand my powers of procrastination.
A Really Dynamic Essbase Dimension Load rule
The standard way to build a Load rule is to assign it to a specific dimension and then assign the columns to the parent, child, alias, property, formula, UDA, etc. In a 10-dimension database like ASOsamp, with 10 possibly nonmatching dimen-sions in the fact data, many Load rules would have to be built and tested; 10 of them, actually.
Figure 2.14 is an example from Sample.Basic’s Parchil dimension Load rule. Note the header strings as there will be a quiz later on their significance:
• PARENT0, Product
• CHILD0, Product
• ALIAS0, Product
The standard way to build a dimension Load rule, be it Parent/Child, Generation, or whatever, is to assign via the Dimension Build Settings dialog box the dimension name and build method.
That is the standard way; the lazy programmer selects the Data Source Properties dialog box in the Load rule editor per Figure 2.15 and sets the “Record containing dimension building field name” Header property to 1.
With this set, all that is needed is a header record that looks just like a Parent/Child Load rule; this header record must include the dimension name. If the data source can supply that dimension information within the header, only one Load rule is required for all dimensions. One caveat, you must manually associate the Dimension Build Settings as Parent/Child for each one of the dimensions.
The Procedure PROC_DataNotInMetaData populates the table hParentChild with all of the information needed to load a suspense hierarchy and does it dynamically for each dimension. A simple SELECT statement in the Load rule (SELECT
* FROM hParentChild) gives Dynamic dimension building for all dimensions with one Load rule. Figure 2.16 illustrates the Year dimension suspense member dimension Load rule after it loads from the hParentChild table. It still is not much to look at. Oddly, the header records in the lower pane do not show the expected field type and generation information as per Figure 2.14; if this were sourced from a text file it would. One of the “mysteries” of at least my install of Essbase 11.1.2.1, but nothing to worry about.
What is important is that invisible record. Yes, I know, it cannot be seen in the Load rule, so take a look at row 1 of Figure 2.17. In the below query output, the first row contains PARENT0, Years, CHILD0,Years, and ALIAS0,Years. Those field values are the information Essbase needs to identify the dimension and the field properties, just as if they had been manually defined in the Load rule.
Putting It All Together
PROC_DataNotInMetaData finds orphaned members in fact data and dynmically creates parent/child hierarchies that the Load rule hDynSQL can use for all dimensions. This combination, when called by ODI Packages, reduces 20 potential
Figure 2.14 Sample.Basic’s Parchil Dimension Load rule.
objects in ODI (10 Interfaces) and Essbase (10 Load rules) to two overall. It has been my experience that the fewer the objects, the less there is to go wrong, Additionally, the Procedure and Load rule (the Load rule will need a one-time dimen-sion assignment per database) can be used across multiple Essbase databases. This flexibility and reuse underscores the flexibility and power of both ODI and Essbase, They really are that awesome.