Data Mart Limits

If you must introduce data outside the warehouse to supplement Mart tables, best practice is to load all data into the warehouse and then load this data into the Data Mart tables. As a last resort, use views, either materialized or non-materialized, of the required databases. Common fields between views and Mart tables enable joins between the two. Keep the following issues in mind when using views:

• Performance implications. Views can severely impact the performance of the Mart. You should not use them without being fully aware that performance degradation can occur.

• Data Congruency. Data directly in the warehouse such as transactions may be updated, but positions in the Mart may not match up. When you use a view, you pull data directly from the database and not from the Mart which may have out of sync data

When you use Data Mart, you can use views to supplement your Eagle data. This practice can offer an opportunity to use the Data Mart schema as an integration point for enterprise information delivery. The Eagle Portal Access SQL Query is used by clients to create queries and dashboards that source data from non-Eagle sources, and integrate those sources with their Eagle data. See the section Eagle Portal Administration for information.

Ties to Field Attributes

Field attributes are used to build all of the data in the Mart, so Mart data tables contain no values that cannot be produced by field attributes. However, there are some steps you can take to compensate for this limitation:

• Views against warehouse fields can be built, and field attributes built against those views.  These field attributes can underlie fields in Mart tables.  However, prior to V11.0 there is a step you must take to support view-based field attributes in Data Mart. 

• Some warehouse tables are structured in ways that make OLAP processing impossible.  For these, a view may be created directly against the warehouse table data, and included in the Data Mart database.

• Performance needs to be considered when using views.

Use Cases Not Supported by Data Mart

Data Mart does not support the following reporting requirements:

• “Any date to any date” performance return linking. Because you can select any two begin and end dates for which to link a return, the possibilities are infinite, and not storable. Return linking of this type is likely to be completed interactively, and is best supported by an underlying Performance Analysis calculation performed on the fly. You can integrate this type of Performance Analysis OLAP process with Data Mart in an Advanced report or in Portal.

• Client data (as distinguished from entity data). Many firms use third-party Client Relationship Management (CRM) systems to manage contact and related data. Views of CRM data tables may be deployed to the DATAMARTDBO table space, and joined to Fund Summary if a common field such as entity_id is available.

• Most Advanced field attributes are not supported. Exceptions include Special Fields and source-specific fields. See Data Mart for more information.

Out of Scope for Data Mart

In general, Data Mart serves as a source of reportable data for information delivery processes that are stable and repeatable in nature. If the data is not report-ready or if the information requirement is unlikely to persist, Data Mart may not be a good candidate. For example:

• Account reconciliation. You can reconcile data between sources using source-specific snapshots. However, for most reporting purposes, best practices is for you to reconcile first and then load only validated, reconciled data into the warehouse.

• Accounting and Performance operational reports. Accountants and Performance Analysts engaged in intra-day operational and data QA activities may send pre-approval data to the Eagle warehouse that is relayed to Data Mart as a part of automated processes such as Process Manager. You should weigh the costs and benefits of such a workflow, and guard against unintended reporting of unapproved in-flight accounting and performance fields.

• Ad hoc reporting. Investigational queries into the database may go against fields that would otherwise support few, if any, reporting needs. Such fields may not be a good use of Data Mart “real estate”, making reporting against them a very appropriate use of OLAPs. Note that “data mining” against more reportable fields with the aid of a cubing application (for example) is not a very appropriate use of Data Mart.

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.