How to Configure an MDL file not to generate the SQLStatistics call for ODBC connections

Document ID : KB000055212
Last Modified Date : 14/02/2018
Show Technical Document Details

Description

By default, creating of indexes in CleverPath Reporter is set to true so that tables can be joined based on indexes which results in faster execution. However, on some databases this causes the SQLStatistics call to run on the database which can slow down processing on the database side.

This document will explain how to turn off indexing in CleverPath Reporter so that the SQLStatistics call will not run.

Solutions

  1. Open your default.mdl file (which can be found under \config directory) in your CleverPath Reporter Administrator.

    Figure 1

  2. If you have selected all available data sources you will see the dialog below. The most important one that needs to be selected is the ODBC datasource.

    Figure 2

  3. Click on "Query Governor Options" tab and make sure "Save Options to Workspace" checkbox is checked.

    Figure 3

  4. Click on "Configurations" tab and then click on "New" button to define a new configuration. Give this configuration a name and select ODBC as the data source. Say OK to create the new configuration.

    Figure 4

  5. In the Configuration dialog, click on "Data Sources", go to "Query Governor Options" tab and check "Save Options to Workspace" checkbox. Depending on the requirement you can customize your configuration here but we do not need them at this moment.

    Figure 5

  6. Now save this configuration (go to File->Save). Also install this configuration into your prorep.prf file by going to File->Install Workspace and selecting prorep.prf file in your dialog.

    Figure 6

  7. Before creating a report open your default.mdl file under <CleverPath_Install_Dir>\config in an editor and make sure the flag INDEXES has a NO next to it. If you find YES manually change it to a NO. This is how my default.mdl file looks.

    Figure 7

  8. Now Open your CleverPath Builder and create a new report. While selecting the source type select the newly defined configuration and you should be able to see a list of tables.

    Figure 8

By setting INDEXES to NO we will be turning off creating indexes for the tables and hence no SQLStatistics call will be generated.

We take pride in our technical documents and are interested in your feedback.
Please email your comments to us directly at:
portalsdksupport@ca.com Be sure to include the document id in the message.