Database Reference
In-Depth Information
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 11
th
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.
