Database Reference
In-Depth Information
Table 2.4
Variable Ty
pes
Ty p e
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
there are many use-cases that show the purely graphical approach to be too rigid; this
is where code comes into play. happily, oDI anticipates this need by providing refresh
variables, Procedures, and oS Command objects, all of which support code.
variables are just that, variables, but there are four different kinds, all of which are
used in this chapter's Packages per table 2.4.
A given variable can have multiple types within a Package. A variable can start out as
a refresh variable to get a value via an SQL query and then become an Evaluate variable
so that the result can be tested. This polymorphism can be confusing, but is a very pow-
erful concept. All that it takes is dragging and dropping the variable into the Package
twice or more and then setting the variable type. refresh variables typically, but not
always, use SQL to provide single numeric or alphanumeric values.
Procedures are a set of commands (just like variables, many different languages are
supported although this example only uses SQL) that perform extracts and transforma-
tions and return sets of values. As a bonus, within the context of Essbase and oDI, they
can be considerably more flexible than Interfaces as we shall see.
Alas, there is no maxL knowledge module for oDI. however, that is not an issue as
maxL scripts can be launched through oDI's oS Command object. make that maxL
script just as parameterized as you need; I have stopped short of the full treatment to
make it somewhat more readable.
2.4.3 Load Missing Dimensionality to Essbase
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 data to the metadata 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.
