13 Configuring Oracle Loyalty Analytics

This section describes how to configure Oracle Loyalty Analytics used with a Siebel CRM source system.

It contains the following topics:

Section 13.1, "Overview of Oracle Loyalty Analytics"
Section 13.2, "Configuration Required Before a Full Load"
Section 13.3, "Configuration Steps for Controlling Your Data Set"
Section 13.4, "Configuring SQL Transformations in Informatica for Database Dependencies"

13.1 Overview of Oracle Loyalty Analytics

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.

13.1.1 Oracle Loyalty Analytics Roles

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: Promotion effectiveness Outstanding program liability Partner profitability Effectiveness of Loyalty program offerings (Products and Services offered for Accrual and Redemption)
Loyalty Administrator	Administrator	An experienced user.	This user has access to all dashboards and reports.

13.2 Configuration Required Before a Full Load

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.1, "About Configuring Domain Values and CSV Worksheet Files for Oracle Loyalty Analytics"
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.

13.2.1 About Configuring Domain Values and CSV Worksheet Files for Oracle Loyalty Analytics

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

13.2.2 How to Configure the domainValues_Loyalty_Statement_Type.csv

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

13.2.3 How to Configure the domainValues_Member_Status.csv

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

13.2.4 How to Configure the domainValues_Transaction_Status_Types.csv

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

13.2.5 How to Configure the domainValues_Loyalty_Tier_movement_Type.csv

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.

13.2.6 How to Configure the file_Redeemed_Unit_Cost.csv

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.

13.3 Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Oracle Loyalty Analytics. It contains the following topic:

Section 13.3.1, "How to Configure the LoyaltyTotalRevenueInput.xls"

13.3.1 How to Configure the LoyaltyTotalRevenueInput.xls

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.

13.4 Configuring SQL Transformations in Informatica for Database Dependencies

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.