Convert Active Reports to Data Mart
...
Data Mart encourages standardization of field definitions and helps reduce unintended duplication of field attributes that sometimes accompanies OLAP report development. See “Field Standardization” for additional information.
Support Non-OLAP Data in the Mart
All data built by PACE in Data Mart tables comes from the action of an OLAP process. There are strong arguments for maximizing the potential of the OLAPs to populate as much required reportable data as possible, given the powerful and flexible tools available in the Data Mart for data management of the Mart. However, it is sometimes necessary to support data in the Mart not placed there by the OLAPs.
When to Move Non-OLAP Data to the Mart
Some types of data either should or must bypass the OLAP processes that build data into mart tables:
Data in structures not supported by OLAPs. The Eagle data model is very flexible and supports a wide range of data structures. You can build custom tables and join them to core tables to permit access by the OLAPs. You can also create database views and build field attributes to report their contents. However, there may be instances where data is loaded to the Eagle warehouse for which OLAP access is not readily available and cannot easily be provided.
Report-ready, single-use data loaded in high volume. Even in the case of single use data, Eagle recommends as a best practice that all data is stored in the warehouse followed by Data Mart running the OLAP processes to move a set of values from one warehouse schema to the Data Mart schema. This is the best approach if you want to load data from an external source such as report-ready performance data.
Data Moved in Anticipation of a Future Release Enhancement. You may need to deliver a type of information that is supported in Data Mart only in a future release due to a product enhancement. Release upgrade may not be practical before the information is required. If the nature of the enhancement is to enable a new type of field to be built into existing Mart tables, a temporary workaround strategy may be available. Refer to “Direct Loads to Future Table Extensions” for additional information.
How to Move Non-OLAP Data to the Mart
The following sections describe how to deploy non-OLAP data to the Data Mart schema.
Non-Materialized Views
A non-materialized view is simply a SQL query against other database tables that is exposed to reports and applications as if it were a physical table. This simplifies access since joins between tables and views are supported. The main drawback of such a view is that its use involves the latency of execution of a database query. Reserve this type of view for the simplest views in use.
Materialized Views
Materialized views are physically built from data returned by underlying SQL. They are a sort of database copy. Queries against them perform much better than those against non-materialized views of the same datasets, since data is physically present and does not have to be assembled on the fly. They are best suited to views of larger numbers of fields. A drawback of materialized views is that they must be updated and maintained. Oracle offers a powerful toolset, Oracle Materialized Views, for deploying materialized views.
Direct Loads to Reserved Columns of Mart Tables
The Selective Fields enhancement in V9.0 Data Mart allows you to directly load fields to Data Mart tables as long as you can populate all rows of those tables by a regular Data Mart Submit. You could reserve some fields in the tables that need supplemental data from direct loads. To do this, create and select a set of dummy field attributes for the model to create columns in the table to receive the external load. Selective fields are then used to avoid populating those fields in the regular build, reserving them for the external load. Eagle recommends you create and populate an extension since the Mart does not delete data, but simply updates it.
This practice introduces complexities, and you should attempt it only in consultation with our Professional Services Consulting Group. It may be simpler just to add a custom table to the Data Mart schema and join to appropriate tables in reporting.
Direct Loads to Future Table Extensions
If you want to use a type of Mart data fields that are only supported in a release after yours, you may be able to load those fields to a table having the same key structure as a Data Mart table extension. You can give the table the same name as the extension you would create for the fields in a higher release, and report against the table as if it were a regular Mart table extension. Then, when you upgrade, create the table extension in the Mart and give it the name of the table you are using for the new fields. You add to that extension the Mart fields necessary to create your table fields, now supported in the Mart. This is a simple process with the benefit that reporting data access logic need not change at all.