Integration Wizard best practices

Products

• Information Centric Analytics

Issue/Introduction

Recommended practices and techniques for integrating data sources with Information Centric Analytics (ICA) using the Integration Wizard (IW).

Environment

Release : 6.x

Component : Integration Wizard

Resolution

NOTE: The following recommendations assume a basic understanding of T-SQL. Any changes to the RiskFabric relational database pose the potential to affect the operation of ICA. Always ensure you have created a backup of the database before making any changes.

1. Review the column names of the integration mapping entity prior to developing the data source query for the integration. These details can be found in the Entity Mapping Fields in the Integration Wizard section of the Symantec ICA Integration and Solution Accelerator Guides. Reviewing these in advance will give you a better concept of the data the integration entity will accept for the integration mapping. Reviewing the entity will provide insight into the data that will be required by the integration entity and it will also document the data type and length accepted by the column. This will provide you an opportunity to create a plan to resolve any data type mismatches or any data sizing issues in advance.
2. When creating a data source query, alias the source column names within the query to align the column names in the data source with the destination column name defined for the integration entity. When source column names or their aliases match destination column names, the IW automatically maps the columns defined for the integration, eliminating the need to manually map the source to target columns when defining the integration mapping.
   • For example, you are using the Computer Endpoints entity type to load computer endpoints. The primary key column in your source system is named AssetTag and you want to map it to the column SourceComputerKey. Aliasing AssetTag as SourceComputerKey will enable the IW to automatically map the two columns.
3. To prevent data type errors from occurring during integration processing, use CAST statements in your SQL statements to minimize the risk of data type or size conflicts when loading data into ICA.
   • The following sample CAST statement will cast the column SamAccountName as an nvarchar(256) column and alias the column as AccountName. This will ensure the data selected is capped at 256 characters and enable the IW to automatically map the source column to the destination AccountName column.
     

     CAST ([sAMAccountName] AS nvarchar(256)) AS "AccountName"

4. When defining the integration mappings for a column, you can specify a source column from a data source query, a fixed value, or a formula (either a built-in formula or a custom formula).
4. ICA includes several formulas you can use to convert and standardize source data. For example, one of the most commonly used formulas ('Convert epoch time to SQL Server datetime') converts UNIX epoch time to SQL Server's datetime format.
5. When creating a formula, enclose variables in braces: {}. This is required to correctly define the variable and prompt ICA to specify a column value to pass during run time. Formulas are applied when the data are moved from staging tables into pre-processing tables.
7. When defining a data source query within ICA, it is possible to have the data source query run on an intraday schedule. If the data source query is scheduled to run on an intraday schedule, set the integration mapping to also run on an intraday schedule. The data source query schedule is independent of the integration mapping schedule and the integration mapping needs to be configured to run as part of the intraday process.
8. If you would like the integration mappings to run in a specific order, configure a run order when creating integration mappings. Configuring a run order will dictate the sequence in which mappings will run relative to other mappings. This is important if any mappings or formulas are dependent on data from other mappings.
9. Determine whether a watermark is required for a data source query. If there is no watermark defined for a data source query, its staging table will be truncated and reloaded on a nightly basis. If there is a watermark defined for a data source query, the data in its staging table are never truncated and data will continue to accumulate in the staging table.
10. When a watermark is defined for a data source query, ensure you define a data retention policy for the staging table. Establishing a retention policy for the data source query aids in managing the quantity of information retained in the staging tables. If no watermark is defined for the integration, there is no need to establish a retention policy.