Configuring Data for Oracle Pharma Sales Analytics

This appendix contains information about how to populate data in the Siebel Pharma application to meet the requirements of Oracle Pharma Sales Analytics. The information is for Siebel Pharma and Oracle Pharma Sales Analytics implementation teams, Siebel Pharma application administrators and trainers. It assumes that you are familiar with the functionality and conventions of working with and administering the Siebel Pharma application.

For further information about transactional data extract, transform and load (ETL) rules of Pharma Sales Analytics, see Oracle Business Intelligence Applications Installation and Configuration Guide.

This appendix contains information about the following topics:

Transactional Data Requirements

Oracle Pharma Sales Analytics is a set of prebuilt analytical applications to support the analysis and reporting needs of sales and marketing users. The Oracle Pharma Sales Analytics applications are built on the Oracle Business Analytics Warehouse, which is a unified data repository for all customer-centric data to support the analytical requirements of Siebel Pharma Applications.

The Oracle Business Analytics Warehouse contains a number of Business Analytics Warehouse tables that are specific to Oracle Pharma Sales Analytics. These tables support the analysis of data stored in the Siebel Pharma application, as well as data from external syndicated data sources. The Oracle Business Analytics Warehouse contains a set of ETL (extract-transform-load) processes that take data from the Siebel Business Applications transactional data (OLTP) and loads this data into the Oracle Business Analytics Warehouse tables.

To implement Oracle Pharma Sales Analytics successfully you must understand the key entities and relationships in Oracle’s Siebel Pharma application that Oracle Pharma Sales Analytics uses.

The Pharma Sales Analytics ETL processes impose some requirements on how the data must be configured and populated in the Siebel Pharma application in order to minimize the need for configuring data entities, relationships, and hierarchies within the Oracle Pharma Sales Analytics application.

The Oracle Pharma Sales Analytics ETL rules are configured to source data from specific, standard Siebel Pharma transactional database tables and columns. These database tables and columns are mapped to standard Siebel Pharma business components, screens and views. The Pharma Sales Analytics ETL rules require that data is populated in a standard way to make sure that data is successfully loaded into the Oracle Business Analytics Warehouse tables without the need for additional configuration.

The following key entities must be configured and populated correctly in the Siebel Pharma transactional system:

Product hierarchies and product costs
Product catalog and category hierarchies
Sales objectives

Configuring Product Hierarchies and Product Costs

This topic details the tasks performed by application administrators to configure product hierarchies and product costs. Oracle Pharma Sales Analytics provides a prebuilt product hierarchy that allows users to analyze and roll up call activity, medical events, sales and Rx data at the form strength, brand, submarket, and market levels. Oracle Pharma Sales Analytics also allows users to analyze sales and Rx data of competitor brands.

The following table shows the required relationships between each of the levels in the Product Level column.

Pharma Sales Analytics Product Hierarchy	OLTP Product Type	OLTP Product Level	Example	Parent Product	Cardinality
Form/Strength	Sample	3	Aracid 400 MG	Aracid	M:1
Product	Detail	2	Aracid	COPD	M:1
Product	Competitive Products	No Level	SImAir	Asthma or COPD	M:1
Submarket	Submarket	No Level	COPD	Asthma	M:1
Market	Market	1	Asthma	No Parent	None

Oracle Pharma Sales Analytics also allows users to analyze ROI of call detailing and sampling. The underlying cost information used in these ROI calculations is sourced from the product cost fields within the Siebel Pharma application.

To configure product hierarchies and product costs

Navigate to the Administration - Product screen, then the Products view.

In the Products list, create a new record, and complete the necessary fields.

Some fields are described in the following table.

Field	Comments
Name	The name of the form strength, product/brand, submarket, or market.
Product Type	The type of product as listed in the previous table.
Parent Product	The parent product of this product within the product hierarchy. The appropriate parent product must be selected for the current product level as listed in the previous table. Market type products do not have a parent product.

Drill down on the product.
Click the More Info tab.
In the Level field, select the correct numerical value for the level as listed in the previous table.
If the Product Type is Sample, then in the Sample Cost field enter the appropriate sample cost.
If the Product Type is Detail, then in the Promo Cost field enter the appropriate promotional cost.
Repeat Step 2 through Step 7 for the remaining products in the product hierarchy.

For more information about managing products, see Managing Products for Life Sciences

Configuring Product Catalog and Category Hierarchies

This topic details the tasks required to configure product catalogs and category hierarchies performed by application administrators. Oracle Pharma Sales Analytics allows users to use a number of prebuilt alternate product hierarchies to analyze and roll up sales and Rx data. These prebuilt hierarchies are as follows:

Manufacturer Product Category
Application Form Product Category
ATC (Anatomical Therapeutic Class) Product Category
User Code (USC) Product Category

Chemical Product Category

The following table shows the Standard Product Hierarchy along with the prebuilt alternative product hierarchies.

Table Standard Product Hierarchy with Prebuilt Product Hierarchies

Standard Product Hierarchy	Manufacturer Product Hierarchy	Application Form Product Category	ATC Product Category	User Code Product Category	Chemical Product Category
Market	Manufacturer	Application Form I	ATC I	USC I	Family
Submarket	Product	Application Form II	ATC II	USC II	Subfamily
Product	Product	Application Form III	ATC III	USC III	Subfamily
Form/Strength	Product	Application Form IV	ATC IV	USC IV	Subfamily

The Oracle Pharma Sales Analytics ETL uses data stored in the standard Siebel Pharma Product Catalog and Category application screens to create these alternate hierarchies.

To configure product catalog and product hierarchies

Navigate to the Administration - Catalog screen, then the Catalogs view.

In the Catalogs list, create a new record and complete the necessary fields.

Field	Comments
Name	The name of the alternate Product hierarchy, for example, R-Respiratory for an ATC hierarchy for Asthma products.
Description	A general description of the catalog.
Effective Start Date	Current date.
Private	The product catalog is only used by the Oracle Pharma Sales Analytics ETL. Select this field to prevent the catalog from being visible to other users within the Siebel Pharma Application.

Drill down on the Catalog.

In the Category list, create a new record for the highest (first) level of the hierarchy and complete the necessary fields.

Some fields are described in the following table.

Field	Comments
Name	Name of the hierarchy level (for example, R3-Bronchodilators and Anti-asthma Preparations for the highest level in an ATC hierarchy for Asthma products).
Display Name	Display name of the hierarchy level (for example, R3-Bronchodilators and Anti-asthma Preparations for the highest level in an ATC hierarchy for Asthma products).
Effective Start Date	Current date.
Usage	From the Pick Category Usage Type dialog box, select one of the following usage types to set the type of alternate Product Hierarchy, and then click OK: ATC Application Form Manufacturer USC Chemical
Private	The Product Catalog is only used by the Oracle Pharma Sales Analytics ETL Check this flag to prevent the Catalog from being visible to other users within the Siebel Pharma Application.

For each lower level in the hierarchy, create a new record in the Category list, and repeat Step 4.
Set the relationships between the levels by using the Demote level button.
For the lowest level in the hierarchy only, click the Products link under the Categories tab.
Create a new record in the Product list, and select the appropriate Product record, which is the Form/Strength level Product in the standard Product hierarchy.
This action allows Oracle Pharma Sales Analytics to link the alternate product hierarchy to the standard product hierarchy.

Setting Up Sales Objectives

This topic details the tasks that users must do to set up correctly the sales objectives in Oracle Pharma Sales Analytics. Oracle Pharma Sales Analytics allows users to analyze actual call activities, sales, and Rx results and compare them with their sales objective targets. Users must enter the sales objectives data correctly in the Siebel Pharma Application’s Objectives screens to enable this analysis.

To set up sales objectives

Navigate to the Objectives screen, then the Retail Objectives List view.

In the Retail Objectives list, create a new record and complete the necessary fields.

Some fields are described in the following table.

Field	Comments
Unit	A unit can only be NRx, TRx, indirect sales, indirect units, sales amounts, sales units, or number (#) of calls.
Product	Select only one product at the appropriate product level. For a unit value of NRx, TRx, or number (#) of Calls, then the product value must be detail. For a unit of indirect sales, indirect units, sales amounts, or sales units, then the product level is form/strength.
Team	Only one team member can be the owner of each objective.

In the Retail Objectives list, drill down in the Objective of a record.
Click the Target Contacts tab.
In the Target Contacts list, create a new record for each target contact you want to add and complete the necessary fields.
For example, for each target contact that you add, enter a value in the Target Value field.
Click the Target Accounts tab.
In the Target Accounts list, create a new record for each target account you want to add and complete the necessary fields.
For example, for each target account that you add, enter a value in the Target Value field.

Entering Call Activity Data

Oracle Pharma Sales Analytics allows users to analyze the Account Calls, Contact Calls, and Attendee Calls data entered in the Siebel Pharma application. The Oracle Pharma Sales Analytics ETL requires a number of key call activity fields to be populated and the data to be entered correctly so that the Oracle Business Analytics Warehouse is loaded.

The following table outlines which fields must be populated for Oracle Pharma Sales Analytics.

Table Fields that must be Populated for Oracle Pharma Sales Analytics

Records	Fields That Must Be Populated
Account Call - Header Records	Status (set to Submitted) Account Address tart Date
Contact Call - Header Records	Status (set to Submitted) Last Name, First Name Address Start Date
Attendee Call - Header Records	Status (set to Submitted) Last Name, First Name Address Start Date
Call Products Detailed - Child Records	Product Priority Indication
Samples Dropped - Child Records	Name Quantity
Promotional Items Dropped - Child Records (Contact calls)	Name Quantity

Configuring Sales Territories for Pharma Sales Analytics

This topic describes how to perform the following tasks:

Creating Primary Sales Force Hierarchies

Oracle Pharma Sales Analytics provides a prebuilt sales hierarchy configuration that determines the rollup and visibility of sales, Rx and call activity data for the different levels of users within a sales organization. This configuration includes prebuilt aggregation tables and de-duplication of data at each level of the sales hierarchy to support accurate analysis and improved performance. The primary sales force hierarchy is configured as a position hierarchy within the Siebel Pharma application, with the sales territory level position following the position hierarchy. This topic details the tasks that application administrators must perform to configure the position hierarchy for use in Oracle Pharma Sales Analytics.

To create a primary sales force hierarchy

Navigate to the Administration - Group screen, then the Positions view.

In the Positions list, create a new high-level position record in the Sales Force Hierarchy and complete the necessary fields.

Some fields are described in the following table.

Field	Comments
Division	Division for the primary sales force hierarchy.
Position	Position associated with the primary sales force hierarchy.
Position Type	Select the position type from the drop-down list.

For each level that appears after the sales hierarchy, repeat Step 2 down to the sales territory level position.

In the Parent Position field for each record, select the Position for the level preceding the current Position.

The prebuilt Oracle Pharma Sales Analytics configuration supports the sales hierarchy levels shown in the following table.

Field	Comment	Comments
Organization	Highest level of the sales organization that is sometimes referred to as Franchise. For example, Respiratory Franchise refers to a sales force commissioned to promote respiratory products. This level does not have any parents.	None
Sales Force	Child of Organization. Refers to a sales force promoting products within a sales organization.	M:1
Area	Child of Sales Force. Refers to a large geographic sales area, such as US East, US West, or US South.	M:1
Region	Child of Area. Refers to a sales geographic region.	M:1
District	Child of Region. Refers to a sales geographic district.	M:1
Territory	Child of District. Refers to a sales geographic territory composed of ZIP codes, bricks, contacts, or accounts.	M:1

All sales hierarchy levels must be created as positions and linked to a position hierarchy to populate correctly the Oracle Business Analytics Warehouse.

The lowest territory level position in the hierarchy must be the position associated with the assignment rule definition for the sales territory alignment, as described in Creating Sales Territories.

Creating Sales Territories

Sales territory alignments are used within Oracle Pharma Sales Analytics to assign sales and Rx data to the appropriate sales territory level positions. A sales territory alignment is the relationship between each sales territory position and a set of ZIP codes, bricks or accounts, or contacts. This territory alignment relationship is created and managed as a set of assignment rules in the Administration - Assignment screens within the Siebel Pharma application.

The following procedure details the task that application administrators must perform to configure the assignment rules in Oracle Pharma Sales Analytics.

To create a new assignment rule

Navigate to the Administration - Assignment screen, then the Assignment Rules List view.
In the Assignment Rules list, create a new assignment rule record and complete the necessary fields.
- In the Objects to be Assigned field, select either Account or Contact from the Assignment Object dialog box, and click OK.
- In the Rule Group field, select Default Rule Group from the Pick Rule dialog box, and click OK.
- In the Activation field, enter the rule activation date.
- In the Name field, type in the name of the new assignment rule.
- Drill down on the assignment rule Name field.
Click the Criteria tab

In the Assignment Criteria list, create new criteria records for the assignment rule and complete the necessary fields.

In the Rule Criterion field, select one of the assignment criteria listed in the following table.

Note: A territory can have one or more criteria defined for it, depending upon the territory alignment requirements (for example, you can assign both account ZIP code and contact ZIP code to the same territory).

Assignment Criteria	Assignment Type	Description	Cardinality
Account ZIP Code	ZIP Code	Assigns an account ZIP code or group of ZIP codes to a sales territory.	M:M
Contact ZIP Code	ZIP Code	Assigns a contact primary address ZIP code or group of primary address ZIP codes to a sales territory.	M:M
Account Brick	Brick	Assigns an account brick or group of bricks to a sales territory.	M:M
Contact Brick	Brick	Assigns a contact primary brick or group of bricks to a sales territory.	M:M
Account	Account	Assigns an account name or groups of contacts to a sales territory.	M:M
Contact	Contact	Assigns a contact name or group of contact names to a sales territory.	M:M

In the Comparison Method field, select Compare to Object.
For each assignment criteria record created, add the appropriate records for the criteria within the given territory.
For example, for an account ZIP code criteria create a new record for each ZIP code range, and enter values in the ZIP Code Low and ZIP Code High fields.

Click the Position Candidate tab.
In the Assignment Rule Position list, add the appropriate position for the given territory.
Repeat the process for each sales territory that must be defined.

Creating Alternate Sales Force Hierarchies

Oracle Pharma Sales Analytics supports the ability to analyze Rx script data and compare it with alternate sales force organizational structures. These alternate sales force hierarchies can support co-marketing sales organizations where Rx scripts might be 100% allocated to both sales forces, as well as Mirror Sales Forces where Rx Script allocation might be shared between the Sales Forces.

You can assign scripts to each alternate sales representative by:

Using a report or metadata calculation based on the attribute of the W_POSITION_D dimension table
Creating a new set of aggregation tables to de-duplicate scripts

The alternate sales force hierarchies are configured as an additional set of positions and alignment rules within the Siebel Pharma application. This topic details the tasks that application administrators perform to configure these positions and alignment rules.

Note: The allocation of Rx scripts to alternate sales force positions uses an allocation percentage that is stored in the S_POSTN.OWNER_ALLOC_PCT column in the Siebel Pharma transactional database. This database column is not exposed in the standard Siebel Pharma Application. To complete the steps for creating the alternate sales force hierarchies, add the OWNER_ALLOC_PCT column to the position business component, and expose it in the Position List Applet in the Siebel runtime repository.

To create an alternate sales force hierarchy

Navigate to the Administration - Group screen, then the Positions view.

In the Positions list, create a new high-level position record in the Sales Force Hierarchy and complete the necessary fields.

Some fields are described in the following table.

Field	Comments
Division	Division for the alternate sales force hierarchy.
Position	Position associated with the alternate sales force hierarchy.
Position Type	Select the position type from the drop-down list.

For each of level that appears after the Sales Hierarchy, repeat Step 2 down to the Sales Territory level position.

In the Parent Position field for each record, select the Position for the level preceding the current Position.

The prebuilt Oracle Pharma Sales Analytics configuration supports the following sales hierarchy levels.

Level	Description	Cardinality to Parent
Organization	The highest level of the sales organization that is sometimes referred to as Franchise. For example, Respiratory Franchise refers to a sales force commissioned to promote respiratory products. This level does not have any parents.	None
Sales Force	Child of Organization. Refers to a sales forces promoting products within a sales organization.	M:1
Area	Child of Sales Force. Refers to a large geographic sales area, such as US East, US West, or US South.	M:1
Region	Child of Area. Refers to a sales geographic region.	M:1
District	Child of Region. Refers to a sales geographic district.	M:1
Territory	Child of District. Refers to a sales geographic territory composed of ZIP codes, bricks, contacts, or accounts.	M:1

Create all levels for the sales hierarchy as positions, and link them to a position hierarchy to populate the Oracle Business Analytics Warehouse correctly.
For the territory level position in this new alternate hierarchy, set the Position Type field to either mirror, swat, or job share as appropriate for the alternate sales force.

Note: Select these position types for the territory level positions in the alternate sales force.
In the new Owner Allocation field that has been exposed in the Position List Applet, enter the percentage allocation of scripts as a decimal for the alternate territory level position.
The Pharma Sales Analytics ETL populates the value from the Owner Allocation Percentage field to a column in the Position Dimension that can then be used to calculate measures and aggregates for Rx script allocations.
After the alternate sales force positions and hierarchy have been created, you must create an additional alignment rule for the alternate sales territory as an assignment rule.
For more information about creating these additional Assignment Rules, see Creating Sales Territories.

Note: The alternate sales force territory level position must be selected as the candidate position for these assignment rules.

Creating Target Lists Using Oracle Business Intelligence

Target List Creator enables the automatic import of customer lists derived from an Oracle BI query into Siebel List Management. This functionality is enabled through the Oracle Business Intelligence Presentation Services and can be used from Oracle BI Interactive Dashboards or Oracle BI Answers integrated with the Siebel Enterprise application.

Note: This functionality is certified and tested with the Siebel Pharma Mobile Web Client, as well as the Siebel Pharma Connected Client and Oracle Pharma Sales Analytics application.

This topic describes the following:

About Target List Creator

The following topics outline the components and functionality of the target list creator:

Components

The following components support the Target List Creator functionality:

Create Target List view. Oracle Business Intelligence Presentation Services includes a report view called Create Target List that displays a link used to initiate a business service in the Siebel Pharma application.
LS Analytics Target Service business service. The Siebel Pharma application includes a business service called LS Analytics Target Service that provides the data integration capability.

When Target List Creator is used in connected mode, both the Oracle BI Presentation Services and Siebel Application Interface must reside on the same machine so that the Create Target List link can be used within an Oracle BI report. The Oracle BI Open Intelligence Interface also needs to be installed and configured on the Siebel Pharma application server to enable connectivity between the Siebel Pharma business service and the Oracle BI Server. For more information, see Oracle Business Intelligence Infrastructure Installation and Configuration Guide.

Functional Overview and Process

The following outlines the functionality and general process steps of the Target List Creator components:

When a user clicks the Create Target List link in an Oracle BI Interactive Dashboard report, a JavaScript function prompts the user to enter a name for the new list. The function then passes this name and the logical SQL for the Oracle BI Query through the Siebel Pharma application to the LS Analytics Target Service business service.
The LS Analytics Target Service business service:
1. Verifies that the field representing the row Id of the target object is the first column selected in the query. For example, if you create a target list for Contacts, then in the default configuration the Contacts.ROW_ID column must be the first column in the query.
2. The Oracle BI presentation columns that represent the Row Id values are configured in user properties for the LS Analytics Target Service business service. The Account Row Id Fields user property maps to the Oracle BI presentation column for the Siebel Account Row Id. The Contact Row Id Fields user property maps to the Oracle BI presentation column for the Siebel Contact Row Id. If the Oracle BI presentation columns used to store the Row Id values differ from the default configuration, then the column mappings for the user properties can be changed in the Siebel runtime repository.
3. Then a new target list header record is created in the Siebel Pharma application's database. The List Name is set to the Name entered by the user in the Oracle Business Intelligence dialog box and passed from Oracle Business Intelligence to Siebel CRM.
4. The LS Analytics Target Service business service then uses the Oracle BI ODBC DSN (OpenDataBase Connectivity Data Source Name) name defined in the Siebel Pharma application's AnalyticsDataSrc to connect back to the Oracle BI server.
5. The business service executes the logical SQL passed from Oracle Business Intelligence, iterates through the results, and creates a new List Item for each record.
6. The validate targets user property on the business service can be turned on to provide additional validation by ensuring that the record in Oracle Business Intelligence already exists in the user's local Siebel database. Possible values for the user property are N or Y. The default value is N.
Note: The business component used for the contact target items is List Mgmt List Member, while the business component used for account list items is SLM List Member - Account. Both of these business components must be present in the List Mgmt business object.
After the list is created, the application automatically navigates to the view set in the Business Service's Redirect View User Property. This view must be associated with the List Mgmt Business Object and must have the List Mgmt Lists Business Component as its primary business component. The default view is My List Mgmt Lists View.

Using Target List Creator with Siebel Pharma Applications in Connected Mode

You can also use the Target List Creator with the Siebel Pharma application and Oracle Pharma Sales Analytics in connected mode. This topic lists the tasks that you must carry out to verify the Siebel Pharma application's configuration to support using the Target List creator in connected mode:

Install and Configure the Oracle Business Open Intelligence Interface

The LS Analytics Target Service business service uses an ODBC DSN to connect back to the Oracle BI Server and execute the Oracle BI logical SQL passed into it. This ODBC DSN needs to be setup using the Oracle BI Open Intelligence Interface (ODBC) client, and requires that this client be installed on the Siebel Pharma application server. To install and configure the Oracle BI Open Intelligence Interface, see Oracle Business Intelligence Applications Installation and Configuration Guide.

Configuration Changes to the Siebel Business Application

To enable the LS Analytics Target Service Business Service to be called from the Siebel browser client, the Business Service name has to be present as a ClientBusinessService user property for the Siebel Business Application being used.

To verify the LS Analytics Target Service business service

In Siebel Web Tools, find the Siebel Life Sciences application record in the Application objects list.
In the Application User Prop list verify that the following entry exists:
ClientBusinessService10 = LS Analytics Target Service

Configuring the Application's Server Component Parameters and Enterprise Profile

The Oracle BI Open Intelligence Interface ODBC DSN connection must be configured as a new data source profile for the Siebel Enterprise and must be associated with the Siebel Business Application's Object Manager configuration.

To configure a new data source profile and associate with AnalyticsDataSrc

Log in to the Siebel Business Application as an administrator.
Navigate to the Administration - Server Configuration screen, then the Enterprises view.
Click the Profile Configuration tab.
Search for the AnalyticsDataSrc in the list. If it does not exist, then copy the existing ServerDataSrc record, and rename the copy to AnalyticsDataSrc.

Update the following parameters with the values indicated:

	DSDLLName = sscdsacon
	DSConnectString = AnalyticsWeb
	DSTableOwner = <blank>

Click the Component Definitions tab.
In the Component list, search for the appropriate Application's Object Manager.
For example, for Oracle’s Siebel Pharma, search for ePharmaObjMgr_ENU.
With the Component selected, click Menu (the cogwheel icon), and then select Start Reconfiguration.
Search for OM - Named Data Source name in the Component Parameters list.
Append the current list at the end with AnalyticsDataSrc.
Click Menu (the cogwheel icon), and select Commit Reconfiguration from the drop-down list.
Log out of the application.
Log in to the server manager, and restart the component by executing the following commands:
```
shutdown comp ePharmaObjMgr_enu
startup comp ePharmaObjMgr_enu
```
Log in to the application again.
The Create Target List functionality is enabled.