Build Query for the Mart Data

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

The Query Expert allows you to create and copy SQL code that underlies a view of Mart data. You can then paste the copied SQL code into a third party reporting tool and modify it further if necessary.

The Query Expert allows you to:

  • Generate a query for the data as it is presented in the View Mart Data window. Any fields that you select to view in the window are reflected in this query.

  • Generate a query that can fetch a selection of Mart data for a given fund and effective date. Any combination of the Fund Summary table, one group level table, and one detail level table can be represented in this query. A specific selection of available fields can be performed.

For each function, if more than one Mart table is involved in the generated SQL query, the necessary joins are included. You can copy the SQL code for that query to your system's clipboard, and then paste the code into a thirdparty tool's data access component. Once in the reporting tool, the SQL provides a "virtual table" of Mart data for your use in reporting.

Build a Query for Data Viewed in the Data Mart

The Query Expert allows you to build a query for data viewed in the Data Mart.

To build a query for data in the View Mart Data window:

  1. Click the View Mart Data icon in the left panel of the Manage Mart or Execution Log window.

  2. Display the data for which you want to build a query. See View Data for details.

  3. Click the Query Expert link.
    You see the following the Data Mart Query Expert dialog box.

  4. The SQL queries that select the data shown in View Mart Data for the Fund Summary and selected Mart model table are displayed in the first two sections. Code for a join of these two datasets is listed in the last section of the dialog box.

  5. Click the Copy link at the top of the specified section to copy the SQL code for that level of query into your system's clipboard.

  6. In your reporting tool's data access interface, paste the SQL code from your clipboard into the tool.
    Note: Within your third-party reporting tool, you must replace the three variables, <YOUR FUND ID>, <EFFECTIVE DATE>, and <SNAPSHOT ID>, with values or parameters appropriate for your report.

  7. Click Close to close the dialog box.

Build a Query for Data Not Viewed in the View Data Mart

The Query Expert also allows you to automatically generate the SQL code that retrieves any Mart data, beyond what may be displayed in the View Mart Data window.

To build a query for data not listed in the View Mart Data window:

  1. Click the Query Expert link.
    You see the Query Expert dialog box.

  2. Click the Build a Query tab.
    You see the Build a Query tab.

  3. In the Select required models to query the data section, select one or more model types for your query.
    You can choose more than one type of model. Options include:
    - Fund model. Click this option to select the Fund Summary model in the Data Mart.
    - Group level model. Click this option and use the dropdown list to select from all Group models available in the Data Mart.
    - Detail level model. Click this option and use the dropdown list to select from all Detail models. However, if you chose a Group model, your choice of Detail models is restricted to those types, such as positions or performance, represented by fields of the Group model.
    You can include the following Detail models:

    • Cash Flow

    • Ledger Activity

    • Lot Level Positions

    • Performance Details

    • Position Details

    • Transaction Details
      Note: The Security Details and Issuer Details models are automatically selected if you select another Detail model.

  4. Click the Get Fields link.
    All fields for the selected models are displayed in the Select Fields section. If you selected a detail level model in the query options, all security_details and issuer_details fields are displayed by default.

  5. Select the fields for which want to build a query.

    • To select all fields in the models, click the Select All link.

    • To clear all selections, click the Deselect All link.

    • To select individual fields, enable the check box beside each required field.

    • To collapse the view to the model level only, click the expand icon.

  6. Click the Build Query link in the Selected Fields section of the dialog box.
    The Data Mart builds the SQL query code that returns the data you selected.
    NOTE: The SQL that results leaves the specification of entity, effective date, and snapshot as place holders, since you will likely use a parameter to prompt your report user to fill in these items.
    The following figure shows an example query. 

    Example Query

     

  7. Click the Copy link to copy the SQL code for that level of query into your system's clipboard.

  8. In your reporting tool's object access interface, paste the SQL code from your clipboard into the tool.Copied code generates a virtual table or flat file.

  9. On the Data Mart Query Expert dialog box, click Close.