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

Setting Up the Marketing List Catalogs

Setting up list catalogs is very similar to setting up any presentation catalog. A List catalog is used to generate a list of data at the target level. Lists are generated from a user interface (similar to Oracle BI Answers) that issues queries against the Oracle BI Server.

The following Siebel-specific subject areas for list generation are preconfigured for Siebel applications users:

• Marketing Account List. Based on Marketing Account List Business model this catalog is used for List output generation at the Account target level.
• Marketing Contact List. Based on Marketing Contact List Business model this catalog is used for List output generation at the Contact target level.
• Campaign Load - Accounts. Based on Marketing Account List Business model this catalog is used for generation of data used by the Siebel EAI Campaign load process for the Account Target Level.
• Campaign Load - Contacts. Based on Marketing Contact List Business model this catalog is used for generation of data used by the Siebel EAI Campaign load process for the Contact Target Level.
• Campaign Load - Prospects. Based on Marketing Contact List Business model this catalog is used for generation of data used by the Siebel EAI Campaign load process for the Prospect Data only.

See Marketing Segmentation Metadata Content Subject Area for OTBI and OBIA for a list of subject areas for OTBI and OBIA deployments.

The following topics contain guidelines that you must apply when creating a Business Model in the Administration Tool for a list catalog. Marketers have similar requirements when it comes to list generation, therefore you can also use these guidelines when setting up list catalogs:

• Define the Logical Fact
• Including Fact Information in List Reports
• Business Models Extended for List Generation
• Setting Up Cross-Database Joins
• Multiple Addresses for Individuals or Accounts

NOTE:  The following sections are not applicable for Oracle Sales Cloud applications users. The examples used are Siebel-specific are should be used only as guidelines.

Gather a List of Facts and Dimensions

Gather the list of facts and dimensions that need to be used for the generation of the List. For every dimension, identify the fact that relates this dimension to the target level Dimension. When this dimension is mapped as a logical dimension in the Administration Tool, this fact is included in the logical table source as a join. Not doing so may result in ambiguous queries and incorrect list results.

Define the Logical Fact

The logical fact of the following Siebel-specific business model is usually non-restrictive but needs to be defined. In most business models, including the ones that are used for segmentation catalogs, the <target level> logical tables are mapped as dimensions. Therefore, these business models might not be used for generating lists. You must create new business models. This topic explains how to create these business models.

Logical dimensions are joined to a logical fact table, as shown in Figure 2.

Figure 2. Siebel-specific Example of Logical Dimensions Joined to a Logical Fact Table

Including Fact Information in List Reports

If fact information needs to be included in the list reports, then each dimension needs to be mapped as dimension hierarchy. This is done because facts usually must be reported at the target level and not any other level. For example, if a list report contains the target level ID column such as Contact ID, Asset Name Column and a fact such as Total Value of Assets, then usually marketers require that the Total Value of Assets be reported at the Contact Level and not for every asset.

In Figure 3, the Service Request fact is included in the business model but needs to be reported at only the Contact level and not the Account dimension. Therefore, a Service Request related facts have been set at the All level of the Account Dimension. The Oracle BI Server reads this information and interprets that there is no detail service request information in the database for Accounts and as a result, issues a physical SQL query for service requests that does not include Accounts.

Figure 3. Example of Service Request Fact Reported at the Contact Level

Business Models Extended for List Generation

For customers who have purchased the Siebel Marketing product line from Oracle and who own the Siebel-provided List business models, business models have been extended to make sure that list generation occurs from contact information that resides in the transactional database. This is needed when marketers have added or deleted contacts manually after a campaign is loaded in the campaign contact table. When this happens, the list of target level IDs (contacts) as provided by the segment or segment tree, do not match what is in the campaign load. As a result, the output list needs to be generated from the transactional database campaign promotions table.

In the data model, this table is the S_CAMP_CON table (OLTP Campaign Promotion). Figure 4 illustrates the following:

• The OLTP Campaign Promotion (S_CAMP_CON) table is snowflaked between the OLTP Contacts (S_CONTACT) table and the Contact (W_PARTY_PER_D) table.
• The OLTP Campaign Promotion (S_CAMP_CON) table is also snowflaked between the OLTP Prospects (S_PRSP_CONTACT) table and the Contact (W_PARTY_PER_D) table.

  Figure 4. Example of Snowflaked Tables.

Setting Up Cross-Database Joins

If list information needs to be generated from the data warehouse for the contacts in the transactional database Campaign history table (S_CAMP_CON), a cross-database join needs to be created. Oracle BI supports this type of cross-database join.

To set up a cross-database join

1. In the Administration Tool, in the Physical Layer, select the transactional database Campaign History Table and the target level dimension table in the data warehouse.
2. Right-click and choose Physical Diagram, and then Selected objects only.
3. Create a Physical Join using the following syntax:

   <target level dimension>.<unique Id> = <Siebel transactional database Campaign History Table S_CAMP_CON>.<Key 01/02.../07>

   For more information about which Key column to pick, see Designing Marketing List Formats.

Multiple Addresses for Individuals or Accounts

When Individuals or Accounts have many addresses, the addresses are stored in a separate table. However, depending on the channel you use for targeting, email address might be more relevant than postal address.

When a list is generated, an inner join with the address table might result in fewer contacts because some do not have address information. To prevent this from happening, by default the S_Contact table in the transactional database has a left outer join to the address table. A left outer join is used so that contacts with no addresses can be listed. Figure 5 is an example of a logical table source for the S_Contact table.

Figure 5. Example of Logical Table Source for S_Contact Table