Import rule mapping verification procedure

Products

• Information Centric Analytics

Issue/Introduction

This document will guide you through the process of testing a new user-defined data source integration after configuring a data source query and creating an import rule mapping for the results of the data source query.

Resolution

NOTE: This procedure assumes familiarity with Microsoft SQL Server and T-SQL. Any changes made to the RiskFabric relational database may adversely affect the operation of Information Centric Analytics (ICA). Always ensure you have created a full backup of the database prior to making any changes.

The first segment of this procedure will query the user-defined data source and insert the query results into the destination staging table defined for the data source query:

1. Navigate in the console to Admin > Integration > Data Sources > Choose Data Source > User Defined
2. Locate and expand the data source in question
3. Right-click the query in question and select Run Query
   The Start Query Jobs window will open
4. Check the box next to the query to run and click the Run Selected button
   A temporary notification window will indicate Query Started
5. Click the Refresh button to refresh and monitor the job status until it is marked as Complete
   1. If the job fails, troubleshoot to determine and resolve the cause and then re-run this segment of the procedure

The second segment of this procedure will identify the import rule mapping ID for the import rule mapping you wish to test:

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the RiskFabric relational database
3. From the File menu, select New > Query with Current Connection
   A new query editor window will open
4. Copy the following query and paste it in the new query editor window:

   SELECT ImportRuleMappingID FROM RiskFabric.dbo.IW_ImportRuleMapping WHERE ImportRuleMappingName = '<import-rule-name>';

5. Execute the query by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Query > Execute in the menu bar
6. Note the ImportRuleMappingID returned under the Results tab

The ImportRuleMappingID value will be passed as a parameter to the stored procedure used to move data from the staging table into ICA's pre-processing tables.

The third segment of this procedure will identify the pre-processing table to be passed as a parameter to the stored procedure used to create new entities in the RiskFabric relational database:

1. In SSMS, select File > New > Query with Current Connection in the menu bar
   A new query editor window will open
2. Copy the following query and paste it in the new query editor window:

   SELECT PreProcessTableID, PreProcessTableName FROM RiskFabric.dbo.IW_PreProcessTable ORDER BY 2;

3. Execute the query by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Query > Execute in the menu bar
4. In the query results under the Results tab, identify and note the appropriate PreProcessTableName for the entity type selected in the import rule mapping
5. Note the PreProcessTableID for the PreProcessTableName identified on step 4

The fourth segment of this procedure will move data from the staging table into the pre-processing tables using the stored procedure dbo.spIW_RunImportRuleMappingStaging:

1. In SSMS, select File > New > Query with Current Connection in the menu bar
   A new query editor window will open
2. Copy the following statement and paste it in the new query editor window:

   EXEC RiskFabric.dbo.spIW_RunImportRuleMappingStaging @i_nImportRuleMappingID=<ImportRuleMappingID>;

3. Modify the statement by replacing <ImportRuleMappingID> with the ImportRuleMappingID value identified in the second segment of this procedure
4. Execute the statement by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Query > Execute in the menu bar
5. Select File > New > Query with Current Connection in the menu bar
   A new query editor window will open
6. Copy the following query and paste it in the new query editor window:

   SELECT * FROM RiskFabric.dbo.<PreProcessTableName>;

7. Modify the query by replacing <PreProcessTableName> with the PreProcessTableName value identified in the third segment of this procedure
8. Execute the query by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Query > Execute in the menu bar
9. Examine the results to confirm the data are populated as expected

Once you have determined the data are populated as expected in the pre-processing table, the fifth and final segment of the procedure will move the data from the pre-processing tables into ICA's core tables:

1. In SSMS, select File > New > Query with Current Connection in the menu bar
   A new query editor window will open
2. Copy the following statement and paste it in the new query editor window:

   EXEC RiskFabric.dbo.spIW_RunImportRuleMappingEntityCreation  @i_nImportRuleMappingID=<ImportRuleMappingID>,@i_nPreProcessTableID=<PreProcessTableID>, @i_sSourceTableName='<PreProcessTableName>_<ImportRuleMappingID>';

3. Modify the statement by replacing <ImportRuleMappingID> with the ImportRuleMappingID identified in the second segment of this procedure, replacing <PreProcessTableID> with the PreProcessTableID identified in the third segment of this procedure, and replacing <PreProcessTableName>_<ImportRuleMappingID> with a concatenation of the PreProcessTableName identified in the fourth segment of this procedure and the ImportRuleMappingID identified in the second segment of this procedure
4. Execute the statement by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Query > Execute in the menu bar
5. Select File > New > Query with Current Connection in the menu bar
   A new query editor window will open
6. Copy the following query and paste it in the new query editor window:

   SELECT EntityName, DestinationTableName FROM RiskFabric.dbo.IW_Entity;

7. Execute the query by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Query > Execute in the menu bar
8. Identify and note the DestinationTableName for the EntityName matching the entity type selected in the import rule mapping
9. Select File > New > Query with Current Connection in the menu bar
   A new query editor window will open
10. Copy the following query and paste it in the new query editor window:

    SELECT * FROM RiskFabric.dbo.<DestinationTableName>;

11. Modify the query to replace <DestinationTableName> with the DestinationTableName identified in step 8
12. Execute the query by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Query > Execute in the menu bar
13. Review the query output and confirm the results are as expected