Oracle^® Marketing Segmentation Guide > Configuring Marketing Segmentation Metadata > Setting Up Marketing Segmentation Metadata >

Setting Up Cache for Target Levels

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

• About Marketing SQL Files and Cache
• Building a Marketing Business Model
• Creating and Mapping Marketing Presentation Catalogs

About Marketing SQL Files and Cache

For Siebel users of Marketing Segmentation, preconfigured marketing SQL files contain the DDL (data definition language) statements for creating the cache and the saved result set tables. Depending on the database that is used for segmentation, Siebel application users can open the appropriate file and execute the statements against the database.

The following guidelines apply only to users 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 the relational database management system (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 must 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 is assumed.
• Do NOT change the name and type of the GUID column.

  The value in the GUID column is a unique identifier that identifies the saved result set.

• 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.

Building a Marketing Business Model

This topic contains guidelines for building your marketing business model in the Business Model and Mapping layer of the BI Administration Tool. Figure 1 is an example to help you build your business model.

Figure 1. Example of a Mapped Fact Table

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

• Mapping Fact Tables. 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 join in the business model. After mapping a fact table, you must 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 must 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 marketing presentation catalog by performing the following tasks:

• Create a folder named Cache.
• Create a marketing presentation table. Name the Cache presentation table using the following convention:

  <target level> Cache (This identifies the cache that belongs to each target level.)

• Add columns to the marketing presentation table. For example, add the GUID column and the Qualified-ID column to the <target level> Cache folder by dragging them from the Fact - Marketing <target level> Cache logical table, shown in Figure 1.
• Associate cache metadata with a target level.

  NOTE:  Any Marketing user who writes a cache entry or saves a result set needs to be assigned the POPULATE privilege for the target database. Typically, Marketing users are associated with a group and this group is granted the privilege. Go to Manage->Security and open the Permissions dialog for either a user or group. Select the Query Limits tab and set the Populate Privilege to Allow for Marketing data warehouses. For more information, see the topic about assigning populate privilege to a User or Group in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

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 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.