Introduction
There are many ways to design, configure and operate your Data Mart. Since different approaches are valid in context, it is not possible to define best practices for every task. However, there are some useful lessons from the experience of Eagle and its clients with Data Mart. This document assumes that you are familiar with how to use Data Mart. The information in this section does not cover basic concepts, but instead recommends specific choices of tools and techniques which lead to better results.
Real-Time Reporting
Most Eagle clients design data workflow to follow a daily cycle:
Once markets close and regular-hours trading ends, the daily accounting update cycle sees trades updating positions, prices applied to create market values and reference and analytical data updated. Accounts are reconciled and submitted for automated and manual error checking.
When this activity is complete, end-of-day processing such as entity builds commences, with performance measurement calculations usually coming last. Further data QA and approvals take place.
With all warehouse contents in a validated state, clients build their Mart and use it to deliver enriched information during the ensuing business day.
In an ideal world, there would be no reason to make further changes to data values updated as of past completed business days. However, this is not the case in the real world. Vendor data corrections impact previously loaded security information. Late trades and trade reversal/re-entry activity affect accounting data values in the open accounting period.
It is physically possible to use Process Manager to build a workflow that automatically detects every such intra-day data change and updates the Mart accordingly soon afterward. However, such a “real-time” Mart is likely to produce undesirable results, primarily for two reasons:
A real-time approach may allot insufficient time for data QA, especially when the consumer is a client of the firm. Proper evaluation of updated information must allow for at least the possibility of human intervention in exception-management mode. It is unlikely that this type of discipline can be applied to high-frequency intra-day changes.
If updates take place on a continuous random basis, there is no “snapshot stability” of information. Unstable data is likely to lead to more questions of how recent or how clean the numbers on the page or screen are.
Two approaches to incorporation of data changes are considered best practices:
Batch each day’s corrections and accounting restatements during the day, and evaluate those using standard data QA procedures. As part of the overnight Mart build, incorporate these changes into selective required rebuilds of the Mart for all past dates affected.
Take the same approach, but do so at one or two regular times during the day as well as at end of day.
Both of these approaches allow for proper data validation and promote a stable Mart whose “vintage” can be understood by users, while keeping reportable information quite current with ongoing changes to securities and accounting.
Developing a Data Mart Strategy
...
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.
...
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.
...
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.
...
When more than one type of data must be reported in more than one source hierarchy, the number of required source rules can proliferate and lead to several snapshots and heavy duplication of data. For this reason, you should make choices to limit the required list of snapshots. See “Sharing of Security Details Among Snapshots” for additional information.
...