Create the Content Source

A Content source determines the data that can be retrieved using an SQL connection.

For this reason, you must configure your SQL connection before you add a content source.

This section explains the configurations that are available when you perform one of the following:

• Create a connection to a new content source
• Edit an existing content source.

The content repository connection information is available within the Content Info tab on the SQL Content page.

Other tabs on the SQL Content page let you refine some of this information.

When you set up your content source, there are multiple components, displayed as pages, to configure:

1. Content Source
2. Item Enumeration
3. Item Definition
4. Security Mappings
5. Dataset Mappings
6. Advanced Settings

Set Up the Content Source

Use the following steps to set up the SQL content source:

1. Open BA Insight Connectivity Hub.
2. Select Content Sources from the top menu.
3. Click New -> SQL content.
4. The SQL Content Page appears.
   
   
5. Target:
   1. Required
   2. Select your desired target form the drop-down menu.
6. Connection:
   1. Required
   2. Select an SQL Connection, such as SQL Server Connector.
   3. All configured SQL connections are available.
7. Title:
   1. Required.
   2. Enter a title for this content source.
8. Crawl Start Date:
   1. Required
   2. Leave the default entry 1/1/1990 or enter another date for the using the format MM/DD/YYYY
   3. This is the earliest date for items that are returned by the crawl operation.
      

9. Max Paging Size

   1. Required

   2. Select the page size to be used during item enumeration.

   3. While indexing, SharePoint processes each page of items before requesting the next page.
      

   Note: Max Paging Size and Crawl Start Point only function when the SPW_LASTUPDATE or SPW_LASTUPDATEUTC column is used in the item enumeration query.
   For more information, see the topic "Specify the Item Enumeration."

10. Content Localization
    1. Required
    2. This value identifies the locale of the content for SharePoint.
    3. Leave the default setting 1033 (English) or see the link on the page for other options.
11. Property Prefix:
    1. A prefix for the metadata property titles.
    2. Default is “ESC_”
       
12. Click Save now or when you have made all of your changes to the information in all of the accessible tabs on this page.

Specify the Item Enumeration

Use the Item Enumeration page to specify all of the content to be indexed.

To specify the Item Enumerator settings, follow these steps:

1. Click Item Enumeration on the SQL Content page and the Item Enumerator page appears.
   
2. Item Enumerator SQL
   1. Required
   2. Specify a unique identifier for all of the items that are crawled in this content source.
   3. These items should be returned in ascending order based on the updated date of the item (when possible.)
   4. The expected columns to be returned are:
      

      Table 1: Enumerator Return Columns

      Identifier	Description
      SPW_ID	(Required) This is the unique identifier for the item.
      SPW_SUBID	(Optional) This is used if a two-part identifier is necessary. Typically, this identifier is only used for versioning.
      SPW_LASTUPDATE or SPW_LASTUPDATEUTC	(Optional) This column provides the date and/or time that the item was last modified on. Specify SPW_LASTUPDATE if the date is in the local time to the SharePoint indexing server. Specify SPW_LASTUPDATEUTC if the date is already in UTC time.

      Table 2: The Unique Identifiers

      Identifier	Description
      File Extension	Optional The file extension might be returned in the enumeration, although any name can be used for this column. Connectivity Hub uses this extension when determining whether the item should be passed to the index (which is determined by the allowed extension rules in your target.)

      To support incremental crawls and pagination during full crawls, include a where clause.

      • This clause limits the results to the items that are modified after a passed-in date parameter.

      • This parameter is either @SPW_ LASTUPDATE or @SPW_LASTUPDATEUTC.

      • See the sample query that is provided below Item Enumerator SQL.

      Note: Before you move to the next step, click Test and Load Metadata in order to populate the query columns in the Item Extension drop-down list.

3. Item Extension:
   1.  Optional, but helpful.
   2. This value identifies the item extension that is available to SharePoint during indexing.
   3. You can specify this extension by performing any of the following operations:
      1. Enter a static text value into the text box.
      2. Reference DS Columns with brackets:
         1. Select the column name from the drop-down list.
         2. This selection identifies the column from the item enumeration that specifies the extension for each record.
         3. When you perform this operation, you see the column name inside square brackets that appear in the text box.
      3. Click Advanced and use the text field to enter a VB Script that determines the extension for each item.
         
4. Click Save now or when you have made all changes to the information in all of the accessible tabs on this page.

Specify the Item Definition

Click the Item Definition tab to specify the item information for the items that you choose to index.

This item information is the information to be included in the SharePoint index and available in the search results.  

To specify the Item definition information, follow these steps:

1. Click the Item Definition tab on the SQL content page.
   
2. Item SQL:
   1. Enter the query that returns the data for each item.
   2. There are no required columns to return.
   3. However, the columns that are returned can be used in the remaining fields on this page to define the item, and in the metadata available to this content source.
   4. The following column names have special meaning; they are optional. 
   5. The columns and their use are as follows:
      

      Table 1: Optional Columns

      Column	Description
      SPW_DELFLAG	This column identifies whether the item should be removed from the index or not indexed. A return value of t: true, y: yes, or 1 marks the item as deleted.
      SPW_LASTUPDATE or SPW_ LASTUPDATEUTC	Sets the modified date for the item in the SharePoint index.

      You must also provide the item ID as a parameter. The following parameters are used:

      Table 2: Item ID Parameters

      Parameter	Description
      @S_SPW_ID (string) or @I_SPW_ID (integer)	(Required) The unique identifier for your record is passed to the query. For this reason, only the data for the requested item is returned. The value comes from the SPW_ID column in your item enumerator query.
      @S_SPW_SUBID (string) or @I_SPW_SUBID (integer)	(Optional) In order to use this value, the item enumerator query must provide the SPW_SUBID.

      Note: After you enter your statement, click Test and Load Metadata to validate your query and load the drop-down lists.

3. Unstructured Data Options (required): Use the drop-down list to identify the data item for this record, such as a file or text data:
   1. Leave the default selection None and only metadata is used, or select:
      1. Text: database columns are used
         1. File: network shares are used

         2. File: database columns are used

         3. File: zip files are created

   2. Use the text box to identify the network share location, or database column that contains your binary or text data.

   3. Reference DS Columns with brackets: 

      1. Use the drop-down list to select a column. 

      2. The column selector is used to insert columns that are returned in the item definition query into the text box.

   4. Alternatively, click Advanced and enter a VB.NET script.
   5. Click Compile to validate and use the entered script.
      1. Using the Advanced script overrides any value set in the text box.
         
4. Use the following steps for the following fields:
   1. Item URL (required)
   2. Item Title (required)
   3. Item Author

5. Use the text box to enter a string value for the property. 

   1. You can enter static strings and column values. 

   2. To reference columns values from the item definition SQL, enter the column name in square brackets ([]). 

      • The drop-down list can also be used to select a column from the item definition SQL and to insert this column name into the text area.

      • Click Advanced and use the associated text area to write a VB script  that assigns a value.  

        • When you perform this operation, any string that  you enter into the text box is ignored. See step 3.d: Note, above. 
          

6. Click Save now or when you have made all of your changes to the information in all of the accessible tabs on this page.

Specify the Security Mappings

Use the Security page to determine what information is made available to the specified users and groups by the Connector.

To specify the Security Settings, follow these steps:

1. Click Security on the SQL Content page.
   

2. If you leave Public Flag empty all information is made public to all of your users and groups.

   1. You can also choose a column from the item definition query that will indicate whether the content is public.

   2. The column should be a boolean value, true indicating public.

   3. Use the following table to specify the parameters or return values identified in the following steps and use with grant and deny queries.

   Identified	Description
   @S_SPW_ID or @I_SPW_ID	Required. Input parameter that is used to identify the item.
   @S_SPW_SUBID, @I_SpW_SUBID	Optional. Input Parameter (Optional), used as a secondary identifier for the item.
   SPW_USERID	Returned Column, identifies users with access to the item.
   SPW_GROUPID	Returned Column, identifies groups with access to the item.

3. User Grant SQL:A SQL statement that returns the user IDs of users that have permission to access this item:
   
   1. Required Parameter: @S_SPW_ID (for string) or @I_SPW_ID (for integer)
   2. Optional Parameter: @S_SPW_SUBID (for string) or @I_SPW_SUBID (for integer)
   3. Required Columns: SPW_USERID
      
4. User Deny SQL: A SQL statement that returns the user IDs of users that are specifically denied access to this item:
   
   1. Required Parameter: @S_SPW_ID (for string) or @I_SPW_ID (for integer)
   2. Optional Parameter: @S_SPW_SUBID (for string) or @I_SPW_SUBID (for integer)
   3. Required Columns: SPW_USERID
      
5. Group Grant SQL: A SQL statement that returns the group IDs of groups or roles that have permission to access this item:
   
   1. Required Parameter: @S_SPW_ID (for string) or @I_SPW_ID (for integer)
   2. Optional Parameter: @S_SPW_SUBID (for string) or @I_SPW_SUBID (for integer)
   3. Required Columns: SPW_GROUPID
      
6. Group Deny SQL. A SQL statement that returns the group IDs of groups or roles that are denied access to this item:
   
   1. Required Parameter: @S_SPW_ID (for string) or @I_SPW_ID (for integer)
   2. Optional Parameter: @S_SPW_SUBID (for string) or @I_SPW_SUBID (for integer)
   3. Required Columns: SPW_GROUPID
      
7. Enter a VB.NET script into ACL Script to specify limitations on group and user access.
   1. To write VB.Net scripts, see the Connector Framework Help/Connectivity Hub help.
      
8. Save now or when you have made all of your changes to the information in all of the accessible tabs on this page.

Specifying the Dataset Mappings

Dataset mappings map columns from this content source to dataset query parameters. 

• Details on how to create and use datasets can be found here.

• You can find additional info in the Connectivity Hub Help. 

• To access the Dataset Mappings page, click Dataset Mappings on the SQL Content page and the Dataset Mappings page appears.

Configuring Advanced Settings

Advanced settings are optional. 

For more information on how to use these settings, please contact BA Insight to work with a Solutions Consultant.

To specify advanced settings, use this information:

1. Cache Database.
   1. If you have multiple connector cache databases configured, this drop-down determines which database is used for this content source.
      
2. Filter Script.
   1. VB script executed for each item while crawling.
   2. Return ‘True’ to include the item in the index. 
   3. Return ‘False’ to skip that item and not index it.
      
3. Alternate Date Replace is used to specify the data type and format passed to the database for date parameters.
   
4. Change Log Entry Format
   1. Specifies the format used for item identifiers when building the change during incremental crawls.
   2. The default value ‘[SPW_ID];#[SPW_SUBID]’ treats each item as a unique, single item
      
5. Additional Changes SQL
   1. Is a script that is similar to the enumeration query and will add items to the change log. 
   2. Required to return the column SPW_ID (and SPW_SUBID when used) as the identifier for the items to be crawled. 
   3. Either @SPW_ LASTUPDATE or @SPW_LASTUPDATEUTC is required for incremental updates.
      
6. Metadata Generation SQL
   1. SQL statement used to override the default behavior when auto-generating metadata for this content source.

   2. Required Columns: SPW_PROPERTY, SPW_COLUMN, SPW_COLTYPE ('T' = text, 'N' = float, 'I' = integer, 'D' = date, 'B' = boolean)

   3. Optional Column: SPW_DESC, SPW_SCRIPT, SPW_MULTIFLAG ('T','F'), SPW_MULTIINDEX

Access Existing Content

After you add new content, you can:

• Access information about this content
• Edit, add, and delete this information

To access existing content, follow these steps:

1. In Connectivity Hub, click Content Sources.
   
2. Click the following links under Actions:
   1. Clone: Create a clone of the selected Content Source.
   2. Test: For more information, see Running the Test Bench.
   3. Tasks: For more information, see Managing Tasks.
   4. Metadata: For more information, see Filter Your Metadata.