Database Reference
In-Depth Information
Figure 2.10
Package PKG_hMissingDimMembers.
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.
