Bookshelf Home | Contents | Index | PDF     

Siebel Analytics Server Administration Guide > Configuring Marketing Module Metadata > Setting Up Marketing Segmentation Metadata >

Setting Up Cache for Target Levels

This section discusses the following topics about setting up cache for target levels:

About Marketing SQL Files and Cache

In the Siebel Analytics install directory, there is a directory called schema. This directory contains MKTG.DB2.sql, MKTG.MSSQL.sql, and MKTG.Oracle.sql, the preconfigured Marketing SQL files.

These files contain the DDL (data definition language) statements for creating the cache and the saved result set tables. Depending on the database that you use for segmentation, open the appropriate file and execute the statements against the database. For more information about the SQL files and cache, see About Marketing SQL Files and Saved Result Sets.

The following guidelines apply to using the Marketing SQL files with cache:

  • Make sure that the statements are syntactically correct for the version of the database that is being used. For example, the MKTG.DB2.sql file might not contain the appropriate syntax for the specific version of DB2 that is being used.
  • Make sure that the data types of the QUALIFIED_ID column matches the data type of the target level. For example, if a cache table is being created for the target-level Household and the Household ID in the database is of type INT, then the QUALIFIED_ID should be of the same type.
  • For every target level a separate table needs to be created. The naming convention for the table is M_C_<target level>. Although the cache table can be named with any name that is database supported, for the purposes of the following discussion the existing naming convention will be assumed.
  • Do NOT change the name and type of the GUID column.
  • Execute the statement that relates to creating the Cache table and the corresponding index.
  • For information about statements relating to the creation of Saved Result Sets, see Setting Up Saved Result Sets for Target Levels.

Mapping Fact Tables and Building a Business Model

This section contains guidelines for mapping fact tables and building your business model. You can use Figure 29 to help you build your business model in the Administration Tool.

Figure 29.  Example of a Mapped Fact Table

The following examples describe the business model structure that appears in Figure 29:

  • Mapping Fact Tables. It is recommended that you use the following convention, or a similar convention, when naming fact tables:

    Fact - Marketing <target level> Cache

    For example, for the target level Contact, a table might be named Fact - Marketing Contact Cache.

  • Building a business model. After mapping a fact table, you need to set up a join only in the Business Model and Mapping layer, as described in the following example:

    The corresponding logical dimension table for the target level is joined to the Fact - Marketing <target level> Cache. The join should be a logical 1:M join starting from the target level logical dimension table (1) and going to the Fact - Marketing <target level> Cache logical table (M).

Creating and Mapping Marketing Presentation Catalogs

Create a presentation catalog and create a folder called Cache. For example, add the GUID column and the Qualified-ID column to the Cache folder by dragging them from the Fact - Marketing <target level> Cache logical table, shown in Figure 29.

It is strongly recommended that you name the Cache presentation table using the following convention:

<target level> Cache

This identifies the cache that belongs to each target levels.

NOTE:  When a criteria block is cached, the Populate Stored procedure writes the Cache/Saved Result Set value to the database. All Marketing segmentation users/groups need to be assigned this privilege using the Manage Security feature. For more information, see the topic about Assigning Populate Privilege to a User or Group in Managing Query Execution Privileges.

To associate the cache metadata with a target level

  1. In the Administration Tool, open your repository in Offline mode, using Administrator/<password>.
  2. From the toolbar, choose Manage > Marketing.
  3. In the left pane, select Qualified List Items and double-click the primary QLI of the target level that you want to enable for caching.
  4. Click the Cache Information tab and perform the following steps:
    1. Click the Cache Catalog ellipsis button and select the presentation catalog that has the presentation table for the Cache table for the target level.
    2. Click the GUID column ellipsis button and select the presentation column that has the GUID information.
    3. Click the Qualified Id column ellipsis button and select the presentation column that has the qualified ID for the target level.

      The columns and catalogs selected are the same as those that you mapped. The physical table and connection pool information is automatically selected.

  5. Verify that the information is correct. If not correct, verify that you selected the correct presentation catalog and columns as instructed in Step 4.
  6. Click Default SQL.

    A statement that is similar to the following appears:

    DELETE FROM M_C_<target level> WHERE GUID = '@{guid}'

    NOTE:  Make sure that the syntax of this statement is correct for your database. The table name might need to be fully qualified. Test this statement by inserting some value in the database table and using the delete statement to delete it.

  7. Click OK, and then check the Marketing metadata for consistency.

Setting Up Analytics Web to Populate Cache and Saved Result Sets

Some queries issued by the segmentation engine require the use of the Execute Physical stored procedure. These queries include delete statements on the cache, delete statements on the saved result sets, and insert statements for the cache and saved result set. The Execute Physical stored procedure must be run by a user with administrator privileges. The administrator user is setup in the instanceconfig.xml file.

To set up the administrative user in the instanceconfig.xml file

  1. Start the Siebel Analytics Web service, and then open your Siebel Analytics URL.

    For example, http://localhost/analytics/saw.dll?EncryptString&String=<type Siebel Analytics Admin password here>

  2. Log in as the Administrator.
  3. Copy the encrypted string in the browser window to a text file.
  4. Navigate to the instanceconfig.xml file at the following default location, and then open it using a text editor:

    SiebelAnalyticsData\Web\Config\

    CAUTION:  DO NOT OPEN in Explorer or double-click the file name.

  5. Scroll down to the <WebConfig> section that is at the bottom of the file.

    NOTE:  The instanceconfig.xml file contains two sections. The Top section is commented out. Do not modify the first set of comments.

    The <WebConfig> in your file should be similar to the file in the following example.

    <WebConfig>

    <ServerInstance>

    <AdministrativeLogin>Administrator</AdministrativeLogin>

    <AdministrativePassword>1d0f03a35f062ba39e024b20aabf2bce8a03</AdministrativePassword>

    </ServerInstance>

    </WebConfig>
  6. Replace the information in this section, using the following example as a guide.

    <WebConfig>

    <ServerInstance>

    <AdministrativeLogin>Siebel Analytics Admin User</AdministrativeLogin>

    <AdministrativePassword>Replace with the encrypted string that you obtained in the steps above</AdministrativePassword>

    </ServerInstance>

    </WebConfig>
  7. Make sure that you remove any blank spaces or new lines at the end of the encrypted password string. Your Login and Password information will be similar to the following example:

    <AdministrativeLogin>SADMIN</AdministrativeLogin>

    <AdministrativePassword>2fdsjhf344..........</AdministrativePassword>

  8. Save the instanceconfig.xml file and exit.
  9. Restart the Siebel Analytics Web service.
Testing the Administrative User Setup

For additional information about creating segments, see Siebel Marketing User Guide. For more information about the Manage Marketing Jobs feature, see Siebel Marketing Installation and Administration Guide.

  • Log in Siebel Analytics as a non-administrator user.
  • Create a simple segment, cache the criteria block, and Run Update counts.
  • The operation should execute successfully.
  • Check the log file for a POPULATE statement that is populating the M_C_<target level> table.
  • Log in Siebel Analytics as an administrator and click the Admin link.
  • Click Manage Marketing Jobs.
  • In the Database Cache section, verify that there is an entry for the criteria block that you cached.
  • Click Purge for that entry.
  • After approximately five minutes, check the log file for the execution of an appropriate DELETE statement. Verify that this statement is similar in syntax to the statement in the Administration Tool in the Cache Information tab of the primary QLI.
    
Siebel Analytics Server Administration Guide