Control Builds of Security and Issuer Data

When you build or update data for detail tables, your build will automatically include Security Details and, if the model is configured, Issuer Details.  This “come along” build of securities with any detail table build addresses the need to be able to identify the security to which any detail table row pertains.

There are a couple options for use of building Securities Details.  These options are controlled in the configuration screens and are global settings.  One is to build all securities. The other, build held securities.  Build all securities options means to build the security details model for every security contained in the SECURITYDBO.SECURITY_MASTER table.  This is very straight forward. Building only held securities has a couple of nuances. Since the Securities Details modes is run last, the engines can determine which securities in the database are actually held by some entity.  The engine will look to see what securities were uses to build the various detail models that for that run. For example, if the Position Details, Performance Details, and Transaction Details models were run, the Securities Details model will run for only those securities that were present in the previous 4 details models run.  There is an additional option where the Define Criteria link can be populated to include other non-held securities that may be required to be built for other reporting purposes. The default behavior is to build for only Held Securities.

Scheduled Submissions

When running scheduled submissions of details models, the Security and Issuer Details models are also run at the end of the process and are included in the schedule unless the person chooses not to include them. This may be accomplished via the user interface. Please see the Data Mart User Guide for details on creating schedules.

Sometimes your workflow calls for a rebuild or partial update of previously built detail data in which you would prefer to save on build time by not rebuilding all security details.  This may be particularly true if you use a dynamic workflow in Process Manager to update detail data as granular as one entity on one date.  The presence of an extensive list of security details fields would increase your preference not to rebuild them all.

You can limit that build by using the selective fields feature to build a bare minimum of these fields, as few as one per model.  Subject to the one-field rule, selective fields does not require any particular table extension to be built.

Data Mart submissions that are scheduled using Automation Center to handle detail builds with Security and Issuer data differently than adoc submissions. When running builds with Process Manager there are a couple of things to keep in mind in order to minimize the processing cycle times. Since Security Detail and Issuer Detail builds generally occur with all other detail model builds at the end, it is generally wise to ensure that these security and issuer builds occur only once at the end. This is especially important when process manager is set up to handle builds for adjustments and and “as of” changes.  The best practice for these events is to create a initiator that will trigger these detail model builds there may be many that going on at different times).  Once all these builds are complete then an second event should be created to run the security details and issuer details model once at the end.  This will require separate schedules for the details models and the security/issuer models. 

If You Build Tax Lot Data

A proper build of the Data Mart Lot Level Details Table requires that the lot_number field of the holdingdbo.lot_level_details table be unique within a given security_alias.  This field should be the sequence number of the portfolio’s buy lot for the security in question.  However, some accounting systems including STAR do not populate this field in the way that Data Mart’s underlying OLAP process requires.

If your lot_number field is not unique within each security, you may load rows to this table while populating the lot_number field with the value of the lot_level_position field, which is unique within the entire table.