OBIA Financial Analytics on Peoplesoft configuration

In this post we will see some important configurations steps for setting up Oracle Financial Analytics on Peoplesoft ERP.

Configuring BI Applications 11.1.1.10.1

Once the OBIA installation completed ,next we need to enable the purchased functional area and start extracting data into datawarehouse. In order to seutup we need to use below tools/applications.

1. The Oracle BI Applications Configuration Manager (BIACM)
2. Oracle BI Applications Functional Setup Manager (FSM part of BIACM itself)
3. Oracle Data Integrator (ODI)

BIACM and FSM are web based applications that runs on BI Managed server,these apps facilitates to enable functional areas ,configure required parameters with respect to functional area for extracting data.

Briefly BIACM is for adminstering the entire OBIA environment and configuring ODI.

FSM acts as a lightwieght Project management tool for configuring specific functional area and can be used to assign configuration tasks to specific users to perform.

Steps Involved in Configuring a BI Applications 11.1.1.10.1 Data Load

1. Check ODI Agent status

First step is to make sure ODI agent is up and running since it is responsible for executing data loads.

Login console and make sure odi_server1 is up and running if not make it start from there it self.

and then check "oraclediagent" deployment in the deployments and make sure it is in active state.

2.Configure OBIA environment through BIACM

Access the BI Apps Configuration Manager (BIACM) using

https://hostname:9704/biacm

Note: To setup the source system and target system configurations, login to BIACM using a user that has Admin privileges (in this case, BIAppsAdmin). Logging in with normal user like weblogic will not allow doing the system setup.

In the BIACM Console,

Click on the System Setups -> Define Business Intelligence Applications Instance

Clicking on this task then presents three tabs for configuration: Source Systems, Target Warehouse, and Environment Configuration. If you look at the Target Warehouse and Environment Configuration tabs, you'll see that the BI Applications Configuration Utility has already configured these for you. However, there are no Source Systems defined, so that becomes our first task.

2.1  System Setups -Source Sytem setup:

• Click the Source Systems tab
• Click the green plus symbol to add a new source system. 

Now you have to register source in both biacm and odi

Enter the following details, when prompted:

Sl#	Parameter Name	Example Values
1	Product Line	PeopleSoft Enterprise
2	Product Line Version	PeopleSoft Enterprise 9.2
3	Source Instance Name	PSFT_9_2_SRC (Appropriate Instance Name)
4	Description	PSFT_9_2_SRC (Any Description)
5	Data Source Number	29 (Appropriate number for the source system)

Click the Next button to register the source in the ODI Topology.

Provide all the necessary information and click Test button to make sure all are correct.

Now click on next to register File connection.

In the Technology: File System tab back in Configuration Manager, enter the following information:

1. ODI Data Server Name: PSFT_9_2_SRCFILES
2. Host Name: <middleware_host>
3. Path to File:  /<middleware_home>/Oracle_BI1/biapps/etl/data_files/src_files/PSFT_9_2

Finally, click the Save and Close button and then Done to finish this configuration process.

At this point, Configuration Manager has written the configuration from this one screen into the ODI Repository for four different objects in the Physical and Logical Topology.

Appendix 1:
For implementing Financial Analytics, we have to move below 2 csv files to the above mentioned folder. 

•      file_group_acct_codes_psft.csv
•         file_glacct_segment_config_psft.csv

In file_group_acct_codes_psft.csv file you have to supply values for
BUSINESS_UNIT,FROM_ACCT,TO_ACCT,GRP_ACCT_NUM

Here BUSINESS_UNIT is the mandatory values ,technically if it is null you wont see any of your account groouping names in DWH tables beacuse in ELT there is "not null" condition on this column while loading into W_PSFT_GRP_ACCT_TMP table.

In  file_glacct_segment_config_psft.csv file you have to supply active chartfield values under SEG 1 to n columns.

For Account,Balancing Segment,Cost Center,Project yu dont need to mention them in file because there were separate WID columns in all Fin tables .

3. Enable Licensed BI Offereings in FSM

Configure Offerings:

This step is done from the Functional Setup Manager from the Configuration Manager.

1. Click Perform Functional Configurations in Configuration Manager, and then login to Functional Setup Manager.
2. Then, click Configure Offerings; here we will enable our licensed offering for implementation.
3. Check the Enable for Implementation checkbox beside the required functional modules and press Yes to continue and click. Select the following for PeopleSoft Enterprise:

●        Financial Analytics

Create Implementation Project:

An implementation project is nothing more than a collection of tasks required to configure the offerings you include in the project.

Much like the load plan, BI Applications 11.1.1.10.1 contains a master task list for every offering, with some tasks having a one-to-many relationship to the offerings. Creating an implementation project is very similar to generating a load plan in that you only get a subset of the master based on your selections.

1.       Click Manage Implementation Projects tab and click the 'Create' icon

2.       Enter and select the values as below and Click ‘Next’ button

3. Expand Oracle Financial Analytics -> PeopleSoft Enterprise – Oracle Financial Analytics. Check the Include checkbox for all the applicable modules. You need to select one by one. After selecting one module it takes close to 2-3 seconds to save automatically and then proceed to next module.

4.  After saving, you will see the Project is created and a Task List for Financial Analytics is made.

5.  Click Save and Open Project

Upon opening the project, you are presented with the countless tasks to configure Financial Analytics. The tasks in Functional Setup Manager generally fall into three categories:

1. Informational tasks that route you to the proper place in the documentation.
2. Parameter or Domain tasks that route you to the proper place in Configuration Manager.
3. Text file configuration tasks that tell you what file to edit.

The functional configuration can be achieved by completing the tasks in the Task list created in the Implementation Project.

4. Configure Licensed BI Offereings in BIACM

From the Configuration Manager,

1. Click System Setups > Manage Business Intelligence Applications on the left-hand sidebar in Configuration Manager.
2. Then, within the Manage Business Intelligence Applications page, select the offerings and the subject areas required.
3. Then, press the OK button in the dialog then is then displayed, and once it closes, press Save.
4. From the Business Intelligence Application Offerings and Associated Sources tab, expand the selected Offerings and map the offering to the correct source.

5.From the Business Intelligence Application Offerings and Associated Sources tab, expand the selected Offerings and map the offering to the correct source.

5. Create and Execute Domain Load

The Domain-Only Load Plan will run some targeted data extractions from the source system to assist with configurations for specific applications

From Configuration Manager,
1. Click Load Plans Administration > Manage Load Plans
2. When the Manage Load Plans page opens, click the green plus symbol to add a new load plan, and enter the following details to define the new domain load plan

Name: PSFT_9_2_Domain_Load
Description: PSFT_9_2_Domain_Load
Load Plan Type: Domain-only Extract and Load (SDE and SIL)

Source Instances: PSFT_9_2_SRC
(The above fields can be altered based on your source system except 'Load Plan Type')

1.       Then, press the Next button to display the list of available fact table groups

2.       Select the modules as below:

a.       Financial Analytics

3.       Click the Save button and select Save and Generate Load Plan when prompted

Once the generation is complete, you may proceed execute the load plan make sure it runs successful.

After the domain load execution proceed to configure your BI system as followed in further steps.

6. Functional Configuration Tasks

The Functional Configuration consists of the following actions,

1. Setting up Global and Module Specific Data Load Parameters(required Before Executing Full Load Plan)
2. Setting up Reporting Parameters
3. Domain Values and Domain Member Mapping

In BIACM navigate to -> Data Load Parameters Administration -> Manage Data Load Parameters

a.       Search for required parameter by name or code

b.       Click on the parameter row to select the parameter

c.       If the parameter is a Global parameter, then the Global Parameter Value field displays the actual value.

Parameters required for Full load:

The parameters that are required before a full load are marked with yellow warning symbol.

Usually we need to set below parametrs

Configure Global Currencies
Specify Gregorian Calendar Date Range
Specify the Enterprise Calendar
GBL_CALENDAR_ID :
SELECT DISTINCT SETID || '~' || CALENDAR_ID FROM PS_CAL_DEFN_TBL;

Configure Initial Extract Date

Configure Data Load Parameters for PeopleSoft Trees for Item Categories

1. TREE_SETID_NAME_LIST

Query to get values:

SELECT SETID||'~'||TREE_NAME from PSTREEDEFN where TREE_STRCT_ID in ('ITEMS') ;

2. TREE_STRUCT_ID_LIST

ITEMS

Configure Data Load Parameters for PeopleSoft Department Tree

1.TREE_SETID_NAME_LIST_DEPT

Query to get values:

SELECT SETID||'~'||TREE_NAME from PSTREEDEFN where TREE_STRCT_ID in ('DEPTID' ) ;

2. TREE_STRUCT_ID_LIST_DEPT

DEPTID

Configure Data Load Parameters for PeopleSoft Company (Business Unit) Tree

1.TREE_SETID_NAME_LIST_COMPANY

Query to get values:
SELECT SETID||'~'||TREE_NAME from PSTREEDEFN where TREE_STRCT_ID in ('CONSOLIDATION' ) ;

2. TREE_STRUCT_ID_LIST_COMPANY
CONSOLIDATION

Configure Data Load Parameter for PeopleSoft Product Hierarchy

TREE_NAME1


Balancing Segment Dimension
1.TREE_SETID_NAME_LIST
SELECT A.SETID||'~'||A.TREE_NAME FROM PSTREEDEFN A, PSTREESTRCT B WHERE A.TREE_STRCT_ID=B.TREE_STRCT_ID AND B.DTL_RECNAME='FUND_TBL';

2.TREE_STRUCT_ID_LIST
SELECT TREE_STRCT_ID FROM PSTREESTRCT WHERE DTL_RECNAME = 'FUND_TBL';

FUND chartfield has to be mapped to Balancing segment Dimension

Cost Center Dimension
1.TREE_SETID_NAME_LIST
SELECT A.SETID||'~'||A.TREE_NAME FROM PSTREEDEFN A, PSTREESTRCT B WHERE A.TREE_STRCT_ID=B.TREE_STRCT_ID AND B.DTL_RECNAME='DEPT_TBL';

2.TREE_STRUCT_ID_LIST
SELECT TREE_STRCT_ID FROM PSTREESTRCT WHERE DTL_RECNAME = 'DEPT_TBL';

Department chartfield has to be mapped to Cost Center Dimension

Natural Account Dimension
1.TREE_SETID_NAME_LIST
SELECT A.SETID||'~'||A.TREE_NAME FROM PSTREEDEFN A, PSTREESTRCT B WHERE A.TREE_STRCT_ID=B.TREE_STRCT_ID AND B.DTL_RECNAME='GL_ACCOUNT_TBL';

2.TREE_STRUCT_ID_LIST
SELECT TREE_STRCT_ID FROM PSTREESTRCT WHERE DTL_RECNAME = 'GL_ACCOUNT_TBL';

Account chartfield has to be mapped to Natural account diemension

Domains and Domain member mappings

DOMAINS	Domains are pre-seeded dimensional values that help define business metrics. For example, in Financial Analytics, domains store information about the General Ledger accounts. Domains are typically located in the source system. If domains are not available in a source system, then they can be sourced from a flat file.
DOMAIN MAPPINGS	Domain Mappings specify how data in a source system is extracted and loaded into Oracle Business Analytics Warehouse.
TYPES OF DOMAINS	1.       Regular Domains Regular Domains have members consisting of a single value. For example, members for a Purchase Order Status domain might have the following members: ●        Cancelled ●        Closed ●        Incomplete These single values map to single member values in the target system. For example, Cancelled maps to Cancelled, Closed maps to Closed, and so on. 2.       Band Domains Band Domains have members consisting of two values (Range Start, and Range End) that specify a range. For example, an Account Employee Size domain might have the following members: a.       1, 5000 b.       5001, 10,000 c.       10,001, 1,000,000. Each range maps to a single target Domain Member. For example, 1, 5000 maps to Small, 5001, 10,000 maps to Medium, and so on.
SOURCE DOMAINS	Data fields in a Source System application are referred to as Source Domains
WAREHOUSE DOMAINS	Data fields in Oracle Business Analytics Warehouse are referred to as Warehouse Domains.
DOMAIN MEMBERS	Domain Members are the permitted values for a Source or Warehouse Domain. For example, the Domain Members for MARITAL_STATUS include D for Divorced, M for Married, S for Single, and so on.
WAREHOUSE DOMAIN HIERARCHIES	Warehouse Domain Hierarchies are Domains that have been organized into hierarchies to enable the data to be more effectively analyzed. For example, in Oracle HR Analytics, you might need to have a workforce event hierarchy: Event Group -> Event Sub-group -> Event Detail.
DOMAIN MEMBER MAPPINGS	Oracle Business Intelligence Applications ships default domain value mappings that map the seeded BI Application domain values to the seeded configuration data in Oracle Enterprise Resource Planning applications. When you configure your Offerings, you need to review the default mappings for domain values, and if necessary update them to suit the categories that you want to use to report on your data. For example, In Oracle HR Analytics, the default domain values for Performance Range might be similar to the following: 0 - 50: PERF_RANGE_1 50 - 60: PERF_RANGE_2 60 - 70: PERF_RANGE_3. If you want to use these default categories, you do not need to make any changes to these mappings before you start your ETL processes. If you want to make changes to default mappings, then you use the Domain Mappings tab to implement these changes. For example, you might want to change the range for PERF_RANGE_1 from 0 - 50 to 0 - 100. Or you might want to add a new category named PERF_RANGE_4 and assign the range 100 - 500 to the new PERF_RANGE_4 category

Now edit the domain memeber mappings based on business requirements .

Create the Full Data extract load plan  and execute from biacm and load can be monitored from ODI studio.