Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.3 Part Number E19039-01 |
|
|
PDF · Mobi · ePub |
This section describes how to configure Oracle Loyalty Analytics used with a Siebel CRM source system. It contains the following topics:
Section 13.3, "Configuration Steps for Controlling Your Data Set"
Section 13.4, "Configuring SQL Transformations in Informatica for Database Dependencies"
Oracle Loyalty Analytics helps you analyze the effectiveness of your customer loyalty programs. It is designed to help loyalty organizations leverage the power of timely, actionable information to improve the quality of decisions and ultimately optimize performance. Oracle Loyalty Analytics provides insight into the following Siebel Loyalty areas:
Loyalty programs
Membership activities
Transactions
Effectiveness of promotions
Partner activities
Finance
Oracle Loyalty Analytics supports Siebel 8.1.1 and the Universal adapter.
The RPD contains preconfigured roles for Oracle Loyalty Analytics dashboard users. Each role is based on a particular Siebel Loyalty role and the responsibilities defined for that role. The roles determine which dashboards users can access. Table 13-1 describes the roles in the RPD for Oracle Loyalty Analytics.
Table 13-1 Oracle Loyalty Analytics Roles
Role | Description | Experience Level | Usability Requirements and Expectations |
---|---|---|---|
Loyalty Marketing Manager |
This person is responsible for the overall health and status of the Loyalty program. Responsible for creating loyalty promotions and measuring the effectiveness of the program and promotions. |
This person is an experienced and savvy marketing manager. |
Expects to review the current status of the Loyalty Program on a daily basis. Expects to review the Loyalty Program Membership trends and Member activity regularly and to measure the effectiveness of the loyalty program and promotions. |
Loyalty Partner Manager |
This person is responsible for managing the partners and partner relationships in Loyalty Program. This person decides who should stay a partner in the program. |
This person is an experienced marketing manager who is responsible for maintaining relationships with the partners. |
Expects to be able to review the current status of partner accounts and points liability. Expects to be able to review financial aspects of partner billing and member activity with respect to the partner products. |
Loyalty Program General Manager |
Business head of the Loyalty program and is responsible for the program's effectiveness in achieving the underlying CRM and business goals. |
An experienced user. |
The Loyalty program GM is expected to monitor the following areas:
|
Loyalty Administrator |
Administrator |
An experienced user. |
This user has access to all dashboards and reports. |
This section contains configuration steps that you need to perform on Oracle Loyalty Analytics before you do a full data load. It contains the following topics:
Section 13.2.2, "How to Configure the domainValues_Loyalty_Statement_Type.csv"
Section 13.2.3, "How to Configure the domainValues_Member_Status.csv"
Section 13.2.4, "How to Configure the domainValues_Transaction_Status_Types.csv"
Section 13.2.5, "How to Configure the domainValues_Loyalty_Tier_movement_Type.csv"
Section 13.2.6, "How to Configure the file_Redeemed_Unit_Cost.csv"
Note:
In addition to the configuration information in this section, refer to the Oracle Loyalty Analytics Bus Matrix on the My Oracle Support Web site for detailed information about the dimensions that are used for the Oracle Loyalty Analytics metrics.Table 13-2 lists the CSV worksheet files and the domain values for Oracle Loyalty Analytics.
Table 13-2 Domain Values and CSV Worksheet Files for Oracle Loyalty Analytics
Worksheet File Name | Description | Session |
---|---|---|
domainValues_Loyalty |
Lists the Loyalty Statement Types and corresponding domain values for the Siebel application. |
SDE_SBL_LoyStatementFact |
domainValues_Member_Status.csv |
Lists the Member Statuses and corresponding domain values for the Siebel application. |
SDE_SBL_LoyCodeDimension_MemberStatus |
domainValues_Transaction_Status_Types.csv |
Lists the Transaction Status Types, Transaction Types, and Transaction Sub Types and corresponding domain values for the Siebel application. |
SDE_SBL_LoyTransactionIdDimension SDE_SBL_LoyActivityFact |
domainValues_Loyalty_Tier_movement_Type.csv |
Lists the Loyalty Tier Movement Types and corresponding domain values for the Siebel application. |
SIL_LoyTierMoveTypeDimension SIL_LoyTierMoveTypeDimension_Full |
file_Redeemed_Unit_Cost.csv |
Lists the Unit Rates and corresponding domain values for the Siebel application. |
SDE_SBL_LoyCostFact |
This section explains how to configure the domainValues_Loyalty_Statement_Type.csv.
Identify the Loyalty Statement Type in your Oracle source system by using the following SQL:
SELECT S_LST_OF_VAL.CREATED, S_LST_OF_VAL.LAST_UPD, S_LST_OF_VAL.LAST_UPD_BY, S_LST_OF_VAL.NAME, S_LST_OF_VAL.VAL, S_LST_OF_VAL.LANG_ID, S_LST_OF_VAL.CREATED_BY FROM S_LST_OF_VAL WHERE TYPE = 'LOY_STMT_TYPE_CD'
Using a text editor, open the domainValues_Loyalty_Statement_Type.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the values in the VAL column to the STMT_TYPE_CODE and W_STMT_TYPE_CODE columns in the CSV file.
Copy the values in the NAME column to the STMT_TYPE_NAME and W STMT_TYPE_NAME columns in the CSV file.
Save and close the file.
Table 13-3 Sample Domain Values Defined in the domainValues_Loyalty_Statement_Type.csv File
STMT_TYPE_CODE | STMT_TYPE_NAME | W_STMT_TYPE_CLASS | W_STMT_TYPE_CODE | W_STMT_TYPE_NAME |
---|---|---|---|---|
Accrual Dispute |
Accrual Dispute |
STATEMENT_TYPE |
Accrual Dispute |
Accrual Dispute |
Member Gift Points |
Member Gift Points |
STATEMENT_TYPE |
Member Gift Points |
Member Gift Points |
Member Points Adjustments |
Member Points Adjustments |
STATEMENT_TYPE |
Member Points Adjustments |
Member Points Adjustments |
Member Points Purchase |
Member Points Purchase |
STATEMENT_TYPE |
Member Points Purchase |
Member Points Purchase |
Member Points Transfer |
Member Points Transfer |
STATEMENT_TYPE |
Member Points Transfer |
Member Points Transfer |
Member Statement |
Member Statement |
STATEMENT_TYPE |
Member Statement |
Member Statement |
Membership Cancellation |
Membership Cancellation |
STATEMENT_TYPE |
Membership Cancellation |
Membership Cancellation |
Membership Enrollment |
Membership Enrollment |
STATEMENT_TYPE |
Membership Enrollment |
Membership Enrollment |
Membership Renewal |
Membership Renewal |
STATEMENT_TYPE |
Membership Renewal |
Membership Renewal |
Partner Billing |
Partner Billing |
STATEMENT_TYPE |
Partner Billing |
Partner Billing |
Partner Statement |
Partner Statement |
STATEMENT_TYPE |
Partner Statement |
Partner Statement |
Redemption Voucher Issue |
Redemption Voucher Issue |
STATEMENT_TYPE |
Redemption Voucher Issue |
Redemption Voucher Issue |
Status of Membership |
Status of Membership |
STATEMENT_TYPE |
Status of Membership |
Status of Membership |
Tier Change |
Tier Change |
STATEMENT_TYPE |
Tier Change |
Tier Change |
Voucher Cancellation |
Voucher Cancellation |
STATEMENT_TYPE |
Voucher Cancellation |
Voucher Cancellation |
This section explains how to configure the domainValues_Member_Status.csv file.
Identify the Member Status in your Oracle source system by using the following SQL:
SELECT S_LST_OF_VAL.CREATED, S_LST_OF_VAL.LAST_UPD, S_LST_OF_VAL.LAST_UPD_BY, S_LST_OF_VAL.NAME, S_LST_OF_VAL.VAL, S_LST_OF_VAL.LANG_ID, S_LST_OF_VAL.CREATED_BY FROM S_LST_OF_VAL WHERE TYPE = 'LOY_MEMBER_STATUS'
Using a text editor, open the domainValues_Member_Status.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the values in the VAL column to the STATUS_CODE and W_STATUS_CODE columns in the CSV file.
Copy the values in the NAME column to the STATUS_NAME and W STATUS_NAME columns in the CSV file.
Save and close the file.
Table 13-4 Sample Domain Values Defined in the domainValues_Member_Status.csv File
STATUS_CODE | STATUS_NAME | W_STATUS_CLASS | W_STATUS_CODE | W_STATUS_NAME |
---|---|---|---|---|
Active |
Active |
LOY_MEMBER_STATUS |
ACTIVE |
Active |
Inactive |
Inactive |
LOY_MEMBER_STATUS |
INACTIVE |
Inactive |
Pending |
Pending |
LOY_MEMBER_STATUS |
ACTIVE |
Active |
Cancelled |
Cancelled |
LOY_MEMBER_STATUS |
CANCELLED |
Cancelled |
Payment Pending |
Payment Pending |
LOY_MEMBER_STATUS |
ACTIVE |
Active |
Dormant |
Dormant |
LOY_MEMBER_STATUS |
INACTIVE |
Inactive |
Merged |
Merged |
LOY_MEMBER_STATUS |
ACTIVE |
Active |
Pre Allotted |
Pre Allotted |
LOY_MEMBER_STATUS |
ACTIVE |
Active |
This section explains how to configure the domainValues_Transaction_Status_types.csv file.
Identify Transaction Status Types, Transaction Types, and Transaction Sub Types in your Oracle source system by entering the first of four SQL statements in this set of steps:
SELECT S_LST_OF_VAL.CREATED, S_LST_OF_VAL.LAST_UPD, S_LST_OF_VAL.LAST_UPD_BY, S_LST_OF_VAL.NAME, S_LST_OF_VAL.VAL, S_LST_OF_VAL.LANG_ID, S_LST_OF_VAL.CREATED_BY FROM S_LST_OF_VAL WHERE TYPE = 'LOY_TXN_STATUS_CD'
Using a text editor, open the domainValues_Transaction_Status_Types.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the values in the VAL column to the STATUS_CODE and W_STATUS_CODE columns in the CSV file.
Copy the values in the NAME column to the STATUS_NAME and W STATUS_NAME columns in the CSV file.
Enter this SQL statement:
SELECT S_LST_OF_VAL.CREATED, S_LST_OF_VAL.LAST_UPD, S_LST_OF_VAL.LAST_UPD_BY, S_LST_OF_VAL.NAME, S_LST_OF_VAL.VAL, S_LST_OF_VAL.LANG_ID, S_LST_OF_VAL.CREATED_BY FROM S_LST_OF_VAL WHERE TYPE = 'LOY_TXN_TYPE_CD'
Copy the values in the VAL column to the STATUS_CODE and W_STATUS_CODE columns in the CSV file.
Copy the values in the NAME column to the STATUS_NAME and W STATUS_NAME columns in the CSV file.
Enter this SQL statement:
SELECT S_LST_OF_VAL.CREATED, S_LST_OF_VAL.LAST_UPD, S_LST_OF_VAL.LAST_UPD_BY, S_LST_OF_VAL.NAME, S_LST_OF_VAL.VAL, S_LST_OF_VAL.LANG_ID, S_LST_OF_VAL.CREATED_BY FROM S_LST_OF_VAL WHERE TYPE = 'LOY_TXN_SUB_TYPE_CD'
Copy the values in the VAL column to the STATUS_CODE and W_STATUS_CODE columns in the CSV file.
Copy the values in the NAME column to the STATUS_NAME and W STATUS_NAME columns in the CSV file.
Enter this SQL statement:
SELECT S_LST_OF_VAL.CREATED, S_LST_OF_VAL.LAST_UPD, S_LST_OF_VAL.LAST_UPD_BY, S_LST_OF_VAL.NAME, S_LST_OF_VAL.VAL, S_LST_OF_VAL.LANG_ID, S_LST_OF_VAL.CREATED_BY FROM S_LST_OF_VAL WHERE TYPE = 'LOY_REDEMPTION_TYPE_CD'
Copy the values in the VAL column to the STATUS_CODE and W_STATUS_CODE columns in the CSV file.
Copy the values in the NAME column to the STATUS_NAME and W STATUS_NAME columns in the CSV file.
Save and close the file.
The following tables show sample domain values defined in the domainValues_Transaction_Status_Types.csv for these Loyalty status classes:
Transaction Status
Transaction Status Type
Transaction Status Sub Type
Redemption Type
Table 13-5 Sample Domain Values for Transaction Status Defined in the domainValues_Transaction_Status_Types.csv File
STATUS_CODE | STATUS_NAME | W_STATUS_CLASS | W_STATUS_CODE | W_STATUS_NAME |
---|---|---|---|---|
Cancel - Pending |
Cancel - Pending |
LOY_TXN_STATUS_CD |
Cancel - Pending |
Cancel - Pending |
Rejected - Manager |
Rejected - Manager |
LOY_TXN_STATUS_CD |
Rejected - Manager |
Rejected - Manager |
Submitted |
Submitted |
LOY_TXN_STATUS_CD |
Submitted |
Submitted |
Processed |
Processed |
LOY_TXN_STATUS_CD |
Processed |
Processed |
Accepted |
Accepted |
LOY_TXN_STATUS_CD |
Accepted |
Accepted |
Cancelled |
Cancelled |
LOY_TXN_STATUS_CD |
Cancelled |
Cancelled |
Partner Pending |
Partner Pending |
LOY_TXN_STATUS_CD |
Partner Pending |
Partner Pending |
Queued |
Queued |
LOY_TXN_STATUS_CD |
Queued |
Queued |
Rejected - Engine |
Rejected - Engine |
LOY_TXN_STATUS_CD |
Rejected - Engine |
Rejected - Engine |
In Progress |
In Progress |
LOY_TXN_STATUS_CD |
In Progress |
In Progress |
Pending Next Approval |
Pending Next Approval |
LOY_TXN_STATUS_CD |
Pending Next Approval |
Pending Next Approval |
Unspecified |
Unspecified |
LOY_TXN_STATUS_CD |
Unspecified |
Unspecified |
Approved |
Approved |
LOY_TXN_STATUS_CD |
Approved |
Approved |
Acceptable |
Acceptable |
LOY_TXN_STATUS_CD |
Acceptable |
Acceptable |
Manually Cancelled |
Manually Cancelled |
LOY_TXN_STATUS_CD |
Manually Cancelled |
Manually Cancelled |
Partner Approved |
Partner Approved |
LOY_TXN_STATUS_CD |
Partner Approved |
Partner Approved |
Partner Rejected |
Partner Rejected |
LOY_TXN_STATUS_CD |
Partner Rejected |
Partner Rejected |
Unspecified |
Unspecified |
LOY_TXN_STATUS_CD |
Unspecified |
Unspecified |
Table 13-6 Sample Domain Values for Transaction Status Type Defined in the domainValues_Transaction_Status_Types.csv File
STATUS_CODE | STATUS_NAME | W_STATUS_CLASS | W_STATUS_CODE | W_STATUS_NAME |
---|---|---|---|---|
Accrual |
ACCRUAL |
LOY_TXN_TYPE_CD |
Accrual |
Accrual |
Unspecified |
Unspecified |
LOY_TXN_TYPE_CD |
Unspecified |
Unspecified |
Redemption |
REDEMPTION |
LOY_TXN_TYPE_CD |
Redemption |
Redemption |
Service |
SERVICE |
LOY_TXN_TYPE_CD |
Service |
Service |
Partner Price |
PARTNER_PRICE |
LOY_TXN_TYPE_CD |
Partner Price |
Partner Price |
Table 13-7 Sample Domain Values for Transaction Status Sub Type Defined in the domainValues_Transaction_Status_Types.csv File
STATUS_CODE | STATUS_NAME | W_STATUS_CLASS | W_STATUS_CODE | W_STATUS_NAME |
---|---|---|---|---|
Availment |
Availment |
LOY_TXN_SUB_TYPE_CD |
Availment |
Availment |
Incorrect Accrual |
Incorrect Accrual |
LOY_TXN_SUB_TYPE_CD |
Incorrect Accrual |
Incorrect Accrual |
Manual Credit |
Manual Credit |
LOY_TXN_SUB_TYPE_CD |
Manual Credit |
Manual Credit |
Point Purchase |
Point Purchase |
LOY_TXN_SUB_TYPE_CD |
Point Purchase |
Point Purchase |
Manual Debit |
Manual Debit |
LOY_TXN_SUB_TYPE_CD |
Manual Debit |
Manual Debit |
Reactivation |
Reactivation |
LOY_TXN_SUB_TYPE_CD |
Reactivation |
Reactivation |
Transfer |
Transfer |
LOY_TXN_SUB_TYPE_CD |
Transfer |
Transfer |
Transfer Points |
Transfer Points |
LOY_TXN_SUB_TYPE_CD |
Transfer Points |
Transfer Points |
Cancellation |
Cancellation |
LOY_TXN_SUB_TYPE_CD |
Cancellation |
Cancellation |
Lounge Purchase |
Lounge Purchase |
LOY_TXN_SUB_TYPE_CD |
Lounge Purchase |
Lounge Purchase |
PromotionEnrolment |
PromotionEnrolment |
LOY_TXN_SUB_TYPE_CD |
PromotionEnrolment |
PromotionEnrolment |
Voucher Reissue |
Voucher Reissue |
LOY_TXN_SUB_TYPE_CD |
Voucher Reissue |
Voucher Reissue |
Unspecified |
Unspecified |
LOY_TXN_SUB_TYPE_CD |
Unspecified |
Unspecified |
Enrolment |
Enrolment |
LOY_TXN_SUB_TYPE_CD |
Enrolment |
Enrolment |
Loan |
Loan |
LOY_TXN_SUB_TYPE_CD |
Loan |
Loan |
Auto Accrual |
Auto Accrual |
LOY_TXN_SUB_TYPE_CD |
Auto Accrual |
Auto Accrual |
Gift |
Gift |
LOY_TXN_SUB_TYPE_CD |
Gift |
Gift |
Missing Accrual |
Missing Accrual |
LOY_TXN_SUB_TYPE_CD |
Missing Accrual |
Missing Accrual |
Loan Repayment |
Loan Repayment |
LOY_TXN_SUB_TYPE_CD |
Loan Repayment |
Loan Repayment |
Membership Renewal |
Membership Renewal |
LOY_TXN_SUB_TYPE_CD |
Membership Renewal |
Membership Renewal |
Product |
Product |
LOY_TXN_SUB_TYPE_CD |
Product |
Product |
Gift Points |
Gift Points |
LOY_TXN_SUB_TYPE_CD |
Gift Points |
Gift Points |
Membership Cancellation |
Membership Cancellation |
LOY_TXN_SUB_TYPE_CD |
Membership Cancellation |
Membership Cancellation |
Table 13-8 Sample Domain Values for Redemption Type Defined in the domainValues_Transaction_Status_Types.csv File
STATUS_CODE | STATUS_NAME | W_STATUS_CLASS | W_STATUS_CODE | W_STATUS_NAME |
---|---|---|---|---|
Product |
Product |
LOY_REDEMPTION_TYPE_CD |
Product |
Product |
Loan Repayment |
Loan Repayment |
LOY_REDEMPTION_TYPE_CD |
Loan Repayment |
Loan Repayment |
Expired |
Expired |
LOY_REDEMPTION_TYPE_CD |
Expired |
Expired |
This section explains how to configure the domainValues_Loyalty_Tier_movement_Type.csv file.
Note:
This table is used after staging and therefore cannot be configured until after staging is complete.Identify the Loyalty Tier Movement Type in your Oracle source system by using the following SQL:
SELECT W_CODE_D.SOURCE_CODE, W_CODE_D.SOURCE_CODE_1, W_CODE_D.SOURCE_NAME_1, W_CODE_D.CATEGORY, W_CODE_D.LANGUAGE_CODE, W_CODE_D.MASTER_CODE, W_CODE_D.MASTER_VALUE FROM W_CODE_D WHERE CATEGORY = 'LOY_MEM_TIER_APPR_STATUS_CD'
Using a text editor, open the domainValues_Loyalty_Tier_movement_Type.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the values in the SOURCE_CODE column to the TIER_MOVE_CODE and W_TIER_MOVE_CODE columns in the CSV file.
Copy the values in the SOURCE_NAME_1 column to the TIER_MOVE_NAME and W_TIER_MOVE_NAME columns in the CSV file.
Save and close the file.
The file_Redeemed_Unit_Cost.csv flat file is used to retrieve a unit rate associated with a program, product, and partner. The unit rate is used to calculate the amount of redemption points. This calculation is in accordance to the International Financial Reporting Interpretations Committee (IFRIC) guidelines on how to recognize revenue in Loyalty.
To configure the file_Redeemed_Unit_Cost.csv:
Using a text editor, open the file_Redeemed_unit_cost.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Enter the appropriate data for the following columns:
Column Name | Description |
---|---|
PARTNER_ID | Name of the partner of the product. |
PRODUCT_ID | Name of the product being redeemed. |
PROGRAM_ID | Name of the program the product is associated with. |
CURCY_CD | Currency code of the points. |
COST_PER_POINT | The cost per point for redeemed points. |
Save and close the file.
This section contains additional configuration steps for Oracle Loyalty Analytics. It contains the following topic:
The LoyaltyTotalRevenueInput.xls file is used to retrieve data about the total revenue, which is used in comparison metrics under the revenue and member analysis areas. This flat file contains the following columns:
Total Revenue
Document Currency Code
Exchange Rate
Simple Time Dimension attributes
Note:
This flat file is used to retrieve the Total Revenue amount; the only dimension analysis that can be performed is on date and time.You import the data from this flat file into the RPD Physical layer using the Excel spreadsheet option. You need to create an ODBC data source named "Loyalty Input Data Source" and point it to the flat file for the data import.
For information about how to configure an ODBC data source name (DSN), see Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition.
You must also enter this table under Loyalty Input Connection Pool in the Physical layer of the RPD.
To configure the LoyaltyTotalRevenueInput.xls:
Using a text editor, open the LoyaltyTotalRevenueInput.xls located in the MW_HOME\biapps\dwrep\Loyalty Analytics folder.
Enter the appropriate data for the following columns:
Column Name | Description |
---|---|
Total Amt | Total revenue amount for the year, quarter, and month. |
DOC_Curr_Code | The currency code. |
Global1_Exchange_Rate | The exchange rate to be used to exchange amount from currency to company currency. |
Date Key | DATE_KEY in the spreadsheet should be the same ROW_WID value from W_DAY_D, for example 20081231, which stands for December 31, 2008. |
Year | The applicable year. |
Qtr | The applicable quarter. |
Month | The applicable month. |
Save and close the file.
This section describes how to configure SQL transformations for the "PLP_LoyMemberTierMovementQtrAggr" task in Informatica to work with Microsoft SQL Server, DB2, and Teradata databases.
To configure SQL transformations in Informatica for database dependencies:
In Informatica PowerCenter Designer, connect to the repository.
Open the PLP folder.
Check out the mapping PLP_LoyMemberTierMovementQtrAggr.
Perform the following steps for your database version:
For data warehouses on Microsoft SQL Server databases:
Open the SQL_LoyMemberTierMove_Agg SQL transformation and display the SQL Settings tab. Then, change the Database Type to Microsoft SQL Server.
Display the SQL Ports tab. Then, change the Native Type datatype for ALL columns that read 'bit' and change the value to 'varchar'.
For data warehouses on DB2 databases:
Open the SQL_LoyMemberTierMove_Agg SQL transformation and display the SQL Settings tab. Then, change the Database Type to DB2.
Display the SQL Ports tab. Then, change the Native Type datatype for ALL columns that read 'char' and change the value to 'varchar'.
For data warehouses on Teradata databases:
Open the SQL_LoyMemberTierMove_Agg SQL transformation and display the SQL Settings tab. Change the Database Type to TeraData.
Display the SQL Ports tab. Then, change the Native Type datatype for ALL columns that read 'char' and change the value to 'varchar'.
Check in the mapping PLP_LoyMemberTierMovementQtrAggr.