Additional record configurations

A filter is used to set constraints on dates and other data. Any filters defined here are converted to a WHERE clause that is added to the end of the auto-generated SQL query for the record. A default time filter is automatically defined based on the record time. The Time Filter is intended to allow you to retrieve just new records when they are first generated, or existing records after they have been modified.

Click the Filters tab any time to view or edit a filter.

You can edit the Time Filter to filter records based on criteria other than the time field used for Record Time. For example, you can filter based on the last time the data was updated, or on a weekly or monthly basis regardless of the data or how it has changed. You can leave a filter blank to return all of the records in the table each time.

To edit a filter, double-click the filter name and click Edit expression.

Insight adds all filters to the $filter query part.

To ensure achieving the expected result, verify that the required filter is supported in the selected OData source. Otherwise, you can use the following workaround:

1. Create a temporary OData-based on-demand record where you use the OData supported filters.

2. Create a new record and add the OData-based record from the previous step to use all filters.

You can review and modify the SQL statement to be executed when loading the record.

Click the SQL tab any time to view or edit the SQL statement.

Fields from the Mapping go into the SELECT portion of the SQL statement and the Filter Conditions go to the WHERE portion.

You can include your own SQL statement without giving up the auto-generated SQL statement. One way to do this is to create a derived field whose value is the SQL that when executed, will generate the value of the field. This result would be entered directly as the value of the field.

We recommend that you execute a test of the record before publishing the solution. Insight provides a testing tool to ensure the database connection is valid and to verify that queries can be executed to generate the records.

1. In the Record Editor, click the Test tab.

2. Select a valid time interval.

3. Click Test or Advanced test.

4. Click the Show/Hide results button to see the actual records created by Studio to ensure the correct data from the database is mapped to the correct record field.

When performing a massive data load for an existing record, to optimize the performance, use the Deactivate indexes option to deactivate indexes before data load and fully rebuild the indexes after data load is complete. The option is available on the record and execution plan level. In case the option is enabled for an execution plan, the record settings are overwritten. You may want to enable the option for execution plans, as they may change, while the records are the same. Follow the instructions below to enable the option.

• Select the record, navigate to the Property Panel, find the Deactivate indexes option and select the check box.

• To enable this option for an execution plan, select the step in the plan and navigate to the Property Panel. In the Deactivate indexes option, select Mode on.

The feature affects indexes in a different way depending on the type of database.

• MS SQL: All indexes except clustered are disabled before data load. After data load is complete, the indexes are rebuilt.

• Oracle: All indexes are marked as "unusable" before data load. After data load is complete, the indexes are rebuilt and marked as "online".

• My SQL: All indexes are disabled before data load. After data load is complete, the indexes are enabled.

In the record, you can activate the Use Clustered Index option for a Date/Time field, if the Purpose of the field is set to Time. In the Property panel, select the check box to enable a clustered index, if the check box is clear, a non-clustered index is used. Take this into account when you work with customized indexes.