Database Reference
In-Depth Information
tab, the ephemeral nature of these RAM-based processing areas makes debugging impossible as once the process is
finished, any temporary tables are destroyed. Additionally, while the in-memory engines are fast, they do not scale well as
they are limited by available memory.
A better approach is to use a separate SQL data store that has already been defined as an ODI Data Model. That
means that there are three data stores in a given process: the source, the staging area, and the target. “What,” you
gasp as you fight for air, “break the E-LT architecture after Oracle went through all that trouble to make it faster/better/
smarter?” You bet, because with Essbase the concept of E-LT has already struck out:
• There are no tables at the Essbase target as Essbase is not relational, so tables cannot be created there, only
in some third area. Strike one against E-LT for Essbase.
• There is no data manipulation language at the Essbase target. Essbase has MDX formulas and external ASO
Calc Scripts, BSO Calc Scripts, MaxL, Esscmd, and the various APIs, but nothing that acts as a data manipu-
lation language. Strike two against E-LT for Essbase.
• The lack of target tables and data manipulation functionality means something has to perform Extract,
Transform, and Load processing. Essbase thus requires an ODI server agent and engine. Note: the ODI server
and agent can coexist with many other products on a given physical server; it need not truly be a separate box.
Strike three against E-LT for Essbase.
• No change on your part is required; the default processing within the in-memory engine using implicit SQL
puts paid to the notion of Essbase E-LT: Essbase with ODI is not E-LT. For your purposes, this is great because
you can take advantage of the traditional ETL architecture by picking a separate relational data store to
cleanly store those temporary work tables, as shown in Figure 2.7. Choosing this relational data store is as
easy as ticking the “Staging Area Different From Target” box on the Interface Overview tab and then selecting
the relational staging Data Model, in this example named ODISTAGE.
This selection results in temporary tables written to the separate staging area, thus providing the best of both worlds:
supplemental debugging information and an uncluttered, easy to read target data store.
Each extracted dimension requires its own Interface. There are only a few things to
note in Figure 2.8's Interface:
• The Dimname field is hard coded with the dimension name.
• Identical field names map automatically.
• membername is the primary key for the Dimensions table.
• All mapping takes place in the Staging Area.
Figure 2.7
Selecting a different staging area.
