Select Fields

Owned by Kavitha Thangaswamy (Unlicensed)
Last updated: Jul 25, 2023 by Pamela Totas (Deactivated)
4 min read

Only fields that are valid for their model type can be selected.

Time Series Fields

Time series fields, and detail calculation and inference fields based upon them, are supported in the Position Details and Security Details models, depending upon the type of time series field. You can only select rollup and rollup calculations of time series fields for Fund Summary or group-level models. Note that these rollups are summaries of security-level time series calculations rather than time series calculations defined on data rolled up to the group or fund level.

Classification Field Attribute

You can use a Classification field attribute in the Data Mart Security Details model. Classification fields take the data classification determined by a regular dictionary and place the resulting category in a column as if it were an analytical characteristic like currency.

Aggregation Level

  • You must populate the Fund Summary and Group models with rollup fields. Performance analysis fields are considered rollups, and are also valid for these models.

  • You can select rollup or non-rollup fields for Detail models. Rollup fields that you select at the detail level produce the same results as non-rollup versions of the same field.

Data Type

  • A Group model based on non-generic grouping fields may use only generic fields, or non-generics of the same type as the grouping fields.

  • You can select entity descriptive data only in the Fund Summary model. Entity hierarchy data may be selected in Group models. If you build data for a composite entity and you select "Always composite holdings" in the Configuration dialog box, you can include the entity ID in a Detail model to identify which member of the composite the detail data pertains to.

  • For Detail models, you can choose the same field types as you would include in an OLAP report of the same type. This always includes Special fields, in addition to the detail data type itself; for example, positions. Note that position and lot detail models can also include cash and trade adjustment fields, because adjustment fields are rollups of cash or trade activity stated at the position or lot level as of the same date as the position or lot.

  • Logic fields, such as Detail Calculations and Inference fields, may be selected for models in which the underlying data they reference would also be valid for the model.

Supported Performance Fields

  • If you map a Performance dictionary to a Group model, it must have the same number of levels (below Entity ID) as the selected grouping. The system only lets you choose a compatible dictionary in the mapping process.

  • The following Performance fields are supported in Data Mart:
    - The Entity Description field provides a field attribute which uses benchmark description information to generate text that describes benchmark construction and content. Data Mart supports these fields, since they are used in reporting.
    - Dynamic Mutual Fund Returns. These fields provide access to NAV table data. Refer to Retail Fund Performance User Guide for detail.
    - Fixed Income Multi-period Attribution
    - Performance Analysis
    - Composite Performance Analysis Fields in Fund Summary only.
    - Performance Attribution - Performance IRR Analysis
    - Performance Link Analysis
    - Performance Risk Analysis

Fields Supported with Restrictions

  • Trade Adjustment

  • Cash Adjustment
    These fields must always be accompanied in a model's field list by at least one position field, so that the OLAP report that generates the adjustment field data operates successfully.

  • Fields in tables added to the data model
    If your organization stores position or lot fields in extension tables that were added to PACE, your database administrator must follow the procedures for adding the tables. New tables must be registered in pace_masterdbo.scrub_tables.
    Any position detail table supported by the Data Mart that does not have a scrub_type of PD (position detail) or PL (lot detail) must be in this table with a scrub_type value of PDX (position detail) and PLX (lot detail). This requirement does not apply to fields from tables that are not position or lot types.

  • Lot Fields
    When you populate lot level position data, you must make sure that the Lot Level Position table in the Holdings database contains values of the lot_number field that are unique within a given security_alias. The lot number indicates the acquisition sequence of the lots comprising a position.
    If lot_number values are not unique across lots for a given security_alias, Data Mart selects only one of the lots for each security. To make sure you are using a unique value, have your database administrator update the lot_number field with the lot_level_position field.

Look Through Grouping Fields

  • Simple fields of the type: Entity, Position Detail, Security

  • Range Rules

  • Regular dictionaries only. Performance dictionaries are not supported since look through Performance analysis requires the use of Dynamic Performance.

Unsupported Fields

  • Internal fields with a field attribute ID between 100 and 999 (note that internal fields within the Special category are supported).

  • The following performance calculation fields: (note the results of these return calculation fields are supported as Performance Analysis fields)
    - Mutual Fund Performance (these are fields pointed at the HOLDINGS.NAV table. NAV data is available through Dynamic Mutual Fund Return fields, which are supported in the Fund Summary model)
    - Internal Rate of Return
    - Modified Dietz
    - Average Invested Balance
    - Dollar Value Added
    - Net Asset Value (NAV)
    - Composite Weighted Return
    - Performance Return
    - Performance Query Tool Report Fields
    - Performance Attribution Group Fields (note individual Performance Attribution fields are supported)
    - Fixed Income Multiperiod Attribution Group Fields
    - Attribution Diagnostic Effect Fields. The Mart cannot support attribution diagnostic effect fields (Note if selected for a model, this type of field causes serious problems. This applies to both regular and fixed income attribution.)
    - Client Fields 
    - Advanced Fields
    - Compound Security Instrument and Rollup Fields
    - Foreign Exchange Rate Fields
    - System Fields
    - Tax Fields
    - Cash Balance Fields

Character Fields

  • These fields have a default field length of 50 characters.

  • Use the Field Size Override column in the Fields tab of the model user interface in Manage Marts to control the maximum column size of a text field. You can set the width to any value up to the maximum that is supported for the data type of the Mart table field in question.

  • The 50 character default applies to rollups of all character valued warehouse fields, even those with a length greater than 50 characters.

  • If you define the character field attribute for a valid database, table, and column, for example a direct pass-through of a warehouse table field, then the field attributes field length in Data Mart equals that of the warehouse field to which it points.