Skip Headers
Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users
Release 7.9.6.3
Part Number E19039-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
PDF · Mobi · ePub

5 Configuring Oracle Financial Analytics

This section describes how to configure Oracle Financial Analytics. It contains the following topics:

5.1 Overview of Oracle Financial Analytics

Oracle Financial Analytics consists of the following:

5.2 Configuration Required Before a Full Load for Financial Analytics

This section contains configuration steps that you need to perform on Oracle Financial Analytics before you do a full data load. It contains the following topics:

5.2.1 Configuration Steps for Financial Analytics for All Source Systems

This section contains configuration steps before you do a full data load that apply to all source systems.

Note:

For configuration steps that apply to all Oracle BI Applications modules see Chapter 3, "Configuring Common Areas and Dimensions."

Note: If you want to deploy multi-calendar with Oracle Financial Analytics, you must enable a number of initialization blocks that are disabled by default. For more information, see Section 3.1.4.1.1, "About Enabling Initialization Blocks Required for Calendars."

5.2.2 Configuration Steps for Financial Analytics for Oracle EBS

This section contains configuration steps before you do a full data load that apply to Oracle EBS. It contains the following topics:

5.2.2.1 About Configuring Domain Values and CSV Worksheet Files for Oracle Financial Analytics

If you modify or extend a seeded list of values, you must configure the CSV files for Oracle Financial Analytics by mapping values from your source systems to the domain values.

The following sections explain how to extract the lists of values from your source system, which you then compare with the seeded values. If the lists of values are different from the seeded values, you need to follow the instructions to configure the domain values and CSV worksheet files.

Typically, the number of rows returned by the source system will not match the number of rows provided in the domain valuesXXX.csv files. If so, you need to delete extra rows or insert additional rows in the CSV file to accommodate all the values returned by the source system in context. You also need to determine which row should have 'Return' and which row should have 'Delivery' in the W_XACT_TYPE_CODE column.

Table 5-1 lists the CSV worksheet files and the domain values for Financial Analytics and Oracle EBS in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

Table 5-1 Domain Values and CSV Worksheet Files for Oracle Financial Analytics and Oracle EBS

Worksheet File Name Description Session

domainValues_Xact_Types_DocTypes_ora12.csv

List the Transaction types and the corresponding domain values for the Oracle EBS R12 application.

For more information about updating the values in this file, see Section 5.2.2.2, "How to Configure Transaction Types for Oracle General Ledger and Profitability Analytics (for Oracle EBS R12)."

SDE_ORA_TransactionType Dimension_GLCOGSDerive

domainValues_Xact_Types_DocTypes_ora11i.csv

List the Transaction types and the corresponding domain values for the Oracle EBS 11i application.

For more information about updating the values in this file, see Section 5.2.2.3, "How to Configure Transaction Types for Oracle General Ledger and Profitability Analytics (for Oracle EBS R11i)."

SDE_ORA_TransactionType Dimension_GLCOGSDerive

5.2.2.2 How to Configure Transaction Types for Oracle General Ledger and Profitability Analytics (for Oracle EBS R12)

This section explains how to configure Transaction Types for Oracle General Ledger and Profitability Analytics, Release 12, using the configuration file domainValues_Xact_Types_DocTypes_ora12.csv.

To configure Transaction types for Oracle General Ledger and Profitability Analytics:

  1. Identify the entry types in the Oracle Inventory application by using the following SQL:

    select mtt.transaction_type_id, mtt.description from mtl_transaction_types mtt;

    This query gives the transaction type codes in the Oracle Inventory application and their corresponding descriptions.

  2. Open the domainValues_Xact_Types_DocTypes_ora12.csv file using a text editor in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

    Look for only the rows in the file which has XACT_CODE = 'COGS' or XACT_CODE = 'DEFERRED_COGS'. From among these records, look for those with W_XACT_TYPE_CODE = 'DELIVERY' or 'RETURN'. The column XACT_TYPE_CODE will contain the entry type codes from the Oracle Inventory application, and the column W_XACT_TYPE_CODE is the corresponding domain value to which this entry type code will be mapped to.

  3. Map the entry type codes returned by the query above from the Oracle Inventory application to the domain values.

    Make sure the entry type codes go into the XACT_TYPE_CODE column in the file. Note that it is not necessarily a one-to-one mapping between the domain values and the entry types. For example, you can have multiple entry types mapped into the DELIVERY/RETURN domain value. Be careful not to map the same entry type to two different domain values.

  4. Save and close the file.

  5. In Informatica PowerCenter Designer, open the mapping SDE_ORA_GLCOGSFact in the folder SDE_ORAVersion_Adaptor.

  6. Open the mapplet mplt_BC_ORA_GLCOGSFact contained inside this mapping

  7. Open the Source Qualifier SQ_MTL_TRANSACTION_ACCOUNTS.

  8. Modify the SQL so that it extracts the additional transaction type IDs, from step 1, that need to be extracted.

  9. In Informatica PowerCenter Workflow Manager, make the same change to the SQL Override in the full load session SDE_ORA_GLCOGSFact_Full.

5.2.2.3 How to Configure Transaction Types for Oracle General Ledger and Profitability Analytics (for Oracle EBS R11i)

This section explains how to configure Transaction Types for Oracle General Ledger and Profitability Analytics, Release 11i, using the configuration file domainValues_Xact_Types_DocTypes_ora11i.csv.

To configure Transaction types for Oracle General Ledger and Profitability Analytics:

  1. Identify the entry types in the Oracle Inventory application by using the following SQL:

    select mtt.transaction_type_id, mtt.description from mtl_transaction_types mtt;

    This query gives the transaction type codes in the Oracle Inventory application and their corresponding descriptions. The Oracle Inventory application is a data source from which Oracle General Ledger and Profitability Analytics extract data.

  2. Using a text editor, open the domainValues_Xact_Types_DocTypes_ora11i.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

    Look for only the rows in the file which has XACT_CODE = 'COGS'. From among these records, look for those with W_XACT_TYPE_CODE = 'DELIVERY' or 'RETURN'. The column XACT_TYPE_CODE will contain the entry type codes from the Oracle Inventory application, and the column W_XACT_TYPE_CODE is the corresponding domain value to which this entry type code will be mapped to.

  3. Map the entry type codes returned by the query above from your Oracle Inventory Application to the domain values.

    Make sure the entry type codes go into the XACT_TYPE_CODE column in the file. Note that it is not necessarily a one-to-one mapping between the domain values and the entry types. For example, you can have multiple entry types mapped into the DELIVERY/RETURN domain value. Be careful not to map the same entry type to two different domain values.

  4. Save and close the file.

5.2.2.4 How to Specify the Ledger or Set of Books for which GL Data is Extracted

If you have an Oracle EBS source system, you can specify from which set of books or ledgers you extract the GL data.

Oracle BI Applications enables you to configure:

  • The list of ledgers or set of books from which to extract the GL data.

  • The types of ledgers or set of books for which to extract the GL data.

You can use either of these configuration points separately and combine them. When installed out-of-the-box, Oracle BI Applications extract all GL data for all ledgers or set of books.

For Oracle 11i customers, to configure the list of sets of books for which to extract the GL data, do the following:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Display the Source System Parameters tab.

  3. Query for the parameter $$FILTER_BY_SET_OF_BOOKS_ID.

  4. Double-click the Value column to open the text box, and enter 'Y'.

    Make sure to put single quotes around the Y.

  5. Save the changes.

  6. Query for the parameter $$SET_OF_BOOKS_ID_LIST.

  7. Double-click the Value column to open the text box, and enter the IDs of the sets of books for which you want to extract GL data.

    Specify the list of sets of book IDs separated by commas (do not enter single or double quotes).

    For example, if you want to extract GL data for sets of books with IDs: 101, 207, and 303, then set the value of this parameter to the following:

    101, 207, 303

  8. Save the changes.

You can also specify the Set of Books type for which you want to extract GL data. In Oracle 11i, there are three set of books types (this is based on the column GL_SETS_OF_BOOKS.mrc_sob_type_code):

  • P (Parent)

  • R (Reporting)

  • N (None)

For Oracle 11i customers, to configure the types of set of books to extract the GL data, do the following:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Display the Source System Parameters tab.

  3. Query for the parameter $$FILTER_BY_SET_OF_BOOKS_TYPE.

  4. Double-click the Value column to open the text box, and enter the value 'Y'.

    Make sure to put single quotes around the Y.

  5. Save the changes.

  6. Query for the parameter $$SET_OF_BOOKS_TYPE_LIST.

  7. Double-click the Value column to open the text box, and enter the types of set of books for which you want to extract GL data.

    Specify the list of set of book IDs in single quotes and separated by commas.

    For example, if you want to extract GL data for all Parent set of books and all Reporting set of books, then set the value of this parameter as follows:

    'P', 'R'

  8. Save the changes.

For Oracle EBS R12 customers, to configure the list of ledgers to extract the GL data for, do the following:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Display the Source System Parameters tab.

  3. Query for the parameter $$FILTER_BY_LEDGER_ID.

  4. Double-click the Value column to open the text box, and enter the value 'Y'.

    Make sure to put single quotes around the Y.

  5. Save the changes.

  6. Query for the parameter $$LEDGER_ID_LIST.

  7. Double-click the Value column to open the text box, and enter the IDs of ledgers for which you want to extract GL data for.

    Specify the list of ledger IDs separated by commas (do not use single quotes).

    For example, if you want to extract GL data for ledgers with IDs: 101, 207, and 303, then set the value of this parameter as follows:

    101, 207, 303

  8. Save the changes.

You can also specify types of ledgers you want to extract GL data for. In Oracle EBS R12, there are four types of ledgers (this is based on the column GL_LEDGERS.ledger_category_code):

  • PRIMARY

  • SECONDARY

  • ALC

  • NONE

For R12 customers, to configure the types of ledgers to extract the GL data for, do the following:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Display the Source System Parameters tab, and query for the parameter $$FILTER_BY_LEDGER_TYPE.

  3. Double-click the Value column to open the text box, and enter the value 'Y'.

    Make sure to put single quotes around the Y.

  4. Save the changes.

  5. Query for the parameter $$LEDGER_TYPE_LIST.

  6. Double-click the Value column to open the text box, and enter the types of ledgers for which you want to extract GL data for.

    Specify the list of set of book IDs in single quotes and separated by commas.

    For example, if you want to extract GL data for all Primary ledgers and all Secondary ledgers, then simply set the value of this parameter to as follows:

    'PRIMARY', 'SECONDARY'

  7. Save the changes.

5.2.2.5 Oracle EBS Currency Balance Types

The ETL process extracts four different currency balance types from Oracle EBS, namely base currency, translated currency, entered currency, and statistical balance. These records are loaded and stored in W_GL_BALANCE_F table in the Oracle Data Warehouse.

The following table shows sample records of the different balance types. All four records are from ledger 'US Ledger' with U.S. dollars (USD) as the base ledger currency. These five columns in the following table are columns in the physical data warehouse table:

  • LOC_CURR_CODE

  • ACCT_CURR_CODE

  • TRANSLATED_FLAG

  • BALANCE_LOC_AMT

  • BALANCE_ACCT_AMT

Line Ledger Name LOC_CURR_CODE ACCT_CURR_CODE TRANSLATED_FLAG BALANCE_LOC_AMT BALANCE_ACCT_AMT Balance Type Description
1 US Ledger USD NULL N 200 0 A Base Currency Balance record with base ledger currency of USD
2 US Ledger USD GBP Y 0 100 A Translated Currency Balance record with translation currency of GBP (British pounds). USD is the base ledger currency.
3 US Ledger USD JPY N 0 10000 An Entered Currency Balance record with entered currency in JPY (USD is the base ledger currency)
4 US Ledger USD STAT N 0 150 A Statistical balance amount

Users can query these balances by using different value columns and filters. The following tables contain sample queries that you can use to view different currency balance types and their corresponding results from the sample warehouse data in the preceding table.

Note:

Ledger 'US Ledger' corresponds to LEDGER_WID = 85228 in the following sample scenarios.
Sample Desired Queries Physical Table Value Column Physical Query Filters Result for Value Column
A To query: balance amount for base currency of USD, in ledger 'US Ledger' BALANCE_LOC_AMT LEDGER_WID = 85228 200
B To query: translated balance amount for translated currency of GBP, in ledger 'US Ledger' BALANCE_ACCT_AMT LEDGER_WID 85228

AND TRANSLATED_FLAG = 'Y'

AND ACCT_CURR_CODE = 'GBP'

 100
C To query: entered balance amount for entered currency of JPY, in ledger 'US Ledger' BALANCE_ACCT_AMT LEDGER_WID = 85228

AND ACCT_CURR_CODE = 'JPY'

AND TRANSLATED_FLAG = 'N'

 10000
D To query: statistical balance amounts, in ledger 'US Ledger' BALANCE_ACCT_AMT LEDGER_WID = 85228

AND ACCT_CURR_CODE = 'STAT'

 150

Oracle BI Answers users can create reports on currency balance types by selecting the desired facts from the Financials - GL Balance Sheet presentation table, as shown in the following table.

Sample Scenario Desired Report Presentation Table Presentation Column Result in Report
A View the local currency balance amounts (Debit Local Amount), such as in sample query A in the preceding table Ledger

Facts – GL Balance

 Ledger name

Debit Local Amount

 Apply filter on: 
"Ledger Name" = 'US Ledger'
Ledger Name = US Ledger
Debit Local Amount = 200
B View the translated currency balance amounts (Debit Translated Amount), such as in sample query B in the preceding table Ledger

Facts – GL Balance

GL Balance Details

 Ledger Name

Debit Translated Amount

Translated Currency Code

 Apply filters on: 
"Ledger Name" = 'US Ledger' 
"Translated Currency Code" = 'GBP'
Ledger Name = US Leger
Debit Translated Amount = 100
Translated Currency Code = GBP
C View the entered currency balance amounts (Debit Entered Amount), such as in sample query C in the preceding table Ledger

Facts – GL Balance

GL Balance Details

 Ledger Name

Debit Entered Amount

Entered Currency Code

 Apply filters on: 
"Ledger Name" = 'US Ledger'
"Entered Currency Code" = 'JPY'
Ledger Name = US Leger
Debit Entered Amount = 10000
Entered Currency Code = JPY
D View the statistical amount (Statistical Amount), such as in sample query D in the preceding table Ledger

Facts - Balance Sheet Statement

 Ledger Name

Statistical Amount

 
Ledger Name = US Leger
Statistical Amount = 150

5.2.3 Configuration Steps for Financial Analytics for PeopleSoft

This section contains configuration steps before you do a full data load that apply to PeopleSoft. It contains the following topics:

5.2.3.1 About Configuring Domain Values and CSV Worksheet Files for Oracle Financial Analytics

If you modify or extend a seeded list of values, you must configure the CSV files for Oracle Financial Analytics by mapping values from your source systems to the domain values.

The section Section 5.2.3.2, "How to Configure Domain Values for Oracle Receivables Analytics" explains how to extract the lists of values from your source system, which you then compare with the seeded values. If the lists of values are different to the seeded values, you need to follow the instructions to configure the domain values and CSV worksheet files.

Table 5-2 lists the CSV worksheet files and the domain values for Financial Analytics and PeopleSoft in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

Table 5-2 Domain Values and CSV Worksheet Files for Oracle Financial Analytics

Worksheet File Name Description Session

domainValues_Xact_Types_DocTypes_psft.csv

List the Transaction types and the corresponding domain values for the PeopleSoft application.

For more information about updating the values in this file, see Section 5.2.3.2, "How to Configure Domain Values for Oracle Receivables Analytics."

SDE_PSFT_TransactionType Dimension_ARDerive

5.2.3.2 How to Configure Domain Values for Oracle Receivables Analytics

This section explains how to configure domain values for Oracle Receivables Profitability Analytics.

To configure domain values for Oracle Receivables Analytics:

  1. Identify the entry types in your Receivables application by using the following SQL:

    SELECT DISTINCT ENTRY_TYPE, DESCR FROM PS_ENTRY_TYPE_TBL

    This query gives the transaction type codes in your Oracle Inventory application and their corresponding descriptions. The Oracle Inventory application is a data source from which Oracle General Ledger and Profitability Analytics extract data.

  2. Using a text editor, open the domainValues_ Xact_Types_DocTypes_psft.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

    Look for only the rows in the file that have XACT_CODE = 'RECEIVABLE' and W_XACT_TYPE_CODE = 'ORIGINAL' or 'APPLICATION'. The column XACT_SUBTYPE_CODE will contain the entry type codes from PeopleSoft Receivables and the columns W_XACT_TYPE_CODE and W_XACT_SUBTYPE_CODE are the corresponding domain values these entry type codes will be mapped to.

  3. Copy the TYPE column to the XACT_TYPE_CODE column in the file.

    The data must be copied starting from the second line. The first line is the column header.

  4. Map the entry type codes returned by the query above from your PeopleSoft Receivable Application to the domain values.

    Make sure the entry type codes go into the XACT_TYPE_CODE column in the file. Note that it is not necessarily a one-to-one mapping between the domain values and the entry types. For example, you can have multiple entry types mapped into the ORIGINAL/INVOICE domain value. Be careful not to map the same entry type to two different domain values.

  5. Save and close the file.

  6. In Informatica PowerCenter Designer, open mapplets mplt_BC_PSFT_ARTransactionFact_Item and mplt_BC_PSFT_ARTransactionFact_ItemActivity in the SDE_PSFTVersion_Adaptor folder.

  7. Open the Source Qualifier transformation.

    At the end of the SQL, there is a IN clause that should include all the entry type codes that map into W_XACT_TYPE_CODE = 'ORIGINAL' in the CSV file prepared above.

  8. In Informatica PowerCenter Workflow Manager, make the same change to the full load sessions that correspond to the SDE_PSFT_ARTransactionFact_Item_Full and SDE_PSFT_ARTransactionFact_ItemActivity_Full mappings.

5.2.3.3 How to insert beginning GL Balances into Oracle BI Applications

Before you run the initial ETL, ensure that you set the source system parameters in DAC in order to compute the GL balances correctly.

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Display the Source System Parameters tab.

  3. Set the following parameters:

    • Set the parameter $$INITIAL_EXTRACT_DATE to the date from which you want to extract data from your PeopleSoft application to the data warehouse. Ensure that this Parameter is the beginning of an accounting period and not just any date in the middle of a period. For example, if you decide to extract data from June 2005, and the June 2005 accounting period starts from 5th June, set the date to 5th June, 2005.

    • Setup the parameter $$YEAR to the same as the fiscal year of $$INITIAL_EXTRACT_DATE parameter above. For example, if June 2005 is fiscal year 2005, set this parameter to 2005.

    • Set the parameter $$ACCOUNTING_PERIOD to the accounting period number of the $$INITIAL_EXTART_DATE parameter above. For example, if June 2005 is the 6th period of fiscal year 2005, set this parameter to 6.

5.2.3.4 Oracle GL Budget Analytics for PeopleSoft Configuration

PeopleSoft GL Budget Analytics provides data from the following two areas:

  • Commitment Control Budget

  • Standard General Ledger Budget

By default, the Data Warehouse is updated with Commitment Control Budget data. To add data from Standard General Ledger Budget to the data warehouse, you have to set up a configuration tag called Budget Standard in DAC. The tag should provide two options-an option for running only Commit Control Budget and an option for running both Commit Control Budget and Standard General Ledger Budget. DAC uses the Commitment Control Budget in the tag by default.

5.2.3.4.1 PeopleSoft Tree for Commitment Control Budget

You can set up Commitment Control Budget based on Chartfield and PeopleSoft trees. For instructions about how to perform this configuration, which involves adding the Chartfield hierarchy to the data warehouse, see Section 5.3.3.3, "General Ledger Chartfield and Chartfield Hierarchy Extraction Customization."

5.2.3.5 PeopleSoft Currency Balance Types

The ETL process extracts four different currency balance types from PeopleSoft, namely base currency, translated currency, entered currency and statistical balance. These records are loaded and stored in the warehouse table W_GL_BALANCE_F. The following table shows some sample records of different balance types. The first four records are from Primary ledger 'Euro Ledger' with EUR as the base ledger currency. The next three records are from Translation ledger 'US Ledger' with USD as the base ledger currency. These five columns in the following table are columns in the physical data warehouse table:

  • LOC_CURR_CODE

  • ACCT_CURR_CODE

  • TRANSLATED_FLAG

  • BALANCE_LOC_AMT

  • BALANCE_ACCT_AMT

Line Ledger Name LOC_CURR_CODE ACCT_CURR_CODE TRANSLATED_FLAG BALANCE_LOC_AMT BALANCE_ACCT_AMT Balance Type Description
1 Euro Ledger (Primary ledger) EUR EUR N 390 50 A Base Currency Balance record with base ledger currency of EUR
2 Euro Ledger

(Primary ledger)

 EUR USD N 0 200 An Entered Currency Balance record with entered currency in USD (EUR is the base ledger currency)
3 Euro Ledger

(Primary ledger)

 EUR STAT N 0 80 A Statistical balance amount
4 US Ledger

(Translation ledger)

 USD USD Y 340 340 A Translated Currency Balance record with translation currency of USD

Note: US Ledger is a Translation ledger
5 US Ledger

(Translation ledger)

 USD EUR N 0 50 An Entered Currency Balance record with entered currency in EUR (USD is the base ledger currency)

Note: US Ledger is a Translation ledger

Users can query these balances by using different value columns and filters. The following table contains sample queries to show how to view different currency balance types and their corresponding results from the sample warehouse data in the preceding table.

Note:

Primary ledger 'Euro Ledger' corresponds to LEDGER_WID = 85228 and Translation ledger 'US Ledger' corresponds to LEDGER_WID = 87320 in the following sample scenarios.
Sample Desired Queries Physical Table Value Column Physical Query Filters Result for Value Column
A To query: balance amount for base currency of EUR, in Primary ledger 'Euro Ledger' BALANCE_LOC_AMT LEDGER_WID = 85228 390
B To query: entered balance amount for entered currency of EUR in Primary ledger 'Euro Ledger' BALANCE_ACCT_AMT LEDGER_WID = 85228

AND ACCT_CURR_CODE = 'EUR'

AND TRANSLATED_FLAG = 'N'

 50
C To query: entered balance amount for entered currency of USD, in Primary ledger 'Euro Ledger' BALANCE_ACCT_AMT LEDGER_WID = 85228

AND ACCT_CURR_CODE = 'USD'

AND TRANSLATED_FLAG = 'N'

 200
D To query: statistical balance amount, in Primary ledger 'Euro Ledger' BALANCE_ACCT_AMT LEDGER_WID = 85228

AND ACCT_CURR_CODE = 'STAT'

 80
E To query: translated balance account amount for translated currency of USD, in Translation ledger 'US Ledger' BALANCE_ACCT_AMT LEDGER_WID = 87320

AND TRANSLATED_FLAG = 'Y'

 340
F To query: entered balance amount for entered currency of EUR, in Translation ledger 'US Ledger' BALANCE_ACCT_AMT LEDGER_WID = 87320

AND ACCT_CURR_CODE = 'EUR'

AND TRANSLATED_FLAG = 'N'

 50

Oracle BI Answers users can create reports on currency balance types by selecting the desired facts from the Financials – GL Balance Sheet presentation table, as shown in the following table.

Sample Scenario Desired Report Presentation Table Presentation Column Result in Report
A View the local currency balance amounts (Debit Local Amount), such as in sample query A in the preceding table Ledger

Facts – GL Balance

 Ledger Name

Debit Local Amount

 Apply filter on: 
"Ledger Name" = 'Euro Ledger'
Ledger Name = Euro Ledger
Debit Local Amount = 390
B View the local currency balance amounts (Debit Amount), such as in sample query B in the preceding table Ledger

Facts – GL Balance

 Ledger Name

Debit Amount

 Apply filter on: 
"Ledger Name" = 'Euro Ledger'
Ledger Name = Euro Ledger
Debit Amount = 50
C View the entered currency balance amounts (Debit Entered Amount), such as in sample query C in the preceding table Ledger

Facts – GL Balance

GL Balance Details

 Ledger Name

Debit Entered Amount

Entered Currency Code

 Apply filters on: 
"Ledger Name" = 'Euro Ledger'
Entered Currency Code" = 'USD'
Ledger Name = Euro Ledger
Debit Entered Amount = 200
Entered Currency Code = USD
D View the statistical amount (Statistical Amount), such as in sample query D in the preceding table Ledger

Facts – Balance Sheet Statement

 Ledger Name

Statistical Amount

 
Ledger Name = Euro Ledger
Statistical Amount = 80
E View the translated currency balance amounts (Debit Translated Amount), such as in sample query E in the preceding table Ledger

Facts – GL Balance

GL Balance Details

 Ledger Name

Debit Translated Amount

Translated Currency Code

 Apply filters on: 
"Ledger Name" = 'US Ledger' 
"Translated Currency Code" = 'USD'
Ledger Name = US Ledger
Debit Translated Amount = 340
Translated Currency Code = USD
F If we want to view the Entered currency balance amounts (Debit Entered Amount), such as in sample query F in the preceding table Ledger

Facts – GL Balance

GL Balance Details

 Ledger Name

Debit Entered Amount

Entered Currency Code

 Apply filters on: 
"Ledger Name" = 'US Ledger' 
"Entered Currency Code" = 'EUR'
Ledger Name = US Ledger
Debit Entered Amount = 50
Entered Currency Code = EUR

5.2.4 Configuration Steps for Financial Analytics for Oracle's JD Edwards EnterpriseOne and JD Edwards World

This section contains configuration steps before you do a full data load that apply to Oracle's JD Edwards EnterpriseOne and JD Edwards World. It contains the following topics:

5.2.4.1 About Configuring Domain Values and CSV Worksheet Files for Oracle Financial Analytics for Oracle's JD Edwards EnterpriseOne and JD Edwards World

Table 5-3 lists the CSV worksheet files and the domain values for Financial Analytics for JD Edwards EnterpriseOne and JD Edwards World.

Table 5-3 Domain Values and CSV Worksheet Files for Oracle Financial Analytics for JD Edwards EnterpriseOne and JD Edwards World

Worksheet File Name Description Session

domain_values_payment_method_jde.csv

Lists the Payment Method and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Payment_Method_Dimension

domainvalues_src_Transaction_Type_Codes_jde.csv

domainvalues_lkp_Transaction_Type_Codes_jde.csv

Lists the Transaction Types and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Transaction_Type_Dimension

domainvalues_make_buy_indicator_product_jde.csv

Lists the Make Buy Indicator Product and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_ProductDimensions

file_fin_stmt_item_names_jde.csv

file_fin_sub_names_jde.csv

Lists the Financial Statement Item Name and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_CodeDimension_Fin_Stmt_Item_Names and SDE_JDE_CodeDimension_Fin_Sub_Names

file_group_acct_codes_jde.csv

Lists the Group Account Codes and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Stage_GroupAccountNumberDimension

file_grpact_fstmt.csv

Lists the Group Account financial Statement and the corresponding domain values for the JD Edwards EnterpriseOne or the JD Edwards World application.This file works in conjunction with the group_account_codes flatfile.

SIL_Stage_GroupAcctNumber_FinStatementItem

file_glacct_catcodes_segment_config_jde.csv

Lists the GL Account Category Codes Segment and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Stage_GLAccountDimension_CatCodesFlatFile

file_glacct_segment_config_jde.csv

Lists the GL Account Segment and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Stage_GLAccount_SegmentConfig_Extract

file_lkp_fiscal_period_Qtr_Config_jde.csv

Lists the Time Dimension Fiscal Period and the corresponding domain values for the JD Edwards EnterpriseOne or JD Edwards World application.

SDE_JDE_Timedimension_FiscalPeriod_Extract

5.2.4.2 How to Configure domainvalues_payment_method_jde.csv

This section explains how to configure the domainvalues_payment_method_jde.csv. You populate this lookup flat file with data before running the mapping to load W_PAYMENT_METHOD_D.

To configure domainvalues_payment_method_jde.csv:

  1. Identify the Payment Methods in your JD Edwards EnterpriseOne or JD Edwards World source system by using the following SQL:

    Select
DRKY 
from F0005
WHERE 
DRSY ='00' AND DRRT ='PY'

  2. Using a text editor, open the flat file domainvalues_payment_method_jde.csv located in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).

  3. Copy the output of the query to the UDC column of the flat file.

  4. Map each of the values under the UDC column to one of the Payment Methods defined for the Oracle Business Analytics Warehouse and enter it under column W_PAYMENT_METHOD_D.

    For more information about the W_PAYMENT_METHOD_D domain values, see Oracle Business Analytics Warehouse Data Model Reference.

  5. Use commas to separate the entries under the UDC column and W_PAYMENT_METHOD_D. Ensure that you do not include any spaces.

  6. Save and close the file.

5.2.4.3 How to Configure domainvalues_src_transaction_type_codes_jde.csv and domainvalues_lkp_transaction_type_codes_jde.csv

This section explains how to configure the domainvalues_src_transaction_type_codes_jde.csv and domainvalues_lkp_transaction_type_codes_jde.csv files. These flat files are used as source flat files in SDE_JDE_Transaction_Type_Dimension to retrieve the value of W_XACT_TYPE_CODE, W_XACT_SUBTYPE_CODE, and RECEIPT_TYPE by matching the UDC value of the lookup table with the DRKY value from the source table.

If a UDC value from 00/DT or 98/IT does not map to one of the XACT_CODEs listed in the Transaction Types dimension class table for Financial Analytics, then do not include it in the domainvalues_src_transaction_type_codes_jde.csv file. For example, Document Type IA (Inventory Adjustments) does not map to a XACT_CODE domain value and therefore it should not be included in the CSV file.

Furthermore, review the UDC values that come pre-populated in the domainvalues_src_transaction_type_codes_jde.csv file and make any necessary changes to the pre-populated domain values in order to align with how these UDCs are used in your JD Edwards EnterpriseOne or JD Edwards World implementation. For example, if UDC = '%' does not correspond to any of the XACT_CODE domain values then remove the row from the domainvalues_src_transaction_type_codes_jde.csv file.

All UDCs that do correspond to a XACT_CODE domain values must be included in this CSV file and properly configured. If not, then metrics pertaining to the domain values (for example Payables and Receivables) are at risk for not calculating correctly.

For more information on XACT_CODE domain values, see Transaction Types Dimension Class Table for Financial Analytics which appears under the W_XACT_TYPE_D Domain Values in the Oracle Business Analytics Warehouse Data Model Reference.

To configure the CSV files:

  1. Identify XACT_TYPE_CODE in your JD Edwards EnterpriseOne or JD Edwards World source system by using the following SQL:

    Select
 DRKY 
 From F0005 
 WHERE (DRSY ='00' AND DRRT ='DT') OR 
 (DRSY ='98' AND DRRT ='IT')

  2. Using a text editor, open the domainvalues_src_transaction_type_codes_jde.csv file located in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).

  3. Using the output from the query, append any UDC values that are not in the CSV file to the end of the CSV file. This is necessary only if the UDC pertains to the XACT_CODE domain value.

  4. Map each UDC value to one domain value in each of these domain columns: XACT_CODE, W_XACT_TYPE_CODE, and W_XACT_SUBTYPE_CODE.

  5. Save and close the file.

  6. Copy and paste the domainvalues_src_transaction_type_codes_jde.csv into the $PMServer\lkpfiles location and change the file name to domainvalues_lkp_transaction_type_codes_jde.csv.

  7. Update the above files when necessary.

5.2.4.4 How to Configure domainvalues_make_buy_indicator_product_jde.csv

This section explains how to configure the domainvalues_make_buy_indicator_product_jde.csv file. Use this file to identify the Product Stocking Types in your JD Edwards EnterpriseOne or JD Edwards World source system. In W_PRODUCT_D, the MAKE_BUY_IND column represents the source system domain value.

To configure the domainvalues_make_buy_indicator_product_jde.csv:

  1. Identify XACT_TYPE_CODE in your JD Edwards EnterpriseOne or JD Edwards World source system by using the following SQL:

    Select
DRKY
From F0005
WHERE DRSY ='41' AND DRRT =' I'

  2. Using a text editor, open the domainvalues_make_buy_indicator_product_jde.csv file located in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).

  3. Copy the output of the query to the UDC column of the flat file.

  4. Map each of the values under the Product Stocking Type column to the Make Buy Indicator column in the Oracle Business Analytics Warehouse.

    For more information about the W_PRODUCT_D domain values, see Oracle Business Analytics Warehouse Data Model Reference.

  5. Use commas to separate the entries under the Product Stocking Type and Make Buy Indicator columns. Ensure that you do not include any spaces.

  6. Save and close the file.

5.2.4.5 About the file_fin_stmt_item_names_jde.csv and file_fin_sub_names_jde.csv Files

The file_fin_stmt_item_names_jde.csv is used to identify the CODE-NAME combinations of Financial Statement Item Names. For more information about the published values, see Oracle Business Analytics Warehouse Data Model Reference. The file_fin_sub_names_jde.csv can be ignored.

The file_fin_stmt_item_names_jde.csv flat file is pre-populated; you do not have to configure it. However, you should review it to make sure the file is populated with values. The file_fin_sub_names_jde.csv flat file is also pre-populated. You can ignore this file, but do not delete it.

To review the files:

  1. Using a text editor, open the file_fin_stmt_item_names_jde.csv located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

    The target for this data is W_CODE_D and therefore follows the typical CODE dimension load format.

  2. Save and close the file.

5.2.4.6 How to Configure the file_group_acct_codes_jde.csv

This section explains how to configure the file_group_acct_codes_jde.csv. This flat file is used to identify the Group Account Codes for each object account range for each company. For example, for company 00001 you might specify group account codes for accounts 1000 to 2000 as REVENUE.

For a detailed list of the domain values for all possible Group Account Codes, see Oracle Business Analytics Warehouse Data Model Reference.

To configure file_group_acct_codes_jde.csv:

  1. Using a text editor, open the file_group_acct_codes_jde.csv located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

  2. In the Company field, populate the company that you are setting up and specify the ranges in the From and To column of that row with the corresponding Group Account Code.

  3. Save and close the file.

5.2.4.7 How to Configure the file_glacct_catcodes_segment_config_jde.csv

This section explains how to configure the file_glacct_catcodes_segment_config_jde.csv. This file is used to determine which Account Segment Code (1 to 30) takes which F0901 column value for each record. You associate a segment code (1 to 30) to these columns in the F0901 account master: CO, MCU, OBJ, SUB, SBL, SBLT, and any of 43 category codes based on your reporting needs.

Determine the columns that you need for reporting and populate the flat file accordingly. Do not change the values of the SEGMENT_NAME column. The list of values that the JDE_COLUMN can take include GMCO, GMMCU, SBL, SBLT, GMSUB, GMOBJ, and any value from GMR001 through GMR0043.

To configure file_glacct_catcodes_segment_config_jde.csv:

  1. Using a text editor, open the file_glacct_catcodes_segment_config_jde.csv located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

  2. Add values beside the comma using the above domain values for the configuration. Ensure that you do not include any spaces.

  3. Save and close the file.

5.2.4.8 How to Configure the file_glacct_segment_config_jde.csv

This section explains how to configure the file_glacct_segment_config_jde.csv. This file is generic and determines which among the first six segment codes of account dimension is used for aggregation in the GL_BALANCE_F. The row with row_id = 1 is redundant; whether or not it is populated has no impact. However, the dummy row, available in the sample file, might be necessary. The role of this row with row_id = 1 is taken care of by the file_glacct_catcodes_segment_config_jde.csv file.

To configure file_glacct_segment_config_jde.csv:

  1. Using a text editor, open the file_glacct_segment_config_jde.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

  2. In the row with ROW_ID = AGGREGATION, assign a value of Y to any of the first six segment codes to include these columns in the aggregation. Leave the trailing commas as such. Ensure that you do not include any spaces.

  3. Save and close the file.

5.2.4.9 How to Configure the file_lkp_fiscal_period_Qtr_Config_jde.csv

This section explains how to configure the file_lkp_fiscal_period_Qtr_Config_jde.csv. You must configure this file to support the metrics that are based on the Fiscal Quarter.

To configure file_lkp_fiscal_period_Qtr_Config_jde.csv:

  1. Identify the Fiscal Quarter data in your JD Edwards EnterpriseOne or JD Edwards World source system by using the following SQL:

    Select
CDDTPN,CDFY from F0008

  2. Using a text editor, open the file_lkp_fiscal_period_Qtr_Config_jde.csv located in $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).

  3. For each Date pattern, set the following fields:

    Field Value
    FiscalPattern CDDTPN
    YEAR CDFY
    Period Period Number Numeric
    Quarter No As there is no concept of Quarter in JD Edwards EnterpriseOne and JD Edwards World, define a numeric quarter number to which the period belongs.
    Quarter Start Date Customized Quarter Start Date for each period. Each quarter can span as many periods as users configure. The format is DD/MM/YYYY.
    Quarter End Date Customized Quarter End Date for each period. Each quarter can span as many periods as users configure. The format is DD/MM/YYYY.

    Note:

    Ensure that there are no unnecessary spaces in the flat file.

  4. Save and close the file.

5.2.4.10 Configuring the Exchange Date for JD Edwards EnterpriseOne Accounts Payable

You can populate the field EXCHANGE_DT in Accounts Payable (AP) with either the invoice date or the general ledger date using the DAC parameter $$EXCHANGE_DATE. Based on your selection, the EXCHANGE_DT field in the W_AP_XACT_FS table is populated with the appropriate date in the Oracle Business Analytics Warehouse.

To configure the $$EXCHANGE_DT parameter:

  1. Make sure to configure the invoice date or general ledger date the same as it is in JD Edwards EnterpriseOne so that your results are the same as the JD Edwards EnterpriseOne source system values.

  2. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  3. Click the Tasks tab, and perform a query for the AP transaction fact-related tasks.

  4. In the Detail area of the selected tasks, click the Parameters tab.

  5. Modify the parameter value as follows:

    • Specify INV to use the invoice date.

    • Specify GL to use the general ledger date.

  6. Save your changes.

5.2.4.11 About the Exchange Date for JD Edwards EnterpriseOne Account Receivables

The EXCHANGE_DT field in W_AR_XACT_FS is populated from the F03B11-RPERDJ table. The JD Edwards EnterpriseOne application stores the exchange date in the F03B11 table, which contains the value selected by the user through the processing option.

5.3 Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Oracle Financial Analytics. It contains the following topics:

5.3.1 Configuration Steps for Financial Analytics for All Source Systems

This section contains additional configuration steps that apply to all source systems. It contains the following topics:

5.3.1.1 How to Include Unapplied Payments in AR Aging Calculations

Follow these steps to include Unapplied Payments in Aging Calculations for AR Aging tables:

  1. In Informatica Designer, open the mapping PLP_ARSnapshotInvoiceAging in the PLP Folder.

  2. Edit the Source Qualifier transformation SQ_IA_AR_XACTS, and open the SQL Query in the Properties tab.

  3. In the WHERE clause of the SQL statement:

    1. Change the SQL text from:

      W_XACT_TYPE_D.W_XACT_SUBTYPE_CODE IN ('INVOICE','CR MEMO','DR MEMO') AND

      To:

      W_XACT_TYPE_D.W_XACT_SUBTYPE_CODE IN ('INVOICE','CR MEMO','DR MEMO',
'PAYMENT') AND

    2. Change the SQL text from:

      W_XACT_TYPE_D.W_XACT_TYPE_CODE = 'ORIGINAL' AND

      To:

      W_XACT_TYPE_D.W_XACT_TYPE_CODE IN ('ORIGINAL','PAYMENT') AND

  4. Save the changes.

    The next ETL will populate the Aging tables using UnApplied payments in the calculations.

5.3.1.2 How to Configure the AP/AR Aging Tables

This section explains how to control the lengths of the aging buckets in the AP and AR aging snapshot tables. These tables are:

  • W_AP_AGING_INVOICE_A

  • W_AR_AGING_INVOICE_A

  • W_AP_AGING_SUPPLIER_A

  • W_AR_AGING_CUSTOMER_A

In these four tables, outstanding AP/AR balance information is broken out into rows. Each row represents the outstanding balance information for a particular aging bucket. Four aging buckets are provided out-of-the-box, with the following durations:

  • Bucket 1: 0 – 30 days

  • Bucket 2: 31 – 60 days

  • Bucket 3: 61 – 90 days

  • Bucket 4: 90+ days

Use DAC to configure the lengths of these aging buckets, as described in the following procedure.

To configure the length of aging buckets:

  1. In the Design view, display the Tasks tab.

  2. Query for the following tasks:

    • PLP_APSnapshotInvoiceAging

    • PLP_ARSnapshotInvoiceAging

  3. For each of these tasks, display the Parameters tab in the lower pane and update the parameters with names starting with '$$BUCKET'.

    You can also use the $$HISTORY_MONTHS parameter to specify how many historic month end snapshots you want to retain (the out-of-the-box value is 24 months).

    Note: You cannot create aging snapshots for months prior to your initial load ETL. Aging snapshots are created one month at a time, as you go forward with your regular ETL runs. For example, if you start your system in January 2009 (that is, run the initial ETL some time in January 2009), and you set $$HISTORY_MONTHS to 12, you will have one snapshot at the end of January 2009, two at the end of February 2009, three at the end of March 2009 and so on until the end of December 2009 when you will have 12 snapshots.

  4. Save your changes.

5.3.1.3 How to Set Up Drill Down in Oracle BI Answers from General Ledger to Subledger

Note:

The Drill Down feature is not supported on Oracle JD Edwards source systems.

Oracle BI Applications enables you to trace a GL Journal to the subledger transaction that created that journal. This ability (or drill down) is achieved through the 'Navigation' feature in Oracle BI Answers.

To set up drill down in Oracle BI Answers from General Ledger to subledger:

  1. Create your subledger request from 'Financials - AP Transactions' or 'Financials - AR Transactions' catalog as applicable.

  2. In your request, add a filter on the column 'GL Journal ID' under the 'Document Details' folder and the set the operator of the filter to 'Is Prompted'.

  3. Build your GL Journal request from the 'Financials - GL Detail Transactions' catalog.

  4. To your request, add the column 'GL Journal ID' under the 'Document Details' folder.

  5. Navigate to the Column Properties of this column, and set the Value Interaction property in the Column Format tab to 'Navigate'.

  6. Add a navigation target and set the target location to the sub ledger request you created earlier.

    You may add multiple navigation targets if your GL report shows transactions from multiple subledgers and you want to drill from GL to the appropriate Subledger report. For example, if your GL report shows transactions from AP, AR and Revenue, and you have three subledger reports for each of these, you can add three navigation targets (by selecting the option 'Add Navigation Targets') and set the locations to each of these reports. Subsequently, when you run the GL report and when you click the "GL Journal ID" column Value, a popup appears, where you need to click the appropriate target based on the journal you selected. This will not happen automatically. For example, if you click a journal transaction originating from AP, you need to pick the appropriate subledger report (that is, the AP report in this case) to drill into the AP report and see the details. You can add the Group Account Number attribute from GL Account Dimension to your GL report to easily identify the subledger that the GL transaction belongs to.

Note:

For COGS, the 'GL Journal ID' column is not exposed in any subject areas. It is available in the Business Model and Mapping layer of the Oracle BI repository under the logical tables 'Dim - GL COGS Details'. As a workaround, you can create subject areas to report of detail level transactions for COGS and expose this column under the 'Document Details' folder in the subject area and use similar steps as above to set up a drill-down from GL to COGS.

To create a subject area for COGS:

  1. Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

    The OracleBIAnalyticsApps.rpd file is located at:

    ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\
coreapplication_obisn\repository

  2. Create an empty subject area (for example, Financials – GL Cost of Goods Sold). Set properties by following other subject areas.

  3. Drag 'Dim – GL COGS Details' and 'Fact - Fins - GL Cost of Goods Sold Posted' to the subject area.

  4. Drag other dimensions.

  5. Rename the presentation table 'Dim – GL COGS Details' to 'Document Details'.

  6. Rename the presentation table 'Fact - Fins - GL Cost of Goods Sold Posted' to 'Facts - GL Cost of Goods Sold'. Rename other dimensions if necessary.

You might also follow this same process to create a presentation table for Revenue to be able to drill from GL to Revenue level detail transactions.

5.3.1.4 About the Customer Costs Lines and Product Costs Lines Tables for Oracle Profitability Analytics

This configuration is required only if you are implementing Oracle Profitability Analytics and you want to allocate your expenses by product or by customer dimensions. The out-of-the-box adapter does not capture the miscellaneous cost and expenses associated with generating revenue from a customer or from a product (for example, marketing campaign expenses). You must provide this miscellaneous data through the Universal Adapter, as described in this section.

The configuration files file_customer_cost_line.csv and file_product_cost_line.csv are used to configure the Customer Cost Lines table and the Product Cost Lines table before an ETL full load. These configuration files are located by default in MW_HOME\biapps\dwrep\Informatica\Sample Universal Source Files.

Note: If these configuration files have file has been previously configured, updated copies of the files are located in the INFA_HOME\server\infa_shared\SrcFiles folder on the Informatica Server computer.

  • The file_customer_cost_line.csv file loads the Customer Costs Lines data in the W_CUSTOMER_COST_LINE_F table, and contains the following data:

    • Cost details by customers. The total cost by customers includes both direct cost that is captured in the financial system by products, and the allocated costs that are captured in the costing system.

    • The source of allocations. For example, the Sales and Marketing costs are not captured as direct costs by customers. However, at a later point in time, the costs could be allocated from a combination of General Ledger accounts or Cost Centers into various customers.

    • The source cost centers and General Ledger accounts. The Customer Costs can be categorized by Sales, Marketing, Service, and Operating costs. These could be further broken down to Salaries, Events, and Promotions. The Oracle Business Analytics Warehouse provides a set of categories, and these can be changed depending on the user needs and cost components by which customers are tracked.

    • The actual cost lines are also tracked in this table. In addition to Customers, there are number of other dimensions by which the costs can be tracked such as Sales Region, Sales Geography, Company, Business Area and the associated hierarchies. Not all dimension keys are relevant to all costs.

  • The file_product_cost_line.csv file loads the Product Costs Lines data in the W_PRODUCT_COST_LINE_F table, and contains the following data:

    • Cost details by products. The total cost by products include both direct cost that is captured in the financial system by products, and the allocated costs that are captured in the costing system.

    • The source of allocations. For example, the Sales and Marketing costs are not captured as direct costs by products. However, at a later point in time, the costs are allocated from a combination of General Ledger accounts and Cost Centers into various products.

    • The source cost centers and General Ledger accounts. The product costs can be categorized by Sales, Marketing, Service, and Operating costs. These could be further broken down into Salaries, Events, and Promotions. Oracle Business Analytics Warehouse provides a set of common categories, and these can be changed depending on the user needs and cost components by which products are tracked.

    • The actual cost lines. In addition to Products, there are number of other dimensions by which the costs are tracked such as Sales Region, Sales Geography, Company, Business Area, and the associated hierarchies. Not all dimension keys are relevant to all costs.

5.3.1.5 How to Configure the Customer Costs Lines and Product Costs Lines Tables for Oracle's Profitability Analytics

Follow this procedure to configure the Customer Cost Lines and Product Costs Lines before you perform a full load ETL.

Note: The General Ledger Revenue and General Ledger COGS fact tables are populated by either the Universal source system, the Oracle 11i source system, the JD Edwards EnterpriseOne source system, or the JD Edwards World source system. The Customer Costs Lines and Product Costs Lines fact tables are populated by the universal source system.

To configure the Customer Costs Lines and Product Costs Lines tables:

  1. Copy the configuration files file_customer_cost_line.csv and file_product_cost_line.csv from the MW_HOME\biapps\dwrep\Informatica\Sample Universal Source Files folder to the INFA_HOME\server\infa_shared\SrcFiles folder on the Informatica Server computer.

    Note: You can omit this step if the configuration files have already been copied to the INFA_HOME\server\infa_shared\SrcFiles folder on the Informatica Server computer.

  2. Using a text editor, open the file_customer_cost_line.csv file in the INFA_HOME\server\infa_shared\SrcFiles folder.

  3. Insert a record into the file for each customer costing transaction you want to load into the Customer Cost fact table.

  4. Save the file.

  5. Using a text editor, open the file_product_cost_line.csv file in the INFA_HOME\server\infa_shared\SrcFiles folder.

  6. Insert a record into the file for each product costing transaction you want to load into the Product Cost fact table.

  7. Save the file.

    You are now ready to perform a full load ETL to load the Customer Cost Lines and Product Costs Lines. To do this, use DAC to create and execute an Execution Plan that includes the Universal adapter Subject Areas 'Financials - Customer Expenses' and 'Financials - Product Expenses'.

5.3.1.6 How to Configure Budgets for Oracle Profitability Analytics

If you are using Oracle EBS, PeopleSoft, JD Edwards EnterpriseOne, or JD Edwards World source systems and would like to extract the budget data from these sources and import it into the data warehouse, you can use the preconfigured adapter mappings provided with Oracle Profitability Analytics. However, if you want to use budget data from other external systems, you can import the data into the data warehouse using the Universal adapter as described in this section. This section includes the following topics:

5.3.1.6.1 About Configuring Universal Source Files

The following tables describe the columns in the universal source CSV files file_budget.csv and file_acct_budget.csv, their data types, and how to populate them where applicable.

Table 5-4 shows the structure of the file_budget.csv file. The records in file_budget.csv are loaded into W_BUDGET_D.

Table 5-4 Universal Source for Budget Fact (file_budget.csv)

Column Name Datatype Size Description

BUDGET_NAME

string

80

Budget name.

BUDGET_VERSION

string

30

Budget version.

BUDGET_STATUS

string

30

Budget status.

BUDGET_TYPE

string

30

Budget type.

CREATED_BY_ID

string

80

ID of created by user. Populate with Integration_ID from w_user_d.

CHANGED_BY_ID

string

80

ID of changed by user. Populate with Integration_ID from w_user_d.

CREATED_ON_DT

string

14

Created date.

CHANGED_ON_DT

string

14

Changed date. Used for updating an existing record in the warehouse. Increase the date if you want to update the record. If a record with the same integration_ID already exists in the target table W_BUDGET_D, then the load process will compare the CHANGED_ON_DT values between this record and the record in W_BUDGET_D. If this record's CHANGED_ON_DT is later than the record in W_BUDGET_D, then the load process will perform an update against the record in W_BUDGET_D; otherwise the load process will ignore this record, and no update or insertion will occur. If there is no matching record in W_BUDGET_D with the same integration_ID, then the load process will insert this record into W_BUDGET_D.

AUX1_CHANGED_ON_DT

string

14

-

AUX2_CHANGED_ON_DT

string

14

-

AUX3_CHANGED_ON_DT

string

14

-

AUX4_CHANGED_ON_DT

string

14

-

DELETE_FLG

string

1

-

DATASOURCE_NUM_ID

number

10

A number for your data source. Populate the same datasource_num_id as your main source application.

INTEGRATION_ID

string

80

A unique identifier for the record.

TENANT_ID

string

80

-

X_CUSTOM

string

10

-

Table 5-5 shows the structure of the file_acct_budget.csv file. The records in the file_acct_budget.csv will be loaded into W__ACCT_BUDGET_F.

Table 5-5 Universal Source for Budget Fact (file_acct_budget.csv)

Column Name Datatype Size Description

ADJUSTMENT_FLG

string

1

TBD

AUX1_CHANGED_ON_DT

string

14

TBD

AUX2_CHANGED_ON_DT

string

14

TBD

AUX3_CHANGED_ON_DT

string

14

TBD

AUX4_CHANGED_ON_DT

string

14

TBD

BUDG_BUSN_AREA_ORG_ID

string

80

Company Org identifier. Populate with integration_id from w_int_org_d where business_area_flg = Y.

BUDG_CTRL_AREA_ORG_ID

string

80

Company Org identifier. Populate with integration_id from w_int_org_d where ctrl_area_flg = Y.

BUDG_FIN_AREA_ORG_ID

string

80

Company Org identifier. Populate with integration_id from w_int_org_d where fin_area_flg = Y.

BUDGET_CALENDAR_ID

string

80

TBD

BUDGET_DOC_AMT

number

22

Budget amount in document currency.

BUDGET_GRP_AMT

number

22

TBD

BUDGET_ID

string

80

Populate with the value from integration_id in file_budget.csv

BUDGET_LEDGER_ID

string

80

TBD

BUDGET_LOC_AMT

number

22

Budget amount in local currency.

CHANGED_BY_ID

string

80

ID of changed by user. Populate with Integration_ID from w_user_d.

CHANGED_ON_DT

string

14

Changed date. Used for updating an existing record in the warehouse. Increase the date if you want to update the record. If a record with the same integration_ID exists in the target table W_ACCT_BUDGET_F already, then the load process will compare the CHANGED_ON_DT values between this record and the record in W_ACCT_BUDGET_F. If this record's CHANGED_ON_DT is later than the record in W_ACCT_BUDGET_F, then the load process will perform an update against the record in W_ACCT_BUDGET_F; otherwise it'll ignore this record, no update or insertion will occur. If there's no matching record in W_ACCT_BUDGET_F with the same integration_ID, then the load process will insert this record into W_ACCT_BUDGET_F.

COMPANY_ORG_ID

string

80

Company Org identifier. Populate with integration_id from w_int_org_d where company_flg = Y.

COST_CENTER_ID

string

80

Cost Center identifier. Populate with integration_id from w_cost_center_d.

CREATED_BY_ID

string

80

ID of created by user. Populate with Integration_ID from w_user_d.

CREATED_ON_DT

string

14

Created date.

DATASOURCE_NUM_ID

number

10

A number for your data source. Populate the same datasource_num_id as your main source application.

DELETE_FLG

string

1

TBD

DOC_CURR_CODE

string

30

Document currency code.

GL_ACCOUNT_ID

string

80

GL Account identifier. Populate with integration_id from w_gl_account_d.

GRP_CURR_CODE

string

30

TBD

INTEGRATION_ID

string

80

A unique identifier for the record.

LOC_CURR_CODE

string

30

Local currency code.

PERIOD_BEGIN_DT

string

14

TBD

PERIOD_END_DT

string

14

Populate with your budget period's end date. If your budget is monthly, populate with the month end date.

POSTED_ON_DT

string

14

A date on which this transaction can be reported.

PRODUCT_ID

string

80

Product identifier. Populate with integration_id from w_product_d.

PROFIT_CENTER_ID

string

80

Profit Center identifier. Populate with integration_id from w_profit_center_d.

PROJECT_ID

string

80

-

TENANT_ID

string

80

TBD

X_CUSTOM

string

10

TBD

Note: Date columns should be populated in the CSV file as a number in the format YYYYMMDDHH24MISS.

Use Table 5-6 to understand how the integration_id (key) of some of the key dimensions are constructed for the Oracle EBS source system. You can use this information to populate the dimension foreign key identifiers in the above universal source CSV file for budget fact, if you have to use budget fact in conjunction with dimensions populated from Oracle EBS.

Table 5-6 Populating the integration_id fields in Oracle EBS Source Systems

Field How to populate

GL_ACCOUNT_ID (w_gl_account_d)

ccid

COMPANY_ORG_ID (w_int_org_d)

No need to populate; will be calculated based on GL Account ID.

COST_CENTER_ID (w_cost_center_d)

No need to populate; will be calculated based on GL Account ID.

PROFIT_CENTER_ID (w_profit_center_d)

No need to populate; will be calculated based on GL Account ID.

LEDGER_ID (w_ledger_d)

For Oracle 11i, populate as set of book ID. For Oracle R12, populate as ledger ID.

Use Table 5-7 to understand how the integration_id (key) of some of the key dimensions are constructed for Oracle's JD Edwards EnterpriseOne and JD Edwards World source systems. You can use this information to populate the dimension foreign key identifiers in the above universal source CSV file for budget fact, if you have to use budget fact in conjunction with dimensions populated from Oracle's JD Edwards EnterpriseOne or JD Edwards World.

Table 5-7 Populating the integration_id fields in Oracle's JD Edwards EnterpriseOne and JD Edwards World Source Systems

Field How to populate

GL_ACCOUNT_ID (w_gl_account_d_)

GBAID||'~'||GBSBL||'~'||GBSBLT

COMPANY_ORG_ID (w_int_org_d)

GBCO

COST_CENTER_ID (w_cost_center_d)

GBMCU

PROFIT_CENTER_ID (w_profit_center_d)

GBCO

LEDGER_ID (w_ledger_d)

GBCO

PRODUCT_ID (w_product_d)

If GBSBLT points to item, then update product ID with that GBSBL.

PROJECT_ID (w_product_d)

Not applicable

BUDG_BUSN_AREA_ORG_ID (w_int_org_d)

GBMCU

BUDG_FIN_AREA_ORG_ID (w_int_org_d)

GBMCU

BUDG_CTRL_AREA_ORG_ID (w_int_org_d)

GBMCU

BUDGET_ID (w_budget_d)

Not applicable

5.3.1.6.2 How to Import Budget Data into the Data Warehouse Through the Universal Adapter

Follow these steps to import budget data into the data warehouse through the Universal adapter.

  1. Populate the file_budget.csv and file_acct_budget.csv files with your budget data.

    The CSV files are located in the $pmserver\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).

    Refer the tables above for details of how to populate these files.

  2. In DAC, create a new Execution Plan using the Subject Area 'Financials – Budget' in the 'Universal' container.

  3. Run the new Execution Plan that you created in the previous step.

    Note: This Execution Plan must be run after the regular Execution Plan to populate the data warehouse for the other Subject Areas has completed.

  4. Load new the new budget data or changes to existing budget data.

    Repeat Step 1 and Step 3 as needed to load new budget for the next fiscal period or make corrections to already loaded budget data.

5.3.2 Configuration Steps for Financial Analytics for Oracle EBS

This section contains additional configuration steps that apply to Oracle EBS. It contains the following topics:

5.3.2.1 How GL Balances Are Populated in Oracle EBS

Oracle BI Applications provides two ways to populate the GL balances (stored in the W_GL_BALANCE_F table), as follows:

  • By extracting the GL balances directly from Oracle General Ledger, as follows:

    1. In DAC, go to the Design view, and then display the Subject Areas tab.

    2. Query for the Subject Area 'Financials – General Ledger'. Display the Configuration Tags subtab, and query for the tag 'Oracle – Extract GL Balance'. Activate the tag by deselecting the Inactive check box.

    3. Inactivate the tag 'Financials – Calculate GL Balance by selecting the is Inactive check box.

    4. Click Assemble to reassemble the Subject Area.

    5. Rebuild the Execution Plans that contain this Subject Area.

  • By calculating the GL balances based on the records in the W_GL_OTHER_F table, which stores all journal lines, as follows:

    1. In DAC, go to the Design view, and then display the Subject Areas tab.

    2. Query for the Subject Area 'Financials – General Ledger'. Display the Configuration Tags subtab, and query for the tag 'Financials – Calculate GL Balance'. Activate the tag by deselecting the Inactive check box.

    3. Inactivate the tag 'Oracle – Extract GL Balance' by selecting the is Inactive check box.

    4. Click Assemble to reassemble the Subject Area.

    5. Rebuild the Execution Plans that contain this Subject Area.

5.3.2.2 How to Configure Oracle Profitability Analytics Transaction Extracts

There are two separate transaction extracts for Oracle General Ledger and Profitability Analytics—General Ledger Revenue and General Ledger COGS. By default, the Oracle General Ledger Analytics application extracts only Completed revenue and COGS that have been posted to the general ledger. Completed revenue transactions are those where the RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = Y. If you want to extract incomplete revenue transactions, you can remove the filter in the Business Component.

To modify the extract filter for Oracle Profitability Analytics Revenue:

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_GLRevenueFact mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, remove the statement:

    AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = Y

  4. Validate and save your changes to the repository.

5.3.2.3 How to Configure Cost Of Goods Extract (for Oracle EBS 11i)

For customers of EBS 11i, 11.5.8, 11.5.9, and 11.5.10, the Oracle General Ledger Analytics application extracts only COGS transactions that have been posted to the general ledger, by default. All COGS transactions that have been transferred satisfy the following condition:

MTL_TRANSACTION_ACCOUNTS.GL_BATCH_ID <> -1

If you want to extract all transactions, you can remove the filter in the Business Component mapplet. You need to modify the Business Component mapplet for both the regular extract mapplet as well as the primary extract mapplet. This does not apply to EBS R12; by default all transactions are extracted. The following section applies only to EBS 11i customers.

To modify the extract filter for General Ledger COGS:

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).

  2. In Mapplet Designer, open mplt_BC_ORA_GLCOGSFact.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, remove the statement:

    AND MTL_TRANSACTION_ACCOUNTS.GL_BATCH_ID <> -1

  4. In Mapplet Designer, open mplt_BC_ORA_GLCOGSFact_Primary.

  5. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, remove the statement:

    AND MTL_TRANSACTION_ACCOUNTS.GL_BATCH_ID <> -1

  6. Validate and save your changes to the repository.

    Note: Steps 4 to 6 are optional. Although the mplt_BC_ORA_GLCOGSFact_Primary mapplet is not used in any of the out-of-the-box Subject Areas, Oracle recommends that you follow steps 4 to 6 in case the mplt_BC_ORA_GLCOGSFact_Primary mapplet is used in a custom Subject Area.

  7. Make appropriate changes to the SQL in the Informatica session, 'SDE_ORA_GLCOGSFact_Full', which is present in the corresponding folder in Informatica PowerCenter Workflow Manager.

  8. If you customized the mapping SDE_ORA_GLCOGSFact to extract from Oracle EBS versions 11.5.8, 11.5.9, or 11.5.10, modify the domain values file domainValues_Xact_Types_DocTypes_ora11i.csv.

  9. To extract from Oracle EBS version R12, modify the file domainValues_Xact_Types_DocTypes_ora12.csv.

5.3.2.4 How to Configure AP Balance ID for Oracle Payables Analytics

The AP Balance ID controls the level at which the balance in W_AP_BALANCE_F is maintained. This section contains configuration information for Oracle Payables Analytics that is specific to Oracle. By default, the Accounts Payable (AP) Balance ID is maintained at the following granularity:

SET_OF_BOOKS_ID||'~'||CODE_COMBINATION_ID||'~'||VENDOR_SITE_ID||'~'||
ORG_ID||'~'||VENDOR_ID

However, if you want to maintain your AP balance at a different grain, you can redefine the Balance ID value in the applicable mapplets.

To modify the Accounts Payable Balance ID:

Note:

To modify the Accounts Payable Balance ID, you must modify the following mapplets:

  • mplt_SA_ORA_APTransactionFact_LiabilityDistribution

  • mplt_SA_ORA_APTransactionFact_ExpenseDistribution

  • mplt_SA_ORA_APTransactionFact_Payment

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).

  2. In Mapplet Designer, open the mapplet (for example, mplt_SA_ORA_APTransactionFact_LiabilityDistribution).

  3. Double-click the Expression transformation to open the Edit Transformations dialog.

  4. Display the Ports tab, and edit the expression for the EXT_BALANCE_ID port.

    For example, you might click the Expression field to display the Expression Editor and modify the text in the Formula box.

  5. Validate and save your changes to the repository.

  6. Repeat steps 1 to 5 for each mapplet that is listed above.

5.3.2.5 How to Configure AR Balance ID for Oracle Receivables Analytics and Oracle General Ledger and Profitability Analytics

The AR Balance ID controls the level at which the balance in W_AR_BALANCE_F is maintained.

By default, the AR Balance ID is maintained at the following granularity:

set_of_books_id || '~' || code_combination_id || '~' || customer_id || '~' ||
customer_site_use_id || '~' transaction_currency_code || '~' || org_id

However, if you want to maintain your AR balance at a different grain, you can redefine the Balance ID value in the applicable mapplets.

To modify the AR Balance ID:

Note:

To modify the AR Balance ID, you must modify the following mapplets:

  • mplt_SA_ORA_ARTransactionFact_Adjust

  • mplt_SA_ORA_ARTransactionFact_ARScheduleDerive

  • mplt_SA_ORA_ARTransactionFact_CreditMemoApplication

  • mplt_SA_ORA_ARTransactionFact_ReceivableApplication

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).

  2. In Mapplet Designer, open the mapplet (for example, mplt_SA_ORA_ARTransactionFact_Adjust).

  3. Double-click the Expression transformation to open the Edit Transformations dialog.

  4. Display the Ports tab, and edit the expression for the EXT_NU_AR_BALANCE_ID port.

    For example, you might click the Expression field to display the Expression Editor and modify the text in the Formula box.

  5. Validate and save your changes to the repository.

  6. Repeat steps 1 to 5 for each mapplet that is listed above.

5.3.2.6 How to Configure the AR Adjustments Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only approved adjustment entries against accounts receivable transactions. Approved adjustments are entries where the AR_ADJUSTMENTS_ALL.STATUS = A. If you want to extract additional types of AR adjustment entries, you can remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as those that require more research, those that are rejected, and those that are not accrued charges.

To modify the extract filter for Accounts Receivable adjustments:

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_Adjust mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the SQL Query field and in the User Defined Join field, modify the statement:

    AND AR_ADJUSTMENTS_ALL.STATUS = A

  4. Validate and save your changes to the repository.

  5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_AdjustPrimary mapplet.

5.3.2.7 How to Configure the AR Schedules Extract

By default, Oracle Receivables Analytics extracts only completed schedules; that is, transactions where the RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG(+) = Y. If you want to extract additional types of AR schedule entries, you must remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as those that were marked as incomplete.

To modify the extract filter for Accounts Receivable schedules:

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_ARSchedules mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog.

    In the User Defined Join field and in the SQL Query field, modify the statement:

    AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG(+) = Y

  4. Validate and save your changes to the repository.

5.3.2.8 How to Configure the AR Cash Receipt Application Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only confirmed, cash-receipt application entries against accounts receivable transactions. Confirmed receipts are entries where the AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG = Y OR NULL. If you want to extract additional types of cash-receipt application entries, you can remove the filter in the Business Component mapplet. By modifying or removing the filter, you can extract other entries, such as unconfirmed applications.

You must modify both the regular mapplet (mplt_BC_ORA_ARTransactionFact_ReceivableApplication) as well as the primary extract mapplet (mplt_BC_ORA_ARTransactionFact_ReceivableApplicationPrimary).

To modify the extract filter for AR cash receipt application:

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_ReceivableApplication mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, modify the statement:

    AND NVL(AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG,'Y') = Y

  4. Validate and save your changes to the repository.

  5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_ReceivableApplicationPrimary mapplet.

5.3.2.9 How to Configure the AR Credit-Memo Application Extract for Oracle Receivables Analytics

By default, Oracle Receivables Analytics extracts only confirmed, credit-memo application entries against accounts receivable transactions. Confirmed credit memos are entries where the AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG = Y OR NULL. If you want to extract additional types of AR credit-memo application entries, you can remove the filter. By modifying or removing the filter, you can extract other entries such as unconfirmed, credit memos.

You must modify both the regular mapplet (mplt_BC_ORA_ARTransactionFact_CreditmemoApplication), as well as the primary extract mapplet (mplt_BC_ORA_ARTransactionFact_CreditmemoApplicationPrimary). Repeat the following procedure for each mapplet.

To modify the extract filter for Accounts Receivable Credit-Memo Application:

  1. In Informatica PowerCenter Designer, open the appropriate Oracle Applications folder (for example, SDE_ORAVersion_Adaptor).

  2. In Mapplet Designer, open the mplt_BC_ORA_ARTransactionFact_CreditmemoApplication mapplet.

  3. Double-click the Source Qualifier to open the Edit Transformations dialog, and display the Properties tab.

    In the User Defined Join field and in the SQL Query field, modify the statement:

    AND NVL(AR_RECEIVABLE_APPLICATIONS_ALL.CONFIRMED_FLAG,'Y') = Y

  4. Validate and save your changes to the repository.

  5. Repeat Step 2 to Step 4 for the mplt_BC_ORA_ARTransactionFact_CreditmemoApplicationPrimary mapplet.

5.3.2.10 How to Enable Project Analytics Integration with Financial Subject Areas

You can enable Oracle Financial Analytics for EBS to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics.

You can configure the following Oracle Financial Analytics for EBS Subject Areas to use Project Analytics for EBS tables:

  • Financials -Payables

  • Financials -Receivables

  • Financials - Cost of Goods Sold

  • Financials - Revenue

The following Oracle Financial Analytics fact tables integrate with Project Analytics dimensions:

  • W_AP_INV_DIST_F

  • W_AP_XACT_F

  • W_AR_XACT_F

  • W_GL_COGS_F

  • W_GL_REVN_F

  • W_AP_AGING_INVOICE_A

  • W_AR_AGING_INVOICE_A

To enable the integration:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.

  3. Assemble the Subject Areas.

  4. Click the Execute button and build the Execution Plan for the Subject Areas that you updated.

5.3.3 Configuration Steps for Financial Analytics for PeopleSoft

This section contains additional configuration steps that apply to PeopleSoft. It contains the following topics:

5.3.3.1 Overview of PeopleSoft Tree Data Structures

For PeopleSoft, the Oracle Financial Analytics application uses data from a data structure called PeopleSoft trees to get information about the organization's GL account hierarchies, profit center hierarchies, cost center hierarchies, and so on.

PeopleSoft trees are a flexible, generic way of constructing hierarchical relationships among PeopleSoft entities, such as chartfields, business units, and so on.

5.3.3.2 About PeopleSoft Trees in Oracle Financial Analytics

Oracle BI Applications provides a set of mechanisms to support various tree structures in PeopleSoft applications. Table 5-8 shows the supported structure types, and Table 5-9 shows the supported level types.

Table 5-8 PeopleSoft Tree Structure Types

Structure Type Support

Winter tree

Y

Detail tree

Y

Summer tree

Y

Table 5-9 PeopleSoft Tree Level Types

Level Type Support

Strict-level tree

Y

Loose-level tree

Y

A flattened structure is used to store the hierarchy-related data. Each record of W_HIERARCHY_D and other hierarchy-related dimension tables (for example, W_INT_ORG_DH) represents a single node in the tree. It stores the path information from the top-most node to that node. For the bottom nodes in the detail tree, the extraction process generates a number of records according to the node's range and the record in the relevant table and puts those records into the hierarchy table. Figure 5-1 provides an illustration of this functionality.

Figure 5-1 Example GL Chartfield Hierarchy

Description of Figure 5-1 follows
Description of "Figure 5-1 Example GL Chartfield Hierarchy"

For any node in the tree, except for the bottom nodes, W_HIERARCHY_D will have one corresponding record. For example, for the 'E' node in the tree above (the one highlighted with a blue square), the record shown in Table 5-10 will appear in the database.

Table 5-10 How Node E Is Stored in W_HIERARCHY_D Table

HIER1_CODE HIER2_CODE HIER3_CODE HIER4_CODE HIER[5-20]_CODE

US

E

E

E

E

If the actual hierarchy level in the tree ends earlier than the predefined level of W_HIERARCHY_D, then the lower hierarchy level in the table will repeat the same value as its next higher level. The predefined level is 20, which means if one path of the tree only has five levels, then levels six through 20 will repeat the value of level five.

For the leaf node in the detail tree shown in Figure 5-1, W_HIERARCHY_D will have a record for every existing record within this detail range. The extraction process will find the single record in the relevant table according to the tree's structure. For example, in the CA node (the one with the red square in Figure 5-1), the range is 2334-2434 and 3001-3001. The state table (for example, State_TBL) contains two records: 2340 and 3001. When generating the CA node, the extraction process will extract these two records and put them into W_HIERARCH_D, as shown in Table 5-11.

Table 5-11 Example of CSV Chartfield Mapping Values

HIER1_CODE HIER2_CODE HIER3_CODE HIER4_CODE HIER5_CODE

US

W

CA

2340

2340

US

W

CA

3001

3001

With both the Summer tree and detail tree structure types, the lowest level node is always a node with a specific value. For the winter tree structure type, the lowest level code is the same as the higher level, as illustrated in Figure 5-2.

Figure 5-2 Winter Loose-Level Tree

Description of Figure 5-2 follows
Description of "Figure 5-2 Winter Loose-Level Tree"

The record 'Kuo' is stored in the database as shown in Table 5-12.

Table 5-12 Winter Loose-Level Tree

HIER1_CODE HIER2_CODE HIER3_CODE HIER4_CODE HIER5_CODE

Smith

Cheng

Kuo

Kuo

Kuo

Oracle BI Applications uses temporary tables and staging tables to handle the extraction and load process of the tree. These tables are standard and not tree-specific or tree-structure specific. Table 5-13 lists the tables used during the GL Hierarchy extraction process. The sequence indicates the order in which these tables are populated.

Table 5-13 GL Chartfield Hierarchy

Sequence Table Name

1

W_PSFT_TREE_TMP

2

W_PSFT_GLHIER_CF_DH_TMP

3

W_PSFT_GLHIER_DTLRGE_DH_TMP

4

W_PSFT_GLHIER_VERT_DH_TMP

5

W_PSFT_GLHIER_FLAT_DH_TMP

6

W_HIERARCHY_DS

Table 5-14 lists the tables used during the Internal Organization Hierarchy extraction process.

Table 5-14 Internal Organization Hierarchy

Sequence Table Name

1

W_PSFT_INT_ORG_TREE_TMP

2

W_PSFT_INT_ORG_GLBU_DH_TMP

3

W_PSFT_INT_ORG_DTLRGE_DH_TMP

4

W_PSFT_INT_ORG_VERT_DH_TMP

5

W_PSFT_INT_ORG_FLAT_DH_TMP

6

W_INT_ORG_DHS

7

W_INT_ORG_DH

Note:

The hierarchy related extraction and load tasks are inactivated in the standard application. To activate the hierarchy extractions and load tasks, you need to activate certain configuration tags in DAC, as described in the following section.

Using DAC Configuration Tags to Activate and Inactivate Tasks

The Configuration Tag feature in DAC enables you to activate or inactivate a certain set of tasks in the ETL process. When inactivated, the set of tasks will not be executed when the Execution Plan runs. When activated, the set of tasks will be executed when the Execution Plan runs.

Oracle BI Applications includes two configuration tags for hierarchy extractions and loads, as follows:

  • 'PeopleSoft - Populate Non Business Unit Related Hierarchies,' which contains all but business unit-related hierarchies' extractions and load tasks.

  • 'PeopleSoft - Populate Business Unit Related Hierarchies', which contains business unit-related hierarchies' extractions and load tasks.

By activating and inactivating configuration tags in a Subject Area, you can control whether the hierarchy extraction and load tasks get executed in the Execution Plan.

To activate or inactivate a set of tasks in your ETL process:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Display the Subject Areas tab.

  3. For each Financials Subject Area, do the following:

    1. Select the Financials Subject Area in the top pane, for example Cost of Goods Sold, General Ledger, and so on.

    2. In the bottom pane, display the Configuration Tags subtab.

    3. Deselect the Inactive check box of the hierarchy you want to enable, as follows:

      - For GL Chartfield, deselect the Inactive check box for PeopleSoft - Populate Non Business Unit Related Hierarchies.

      - For Business Unit, deselect the Inactive check box for PeopleSoft - Populate Business Unit Related Hierarchies.

    4. Save the changes.

  4. Click Assemble to reassemble the Subject Areas that you have modified.

  5. Go to the Execute view, and display the Execute view.

  6. For every Execution Plan that contains the Subject Areas that you modified, rebuild the Execution Plan.

  7. Save the Execution Plans that you modified.

Sample Mappings for Internal Organization Hierarchy and GL Chartfield Hierarchy

Oracle BI Applications provides two sets of mappings to extract Internal Organization Hierarchy and General Ledger Chartfield Hierarchy related trees. You can customize the extractions by copying the mappings and modifying the parameters. For instructions, see Section 5.3.3.3, "General Ledger Chartfield and Chartfield Hierarchy Extraction Customization."

5.3.3.3 General Ledger Chartfield and Chartfield Hierarchy Extraction Customization

This section provides an example of how to customize mappings for a GL Chartfield and Hierarchy extraction. In this example, the extraction involves a tree named DEPARTMENT_HIERARCHY with SetID XYZ on chartfield Department, which uses DEPT_TBL as its structure's Record Name and DEPTID as the Field Name.

The mappings that require customization for this example are shown in Table 5-15.

Table 5-15 General Ledger Chartfield Hierarchy Mappings

Execution Sequence Mapping Name Needs Customization?

1

SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract

Y

2

SDE_PSFT_Stage_GLHierarchy_GetAccountChartField

Y

3

SDE_PSFT_GLSegmentDimension_GLAccount

Y

4

SDE_PSFT_Stage_GLHierarchy_GetHierarchyLevel

N

5

SDE_PSFT_Stage_GLHierarchy_DeriveRange

N

6

SDE_PSFT_Stage_GLHierarchy_Flatten

N

7

SDE_PSFT_GLHierarchy_Derive

N

The following procedure shows the steps required to perform this extraction. Note that if your chartfields do not have hierarchies/trees defined in your PeopleSoft system, you might still want to extract just the chartfields and ignore the hierarchy extraction. To do this, in the following steps, perform only the tasks that correspond to the mapping SDE_PSFT_GLSegmentDimension_xyz and ignore the tasks that correspond to mappings SDE_PSFT_Stage_GLHierarchy_xyz.

To customize mappings for a GL Chartfield and Hierarchy extraction:

  1. In Informatica PowerCenter Designer, create new mappings by copying the following existing mappings:

    • SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract

    • SDE_PSFT_Stage_GLHierarchy_GetAccountChartField

    • SDE_PSFT_GLSegmentDimension_GLAccount

    Name the new mappings as follows:

    • SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract

    • SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField

    • SDE_PSFT_GLSegmentDimension_Department

  2. Open the mapping SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract and edit the settings as follows:

    1. In SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract, open the source definition PSFT_TREE_VERTICAL_FLATTENING. Under the 'Properties' tab, change the Tree Name to DEPARTMENT_HIERARCHY, set the SetID to XYZ, and set the Effective date to 01/01/1900 00:00:00. Note that Tree Name, SetID, and Effective Date must be the same as the tree definition in the OLTP system.

    2. Open the Exp_TreeName_SetID Expression transformation, and change the values as follows:

      - Change the value of TREE_NAME to DEPARTMENT_HIERARCHY.

      - Change the value of SETID to XYZ.

      - Change the value of CHARTFIELD to Department. (This name is the same as that defined in file_glacct_segment_config_psft.csv.)

  4. Open the mapping SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField and edit the settings as follows:

    - In the SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField mapping, change the source table and Source Qualifier to DEPT_TBL.

    - Connect SETID, DEPTID, EFFDT, EFF_STATUS, and DESCR from the Source Qualifier to the corresponding fields on the filter Filter_GLACCT (connect DEPTID to CHARTFIELD).

    - In the Expression transformation Exp_TMPGLACCT (the last Expression transformation of this mapping), change the Expression field SOURCE_CHARTFIELD_NAME to Department. (This is the same name as the source table.)

  5. Open the mapping SDE_PSFT_GLSegmentDimension_Department and edit the settings as follows:

    1. Delete the source, then import DEPT_TBL from source system through the Source Analyzer view, drag the imported source to this mapping, and delete the original Source and Source Qualifier after the import.

    2. Connect SETID, DEPTID, EFFDT, EFF_STATUS, and DESCR from the Source Qualifier to the corresponding fields in the filter Filter_GLACCT (connect DEPTID to CHARTFIELD).

    3. In the Expression transformation Exp_CHARTFIELD_NAME (the last Expression transformation of this mapping), change the Expression field SOURCE_CHARTFIELD_NAME to Department.

  6. Save all your changes in the Informatica folder.

  7. Add corresponding sessions and workflows in Informatica PowerCenter Workflow Manager, as follows:

    1. In Informatica PowerCenter Workflow Manager, locate the appropriate PeopleSoft folder (for example, SDE_PSFT_Version_Adaptor).

    2. Open the Task Developer and create a new Workflow for the mappings you just created.

      Tip: To quickly create a new Workflow, copy the SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract workflow and rename it to SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract.

    3. Edit the task in the new SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract Workflow, and use the various tabs on the Edit Tasks dialog to modify the parameters as specified in Table 5-16.

      Note: Do not change the values of the other mappings.

      Table 5-16 General Ledger Chartfield Hierarchy Mappings

      Tab Name Parameter Name Value

      Properties

      Parameter Filename

      SDE_PSFT_88_Adaptor.mapping_name.log

      Properties

      $Source connection value

      $DBConnection_OLTP

      Properties

      $Target connection value

      $DBConnection_OLAP

      Properties

      DTM buffer size

      32000000

      Config Object

      Default buffer block size

      128000

      Config Object

      Stop on errors

      1

      Config Object

      Override tracing

      Normal

      Mapping

      Connection W_PSFT_TREE_TMP - Rational

      $DBConnection_OLAP

      Mapping

      Connection SQ_PSFT_TREE_VERTICAL_FLATTENING - Application

      $DBConnection_OLTP

      Mapping

      Connection W_PSFT_TREE_TMP1 - Rational

      $DBConnection_OLAP

      Mapping

      Connection Lkp_Tree_ChartField - Rational

      $DBConnection_OLTP

      Mapping

      SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract – Session $Source Connection Value

      $DBConnection_OLTP

      Mapping

      SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract – Session $Target Connection Value

      $DBConnection_OLAP

    4. Save your changes.

    5. In Informatica PowerCenter Workflow Manager, create a workflow with the same name as the session.

    6. Drag the newly created session to the panel.

    7. Connect the start with the session.

    8. Create a similar session and workflow for SDE_PSFT_GLSegmentDimension_Department.

  8. Save and check-in all your changes in Informatica PowerCenter Designer and Informatica PowerCenter Workflow Manager.

  9. Reconfigure the DAC settings, as follows:

    1. Log in to DAC, open your custom container, and display the Tasks tab.

    2. Add the three workflows that you just created as new tasks.

      - SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract

      - SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField

      - SDE_PSFT_GLSegmentDimension_Department

      For information about what values to set task property values to, refer to the following tasks:

      - SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract

      - SDE_PSFT_Stage_GLHierarchy_GetAccountChartField

      - SDE_PSFT_GLSegmentDimension_GLAccount

    3. Right-click each task and select Synchronize tasks.

      The Synchronize tasks process reads the source table and target table information for these tasks from the Informatica Repository. To see this information, go to the Tasks tab and display the Source Tables subtab and the Target Tables subtab.

    4. Add the newly created tasks into the DAC task groups.

      Note: Because intermediate temporary tables are reused, it is necessary to include the newly added tasks to task groups to prevent over-loading or pretruncating of data. GL Hierarchy Extract has the following task groups:

      - TASK_GROUP_Extract_GLHierarchy

      - TASK_GROUP_Extract_GLHierarchy_GetChartFieldValues

      - TASK_GROUP_Extract_GLSegmentDimension

      For this example, you need to include the newly added tasks into task groups as follows:

      - Add SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract to the task group TASK_GROUP_Extract_GLHierarchy

      - Add SDE_PSFT_Stage_GLHierarchy_GetDepartmentChartField to task group TASK_GROUP_Extract_GLHierarchy_GetChartFieldValues

      - Add SDE_PSFT_GLSegmentDimension_Department to task group TASK_GROUP_Extract_GLSegmentDimension

      The steps for adding a task to a task group are as follows:

      - In DAC, go to the Design view, and then select the Task Group tab.

      - In the top pane, select the appropriate task group.

      - Display the Child Tasks subtab, and then click Add/Remove in the bottom pane toolbar.

      - Query for the appropriate task, and then click Add.

      - Click OK, and then click Save.

    5. Add SDE_PSFT_Stage_GLHierarchy_AccountHierarchy_Extract and SDE_PSFT_Stage_GLHierarchy_GetAccountChartField tasks as configuration tags in DAC:

      - In the Design view, click Configuration Tags.

      - Query for the tag PeopleSoft - Populate Non Business Unit Related Hierarchies.

      - Display the Tasks subtab, and then click Add/Remove in the bottom pane toolbar.

      - Query for the new mappings you just entered into DAC.

      - Select the mappings, and add them into the right panel.

      - Click OK, and then click Save.

    6. Reassemble all the financial-related Subject Areas:

      - In the Subject Areas tab, select the Subject Area, and click Assemble in the tool bar.

      - Repeat this step for all of the financial-related Subject Areas (for example, Cost of Goods Sold, General Ledger, Payables, Receivables, Revenue)

    7. Rebuild the Execution Plans that contain the Subject Areas you reassembled:

      - Go to the Execute view, and display the Execution Plans tab.

      - Select the appropriate Execution Plan (for example, Financials_PeopleSoft).

      - Click Build in the toolbar.

  10. Make the following changes in the RPD metadata using the Oracle BI Administration tool. The metadata contains multiple logical tables that represent each GL Segment, such as Dim - GL Segment1, Dim - GL Segment2, and so on. Because all these logical tables are mapped to the same physical table, W_GL_SEGMENT_D, a filter should be specified in the logical table source of these logical tables to restrain the output of the logical table to represent only that particular segment.

    You must set the filter on the physical column SEGMENT_LOV_ID to "CHARTFIELD NAME~SET_ID" (in other words, the chartfield name and setid concatenated with a '~' character) that are applicable for that particular segment. Note the following:

    • The "CHARTFIELD NAME" is the same as the CHARTFIELD NAME you specified in the above mappings that extract the trees and/or chartfields. It is also the same as the one defined in the file_glacct_segment_config_psft.csv file.

    • The "SET_ID" is the same as the SET_ID you specified in the above mappings that extract the trees and/or the setids that were extracted in the above mappings that extract chartfields. You might have multiple setids for a particular segment, but only one chartfield name for each segment.

    To specify a filter in the Business Model and Mapping layer of the Oracle BI Repository, do the following:

    1. Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

      The OracleBIAnalyticsApps.rpd file is located at:

      ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository

    2. Expand each logical table, for example, Dim - GL Segment1, and open the logical table source under it.

    3. Display the Content tab.

    4. In the 'Use this WHERE clause…' box, apply a filter on the corresponding physical table alias of W_GL_SEGMENT_D. For example:

      "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_D_Segment1"."SEGMENT_LOV_ID" IN (comma seperated CHARTFIELD NAME~SET_ID)

      If there are multiple setids for a segment, enter all possible "CHARTFIElD_NAME~SETID" values, separated by commas, that correspond to this segment. For example, if this is the "Account" segment, the value for the filter might look like: ('Account~xyz1', 'Account~xyz2', 'Account~xyz3'), where xyz1, xyz2, and xyz3 are the setids that correspond to your Account chartfield values.

  11. Oracle Financial Analytics supports up to 30 segments in the GL Account dimension, and by default delivers 10 GL Segment dimensions in the RPD. If you need more than 10 GL Segments, perform the following steps to add new segments:

    1. In the Physical layer:

      Create a new physical alias of W_GL_SEGMENT_D as "Dim_W_GL_SEGMENT_D_SegmentXX". To do this, right-click the physical table W_GL_SEGMENT_D and select New Object and then Alias. Name the new alias Dim_W_GL_SEGMENT_D_SegmentXX. Similarly, create a new alias of W_HIERARCHY_D as "Dim_W_HIERARCHY_D_SegmentXX".

      In the Physical Diagram, create a Physical Foreign Key between Dim_W_HIERARCHY_D_SegmentXX and Dim_W_GL_SEGMENT_D_SegmentXX similar to the one between Dim_W_HIERARCHY_D_Segment1 and Dim_W_GL_SEGMENT_D_Segment1. The direction of the foreign key should be from W_HIERACHY_D to W_GL_SEGMENT_D; for example, on a '0/1':N cardinality join, W_HIERARCHY_D will be on the '0/1' side and W_GL_SEGMENT_D will be on the 'N' side. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information about how to create physical foreign key joins.

      Similarly, create a physical foreign key join between Dim_W_GL_SEGMENT_D_SegmentXX and Dim_W_GL_ACCOUNT_D, with W_GL_SEGMENT_D on the '1' side and W_GL_ACCOUNT_D on the 'N' side.

      Save your changes.

    2. In the Business Model and Mapping layer:

      Create a new logical table "Dim - GL SegmentXX" similar to "Dim – GL Segment1". This logical table should have a logical table source that is mapped to the physical tables created above (for example, it will have both Dim_W_GL_SEGMENT_D_SegmentXX and Dim_W_HIERARCHY_D_SegmentXX). This logical table should also have all attributes similar to "Dim – GL Segment1" properly mapped to the respective physical tables, Dim_W_GL_SEGMENT_D_SegmentXX and Dim_W_HIERARCHY_D_SegmentXX.

      In the Business Model Diagram, create a logical join from "Dim – GL SegmentXX" to all the relevant logical fact tables similar to "Dim – GL Segment1", with the GL Segment Dimension Logical table on the '0/1' side and the logical fact table on the 'N' side. To see all the relevant logical fact tables, first include Dim – GL Segment1 on the Business Model Diagram, and then right-click that table and select Add Direct Joins.

      Add the content filter in the logical table source of "Dim – GL SegmentXX" as described in the previous step.

      Create a dimension by right-clicking "Dim – GL SegmentXX", and select Create Dimension. Rename this to "GL SegmentXX". Make sure the drill-down structure is similar to "GL Segment1". If you are not sure how to do this, follow these steps: By default, the dimension will have two levels: the Grand Total Level and the Detail Level. Rename these levels to "All" and "Detail" respectively. Right-click the "All" level and select "New Object" and then "Child Level". Name this level as Level1. Similarly, create a level under Level1 and name it as Level2. Repeat this process until you have Level19 under Level18. Now drag the "Detail" level under "Level19" so that "Detail" is the last level of the hierarchy. Now, from the new logical table Dim - GL SegmentXX, drag Level1 Code and Level1 Name attributes to the "Level1" level of the hierarchy. Now go the Properties of the Level and from the Keys tab, create two new keys, one for Level1 Code and one for Level1 Name. When you create keys, make sure the 'Use for Drilldown' option is OFF for Level1 Code and ON for Level1 Name. Also, make sure the Primary Key drop-down is set to Level1 Code. Now, proceed similarly to all 19 levels by dragging the appropriate two attributes to the appropriate levels and create the keys similar to above. For the "Detail" level, drag the Level20 Code and Level20 Name attributes into it and create the keys similar to above.

      Open the Logical Table Source of the new logical table created, Dim - GL SegmentXX. Set the Aggregation Content in the Content tab by setting the Logical Level to "Detail" level of the "GL SegmentXX" dimension/hierarchy created above.

      Similarly, we need to set the aggregation content to all relevant fact logical table sources. Open all Logical Table Sources of all the logical fact tables that are relevant to the new logical table one at a time. Go to the Content tab. If you see that it is set to the "Detail" level of other GL Segment Dimensions such as GL Segment1, GL Segment2, and so on, then set it to the "Detail" level of the "GL Segment XX" dimension/hierarchy created above. If not, skip that logical table source and go to the next one.

    3. Drag your new "Dim - GL Segment XX" dimensions into the appropriate subject areas in the Presentation layer. Typically, you can expose these GL Segment dimensions in all subject areas where the GL Account dimension is exposed. You can also find all appropriate subject areas by right-clicking Dim – GL Segment1 and select Query Related Objects, then selecting Presentation, and then selecting Subject Area.

    4. Save your changes and check global consistency.

  12. Each GL Segment denotes a certain meaningful CHARTFIELD NAME~SET_ID in your OLTP. To clearly identify each segment in the report, you can rename the presentation table "GL SegmentX", logical dimension "GL SegmentX", and logical table "Dim - GL SegmentX" according to its own meaning.

    For example, if you populate Program chartfield into Segment1, you can rename logical table "Dim - GL Segment1" as "Dim - GL Segment Program" or any other appropriate name and then rename the tables in the Presentation layer accordingly.

5.3.3.4 How to Set Up Default Fiscal Calendars For PeopleSoft

In Oracle Business Intelligence Applications Release 7.9.6.3, the semantic layer (RPD) metadata contains session variables to store the current fiscal year, fiscal quarter, fiscal period and so on for a user. Since multiple fiscal calendars are supported in Release 7.9.6.3, you need to get the default fiscal calendar for a user based on the Ledger or Business Unit assigned to the user, and then get the current fiscal year, quarter and so on based on this default fiscal calendar.

The following initialization blocks need to be created in the RPD metadata to get the default fiscal calendar for a user. These initialization blocks read information from the PeopleSoft security tables such as PS_SEC_BU_OPR, PS_SEC_BU_CLS, PS_SEC_LEDGER_OPR, PS_SEC_LEDGER_CLS.

To set up default Fiscal Calendar for PeopleSoft:

  1. Create an initialization block to get one Business Unit for a user. This could be a GL BU, AP BU, AR BU, and so on. Follow these steps:

    1. Create a new initialization block called Operating Unit for Fiscal Calendar, and populate a variable called OU_ORG_FSCL_CALENDAR.

    2. Use the following SQL for the initialization block:

      SELECT MAX(BUSINESS_UNIT) FROM (
SELECT BUSEC.BUSINESS_UNIT
FROM PS_SEC_BU_OPR BUSEC, PS_INSTALLATION_FS INST
WHERE INST.SECURITY_TYPE = 'O' AND BU_SECURITY = 'Y'AND BUSEC.OPRID = ':USER'
UNION ALL
SELECT BUSEC.BUSINESS_UNIT
FROM PS_SEC_BU_CLS BUSEC, PS_INSTALLATION_FS INST, PSOPRDEFN OPR
WHERE INST.SECURITY_TYPE = 'C' AND BU_SECURITY = 'Y' AND BUSEC.OPRCLASS = OPR.OPRCLASS AND OPR.OPRID = ':USER');

      Note: You need to use the PeopleSoft OLTP Connection Pool for this initialization block.

  2. Create an initialization block to get one GLBU+Ledger combination for a user, because calendar is defined at a GLBU+Ledger combination in PeopleSoft, not on Ledger alone. Follow these steps

    1. Create a new initialization block called Ledger for Fiscal Calendar, and populate a variable called LEDGER_FSCL_CALENDAR.

    2. Use the following SQL for the initialization block:

      Note: Remove the comments in the SQL below (lines beginning with "--") from the actual SQL in the initialization block.

      SELECT MAX(LEDGER_ID_FOR_MCAL) FROM (<insert the appropriate SQL from the 
SQL code below, based on the user level and business filters>.

      -- If security is set at user level with filters on Business Unit and Ledger.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_BU_OPR BUSEC, PS_SEC_LEDGER_OPR LEDSEC, PS_BU_LED_GRP_TBL A,
PS_SET_CNTRL_REC B, PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST
WHERE BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND LEDSEC.LEDGER_GROUP =
A.LEDGER_GROUP AND LEDSEC.LEDGER = C.LEDGER AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND
B.SETID = C.SETID AND
A.LEDGER_GROUP = C.LEDGER_GROUP
AND INST.SECURITY_TYPE = 'O' AND BU_SECURITY = 'Y' AND LEDGER_SECURITY =
'Y' AND BUSEC.OPRID = ':USER' AND LEDSEC.OPRID = ':USER' UNION ALL

      -- If security is set at user level with filters on Business Unit only.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_BU_OPR BUSEC, PS_BU_LED_GRP_TBL A, PS_SET_CNTRL_REC B, PS_
LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST
WHERE BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND
B.SETID = C.SETID AND 
A.LEDGER_GROUP = C.LEDGER_GROUP
AND INST.SECURITY_TYPE = 'O' AND BU_SECURITY = 'Y' AND LEDGER_SECURITY =
'N' AND BUSEC.OPRID = ':USER' UNION ALL

      -- If security is set at user level with filters on Ledger only.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_LEDGER_OPR LEDSEC, PS_BU_LED_GRP_TBL A, PS_SET_CNTRL_REC B, 
PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST
WHERE
LEDSEC.LEDGER_GROUP = A.LEDGER_GROUP AND LEDSEC.LEDGER = C.LEDGER AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND
B.SETID = C.SETID AND
A.LEDGER_GROUP = C.LEDGER_GROUP
AND INST.SECURITY_TYPE = 'O' AND BU_SECURITY = 'N' AND LEDGER_SECURITY =
'Y' AND LEDSEC.OPRID = ':USER' UNION ALL

      -- If security is set at permission list level with filters on Business Unit and Ledger.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_BU_CLS BUSEC, PS_SEC_LEDGER_CLS LEDSEC, PS_BU_LED_GRP_TBL A, 
PS_SET_CNTRL_REC B, PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST, PSOPRDEFN OPR
WHERE BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND LEDSEC.LEDGER_GROUP =
A.LEDGER_GROUP AND LEDSEC.LEDGER = C.LEDGER AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND
B.SETID = C.SETID AND
A.LEDGER_GROUP = C.LEDGER_GROUP 
AND INST.SECURITY_TYPE = 'C' AND BU_SECURITY = 'Y' AND LEDGER_SECURITY =
'Y' AND LEDSEC.OPRCLASS = OPR.OPRCLASS AND BUSEC.OPRCLASS = OPR.OPRCLASS
AND OPR.OPRID = ':USER' UNION ALL

      -- If security is set at permission list level with filters on Business Unit only.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_BU_CLS BUSEC, PS_BU_LED_GRP_TBL A, PS_SET_CNTRL_REC B, PS_
LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST, PSOPRDEFN OPR
WHERE BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND
B.SETID = C.SETID AND
A.LEDGER_GROUP = C.LEDGER_GROUP 
AND INST.SECURITY_TYPE = 'C' AND BU_SECURITY = 'Y' AND LEDGER_SECURITY =
'N' AND BUSEC.OPRCLASS = OPR.OPRCLASS AND OPR.OPRID = ':USER' UNION ALL

      -- If security is set at permission list level with filters on Ledger only.

      SELECT A.BUSINESS_UNIT||'~'||C.SETID||'~'||C.LEDGER LEDGER_ID_FOR_MCAL
FROM PS_SEC_LEDGER_CLS LEDSEC, PS_BU_LED_GRP_TBL A, PS_SET_CNTRL_REC B, 
PS_LED_GRP_LED_TBL C, PS_INSTALLATION_FS INST, PSOPRDEFN OPR
WHERE LEDSEC.LEDGER_GROUP = A.LEDGER_GROUP AND LEDSEC.LEDGER = C.LEDGER AND
A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'LED_GRP_LED_TBL' AND
B.SETID = C.SETID AND
A.LEDGER_GROUP = C.LEDGER_GROUP
AND INST.SECURITY_TYPE = 'C' AND BU_SECURITY = 'N' AND LEDGER_SECURITY =
'Y' AND LEDSEC.OPRCLASS = OPR.OPRCLASS AND OPR.OPRID = ':USER');

      Note: You need to use the PeopleSoft OLTP Connection Pool for this initialization block.

  3. Modify the appropriate initialization block to get a default Calendar for a user, as follows:

    1. Open the initialization block "Mcal Calendar Wid Identification."

      The default SQL code is:

      select max(MCAL_CAL_WID) from VALUEOF(OLAPTBO).W_MCAL_CONTEXT_G where
((ledger_id=valueof(NQ_SESSION.LEDGER)) or (org_id=valueof(NQ_SESSION.OU_ORG)')) AND CLASS='GL'

    2. Replace NQ_SESSION.LEDGER with NQ_SESSION.LEDGER_FSCL_CALENDAR and NQ_SESSION.OU_ORG with NQ_SESSION.OU_ORG_FSCL_CALENDAR.

    3. Edit the Execution Precedence, and remove the existing two initialization blocks (Ledgers and Operating Unit Organizations) that are currently set.

    4. Add the two new initialization blocks you created in steps 1 and 2.

      Note: If Security is completely turned OFF in PeopleSoft, you can disable all these initialization blocks.

5.3.3.5 How to Enable Project Analytics Integration with Financial Subject Areas

You can enable Oracle Financial Analytics for PeopleSoft to use dimension tables in Oracle Project Analytics. You can only perform this integration if you have licensed Oracle Project Analytics.

To enable the integration:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Select each Subject Area, and then in the Configuration Tags tab, clear the Inactive check box for the Enable Project Dimensions configuration tag.

  3. Assemble the Subject Areas.

  4. Click the Execute button and build the Execution Plan for the Subject Areas that you updated.

5.3.3.6 Configuring Number of Days based Metrics for PeopleSoft

For certain metrics to function properly, you must configure the following two internal metrics in the Oracle BI Applications metadata repository (RPD):

  • # of Elapsed Days

  • # of Cumulative Elapsed Days

These metrics affect the calculation of other metrics, such as Days Sales Outstanding, Days Payables Outstanding, AP Turnover, AR Turnover, and so forth.

To configure Number of Days based metrics:

  1. Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

    The OracleBIAnalyticsApps.rpd file is located at:

    ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository

  2. In the Business Model and Mapping layer, go the logical table Fact - Fins - Period Days Count.

  3. Under Sources, select the Fact_W_DAY_D_PSFT logical table source.

  4. Clear the Disabled option in the General tab and click OK.

  5. Open the other two logical table sources, Fact_W_DAY_D_ORA and Fact_W_DAY_D_PSFT, and select the Disabled option.

  6. Add the "Fact - Fins - Period Days Count" and "Dim - Company" logical tables to the Business Model Diagram. To do so, right-click the objects and select Business Model Diagram, Selected Tables Only.

  7. In the Business Model Diagram, create a new logical join from "Dim - Company" to "Fact - Fins - Period Days Count." The direction of the foreign key should be from the "Dim - Company" logical table to the "Fact - Fins - Period Days Count" table. For example, on a (0,1):N cardinality join, "Dim - Company" will be on the (0/1) side and "Fact - Fins - Period Days Count" will be on the N side.

  8. Under the Fact - Fins - Period Days Count logical table, open the "# of Elapsed Days" and "# of Cumulative Elapsed Days" metrics, one at a time.

  9. Go to the Levels tab. For the Company dimension, the Logical Level is set to All. Click the X button to remove it. Repeat until the Company dimension does not have a Logical Level setting.

  10. Make sure to check Global Consistency to ensure there are no errors, and then save the RPD file.

5.3.3.7 How to Update Dashboard Pages with PeopleSoft Prompts

Data Source specific dashboard prompts are provided with Financial Analytics to accommodate source specific filtering across all application Dashboard pages. You need to add each PeopleSoft dashboard prompt listed in Table 5-17 to its associated dashboard page as part of the application configuration process.

Table 5-17 Financial Analytics Dashboard Pages with Pre-configured PeopleSoft Path and Prompt Names

Dashboard Dashboard Page Catalog Shared Folder (Shared Folders/Financials) PeopleSoft Prompt Name

General Ledger

Overview

/General Ledger/Key Ratios

Oracle PSFT - GL Key Ratios Prompt

General Ledger

Balance Sheet

/General Ledger/Balance Sheet

Oracle PSFT - GL Balance Sheet Prompt

General Ledger

Cash Flow

/General Ledger/Cash Flow

Oracle PSFT - GL Cash Flow Prompt

General Ledger

Budget vs. Actual

/General Ledger/Budget Actual

Oracle PSFT - GL Budget Prompt

General Ledger

Asset Usage

/General Ledger/Asset Usage

Oracle PSFT - GL Asset Usage Prompt

General Ledger

Liquidity

/General Ledger/Liquidity

Oracle PSFT - GL Liquidity Prompt

General Ledger

Financial Structure

/General Ledger/Financial Structure

Oracle PSFT - GL Financial Structure Prompt

General Ledger

GL Balance

/General Ledger/Transactions

Oracle PSFT - GL Balance Transactions Prompt

General Ledger

Trial Balance

/General Ledger/Trial Balance

Oracle PSFT - GL Trial Balance Prompt

Payables

Overview

/Payables/Overview

Oracle PSFT - AP Overview Prompt

Payables

AP Balance

/Payables/AP Balance

Oracle PSFT - AP Balance Prompt

Payables

Payments Due

/Payables/Payments Due

Oracle PSFT - AP Payments Due Prompt

Payables

Effectiveness

/Payables/Effectiveness

Oracle PSFT - AP Effectiveness Prompt

Payables

Payment Performance

/Payables/Payment Performance

Oracle PSFT - AP Payment Performance Prompt

Payables

Supplier Report

/Payables/Supplier Report

Oracle PSFT - AP Supplier Report Prompt

Payables

Invoice Details

/Payables/Invoice Details

Oracle PSFT - AP Invoice Details Prompt

Payables

All AP Transactions

/Payables/All AP Transactions

Oracle PSFT - AP Txn Prompt

Profitability

Overview

/Profitability/Overview

Oracle PSFT - GL Profitability Overview Prompt

Profitability

P&L

/Profitability/P&L

Oracle PSFT - GL Profitability P&L Prompt

Profitability

Margins

/Profitability/Margins

Oracle PSFT - GL Profitability Margins Prompt

Profitability

Revenue

/Profitability/Revenue

Oracle PSFT - GL Profitability Revenue Prompt

Profitability

Products

/Profitability/Products

Oracle PSFT - GL Profitability Products Prompt

Profitability

Customers

/Profitability/Customers

Oracle PSFT - GL Profitability Customer Prompt

Receivables

Overview

/Receivables/Overview

Oracle PSFT - AR Overview Prompt

Receivables

AR Balance

/Receivables/AR Balance

Oracle PSFT - AR Balance Prompt

Receivables

Payments Due

/Receivables/Payments Due

Oracle PSFT - AR Payments Due Prompt

Receivables

Effectiveness

/Receivables/Effectiveness

Oracle PSFT - AR Effectiveness Prompt

Receivables

Payment Performance

/Receivables/Payment Performance

Oracle PSFT - AR Payment Performance Prompt

Receivables

Customer Report

/Receivables/Supplier Report

Oracle PSFT - AR Supplier Report Prompt

Receivables

Invoice Details

/Receivables/Invoice Details

Oracle PSFT - AR Invoice Details Prompt

Receivables

All AR Transactions

/Receivables/All AR Transactions

Oracle PSFT - AR Transactions Prompt

To update dashboard pages with PeopleSoft prompts:

These instructions explain how to modify the General Ledger dashboard's Overview page prompt as an example of how to modify a prompt.

  1. Access the dashboard page.

  2. Click the Page Options button and then select Edit Dashboard to launch Dashboard Editor.

  3. Remove the existing dashboard prompt from the top section in Dashboard Editor.

    For the Overview page in the General Ledger dashboard, remove the "Oracle EBS - GL Key Ratios Prompt" from Section 1.

    Note:

    Remove the prompt, not the Section.

  4. From the selection pane in the Saved Content area, browse to the Shared Folders where the dashboard prompt to be used for this dashboard page is stored.

    For the Overview page in the General Ledger dashboard, the catalog path is stored in the following location:

    /Shared folders/Financials/General Ledger/Key Ratios Prompt name:
Oracle PSFT - GL Key Ratios Prompt

  5. Drag and drop the dashboard prompt from the shared folder into the section where you removed the prompt in step 3.

  6. Click the Save button to save the dashboard page and exit Dashboard Editor.

    This updates the dashboard page with the PeopleSoft prompt.

  7. Repeat these steps for all Financial Analytics dashboard pages listed in Table 5-17.

5.3.4 Configuration Steps for Financial Analytics for Oracle's JD Edwards EnterpriseOne and JD Edwards World

This section contains additional configuration steps that apply to Oracle's JD Edwards EnterpriseOne and JD Edwards World. It contains the following topics:

5.3.4.1 Configuring GL Balances in Oracle's JD Edwards EnterpriseOne and JD Edwards World

For JD Edwards EnterpriseOne and JD Edwards World GL balances, only Actual Ledger Type is supported. Using a mapplet parameter, you can identify and pass multiple Actual Ledger Types, if required. For Ledger Types AA, A1, and A2, you run the ETL map by passing the value through DAC as AA, A1, A2 (comma separated value) for $$LEDGER_TYPE_ACTUAL. By default, the value for Ledger Type is AA.

W_GL_BALANCE_F supports only Actual ledgers; ledgers with foreign transactions should not be included.

To configure multiple Actual Ledger Types in DAC:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Display the Tasks tab, and perform a query for the task SDE_JDE_GLBalanceFact.

  3. In the Detail area of the selected task, display the Parameters tab. Modify the value in the $$LEDGER_TYPE_ACTUAL parameter, for example:

    $$LEDGER_TYPE_ACTUAL= 'AA', 'A1', 'A2'

  4. Save your changes.

5.3.4.2 Configuring Ledger Type

Oracle BI Applications extracts JD Edwards EnterpriseOne 'Ledger Type' and maps it into the Ledger Dimension (W_LEDGER_D). This mapping provides:

  • The ability to filter or segregate data by Ledger Type for reporting and analysis

  • Support for consolidation ('AC') and restatement ledgers ('XA'), in addition to the Actuals and Budget ledgers

Table 5-18 lists the supported and unsupported ledger types.

Table 5-18 Supported and Unsupported Ledgers

Supported Ledgers Ledgers Not Supported

Actuals, such as 'AA' and 'A1'

Transaction currency - 'CA'

Budget, such as 'BA' and 'B1'

Units - '*U'

Consolidation - 'AC'

Percent of Job complete - 'F%'

Restatement, such as 'XA' and 'YA'

-

Table 5-19 provides an example of how records are populated into the Ledger Dimension. Note that LEDGER_NAME contains the concatenation of Company and Ledger Type. There will be as many entries for company as there are Ledger Types. For example, assuming there are two Ledger Types (AA and A1) and two Companies (00001 and 00050), LEDGER_NAME will contain the four rows shown in the table once the load process is complete.

Table 5-19 Example of Ledger Dimension Table with Support for Ledger Type

LEDGER_NAME LEDGER_DESC CURRENCY_CODE LEDGER_CATEGORY_CODE LEDGER_CATEGORY_NAME

00001~AA

Fixed Asset Company #2

USD

AA

Actuals

00001~A1

Fixed Asset Company #2

USD

A1

User Defined Actuals

00050~AA

SAR TESTING COMPANY

USD

AA

Actuals

00050~A1

SAR TESTING COMPANY

USD

A1

User Defined Actuals

The ETL map is run by passing the Ledger Type value through DAC as comma separated values. By default, the value for Ledger Type is AA.

To configure support for consolidation and restatement ledgers in DAC:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Display the Tasks tab, and perform a query for the task SDE_JDE_GLBalanceFact.

  3. Display the Parameters subtab, and modify the value in the $$LEDGER_TYPE parameter. For example:

    $$LEDGER_TYPE= 'AA', 'XA', 'AC'

  4. Save your changes.

5.3.4.3 Configuring the DAC Parameter for Receivables

The Transaction Type Code dimension table contains a UDC column for UDCs, which are user defined codes in the JD Edwards EnterpriseOne source system. By default, the UDC is set to "RA" in the dimension table. You can override the value of the $$WRITE_OFF_DOC_TYPE DAC parameter to specify any UDC in the source system.

If you configure the DAC parameter to any other value available in the JD Edwards EnterpriseOne source system, then you must open the domainvalues_src_transaction_type_codes_jde.csv file and modify the UDC column values to reflect the UDC that you entered in the DAC parameter.

The following table is an example of two entries in the CSV file in which you would replace RA in the UDC column with the user defined code specified in the DAC parameter.

DRSY DRRT UDC RECEIPT_TYPE XACT_CAT_CODE XACT_CODE W_XACT_TYPE_CODE W_XACT_SUBTYPE_CODE
0 DT RA   ACCT_DOC RECEIVABLE APPLICATION ADJUSTMENT APPLICATION
0 DT RA A ACCT_DOC RECEIVABLE APPLICATION ADJUSTMENT APPLICATION

To configure the $$WRITE_OFF_DOC_TYPE DAC parameter:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Select the Financials - Receivables row.

  3. Click Tasks in the Design area on the left side of the screen, and then highlight the SDE_JDE_AR_Transaction_Fact_Extract task.

  4. Display the Parameters sub-tab.

  5. Select the row with the $$WRITE_OFF_DOC_TYPE parameter, and then double-click in the Value column.

  6. In the Enter Parameter Value screen, replace 'RA' with a UDC in the source system. Ensure that the UDC is in single quotes.

  7. Open the domainvalues_src_transaction_type_codes_jde.csv file and modify the UDC column values to reflect the UDC that you entered in the DAC parameter.

5.3.4.4 Configuring the DAC Parameter when Multicurrency Processing is Disabled in JD Edwards

LOC_CURR_CODE and ACCT_CURR_CODE in the Data Warehouse are non-nullable fields. Unless they are populated, insertion into the Data Warehouse will fail. If multicurrency processing is disabled in JD Edwards EnterpriseOne or JD Edwards World, then you must set the $$DOC_CURR_CODE parameter in DAC to the domestic or base currency in use. By default, the value of this parameter is set to NULL in DAC.

To configure the $$DOC_CURR_CODE parameter:

  1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

  2. Select the Source System Parameters tab.

  3. Select the row with the $$DOC_CURR_CODE parameter, and then double-click in the Value column.

  4. In the Enter Parameter Value screen, replace NULL with the domestic or base currency in use.

5.3.4.5 Configuring Number of Days based Metrics

For certain metrics to function properly, you must configure the following two internal metrics in the Oracle BI Applications metadata repository (RPD):

  • # of Elapsed Days

  • # of Cumulative Elapsed Days

These metrics affect the calculation of other metrics, such as Days Sales Outstanding, Days Payables Outstanding, AP Turnover, AR Turnover, and so forth.

To configure Number of Days based metrics:

  1. Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

    The OracleBIAnalyticsApps.rpd file is located at:

    ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository

  2. In the Business Model and Mapping layer, go the logical table Fact - Fins - Period Days Count.

  3. Under Sources, select the Fact_W_DAY_D_JDE logical table source.

  4. Clear the Disabled option in the General tab and click OK.

  5. Open the other two logical table sources, Fact_W_DAY_D_ORA and Fact_W_DAY_D_PSFT, and select the Disabled option.

  6. Add the "Fact - Fins - Period Days Count" and "Dim - Company" logical tables to the Business Model Diagram. To do so, right-click the objects and select Business Model Diagram, Selected Tables Only.

  7. In the Logical Table diagram, create a new logical join from "Dim - Company" to "Fact - Fins - Period Days Count." The direction of the foreign key should be from the "Dim - Company" logical table to the "Fact - Fins - Period Days Count" table. For example, on a (0,1):N cardinality join, "Dim - Company" will be on the (0/1) side and "Fact - Fins - Period Days Count" will be on the N side.

  8. Similarly, in the Business Model Diagram, add the following logical tables:

    • Fact - Fins - Period Days Count

    • Dim - Ledger

    • Dim - Receivables Org

    • Dim - Payables Org

  9. Click the joins between the three Logical Dimension table joins and the Logical Fact table and delete them.

  10. Under the Fact - Fins - Period Days Count logical table, open the "# of Elapsed Days" and "# of Cumulative Elapsed Days" metrics, one at a time.

  11. Go to the Levels tab. For the Company dimension, the Logical Level is set to All. Click the X button to remove it. Repeat until the Company dimension does not have a Logical Level setting.

  12. Set the Logical Level to All for these three dimensions:

    • Ledger

    • Payables Org

    • Receivables Org

  13. Make sure to check Global Consistency to ensure there are no errors, and then save the RPD file.

5.3.4.6 How to Update Dashboard Pages with Oracle's JD Edwards EnterpriseOne and JD Edwards World Prompts

Data-source specific dashboard prompts are provided with Oracle Financial Analytics to accommodate source specific filtering across all application Dashboard pages. You need to add each JD Edwards EnterpriseOne or JD Edwards World dashboard prompt listed in Table 5-20 to its associated dashboard page as part of the application configuration process. Follow the steps in this section to update existing dashboard prompts with the designated JD Edwards EnterpriseOne or JD Edwards World prompt.

Table 5-20 Financial Analytics Dashboard Pages with Preconfigured JD Edwards EnterpriseOne or JD Edwards World Path and Prompt Names

Dashboard Dashboard Page Catalog Shared Folder (Shared Folders/Financials) JD Edwards EnterpriseOne/JD Edwards World Prompt Name

General Ledger

Overview

/General Ledger/Key Ratios

Oracle JDE - GL Key Ratios Prompt

General Ledger

Balance Sheet

/General Ledger/Balance Sheets

Oracle JDE - GL Balance Sheet Prompt

General Ledger

Cash Flow

/General Ledger/Cash Flow

Oracle JDE - GL Cash Flow Prompt

General Ledger

Budget vs. Actual

/General Ledger/Budget Actual

Oracle JDE - GL Budget Prompt

General Ledger

Asset Usage

/General Ledger/Asset Usage

Oracle JDE - GL Asset Usage Prompt

General Ledger

Liquidity

/General Ledger/Liquidity

Oracle JDE - GL Liquidity Prompt

General Ledger

Financial Structure

/General Ledger/Financial Structure

Oracle JDE - GL Financial Structure Prompt

General Ledger

GL Balance

/General Ledger/Transactions

Oracle JDE - GL Balance Transactions Prompt

General Ledger

Trial Balance

/General Ledger/Trial Balance

Oracle JDE - GL Trial Balance Prompt

Payables

Overview

/Payables/Overview

Oracle JDE - AP Overview Prompt

Payables

AP Balance

/Payables/AP Balance

Oracle JDE - AP Balance Prompt

Payables

Payments Due

/Payables/Payments Due

Oracle JDE - AP Payments Due Prompt

Payables

Effectiveness

/Payables/Effectiveness

Oracle JDE - AP Effectiveness Prompt

Payables

Payment Performance

/Payables/Payment Performance

Oracle JDE - AP Payment Performance Prompt

Payables

Supplier Report

/Payables/Supplier Report

Oracle JDE - AP Supplier Report Prompt

Payables

Invoice Details

/Payables/Invoice Details

Oracle JDE - AP Invoice Details Prompt

Payables

All AP Transactions

/Payables/All AP Transactions

Oracle JDE - AP Txn Prompt

Profitability

Overview

/Profitability/Overview

Oracle JDE - GL Profitability Overview Prompt

Profitability

P&L

/Profitability/P&L

Oracle JDE - GL Profitability P&L Prompt

Profitability

Margins

/Profitability/Margins

Oracle JDE - GL Profitability Margins Prompt

Profitability

Revenue

/Profitability/Revenue

Oracle JDE - GL Profitability Revenue Prompt

Profitability

Products

/Profitability/Products

Oracle JDE - GL Profitability Products Prompt

Profitability

Customers

/Profitability/Customers

Oracle JDE - GL Profitability Customers Prompt

Receivables

Overview

/Receivables/Overview

Oracle JDE - AR Overview Prompt

Receivables

AP Balance

/Receivables/AP Balance

Oracle JDE - AR Balance Prompt

Receivables

Payments Due

/Receivables/Payments Due

Oracle JDE - AR Payments Due Prompt

Receivables

Effectiveness

/Receivables/Effectiveness

Oracle JDE - AR Effectiveness Prompt

Receivables

Payment Performance

/Receivables/Payment Performance

Oracle JDE - AR Payment Performance Prompt

Receivables

Supplier Report

/Receivables/Supplier Report

Oracle JDE - AR Supplier Report Prompt

Receivables

Invoice Details

/Receivables/Invoice Details

Oracle JDE - AR Invoice Details Prompt

Receivables

All AR Transactions

/Receivables/All AR Transactions

Oracle JDE - AR Transaction Prompts

To update dashboard pages with JD Edwards EnterpriseOne or JD Edwards World prompts:

These instructions explain how to modify the General Ledger dashboard's Overview page prompt as an example of how to modify a prompt.

  1. Access the dashboard page.

  2. Click the Page Options button and then select Edit Dashboard to launch Dashboard Editor.

  3. Remove the existing dashboard prompt from the top section in Dashboard Editor.

    For the Overview page in the General Ledger dashboard, remove the "Oracle EBS - GL Key Ratios Prompt" from Section 1.

    Note:

    Remove the prompt, not the Section.

  4. From the selection pane in the Saved Content area, browse to the Shared Folders where the dashboard prompt to be used for this dashboard page is stored.

    For the Overview page in the General Ledger dashboard, the catalog path is stored in the following location:

    /Shared folders/Financials/General Ledger/Key Ratios Prompt name:
Oracle JDE - GL Key Ratios Prompt

  5. Drag and drop the dashboard prompt from the shared folder into the section where you removed the prompt in step 3.

  6. Click the Save button to save the dashboard page and exit Dashboard Editor.

    This updates the dashboard page with the JD Edwards EnterpriseOne or JD Edwards World prompt.

  7. Repeat these steps for all Financial Analytics dashboard pages listed in Table 5-20.

5.3.4.6.1 Additional Dashboard Page Configuration for Ledger Dimension Columns

Dashboard prompts defined during the Financial Analytics configuration process will not require any additional changes because Ledger Name will display the new concatenated Ledger Name ("Company~Ledger Type") values.

LEDGER_CATEGORY_NAME and LEDGER_CATEGORY_CODE can be exposed as Dashboard prompts with a few modifications to the original product configuration:

  • LEDGER_CATEGORY_NAME is already available in the Presentation Subject Area as part of the Ledger Dimension. Therefore, all that is needed is to define LEDGER_CATEGORY_NAME as a prompt for the required Dashboard page or pages.

  • LEDGER_CATEGORY_CODE is available in the RPD Business Model and Mapping (BMM) layer. Therefore, before defining as a Dashboard Page prompt, you will need to expose within the RPD Presentation Subject Area, as a member of the Ledger Dimension.

Table 5-21 summarizes the availability of each column across each area after the standard product configuration, and lists the associated action needed to define as a Dashboard page prompt.

Table 5-21 How to Define Ledger Dimension Columns as Dashboard Page Prompts

Ledger Dimension Column Dashboard Prompt Presentation Subject Area RPD BMM Layer Action to Define as Dashboard Page Prompt

LEDGER_NAME

Yes

Yes

Yes

None. Already available as a Dashboard prompt.

LEDGER_CATEGORY_NAME

No

Yes

Yes

Define as prompt in Dashboard Page.

LEDGER_CATEGORY_CODE

No

No

Yes

  1. Expose in Presentation Subject Area.

  2. Define as prompt in Dashboard Page.

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us