Database Reference
In-Depth Information
4. validate to load source.
a. Extract total values from Essbase
b. Sum data in SQL fact table
c. Compare Essbase data to SQL
Why this last step? Because sometimes even you make a mistake (you are only
human) and sometimes even your code fails to correctly identify or plan for or fix bad
data. Just an example: Essbase member names and aliases cannot begin with a space
or an open parentheses and cannot contain certain nonprintable characters (such as
a <ctrl> z). If a homeless dimensional member like this is found in your fact data and
you try to build a home for this orphan, it will be rejected. Do you know every one of
these possible gotchas? And, even if you did, you would have to fix it, not just by creat-
ing an acceptable metadata member, but you also would have to fix the fact data file.
Another reason validation is critical is complexity. It is possible that the data is there,
but something else is amiss in the design, such as a partition definition.
to paraphrase President ronald reagan, “trust in your data, but verify.”
2.3.6 Tool Selection, or Why There Aren't Any Perfect Solutions
As in real life, there are a variety of ways to accomplish these four steps with each dem-
onstrating a different approach to
Slaying Bad Data
in Essbase via EtL. here is a partial
list by category:
Custom Scripting
• Some combination of scripting languages, e.g., vBScript, PowerShell, Perl,
SQL and/or, maxL
Metadata Management
• oracle hyperion Data relationship management, Fusion Edition (Drm)
Data Loading
• oracle hyperion Financial Data Quality management, Fusion Edition (FDm)
Warehouse-Based Builders
• oracle Essbase Integration Studio (EIS)
• oracle Essbase Studio
Data Quality
• FDm (a rg uably)
• oracle Data Quality for Data Integrator (oDQ)
• oracle Data Profiling (oDP)
Extract, Transform, and Load
• hyperion Data Integration management (DIm) aka Informatica
• IBm DataSphere
• The late, not so great, hyperion Application Link (hAL)
• oracle Data Integrator (oDI)
2.3.7 Why You Need ODI
Although these are all worthy products, many of them deal with only part of the EtL
puzzle and require combination with other technologies, e.g., Drm addresses metadata
only, FDm tackles data only, Studio can solve data quality issues, but only when sourc-
ing from a data warehouse with full referential integrity.
