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. This The information in this section does not cover basic concepts, but instead recommends specific choices of tools and techniques which lead to better results.
When to Use Data Mart
Eagle Information Delivery Buildouts Should Incorporate Data Mart
If you are an Eagle client in the process of building your information delivery solution, we recommend you use Data Mart. Data Mart is at the core of Eagle’s current and future Eagle’s information delivery strategy. As Eagle develops and integrates additional tools for information delivery and portfolio analysis, Data Mart is the default data source for them. For example:
Third-party reporting tools and applications can source from Data Mart, because the Mart is an open database platform.
Data Mart offers a simplification of reporting configuration relative to prior tools.
The Data Mart achieves efficiencies as a “write once, read many” information resource.
...
Eagle Portal is closely integrated with Data Mart
...
.
Eagle Mart, Eagle’s platform for delivering standard out-of-box reporting, performance and
...
Portal content
...
is based on Data Mart.
Data sources outside the Eagle warehouse can often be integrated with Data Mart tables to provide a more comprehensive view of the firm.
Note that Eagle’s Advanced reporting solution based on the Active reports platform can use Data Mart as its source, as can Classic Portal that precedes the new user interface Eagle Portal (V11.0 and later).
...
A more complex question is whether to convert existing reporting to Data Mart. This involves a unique cost/benefit calculation facing each company. Some key considerations are:
Conversion is easier/harder if existing reports have less/more program logic beyond just displaying values of field attributes.
All meta data (field attributes, date rules, source rules, range rules and dictionaries) is reusable in configuration of Data Mart.
Conversion to Data Mart can be done for a part of the report suite, such as a new type of information delivery.
...
Limitations of Data Mart
...
...
Ties to the Eagle Data Model (Opportunity for Enterprise Reporting)
Client data that does not reside in the Eagle warehouse cannot normally be built in Data Mart tables. There are limited opportunities to load data directly to Data Mart tables and bypass the operation of the PACE engines. Contact your Eagle Relationship Manager for additional information.
Eagle’s best practice is to load all data into the warehouse first, and then load the pertinent data into the datamart tables.
If you must introduce data outside the warehouse to supplement Mart tables, best practice is to load all 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. See “How to Move Non-OLAP Data to the Mart”. 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 Eagle Portal Administration Guide, Eagle Portal User Guide, and Knowledge Base Articles #10100 and #10101.
...
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:
You can build views against warehouse fields, and build field attributes built against those views. These field attributes can underlie fields in Mart tables. However, if you are using a version prior to V11.0, you must take an additional step to support view-based field attributes in Data Mart. Refer to Knowledge Base article #10891. Some warehouse tables are structured in ways that make OLAP processing impossible. For these, you can create a view directly against the warehouse table data, and include it in the Data Mart database.
Consider performance when using views as described in the “Ties to the Eagle Data Model” section.
...
to the Eagle Data Model” section.
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 the Data Mart User Guide and Knowledge Base article #10094, for a complete list.
...
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:
...
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.
...
with ongoing changes to securities and accounting.
Developing a Data Mart Strategy
...
Converting Active Reports to Data Mart
You may have built your presentation-quality reports using PACE Active reports. Now that Data Mart is available as an information delivery tool, should you consider converting existing reports to source data against the Mart? This is a complex question, and involves several considerations discussed in the following sections.
...
Field Standardization
Each Advanced report built with underlying OLAP reports has a list of field attributes associated with it. Advanced reports often involve one or more individual field rules for each underlying OLAP report. Standardization and re-use of field attributes and field rules is possible, but many organizations find that it is all too easy to duplicate fields. Data Mart, by contrast, encourages business definition standardization and sharing of field attributes since duplication of data fields is much more easily recognizable in the Mart and thus avoidable.
...
recognizable in the Mart and thus avoidable.
Preserving Advanced Report Functionality
You will not lose the benefits of using Advanced reports under a conversion to Data Mart. You can still schedule Active reports built against the Mart to send to Portal, and still develop them as a Client reporting batch. Report ordering functionality and passing of parameters is the same as when actual OLAP reports underlie underly the Advanced report.
If you implement Data Mart to adopt a reporting tool not supported by Advanced reports, you forego the benefits of Advanced reports related to Portal and Client Reporting.
...
Data Retrieval 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.
However, if reporting in the organization is a matter of running reports in the overnight cycle and not during normal business hours , rather than running reports during the day, Data Mart does not offer as much of a total production time savings.
You can create multiple snapshots allowing you to build and save your data more than once per day and use more than one source rule in the build process. Creating multiple snapshots means an organization could 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. Anchor
...
Potential for Sharing Fields
Your reports share many common fields. Data Mart provides 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.
...
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
...
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 Eagle Global 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.
...
This inventory process should include an effort to standardize , where possible , on a minimal set of definitions of common fields like market value and security price. Standardization makes possible a compact Mart where data elements are widely shared among consumers of the data.
...
Common use cases for multiple source rules or source-specific fields include:
Multiple providers of bond ratings or other security analytic data whose viewpoints must all be considered in the security evaluation process;
Multiple accounting bases, such as GAAP and statutory, that require accounting fields to be computed and reported in different ways.
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.
...
Some types of performance returns, such as returns for specific years or quarters, do not change every day. If you report such returns, you can avoid building them every day or even every month by building them only once on their true effective date, for example on December 31, 2014 2020 for a “Year 2014” 2020” return. Then in reporting, you would always select them as of their effective date.
...
If you install Eagle Mart and the Performance Toolkit, you can see examples of best practices for using extensions to support different types of performance fields. The toolkit uses separate Fund Summary extensions for the following field types:
Performance link fields for periods up to one year
Performance link fields for periods longer than one year
Performance risk analysis fields
GIPS Disclosure fields
GIPS Composite Performance Analysis fields
There are restrictions on building only one type of field attribute in a single extension. For more information, see Knowledge Base article #10890.
Sharing Security Details Among Snapshots
...
Since Data Mart requires you to build Security Details whenever you build any detail-level table, you cannot completely avoid building Security Details in each snapshot. However, you can use Selective Fields to build just one field in Security Details in all but the Default snapshot, and save considerable time. In V11. 0, Data Mart offers the option of building detail-level tables without also building any Security Details.
...
Experience shows that performance OLAP processes typically run best when you change the No. of Funds per Report option in the Configuration dialog box in Manage Mart to about 20. The option is set t0 to 50 by default. You can experiment with this setting to achieve optimal results, especially if you are building a significant amount of performance data.
...