Oracle Marketing Segmentation Guide > Configuring Marketing Module Metadata > Setting Up Marketing Segmentation Metadata >
Enable Sampling for the Target Level
To set up sampling for a given target level, you must copy the relevant tables and map the copied tables into the metadata. Use the guidelines in the following topics:
Create the Underlying Physical Sampling Tables
The first step is to create the underlying physical tables. This step must be completed by an administrator.
- First identify the desired target level and sampling factor.
- Generate DDLs for all physical dimension and fact tables to be sampled.
- Rename the physical table and index names to include the target level and sampling factor. For example, if the original table name was W_PERSON_D. A sampled table name for a 1 percent sample of Contacts could be M_C1_PERSON_D.
- Populate M_C1_PERSON_D with 1 percent of W_PERSON_D.
- Set up the join relationship between the sample contact table and the remaining sampled tables. For example, to populate a campaign history sample table, you would include fact records that join to the sample contact table.
Map the Sample Tables into the Marketing Metadata
After you create and populate the sample tables, you can map them into the Marketing Metadata. Sampling works by using dynamic table names. If a base table such as W_PERSON_D is sampled, the physical table object uses a dynamic name that is based on a session variable. At run-time, depending on the selected sampling factor, the value of the session variable is set to the physical sample table. This causes the BI Server to generate the physical SQL against the sampled table (not against the original base table).
- For each dimension or fact table that you have sampled, create a corresponding session variable. For example, if you sampled W_PERSON_D, you need a session variable named SAMPLE_W_PERSON_D. The initialization block for this variable needs to default the variable value to the original table name, in this case, W_PERSON_D. The same session variable is used across different sampling tables.
- For all sampled tables, find the base physical table object and set the dynamic table name to use the session variables created in the previous step.
- In the Target Level, in the Sampling Tables dialog box, type all the physical sample tables that you created for this target level and sampling factor combination. The Sampled Physical Table Name corresponds to the actual table name in the database. The Repository Table Object corresponds to the table for which you set the dynamic table name. The Factor corresponds to the percentage at which you sampled the table.
Example of How to Map the Sampling Tables into the Marketing Metadata
The example in this topic assumes that the tables needed for obtaining sampled counts on the target level have been created. To correctly map the sampling tables, perform the procedures in this topic in the sequence shown.
To set up session variables for mapping sampling tables to the marketing metadata
- Shut down the Oracle BI Server and the Oracle BI Web service.
- In the Administration Tool, open your repository.
- Map all the sample tables in the physical layer by performing the following steps:
- From the toolbar menu, choose Manage > Variables.
- In the left pane, select Initialization Block, and then in the right pane, right-click and choose New Initialization Block.
- In the Initialization Block dialog box, type the following information in the appropriate fields:
- From the drop-down list under the Name field, choose associate with session variable.
- In the text box, the string (SAMPLE in the example) is not a significant value. You can type any string.
- In the Connection Pool field, choose the connection pool in which the sample table has been mapped.
- Click the Variables tab, then click New.
- In the Session Variables dialog box, from the Type drop-down list, select Session.
- Check the following check box so that non-administrators can set this variable for sampling:
Enable any user to set the value
- In the Default initializer text box, click the ellipsis button.
- In the Expression Builder dialog box, select the target-level table (the dimensional table that represents the target level).
- Click OK three times, and then close Variable Manager dialog box.
To map sampling tables to the marketing metadata
- In the Physical layer, expand the Physical database in which the target-level table and its samples exist, and then double-click the target-level table name.
- In the Physical Table dialog box, click the Dynamic Name tab.
- Select the Marketing Customer Sample Table variable in the list and perform the following steps.
- In the Dynamic Name tab, click Select.
- Verify that the read-only field shows the name of the variable, and then click OK.
- From the toolbar menu, choose Manage > Marketing.
- In the Marketing Metadata dialog box, perform the following steps for every sampling table for this target-level table.
- In the left pane, click Target Levels, and then in the right pane, double-click Customers.
A target level named Customers is preconfigured.
- In the Target Level dialog box, click the Sampling Tables tab, and then click Add.
- In the Sampling Table dialog box, complete the fields.
Some of the fields are described in the following table.
Repository Table Object
Select a target-level table from the list of tables.
The number that represents the following calculation:
(# of rows in the sample table *100)/# of rows in the Target Level Table
- In the Sampling Table dialog box, click OK, and then in the Target Level dialog box, click OK.
- In the Marketing Metadata dialog box, from the Action menu, choose Check Marketing Metadata Consistency.
- In the Oracle BI Tool dialog box, click OK, and then close the Marketing Metadata dialog box.
- Click the save icon, and when asked if you want to check global consistency, click Yes.
For information about saving a repository and checking global consistency, see the topic about creating a new BI repository file in Oracle Business Intelligence Server Administration Guide.