Develop a Data Mart Strategy

Data Staging versus Data Processing

When you run an Advanced Report with underlying OLAPs, all of the underlying processing is done “on the fly”.  Some calculations, especially performance calculations, can be time consuming and lead to long rendering times for reports.  With Data Mart as the data source, all computations are completed before reports are run, so that reports simply retrieve field values from one or more tables.

Note that if reporting in the organization is basically a matter of running reports in the overnight cycle and not during normal business hours, rather than running reports during the day, then Data Mart would not offer as much of a total production time savings.

There is also the ability to build multiple snapshots where you can build and save your data more than once per day and use more than one source rule in the build process. Having the ability to create multiple snapshots means an organization could for example maintain a custodian source and a manager source for the same accounting data. By saving multiple snapshots you could report on intra-day comparisons of data values.

Potential for Sharing Fields

To the extent that your reports share many common fields, Data Mart will provide the efficiency of computing each one of them just once rather than once per report.  Mart data is meant to be shared among reports.  For this reason it is critical to make a careful plan of the data fields in the mart, to assure that all reporting needs are covered by a minimum of fields.

Report Conversion

There is no report conversion utility that would automate such a process.  It would be difficult to design such a capability that would be useful in a large number of cases.

• An Active Reports report sources data from PACE field attributes.  Data Mart can store data for the same field attributes (with the exception of a short list of field types that Data Mart does not support).  The SQL code used by an Advanced Report would need to be reworked in terms of tables accessed and joins used, which will change in a Mart conversion. 

• Eagle Mart, which sources from Data Mart, comes with a set of standard reports including report source code for the non-performance set.  These reports provide examples of building Advanced Reports against the Mart that may be useful to clients doing report conversion.

 Report Development

The Mart offers efficiencies in report development:

• Data Mart simplifies the process of building SQL code for bringing your data together in a report.  Data Mart places data fields of multiple types (position, performance, transaction, etc.) on the same line of a data source table at the group and fund levels of aggregation, which saves you the effort of creating multiple OLAP report rules and profiles and then joining their results in SQL that you must develop.

• If you report data in a time-series fashion, you can do so for some performance fields using the Performance Query Tool.  For other types of data, OLAP processes will be limiting.  Through regular daily or monthly builds, Data Mart establishes a history of all data, making time series reporting easy.

• Data Mart encourages standardization of field definitions and helps reduce the unintended duplication of field attributes that sometimes accompanies OLAP report development.  See “Field Standardization” above.

Supporting 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 user interface for data management of the Mart.  Sometimes, however, it is 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 very wide range of data structures.  Custom tables may be built and joined to core tables to permit access by the OLAPs.  Even database views may be created and field attributes built to report their contents.  However, there may be instances in which 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 be 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 for clients who wish to load data from an external source such as report-ready performance data. 

• Data Moved in Anticipation of a Future Release Enhancement.  Clients may find that they 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—please refer to “Direct Loads to Future Table Extensions” below.

How to Move Non-OLAP Data to the Mart

There area variety of ways to deploy non-OLAP data to the Data Mart schema.

Non-Materialized Views

A non-materialized view is an SQL query against other database tables that is exposed to reports and applications as if it were a physical table.  This simplifies access; 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.  Accordingly, this type of view should be reserved 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 need not be assembled on the fly.  They are best suited to views of larger numbers of fields.  A drawback to materialized views is that they must be updated and maintained.  Oracle offers a powerful toolset for deploying materialized views known as Oracle Materialized Views.

Direct Loads to Reserved Columns of Mart Tables

The “selective fields” enhancement to Data Mart allows you to do a direct load of 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.  You could do this by creating and selecting for the model a set of dummy field attributes to create columns in the table to receive the external load.  Selective fields would then be used to avoid populating those fields in the regular build, thus “reserving” them for the external load. As a recommendation, an extension should be created and populated by the client since the Mart does not delete data but simply updates.

This practice introduces complexities, and you should attempt it only in consultation with Eagle.  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 Mart data fields of a type that are only supported by the product in a release after yours, you may be able to load those fields to a table with the same key structure as a Data Mart table extension.  You would 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, all that happens is that in the Mart you create the table extension and give it the name of the table you have been using for the new fields.  You would 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.