Page Comparison

Anchor
_Toc534679216 _Toc534679216

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.

Anchor
_Toc534679217 _Toc534679217

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. 

   Image RemovedImage Added

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.