Database Reference
In-Depth Information
2.4.2 Comparing Dimensionality
With ASosamp's dimensions in a table, SQL can be used to compare the extracted
dimensionality to the data to be loaded
before
the fact data is loaded, so there are
no
data load errors. This is the referential integrity discussed previously with after-the-fact
enforcement. If the fact data contains members that are not in Essbase, special suspense
or unassigned members can act as a temporary load location so that at least the data
gets counted at the total dimension level. It would be nice to know if this occurred, on
a dimension-by-dimension basis, and when, so an administrator can identify which
dimensions need attention (there really is a proper place for those missing members,
somewhere) and how often this unassigned load occurs.
2.4.2.1 Typical ODI Approach
In order to implement the discovery, repair, and report
tasks discussed above, the typical process would be to build a series of Interfaces to
perform the “are all of the data's members in Essbase” test and then load the difference
to suspense parents. Each Interface would be hard coded to the Essbase application and
database and indeed to each dimension. This requires lots of dragging and dropping and
general guI-ness (graphical user interface), repeated 10 times, once for each dimension.
moving this functionality to another database would require recreating all of the
hard coded Interfaces (and that really frosts my cookies). I will not even touch the whole
spaces-in-dimension-names-make-oDI-throw-a-rod issue.
2.4.2.2 he Better Way
Do not misunderstand me, oDI, despite its quirks, is a power-
ful and flexible tool that can do almost anything. That flexibility is manifested in oDI's
step-based Packages. They can accommodate the standard guI way as outlined above
or combine Interfaces, variables, oS objects, and especially SQL-rich Procedures into
a portable and powerful combination that loops a database's dimensions, loads missing
dimensionality, and logs process status. This approach is just a little more complicated
to set up than a series of Interfaces, but, oh so much more flexible.
The above paragraph's promotion of Procedures is likely to send oDI purists into an
architectural tizzy because:
1. Procedures use code.
2. objects within Procedures are not dynamically updated.
There can be no question that Procedures use code, which is antithetical to the spirit
of guI EtL tools. however, in the context of the solution this chapter provides, and
when used with restraint, Procedures within Packages are far more flexible and reus-
able than Interfaces. moreover, if you do not plan on using oDI, you can take the SQL
in the Procedures and script it in the language of your choosing. That flexibility of
reuse and transferability would be hard to duplicate in object-based Interfaces.
I will cop to the charge that Procedures are not dynamically updated the way
Interfaces are, with columns added or removed as the logical schema changes, although
variable name changes do propagate through. more importantly, we will mitigate this
lack of dynamic updating within procedures by using variables to parameterize the SQL
in the procedures in the next section.
2.4.2.3 Code, Variables, Procedures, and MaxL
Again, the guI in oDI is great, except
when it is not. When a graphical representation of an EtL process is simple and direct,
it is the way to do things, such as the reversing of Essbase dimensions to SQL. however,
