Add Metadata from External Content

• Why Use Datasets?

• How to Add Your Connection

• How to Add the Dataset Connection

• How to Add Your Dataset Contents

• How to Create a Content Source

• How to View the Added Metadata

Why Use Datasets?

Use datasets to add extra metadata from another table using BA Insight Connectors such as SQL and Salesforce.

• For example, you might use your SQL Connector to crawl a table of information about documents; metadata such as the author or title of the document, where related information is in another table in the database.
• To return the related information, you create a database connection to this table using your dataset connection and a where clause.
• This operation enables you to add a dataset to the content source so that when data such as "author" name is related to other relevant information (such as "full name"), it can be seen in search results.

The following sections will outline how you can create an SQL connection, create a dataset connection, add dataset contents, and map your content source to your dataset.

How to Add Your Connection

The following steps use the SQL Connector content and specify the Title, Type, and Connection ID from the content sources.

We add the dataset that looks at the connection; the title and type of the connection for the specified content source is also returned as extra information from the item linked to your item.

1. Click Connections > New > SQL connection.
2. The Connection Info tab appears.
   
3. Click the General Settings tab.

Basic Mode

Complete the following fields:

1. Connection Mode: Select Basic.
2. Database Server Address (required): Enter the IP address for your database.
3. Database Name (required): Enter the name of this database.
4. Connection Timeout: Time in seconds between timeouts.
5. Authentication mode:
   1. Use Service Account: Uses your local machine Service Account. Set to "Use Service Account" by default.
   2. Specify User Account: Enter your SQL login and password in the fields that appear.

Advanced Mode

Setting the Connection Mode to Advanced reveals the Connection String field. All other fields are the same as "Basic" mode.

• Connection String (required): Enter your SQL database connection details using SQL string syntax.
  Due to a vulnerability fix on the SQL Client, the default encryption flag for SQL Connections has been changed to true. As a result, you must specify Encrypt=false in your connection string if you do not support encryption in your database. For example, Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Encrypt=false;

Security: Directory Tab

1. Click the Security Directory tab.
   
2. Group expansion threshold: Specify the Group expansion threshold.
   
3. Create security groups: Checking this box creates missing security groups in the target directory when you run the Security Sync job.
   
4. Click Save to see your new SQL Connection listed in the Connections page.

Security: Scripts Tab

• Users load query: SQL statement that returns all the users in the database.

  • Required columns: SPW_USERID, SPW_USERNAME

  • Optional columns: SPW_ADID, SPW_ACTIVE
    

• Groups load query: SQL statement that returns all the groups and roles in the database.

  • Required columns: SPW_GROUPID, SPW_GROUPNAME

  • Optional columns: SPW_ADID, SPW_ACTIVE

• Group users load query: SQL statement that returns a list of unique user IDs within a specified group ID.

  • Required column: SPW_USERID
  • Required parameter: [SPW_GROUPID]
    

• Group hierarchy load query: SQL statement that returns a list of unique group IDs within a specified group ID.

  • Required column: SPW_GROUPID

  • Required parameter: [SPW_GROUPID]
    

• User load script: VB.Net script that is executed by Security Sync task, once per user.

  • Can be used to return the mapped user from target directory.

  • Script must return a user received from any of the "TargetDirectory.GetUserBy-" methods, which are documented for each supported target directory type.

  • Parameters needed for the mapping are coming from the connector and can be retrieved by "Host.Get-" methods.

  • If the user is not found in the target directory, it is invalid and mapping will be retried in the next Security Sync task again.
    

• Group load script: VB.Net script that is executed by Security Sync task, once per group.
  • Can be used to return the mapped group from target directory.
  • Script must return a group received from any of the "TargetDirectory.GetUserBy-" methods, which are documented for each supported target directory type.
  • Parameters needed for the mapping are coming from the connector and can be retrieved by "Host.Get-" methods.
  • Groups found in target directory will be static, while other ones will be native.
    

How to Add the Dataset Connection

1. Click Datasets > Dataset connections > New > SQL Dataset Connection.
   
2. The SQL Dataset Connection page appears.
   
3. In the Connection Mode field, select Basic or from the drop-down list.
4. If you selected a Basic connection mode, Enter the following information:
   1. Title (required): Enter a unique, descriptive name.
   2. SQL Server (required): Enter the IP address of your database server.
   3. Database Name (required): Enter a name.
   4. Authentication Mode (required): Set to "Use Service Account" by default.
      
5. If you selected an Advanced connection mode, Enter the following information:
   
   1. Title (required): Enter a unique, descriptive name.
   2. Connection String(required): Enter your SQL database connection details using SQL string syntax.
   Due to a vulnerability fix on the SQL Client, the default encryption flag for SQL Connections has been changed to true. As a result, you must specify Encrypt=false in your connection string if you do not support encryption in your database. For example, Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Encrypt=false;
   3. Authentication Mode (required): Set to "Use Service Account" by default.
      
6. Click Save.

Note: SQL Database connections are supported only for SQL Server data providers. 

How to Add Your Dataset Contents

1. In the left panel, click Datasets > New Dataset> SQL Dataset.
   
2. The Edit SQL Dataset page appears.
   
   1. Title: Enter a unique, descriptive title. This field is required.
   2. Connection: Enter the name that you specified in the SQL Dataset Connection page above. This field is required
   3. SQL Query: Using the information associated with this field, enter a valid SQL query statement that specifies which fields should be located in the second table. This statement interrogates the table for the connection information. This field is required.
      Note: if you use an alias in the SQL query that begins with "SPW_", metadata will not be created for that specific column. For example, the following query will not create metadata for the dest_id column: SELECT id,contentid,uniqueHash,lastupdate_d, dest_id AS SPW_DEST FROM [dbo].[items]
   4. SQL Filter Clause
      1. Required
      2. Using the information associated with this field, enter a valid SQL filtering condition. For example:
         • Bracketed [] names for replacement purposes. For example, specify [ID],'P2'.
         • Typed parameters to improve performance, such as @S_PARAMNAME for strings, @I_PARAMNAME for integers where PARAMNAME can be any name.
           
      Parameter names must be written in uppercase. Parameter names that are written in lowercase, such as @i_paramname, will not be recognized.
   5. Property Prefix (required): Property name prefix. Set to "ESC_" by default.

Metadata Settings

1. Select the Metadata Settings tab.
   1. Active: When enabled, any metadata added to this content source will be crawled and indexed. This setting is enabled by default.
   2. Refinable: When enabled, the metadata properties are used a refiner for front-end search results. This setting is disabled by default.
   3. Retrievable: When enabled, the content of the metadata will be able to be returned in search results. This setting is disabled by default.
   4. Searchable: When enabled, you can query against a specific metadata property. This setting is disabled by default.
   5. Full text indexable: When enabled, you can query against the content of a managed property. This setting is disabled by default.
   6. Exact match: When enabled, queries will only return exact matches to the content of the property. This setting is enabled by default.
   7. Sortable: When enabled, you can sort the result set based on the property before the result set is returned. This setting is enabled by default
2. Click Save to save your changes.
   
3. Click Datasets > <name of your newly-added SQL database> >  > Metadata to see a list of the metadata fields.
   

Advanced Settings

1. Select the Advanced Settings tab.
   1. Enable caching: When enabled, SQL Dataset content is cached for increased performance. Disabled by default.
   2. Cache Timeout: Time out in seconds inbetween cache refreshes.
   3. Cache Limit: The number of items that are stored in the cache (5-5000).
   4. Script Library: VBScript functions to be used in all other scripts.

How to Create a Content Source

Use the Content Sources page to create a new SQL or web service connector. The following steps detail how to create a new SQL connector.

1. Navigate to Content Sources.
   
2. Click New... > SQL Content from the drop-down menu.
   
3. The Edit Database Content page appears.
   
4. In the Edit Database Content page, complete the fields. For more information, see How to Set Up Your Content Source for Indexing.
5. Click the Item Enumeration tab. The Item Enumeration page appears. Enter the following information:
   
   1. Enumeration Query: You can copy-paste the example below this field and edit this code.
   2. Test/Load metadata: Click to confirm that your code is correct.
   3. Item extension: Enter an extension such as [SPW_EXTENSION].
      
6. Click the Item Definition tab.
   
7. In the Item Query field, copy-paste the example shown in the UI below this field and edit the code as appropriate to your environment and needs.
   
8. Scroll down to see more fields.
   
9. Enter your information as appropriate in the following fields: 
   1. Test/Load metadata: Click to confirm that you code is correct.
   2. Item Url: Enter a URL, such as www.google.com.
   3. Item Title: Go to the "Pick column" drop-down menu on the far right and click an item such as Title.
      
10. Click the Dataset Mappings tab.
11. Click Add dataset mapping and select your SQL dataset from the drop-down menu.
    
12. Under the Actions column, click > Edit. The Edit Dataset Mapping pop-up displays.
13.  Enter the following information:
    
    1. Parameter: You must link this parameter to one of the items in the content source.
    2. Pick column: Select the column to map to.
    3. Save: Click Save.

How to View the Added Metadata

1. Navigate to the Content Sources page.
   
2. Click <your content source> > > Edit > Metadata.
   
3. Click Generate.