Database Reference
In-Depth Information
can now be enforced. A Product cannot be added to the fact table unless it first exists
in the Product dimension table. The Product dimension table could have more level-0
Products than the fact table, but never the converse.
If Essbase's data and metadata always resided in a relational data store, you could
use this SQL concept of referential integrity to guarantee that bad data loads that result
from missing dimension metadata never occur. If all the sourcing systems were per-
fectly designed this way and all of the data came from the same system and not a little bit
from here and more from there, etc., then you would not need this chapter. your clients
will guarantee, and promise and swear, that this will be true. however, remember they
will only be looking at your cube and you were the last one to touch the data, so
you
will
be blamed if the data is bad. The notion of referential integrity seems like a blind alley.
or is it?
An extract, transform, and load (EtL) package has the power and flexibility to stage
data and metadata from source systems and Essbase itself. It can enforce fact data to
dimension metadata referential integrity regardless of the quality or composition of
the source, and, finally, transfer that fully constrained data and metadata to Essbase in
a fully automated manner. EtL then is the mechanism to Slay Bad Data.
Referential Integrity by Other Means
One important point to note: This chapter's path to referential integrity between fact data and dimension metadata is
accomplished not through foreign keys, but via routines that identify missing dimension metadata and programmatically
add those members to Essbase. This enforces metadata referential integrity by creating homes for the homeless. The
reasons for not using an approach involving the use of primary and foreign keys are threefold:
1. True referential integrity cannot be enforced between the fact data and Essbase dimension metadata because
Essbase is not a relational data source.
2. The timing of the values in fact data and the dimensional metadata are often likely to be dissimilar. Using the
example of Product, think of new Products that might be in a fact table before they are loaded to the Essbase
Product dimension.
3. Shared members or duplicate member hierarchies would break the primary key requirements on the dimension
metadata table. These primary keys could be expanded to multiple fields, but then the fact table would need to
carry corresponding extra fields to match as a foreign key.
2.3.5.1 Four Steps to Data Quality
The following four steps create referential integrity
between the fact data and Essbase.
1. Extract Essbase dimension metadata to a table to ensure the most current val-
ues. This is done after you run any of your existing metadata build rules. you
know, the ones that are supposed to have been supplying you perfect data.
2. Identify and load missing dimension metadata.
a.
Compare each dimension's extracted metadata to the data source(s) via SQL.
b. Load the metadata difference between the extracted Essbase dimensions
and those found in the fact data creating unassigned or suspense hierarchies
in each dimension (dimensional orphanages).
c. note that attribute dimensions are not considered as they are not needed
to find orphan data. Attributes are properties of dimension metadata and
thus are related, but not directly tied to the data.
3. Load the fact data
a.
Load the data, letting Essbase test for bad records knowing that they cannot
happen.
b.
Feel awfully clever as you now have bulletproof data loading.
