| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Version 7.9.6.1 Part Number E14844-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Version 7.9.6.1 Part Number E14844-01 |
|
|
View PDF |
This section contains mandatory and additional configuration tasks that apply to Oracle BI Applications deployed with any source system, as well as mandatory and additional tasks that are specific to the various source systems.
To configure Oracle BI Applications, you first need to perform the steps in Section 3.1, "Source-Independent Configuration Steps."
Note:
Many configuration tasks described in this guide require you to manually enter values obtained from querying your source system (for example, Oracle E-Business Suite). These values are unique to your source system and implementation. To retrieve these values successfully, you need to have a good technical understanding of your source system. If you need assistance in obtaining values from your source system, you should consult with someone in your organization who possesses this knowledge, or consult the Oracle Support Services team for your source system. Make sure to check your configuration entries carefully to avoid data loss during the ETL process.You then need to perform the tasks in one of the following sections depending on the type of your source system:
This section contains configuration steps that apply to Oracle BI Applications deployed with any source system. It contains the following topics:
Initial Extract Date is required when you extract data for a full load. It reduces the volume of data in the initial load. The specified initial extract date will be used as a filter on the creation date of the transactional data in the selected full extract mapping. The default date is January 01, 1970.
When you set the Initial Extract Date parameter, make sure that you set it to the beginning of an accounting period and not a date in the middle of an accounting period. For example, if you decide to extract data from June 2005, and the June 2005 accounting period starts on June 5, set the date to June 5, 2005.
To configure the initial extract date
In DAC, display the Design view, and select your custom container from the drop-down list to the right of the Execute button.
Display the Source System Parameters tab.
Edit the value of the $$INITIAL_EXTRACT_DATE parameter.
Save your changes.
Currency conversions are required because your business might have transactions involving multiple currencies. To create a meaningful report, you have to use a common currency. The Oracle Business Analytics Warehouse stores amounts in the following currencies:
Document currency. The document currency is the currency of the transaction. For example, if you purchase a chair from a supplier in Mexico, the document currency is probably the Mexican peso. Or, if you made a business trip to the United Kingdom and filed an expense report for meal expenses in the UK, the document currency of the expense report will most likely be in GBP.
Local currency. The local currency is the base currency of your ledger, or the currency in which your accounting entries are recorded in.
Global currencies. Oracle BI Applications provides three global currencies, which are the common currencies used by the Oracle Business Analytics Warehouse. For example, if your organization is a multinational enterprise that has its headquarters in the United States, you probably want to choose US dollars (USD) as one of the three global currencies.
The global currency is useful when creating enterprise-wide reports. For example, a user might want to view enterprise-wide data in other currencies. For every monetary amount extracted from the source, the load mapping loads the document and local amounts into the target table. It also loads the exchange rates required to convert the document amount into each of the three global currencies. For fact tables, there are two amount columns covering the Local currency amount and the Document currency amount. In addition, there are three columns covering the Global currency (for example, global _amount1) and their corresponding exchange rate columns.
In most cases, the source system provides the document currency amount. This is the most common situation, and, thus, is the Oracle Business Analytics Warehouse's default for handling currency. If the source system provides only the document currency amount, the source adapter performs lookups to identify the local currency codes based on the source system the appropriate currencies are assigned. After the lookups occur, the extract mapping provides the load mapping with the document currency amount and the document and local currency codes. The load mapping will then use the provided local currency codes and perform currency conversion to derive the local amount. The load mapping will also fetch the global currencies setup from DAC parameters and look up the corresponding exchange rates to each of the three global currencies.
To configure the global currencies you want to report
In DAC, display the Design view.
Select your custom container from the drop-down list to the right of the Execute button.
Display the Source System Parameters tab.
Locate the following parameters, and set the currency code values for them in the Value field:
$$GLOBAL1_CURR_CODE (for the document currency).
$$GLOBAL2_CURR_CODE (for the local currency).
$$GLOBAL3_CURR_CODE (for the global currency).
You can specify any currency, provided an exchange rate is specified for the conversion to the local currency. Make sure that you spell the currencies as they are spelled in your source OLTP system.
Save your changes.
When Oracle BI Applications converts your transaction records' amount from document currency to global currencies, it also requires the exchange rate types to use to perform the conversion. For each of the global currencies, Oracle BI Applications also allows you to specify the exchange rate type to use to perform the conversion. Oracle BI Applications also provides three global exchange rate types for you to configure.
Oracle BI Applications also converts your transaction records' amount from document currency to local currency. Local currencies are the base currencies in which your accounting entries and accounting reports are recorded. In order to perform this conversion, Oracle BI Applications also allows you to configure the rate type that you want to use when converting the document currency to the local currency.
To configure exchange rate types
In DAC, select your custom container from the drop-down list to the right of the Execute button.
Display the Source System Parameters tab.
Locate the following DAC parameters and set the exchange rate type values for them in the Value field:
$$GLOBAL1_RATE_TYPE
$$GLOBAL2_RATE_TYPE
$$GLOBAL3_RATE_TYPE
$$DEFAULT_LOC_RATE_TYPE (the conversion rate type for document currency to local currency conversion).
Make sure you spell the exchange rate type values as they are spelled in your source OLTP system.
Save your changes
This section explains how to set up the different types of calendar that are supported by Oracle Business Intelligence Applications, and contains the following topics:
Section 3.1.4.1, "Overview of Calendars in Oracle BI Applications"
Section 3.1.4.4, "How to set up the Gregorian Calendar Date Range"
Section 3.1.4.5, "How to Configure PeopleSoft Summary Calendars"
Section 3.1.4.6, "How to Include and Exclude Multiple Calendar Support for Subject Areas in DAC"
Section 3.1.4.4, "How to set up the Gregorian Calendar Date Range"
Section 3.1.4.7, "How to set up an enterprise calendar using an Oracle EBS source system"
Section 3.1.4.8, "How to set up an enterprise calendar using a PeopleSoft source system"
Section 3.1.4.10, "How to set up a World calendar using an Oracle JD Edwards World source system"
Section 3.1.4.13, "How to use a fiscal calendar loaded via the Universal adapter"
Section 3.1.4.14, "How to use a PeopleSoft Summary calendar"
Section 3.1.4.15, "Examples of Configuring the Universal Adapter"
Section 3.1.4.16, "How to Reload the Time Dimension Tables After the Data Warehouse Is Loaded"
Oracle Business Intelligence Applications Version 7.9.6.1 supports the following calendar formats:
Enterprise (Global) - cross functional reporting calendar, which can be fiscal or gregorian.
Fiscal - accounting or financial calendar.
Gregorian - regular calendar that starts on January 1st and ends on December 31st.
13 Period - each year is comprised of 13 periods of 28 days.
4-4-5 - each year is composed of twelve periods of either four weeks of 28 days or five weeks of 35 days.
If you want to deploy multi-calendar (for example, with Oracle Financial Analytics), you must have the following Initialization Blocks enabled:
EBS Single Sign-on Integration
EBS Security Context
Ledgers
Operating Unit Organizations
These Initialization Blocks are not enabled out-of-the-box. If you try to load data without enabling these Initialization Blocks, dashboards will not be populated with data, and the BI Server log might show Init Block errors (for example, nQSError:43059).
To enable the Initialization Blocks listed above:
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
Choose Manage, then Variables to display the Variable Manager.
In the left-hand pane, select Session, then Initialization Blocks.
For each Initialization Block in the above list, right-click on the Initialization Block name in the right-hand pane, then select Enable.
Restart the BI server.
This section describes the tables used for Time Dimension calendars (including Gregorian calendar, Fiscal calendar, and Enterprise Calendar).
Gregorian Calendar Tables
W_WEEK_D
W_MONTH_D
W_QTR_D
W_YEAR_D
W_DAY_D
Fiscal Calendar Tables
W_MCAL_DAY_D
W_MCAL_WEEK_D
W_MCAL_PERIOD_D
W_MCAL_QTR_D
W_MCAL_YEAR_D
Enterprise Calendar Tables
W_ENT_WEEK_D
W_ENT_PERIOD_D
W_ENT_QTR_D
W_ENT_YEAR_D
The following table shows Time Dimension Configuration and Context Tables.
Table 3-1 Time Dimension Configuration and Context Tables
| Configuration Table | PeopleSoft Specific Configuration Table | Context Table |
|---|---|---|
|
W_MCAL_CONFIG_G |
W_MCAL_PSFT_SUMP_CONFIG_G |
W_MCAL_CONTEXT_G |
For more information about the configuration tables and context tables, see Section 3.1.4.2, "About Configuring Calendars".
Note: The following tables are obsolete in Oracle Business Intelligence Applications Version 7.9.6 and Version 7.9.6.1:
W_FSCL_WEEK_D
W_FSCL_MONTH_D
W_FSCL_QTR_D
W_FSCL_YEAR_D
Please note that the tables listed above were populated via CSV files in prior releases. Please refer to the upgrade documentation if you need to migrate data in these tables into the new fiscal calendar tables.
Calendars are categorized into two types:
OLTP sourced (also known as Source Calendars)
OLTP sourced calendars are calendars that are defined in ERP sources and brought into the warehouse via ETL maps.
Warehouse generated (also known as Generated Calendars)
Generated calendars are fiscal calendars generated in the warehouse based on configuration files.
Both source calendars and generated calendars are stored in the Multiple Fiscal Calendar (known as MCAL) tables. MCAL tables have the prefix W_MCAL.
This section explains how to configure the different types of supported calendar.
Whatever calendar type you want to deploy, you must set up the start date and end date range for the Gregorian calendar (for more information, see Section 3.1.4.4, "How to set up the Gregorian Calendar Date Range").
W_DAY_D is the base table that represents the time dimension in the Oracle Business Analytics Warehouse. This table needs to be populated as a prerequisite for the multiple fiscal calendar tables. If W_DAY_D is not populated, then the fiscal calendar tables will not be populated.
There are two parameters $$START_DATE and $$END_DATE for the task SIL_DayDimension that need to be setup to load the calendar data in W_DAY_D. The SIL mappings use standard time functions to create records for each calendar day falling within the boundary defined by these two parameters. Once the records are created in W_DAY_D, the aggregate calendar tables are loaded by their respective SIL mapping. Then the fiscal calendar tables (known as MCAL tables) are populated.
Note: The parameters $$START_DATE and $$END_DATE need to include all dates covered by any of the fiscal calendars brought into the warehouse. These parameters are the boundaries for the date dimension and related tables.
An Enterprise calendar (or reporting calendar) enables cross Subject Area analysis. Enterprise calendar tables have W_ENT prefix.
Enterprise calendars can be set to one of the OLTP sourced fiscal calendars or to one of the warehouse generated calendars. This can be done by setting the following source system parameters at the DAC container level:
$$GBL_CALENDAR_ID
$$GBL_DATSOURCE_NUM_ID
The following sections show how to setup the source system parameters for the Enterprise calendar in different scenarios, as follows:
"Scenario 1 Using an Oracle EBS fiscal calendar as the Enterprise calendar"
"Scenario 2 Using a PeopleSoft fiscal calendar as the Enterprise calendar"
"Scenario 3 Using a warehouse generated calendar as the Enterprise calendar"
"Scenario 4 Using a fiscal calendar loaded via the Universal Adapter as the Enterprise calendar"
"Scenario 5 Using an Oracle JD Edwards EnterpriseOne fiscal calendar as the Enterprise calendar"
"Scenario 6 Using an Oracle JD Edwards World fiscal calendar as the Enterprise calendar"
Scenario 1 Using an Oracle EBS fiscal calendar as the Enterprise calendar
Source System DAC Parameters for Oracle EBS Enterprise Calendars:
GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. It should be the MCAL_CAL_NAME~MCAL_PERIOD_TYPE for Non-Generated Calendars. For example GBL_CALENDAR_ID will be 'Accounting~41', if the Enterprise Calendar id='Accounting' and the calendar period_type='41'.
GBL_DATASOURCE_NUM_ID: If Enterprise Calendar is not a Generated Calendar: It should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. For example, if you have two data sources as PeopleSoft and Oracle, and the Global Calendar is from an Oracle data source, then this parameter value should specify an Oracle data source.
Scenario 2 Using a PeopleSoft fiscal calendar as the Enterprise calendar
Source System DAC Parameters for PeopleSoft Enterprise Calendars:
GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. It should be the SETID~CALENDAR_ID for Non-Generated Calendars. For example, GBL_CALENDAR_ID will be 'SHARE~01', if the Enterprise Calendar id='01' and SETID='SHARE'.
GBL_DATASOURCE_NUM_ID: If Global Calendar is not a Generated Calendars: It should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. For example, if we have two Data source as PeopleSoft and Oracle, and our Global Calendar is from Oracle source, then parameter value should be of Oracle source.
Scenario 3 Using a warehouse generated calendar as the Enterprise calendar
Source System DAC Parameters for Generated Enterprise Calendars:
GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 period type of Calendars). By default the 4-4-5 calendar has a CALENDAR_ID of '10000' and the 13-period calendar has a CALENDAR_ID of '10001'.
GBL_DATASOURCE_NUM_ID: If Global Calendar is Generated Calendar: It should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse)
Scenario 4 Using a fiscal calendar loaded via the Universal Adapter as the Enterprise calendar
Source System DAC Parameters for Universal Enterprise Calendars:
GBL_CALENDAR_ID: Should be the INTEGRATION_ID from the file_mcal_cal_d.csv file of the particular calendar which is defined as the Global Calendar.
GBL_DATASOURCE_NUM_ID: If Global Calendar is not a Generated Calendars, then it should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. If it is defined in the file_mcal_period_ds.csv file, then that value should be taken, or else as defined in DAC for Universal adapter.
Scenario 5 Using an Oracle JD Edwards EnterpriseOne fiscal calendar as the Enterprise calendar
Source System DAC Parameters for Oracle JD Edwards EnterpriseOne to configure Enterprise Calendars:
GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. It should be MCAL_CAL_ID. For example GBL_CALENDAR_ID will be 'R', if the Enterprise Calendar id='R'.
GBL_DATASOURCE_NUM_ID: If Enterprise Calendar is not a Generated Calendar, this value should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. For example, if you have two data sources as Oracle and Oracle JD Edwards EnterpriseOne, and the Global Calendar is from a Oracle JD Edwards EnterpriseOne data source, then this parameter value should specify an Oracle JD Edwards EnterpriseOne data source.
Scenario 6 Using an Oracle JD Edwards World fiscal calendar as the Enterprise calendar
In Oracle's JD Edwards World, the Enterprise Calendar is referred to as the World Calendar.
Source System DAC Parameters for Oracle JD Edwards World to configure World Calendars:
GBL_CALENDAR_ID: This parameter is used to select the World Calendar. It should be MCAL_CAL_ID. For example GBL_CALENDAR_ID will be 'R', if the World Calendar id='R'.
GBL_DATASOURCE_NUM_ID: If World Calendar is not a Generated Calendar, this value should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. For example, if you have two data sources as Oracle and Oracle JD Edwards World, and the Global Calendar is from an Oracle JD Edwards World data source, then this parameter value should specify an Oracle JD Edwards World data source.
Oracle Business Intelligence Applications Version 7.9.6.1 supports the following types of generated calendars:
13 period calendars.
4-4-5 calendars (and variants).
This table is used by Oracle Financial Analytics and Oracle Project Analytics facts to lookup the calendar ID for a given ledger or OU (Operating Unit). This needs to be populated for the fact tables to get loaded correctly (the default Execution Plan in DAC does this).
In Oracle EBS, the project calendar and Organization (OU) relationship is sourced from PA_IMPLEMENTATIONS_ALL into W_MCAL_CONTEXT_G. The project calendar comes from the column PA_IMPLEMTATIONS_ALL.PERIOD_SET_NAME. The GL calendar used in project analytics comes from the column PA_IMPLENTATIONS_ALL.SET_OF_BOOKS_ID.
When you set up calendars, note the following:
The W_MCAL_CONFIG_G table controls how generated calendars are created.
If generating the 4-4-5 or 13 period calendars, W_MCAL_CONFIG_G needs to have at least one row for the 4-4-5 period or 13 period. There is no entry needed in this table for Oracle EBS or PeopleSoft source calendars.
Note: Oracle's JD Edwards EnterpriseOne and World Adapters for Financial Analytics do not support 4-4-5 period calendars.
W_MCAL_WEEK_D will be populated only for the generated calendars (i.e. 13 period or 4-4-5 type of calendars), and hence the W_DAY_D week Enterprise columns will be null for non-generated calendars (known as OLTP sourced fiscal calendars). W_ENT_WEEK_D will not be populated if a non-generated calendar is chosen as the Enterprise Calendar.
For 13 period calendars, there is no concept of Quarter, therefore all Quarter columns in W_MCAL_WEEK_D, W_MCAL_PERIOD_D, W_MCAL_YEAR_D will be null. W_ENT_QTR_D will not be populated if a 13 period calendar is chosen as the Enterprise Calendar.
The following table outlines columns in the W_MCAL_CONFIG_G table, which is loaded from the file_mcal_config_g.csv.
Table 3-2 Columns in configuration table W_MCAL_CONFIG_G
| Column Name | Column Description |
|---|---|
|
CALENDAR_ID |
The ID of the calendar that is being configured. This is the primary key for this table. |
|
CALENDAR_NAME |
The name of the calendar that is being configured. |
|
CALENDAR_CLASS |
Automatically generated. |
|
PERIOD_TYPE |
The type of the calendar period that is being configured e.g. '4-4-5' |
|
CAL_ST_DT |
The date from which the calendar generation begins. |
|
CAL_END_DT |
The date at which the calendar generation ends. |
|
CAL_OFFSET |
The offset which identifies the start date of the calendar. Valid Start Day and Offset values are:
|
|
WEEK_ALLOCATION_RULE |
This parameter determines how weeks are allocated in the calendar that is being configured. E.g. '4-4-5' or '5-4-4' or '4-5-4' or '13 period'. |
|
Other standard columns |
W_INSERT_DT, W_UPDATE_DT, TENANT_ID, X_CUSTOM etc. |
The following table outlines Task Level DAC Parameters needed for Generated Calendars.
Table 3-3 Task Level DAC Parameters needed for Generated Calendars
| DAC Parameter Name | DAC Parameter Description |
|---|---|
|
$$13P_CALENDAR_ID |
Task: SIL_TimeDimension_MCalWeek13Period. Required if you want to populate the 13 period type of calendar in your Data Warehouse. The value should be the CALENDAR_ID as defined in the W_MCAL_CONFIG_G table for the 13 period type of calendar. |
|
$$445P_CALENDAR_ID |
Task: SIL_TimeDimension_MCalWeek445. Required if you want to populate the 445 period type of calendar in your Data Warehouse. The value should be the CALENDAR_ID as defined in the W_MCAL_CONFIG_G table for the 445 period type of calendar. |
If there is a week (starting on a Sunday and ending on a Saturday) that falls across two calendar years, the week is counted in both years. For example, the week that starts on 12/30/2007 will be counted in both 2007 and 2008. In 2007, the week start date will 12/30/2007 and the end date will be 12/31/2007. In 2008, this will be the first week with start date as 01/01/2008 and end date as 01/05/2008.
W_DAY_D stores 31 records for each month regardless of whether the month actually has 31 days. If the month has a fewer number of days, there will be records with null values in the Calendar Date and Day Date columns. These extra records are loaded for the calculation of Period Ago metrics in the Oracle BI Repository and will not affect the ETL or reporting.
There are some attributes on the W_DAY_D table that are not mapped in the Physical layer of the Oracle BI Repository. Therefore, before creating any new attribute in the repository, check whether the attribute is already available in the Physical layer and if it can be mapped directly.
If your fiscal calendar contains more than 12 months, the extra months will be assigned a value of 0 for the fiscal quarter. The same holds for the fiscal trimester and fiscal half values.
By default, Oracle BI Applications can generate up to 65536 rows. If you need more than 65536 rows, you can increase the capacity to 262144 rows (718 years) by doing the following:
Duplicate 'SIL_DayDimension_GenerateRows7'.
Rename it 'SIL_DayDimension_GenerateRows8'.
Run this immediately after 'SIL_DayDimension_GenerateRows7'.
This task is a pre-requisite for all types of calendar. This task loads the standard Gregorian calendar into the day dimension table W_DAY_D.
To set up the gregorian calendar:
In DAC, display the Design view, and select the appropriate container (for example, 'Custom Oracle R12.1.1').
Display the Tasks tab.
Query for the task SIL_DayDimension.
Display the Parameters tab in the lower pane.
Use the $$START_DATE and $$END_DATE parameters to specify the date range of the calendar that you want to generate.
Note: The tasks that load the day dimension will run as part of the Execution Plan for your Subject Areas. Please note that there are no separate Subject Areas for common dimensions. They are included in the core Subject Areas.
This configuration step needs to be performed only before loading PeopleSoft fiscal calendars. In PeopleSoft, a summary calendar is based on a detail calendar. Many summary calendars can be based off of single detail calendar. The file_summary_calendar.csv configuration file allows the association of a summary calendar to be used to determine fiscal quarters and year for PeopleSoft detail fiscal periods. For every detail calendar, input the summary CALENDAR_ID and SETID in the file file_summary_calendar.csv. The columns in this file are listed in the table below:
Table 3-4 Fields in the file_summary_calendar.csv configuration file
| Column Name | Column Type | Column Description |
|---|---|---|
|
DETAIL_CALENDAR_SETID |
VARCHAR2(5) |
SETID of the Detail Calendar being configured. |
|
DETAIL_CALENDAR_ID |
VARCHAR2(15) |
CALENDAR_ID of the detail calendar being configured. |
|
SUMMARY_CALENDAR_SETID_QTR |
VARCHAR2(5) |
SETID of the Quarterly Summary Calendar specified. |
|
SUMMARY_CALENDAR_QTR |
VARCHAR2(15) |
CALENDAR_ID of the Quarterly Summary Calendar specified. |
|
SUMMARY_CALENDAR_SETID_YEAR |
VARCHAR2(5) |
SETID of the Yearly Summary Calendar specified. |
|
SUMMARY_CALENDAR_YEAR |
VARCHAR2(15) |
CALENDAR_ID of the Quarterly Calendar specified. |
|
SUMMARY_CALENDAR_SETID_MONTH |
VARCHAR2(5) |
SETID of the Monthly Summary Calendar specified. |
|
SUMMARY_CALENDAR_MONTH |
VARCHAR2(15) |
CALENDAR_ID of the Monthly Calendar specified. |
|
SUMMARY_CALENDAR_SETID_HALF |
VARCHAR2(5) |
SETID of the Half Yearly Summary Calendar specified. |
|
SUMMARY_CALENDAR_HALF |
VARCHAR2(15) |
CALENDAR_ID of the Half Yearly Calendar specified. |
|
Other Standard columns |
N/A |
W_INSERT_DT, W_UPDATE_DT, TENANT_ID, X_CUSTOM etc. |
Since a calendar is defined by its SETID and CALENDAR_ID in People Soft, for a particular Detail Calendar, the associated Summary calendar SETID and CALENDAR_ID are added. After updating this flat file file_summary_calendar.csv, run the ETL process. This flat file loads the table W_MCAL_PSFT_SUMP_CONFIG_G, which is used in the mapping that loads W_MCAL_PERIOD_D to resolve the Quarter Number for that Detail Period. You need to make sure the summary calendar specified covers the date range included in the detail period. If this is not the case, then the MCAL_QTR column in W_MCAL_PERIOD_D will be null.
The screenshot below shows example values in a file_summary_calendar.csv file.
The Multi-Calendar tasks are included in the TASK_GROUP_Load_DayDimension in DAC.
When the TASK_GROUP_Load_DayDimension task group is added to any Subject Area, the Multi-Calendar tasks are not extracted by default. You need to add a Configuration tag to the Subject area before you run the ETL process to pull these tasks into the ETL process, as described in the following steps.
To include and exclude Multiple Calendar support for Subject Areas:
In DAC, display the Design view, and select the appropriate container.
Display the Subject Area tab in the upper pane and select the Subject Area to which you want to add Multi-calendar tasks.
Display the Configuration Tags tab in the lower pane.
Click Add/Remove to display the Choose Subject Area Configuration Tags dialog.
Select the configuration tag named 'Multiple Calendar Support' in the left pane and click Add, then OK.
In the upper pane, click Assemble.
Display the Execute view.
Display the Execution Plans tab.
Select the Execution Plan that includes the Subject Area to which you added the configuration tag named 'Multiple Calendar Support'.
Run this Execution Plan.
To set up an enterprise calendar using an Oracle EBS source system:
In DAC, display the Design view, and select an appropriate adapter.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. It should be the MCAL_CAL_NAME~MCAL_PERIOD_TYPE for Non-Generated Calendars. For example GBL_CALENDAR_ID will be 'Accounting~41', if the Enterprise Calendar id='Accounting' and the calendar period_type='41'.
GBL_DATASOURCE_NUM_ID: If Enterprise Calendar is not a Generated Calendar: Should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. (Example: If we have two Data source as PeopleSoft and Oracle, and our Global Calendar is from Oracle source, then parameter value should be of Oracle source.)
Note:
The tasks that load the Enterprise calendar will run as part of the Execution Plan for your Subject Area. Please note that there are no separate Subject Areas for common dimensions. They are included in the core Subject Areas.To set up an enterprise calendar using a PeopleSoft source system:
In DAC, display the Design view, and select an appropriate adapter.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. It should be the SETID~CALENDAR_ID for Non-Generated Calendars. For example, GBL_CALENDAR_ID will be 'SHARE~01', if the Enterprise Calendar id='01' and SETID='SHARE'.
GBL_DATASOURCE_NUM_ID: If Global Calendar is not a Generated Calendars: Should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. (Example: If we have two Data source as PeopleSoft and Oracle, and our Global Calendar is from Oracle source, then parameter value should be of Oracle source.)
Note:
The tasks that load the Enterprise calendar will run as part of the Execution Plan for your Subject Area. Please note that there are no separate Subject Areas for common dimensions. They are included in the core Subject Areas.To set up an enterprise calendar using an Oracle JD Edwards EnterpriseOne source system:
In DAC, display the Design view, and select an appropriate adapter.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
GBL_CALENDAR_ID: This parameter is used to select the Enterprise calendar. It should be the MCAL_CAL_NAME. For example GBL_CALENDAR_ID will be 'R', if the Enterprise Calendar id='R'.
GBL_DATASOURCE_NUM_ID: If Enterprise calendar is not a Generated calendar, this value should be the DATASOURCE_NUM_ID of the source system from where the calendar definition is taken. For example, if there are two data sources, Oracle EBS and Oracle JD Edwards EnterpriseOne, and the Global Calendar is from an Oracle JD Edwards EnterpriseOne source, then the parameter value should specify an Oracle JD Edwards EnterpriseOne source.
Note:
The tasks that load the Enterprise calendar will run as part of the Execution Plan for your Subject Area. Please note that there are no separate Subject Areas for common dimensions. They are included in the core Subject Areas.To set up World calendar using an Oracle JD Edwards World source system:
In DAC, display the Design view, and select an appropriate adapter.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
GBL_CALENDAR_ID: This parameter is used to select the World calendar. It should be the MCAL_CAL_NAME. For example GBL_CALENDAR_ID will be 'R', if the World Calendar id='R'.
GBL_DATASOURCE_NUM_ID: If the World calendar is not a Generated calendar, this value should be the DATASOURCE_NUM_ID of the source system from where the calendar definition is taken. For example, if there are two different data sources, Oracle EBS and Oracle JD Edwards World, and the Global calendar is from a Oracle JD Edwards World source, then the parameter value should specify an Oracle JD Edwards World source.
Note:
The tasks that load the World calendar will run as part of the Execution Plan for your Subject Area. Please note that there are no separate Subject Areas for common dimensions. They are included in the core Subject Areas.To set up a 13 Period calendar:
In DAC, display the Design view, and select an appropriate adapter.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 period type of Calendars). By default the 4-4-5 calendar has a CALENDAR_ID of '10000' and the 13-period calendar has a CALENDAR_ID of '10001'.
GBL_DATASOURCE_NUM_ID: If Global Calendar is Generated Calendar: Should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse).
Using a text editor, edit the values in file_mmcal_config_g.csv.
In DAC, set the value of 13P_CALENDAR_ID to 10001.
Note: The task SIL_TimeDImension_McalWeek13Period will run as part of the Execution Plan for your Subject Area. Please note that is no separate subject are for common dimensions. They are included in the core Subject Areas.
To set up a 4-4-5 calendar:
In DAC, display the Design view, and select an appropriate adapter.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 period type of Calendars). By default the 4-4-5 calendar has a CALENDAR_ID of '10000' and the 13-period calendar has a CALENDAR_ID of '10001'.
GBL_DATASOURCE_NUM_ID: If Global Calendar is Generated Calendar: Should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse)
Using a text editor, edit the values in file_mcal_config_g.csv.
In DAC, set the value of 445P_CALENDAR_ID to 10000.
Note: The task SIL_TimeDimension_McalWeek445 will run as part of the Execution Plan for your Subject Area. Please note that is no separate subject are for common dimensions. They are included in the core Subject Areas.
To use a fiscal calendar loaded via the Universal adapter:
In DAC, display the Design view, and select the Universal container.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
GBL_CALENDAR_ID: Should be the INTEGRATION_ID from the file_mcal_cal_d.csv file of the particular calendar which is defined as the Global Calendar.
GBL_DATASOURCE_NUM_ID: If Global Calendar is not a Generated Calendars: Should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken, if its defined in the file_mcal_period_ds.csv file then that value should be taken or else as defined in DAC for Universal adapter.
Using a text editor, edit the values in the w_mcal_config_g.csv file.
Using a text editor, edit the values in the w_mcal_config_d.csv file.
If you are using Oracle Financial Analytics and Oracle Project Analytics to lookup the calendar ID for a given ledger OU, using a text editor, edit the values in the w_mcal_context_g.csv file.
Using a text editor, edit the values in the w_mcal_period_ds.csv file (if required).
In DAC, set the value of GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID (as in scenario 4).
You must complete this task before running any Execution Plan that includes multiple calendar support and is sourcing fiscal calendars from a PeopleSoft source system.
To use a PeopleSoft Summary calendar:
Using a text editor, edit the values in summary_calendar.csv.
Run the Execution Plan for your Subject Area.
The Execution Plan will automatically include the task that loads the PeopleSoft summary calendar configuration table.
The Universal Adapter is provided to allow data from sources other than PeopleSoft, Oracle EBS, or Oracle JD Edwards, to be brought into the Multiple Calendar tables. The data from these sources needs to be brought in using the following via CSV files:
file_mcal_config_g.csv – loads W_MCAL_CONFIG_G.
file_mcal_context_g.csv – loads W_MCAL_CONTEXT_G.
file_mcal_cal_d.csv – loads W_MCAL_CAL_D.
file_mcal_period_ds.csv – loads W_MCAL_PERIOD_DS (Staging Table).
This section includes example configuration settings for the Universal Adapter configuration files, and contains the following topics:
Example file_mcal_cal_d.csv settings
Notes:
Primary Key is the ROW_WID and needs to be unique.
Example file_mcal_config_g settings
Notes:
To be used for Generated Calendars for all the adapters.
CALENDAR_ID value for Generated Calendar is used in DAC task level parameter.
DATE columns should be of the format YYYYMMDDHHMMSS (for example, 20000202000000 for 2nd February, 2000).
CALENDAR_NAME for 13 Period type generated Calendar should be '13' or '13 Period'.
REFERENCE_DATE should be of the format 'MMDD' (for example, 0131 for 31st January).
Example file_mcal_context_g settings
Notes:
DATE columns should be of the format YYYYMMDDHHMMSS (for example, 20000202000000).
Example file_file_mcal_period_ds settings
Notes:
DATE columns should be of the format YYYYMMDDHHMMSS (for example, 20000202000000).
The data in time dimension tables is loaded once during the initial full load. Subsequently, the SIL_*_UpdateFlag mappings run everyday to update the domain value codes, which indicate whether a day, week, month, quarter or year is 'Current', 'Next' or 'Previous' as of the current day. The SIL_Fiscal_UpdateFlag mappings also update the flags that indicate whether a fiscal week, month, quarter or year is 'Current', 'Previous' or 'Next' with respect to the system date.
You might want to extend the range of data that you have in your time dimension tables sometime after the data warehouse is in production. In order to achieve this, follow the steps below to initiate the full load ETL run of W_DAY_D and all the aggregate time dimension tables.
To set up the load strategy of the time dimension table
In DAC, go to the Setup view, display the Physical Data Sources tab, and then click on the connection DataWarehouse.
Select the Refresh Dates subtab in the bottom pane.
The Refresh Dates sub-tab displays the refresh dates for all of the tables. Double-click on the refresh date for W_DAY_D, and make it null. Do the same for the following aggregate time dimension tables: W_WEEK_D, W_QTR_D, W_MONTH_D, W_YEAR_D.
In the Design view, select your custom container from the drop-down list.
Display the Tasks tab, and query for the task SIL_DayDimension.
Set the $$START_DATE and $$END_DATE parameters to the appropriate start date and end date of the new date range.
Save the task.
Note:
Make sure that the new date range defined by the parameters $$START_DATE and $$END_DATE encompasses the old date range entirely. Otherwise, records can be lost. Also make sure you have provided the fiscal information for the new date range also in the appropriate fiscal input file. The next time you run your ETL Execution Plan, the calendar dimension will be truncated and reloaded with the new date range that you have specified. Since the calendar dimensions use smart keys, none of the fact tables that refer to these tables need to be reloaded.Stored procedures are a group of SQL statements that perform particular tasks on the database. For example, stored procedures can help to improve the performance of the database.
You can deploy stored procedures by copying the stored procedure files from your Oracle Business Intelligence installation and deploying them to the target data warehouse.
Note:
Some sessions may fail if these procedures are not compiled in the database before running the workflows.To deploy stored procedures
Navigate to the OracleBI\dwrep\Informatica\Stored_Procedure_Scripts folder.
Select the appropriate folder for your target database type (for example, \Oracle\, \DB2\).
Copy the source code in the \<DB type>\Compute_Bounds_<DB type>.sql file into the target data warehouse schema.
For example, for an Oracle database, copy the source SQL code from\Oracle\Compute_Bounds_Ora11i.sql.
Compile the stored procedures in the target data warehouse database.
Note:
If you have problems deploying the stored procedures, see your database reference guide, or contact your database administrator.This section contains additional configuration steps that apply to Oracle BI Applications deployed with any source system. It contains the following topics:
Data Source Numbers are identification numbers that are assigned to data sources so that the data can be identified in the data warehouse. For example, if you have a single Oracle EBS R12 data source, the value '9' is used by default. If you have two Oracle EBS R12 data sources that you want to differentiate in the data warehouse, you must assign a different data source number (for example, 30) to the second data source.
Data Source Numbers are stored in the DATASOURCE_NUM_ID column in the data warehouse.
Oracle BI Applications is installed with a number of pre-defined data source templates that you can edit to specify OLTP and OLAP data sources. The data source templates are pre-seeded with the recommended data source numbers, as specified in Table 3-5. If you specify a data source using one of the pre-defined templates, Oracle recommends that you use the data source number defined out-of-the-box. If you create a new data source without using one of the pre-defined templates, Oracle recommends that you specify the DATASOURCE_NUM_ID for that data source category as listed in Table 3-5. For example, if you specify an Oracle R12 EBS data source, Oracle recommends that you specify the DATASOURCE_NUM_ID value '9'. For instructions on how to specify data sources, see the section 'Setting Physical Data Sources' in Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
Table 3-5 shows the data sources that can be used with Oracle BI Applications and their default DATASOURCE_NUM_ID values.
Note: Not all of the data source types listed in Table 3-5 are supported by current version of Oracle Business Intelligence Applications. For a list of supported source systems, refer to System Requirements and Supported Platforms for Oracle Business Intelligence Applications.
Table 3-5 Data Sources and Associated DATASOURCE_NUM_ID Values
| Data Source Name | Data Source Number |
|---|---|
|
ORA_11_5_8 |
2 |
|
ORA_11_5_9 |
5 |
|
ORA_11_5_10 |
4 |
|
ORA_R12 |
9 |
|
ORA_R1211 |
26 |
|
PSFT_8_4_FINSCM |
7 |
|
PSFT_8_8_FINSCM |
8 |
|
PSFT_8_8_HCM |
6 |
|
PSFT_8_9_FINSCM |
11 |
|
PSFT_8_9_HCM |
10 |
|
PSFT_9_0_ELM |
12 |
|
PSFT_9_0_FINSCM |
13 |
|
PSFT_9_0_HCM |
12 |
|
SEBL_63 |
1 |
|
SEBL_771 |
1 |
|
SEBL_753 |
1 |
|
SEBL_78 |
1 |
|
SEBL_80 |
1 |
|
SEBL_VERT_771 |
1 |
|
SEBL_VERT_753 |
1 |
|
SEBL_VERT_78 |
1 |
|
SEBL_VERT_80 |
1 |
|
SEBL_811 |
1 |
|
SEBL_VERT_811 |
1 |
|
JDE_8.11 SP1 (EnterpriseOne) |
15 |
|
JDE_8.12 (EnterpriseOne) |
15 |
|
JDE_9.0 (EnterpriseOne) |
25 |
|
JDEW_9.2 (World) |
24 |
|
UNIV |
3 |
To configure Data Source Numbers
In DAC, go to the Setup view, and display the Physical Data Sources tab.
Select the data source that you want to configure.
On the Edit subtab, use the Data Source Number field to set the value.
Click Save.
If you do choose to modify the data source number and if you implement Procurement and Spend Analytics Family of Products, it is mandatory that you perform the steps in section Section 4.2.2.1, "How to Configure the DAC Parameter for Purchase Cycle Line".
This section contains configuration steps that apply to Oracle BI Applications deployed with Oracle EBS source systems.
This section contains the following topics:
Section 3.2.1, "Configuration Required Before a Full Load for Oracle EBS"
Section 3.2.2, "Configuration Steps for Controlling Your Data Set for Oracle EBS"
This section contains configuration steps required before a full data load that apply to Oracle BI Applications deployed with Oracle EBS source systems. It contains the following topics:
Section 3.2.1.1, "Configuration of Product Hierarchy (Except for GL, HR Modules)"
Section 3.2.1.4, "About Mapping Oracle GL Natural Accounts to Group Account Numbers"
Section 3.2.1.5, "How to make corrections to the Group Account Number Configuration"
This section contains configuration points for product hierarchy in the Product dimension table and the Inventory Product dimension table.
Hierarchy is defined by using segment values in the flexfield structure of Oracle EBS. Although Oracle EBS allows up to 20 segments, Oracle Business Intelligence Applications supports only 10 level of hierarchy (segment). These hierarchies are obtained from the Oracle EBS Category Set and Category tables. The default product hierarchies are configured out-of-the-box as follows:
Purchasing Category Set is assigned a value of 2, using the DAC source system parameter PROD_CAT_SET_ID1.
Inventory Category Set is assigned a value of 27, using the DAC source system parameter INV_PROD_CAT_SET_ID1.
You configure these parameters and create new PROD_CAT_SETn and INVPROD_CAT_SETn parameters in DAC, based on the Oracle EBS Category Set IDs that you want to load into the Oracle Business Analytics Warehouse.
To configure your product category sets, do the following:
Identify the category sets you want to report (for more information, see Section 3.2.1.1.1, "How to Identify Category Sets from Oracle EBS").
Configure the DAC parameters for product hierarchy (for more information, see Section 3.2.1.1.2, "How to Configure DAC Source System Parameters for Product Hierarchy").
These steps are part of the task in Section 3.2.1.1, "Configuration of Product Hierarchy (Except for GL, HR Modules)".
To find out the category sets that your organization is using
Log in to the Oracle EBS instance.
Click Setup, then Items, then Categories, and then Default Category Sets.
Look for the functional area Inventory and place the cursor in the Category Set field.
Choose Help, then Diagnostics, then Examine, and specify the user password for the application.
Click the Field LOV button and select CATEGORY_SET_ID, and note down the value.
Repeat steps 3-5 for the Purchasing functional area.
These steps are part of the task in Section 3.2.1.1, "Configuration of Product Hierarchy (Except for GL, HR Modules)".
To configure the DAC source system parameters for product hierarchy
In DAC, go to the Design view, and select your custom container from the drop-down list.
Display the Source System Parameters tab.
Locate the INV_PROD_CAT_SET_ID1 and PROD_CAT_SET_ID1 parameters, and for each parameter do the following:
Use the Value field to specify a category set value (that is, replace the default Category Set ID 2 or 27 with a new value).
Note: The value for INV_PROD_CAT_SET_ID parameter should be set to the appropriate Inventory Category Set value. The value for the PROD_CAT_SET_ID parameter should be set to the appropriate Purchase Category Set value.
You can configure up to ten Product Category Hierarchies and ten Inventory Category Hierarchies.
If you want to set up multiple product hierarchies, specify values for the additional INV_PROD_CAT_SET_IDn and PROD_CAT_SET_IDn parameters that you want to deploy.
Click Save.
Notes
The grain of the Product dimension is at the Master level. Therefore, the category set chosen as a value for the Product Dimension parameter (PROD_CAT_SET_ID) must be a Category Set controlled at a Master level but not at the Org level.
For information about adding or customizing Product Category Hierarchies, see Section 16.4, "How to Set Up Product Category Hierarchies".
This section explains how to assign United Nations Standard Products and Services Code (UNSPSC) codes to products and commodities. The United Nations Standard Products and Services Code® (UNSPSC®) provides an open, global multi-sector standard for efficient, accurate classification of products and services.
You can assign UNSPSC codes to your Products by adding the UNSPSC codes to the file_unspsc.csv file, which are then loaded into the W_PROD_CAT_DH table.
UNSPSC Codes are created automatically by the SDE_UNSPSC process in the adapter. This process loads the UNSPSC codes from the file_unspsc.csv into the W_PROD_CAT_DHS table. Make sure that the DATASOURCE_NUM_ID in the file is the same as that defined for the Oracle EBS Adapter. The default values are '9' for Oracle EBS R12 and '4' for Oracle EBS R11510.
The file_unspsc.csv file is located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\i nfa_shared\SrcFiles).
A new load process PLP_ItemToUNSPSC_Classification is added to do the manual classification.
To assign UNSPSC codes to products:
To get the Products used in your deployment, run a select statement on the W_PRODUCT_D table.
For example, you might use Oracle SQLDeveloper to run the following SQL command:
SELECT INTEGRATION_ID, PRODUCT_NAME, PART_NUMBER FROM W_PRODUCT_D;
Note: In the above example SQL statement, the INTEGRATION_ID is the product that needs classification. The PRODUCT_NAME and PART_NUM are additional attributes to assist in classifying the UNSPSC Codes.
Add the product IDs and UNSPSC codes to the FILE_ITEM_TO_UNSPSC.csv file.
The FILE_ITEM_TO_UNSPSC.csv file is located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
The INTEGRATION_ID values populate the PRODUCT_ID field and the UNSPSC Code values populate the UNSPSC_CODE field.
In Informatica PowerCenter Designer, run the \Oracle_BI_DW_Base\PLP\PLP_ItemToUNSPSC_Classification workflow to update the rows in the W_PRODUCT_D table.
Make sure that the DATASOURCE_NUM_ID is the same as your Oracle EBS Adapter.
In Oracle 11i and R12.x applications, the products are defined in a Master Organization and then copied into the other Inventory Organizations for transactions. The Product dimension Extract mapping 'SDE_ORA_ProductDimension' has been enabled for configuration of this Master Organization based on the configuration in the OLTP. By default, the organization ID (that is set by the $$MASTER_ORG parameter in DAC) is set to 204. This organization ID 204 needs to be changed based on the individual implementation of OLTP in your deployment.
Note:
This ETL implementation supports the best practice prescribed by Oracle for the creation of Single Master Organization for defining the Product master. You can assign Multiple MASTER Organizations also under the same parameter by providing a comma-separated string of Organization codes (for example, '204','458').To set the Master Inventory Organization in the Product dimension extract
In DAC, go to the Design view, and select your custom container from the drop-down list.
Display the Tasks tab, and query for the SDE_ORA_ProductDimension task.
In the lower pane, display the Parameters tab, and locate the $$MASTER_ORG parameter.
Use the Value field to specify an appropriate value.
For example, 204. To define multiple master organizations, specify a comma-separated list of organization codes in single quotation marks (for example, '204', '301').
Save your changes.
This section explains how to map Oracle General Ledger Accounts to Group Account Numbers, and includes the following topics:
An overview to setting up Oracle General Ledger Accounts - see Section 3.2.1.4.1, "Overview to Mapping Oracle GL Accounts to Group Account Numbers".
A description of how to map Oracle General Ledger Accounts to Group Account Numbers - see Section 3.2.1.4.2, "How to map Oracle GL Account Numbers to Group Account Numbers".
An example to explain how to add Group Account Number metrics to the Oracle BI Repository - see Section 3.2.1.4.3, "Example of Adding Group Account Number Metrics to the Oracle BI Repository".
Note:
It is critical that the GL account numbers are mapped to the group account numbers (or domain values) because the metrics in the GL reporting layer use these values. For a list of domain values for GL account numbers, see Oracle Business Analytics Warehouse Data Model Reference.You set up General Ledger accounts using the following configuration files that are located in the $PMServer\srcfiles directory:
file_group_acct_names.csv - this file specifies the group account names and their corresponding group account codes.
file_group_acct_codes_ora.csv - this file maps General Ledger accounts to group account codes.
file_grpact_fsmt.csv - this file maps Financial Statement Item Codes to group account codes.
Before you load your data, you must make sure that the account values are mapped consistently across these three configuration files.
The figure above shows how the GROUP_ACCOUNT_NUM field in file_group_acct_names.csv must map to the GROUP_ACCT_NUM field in file_group_acct_codes_ora.csv and the GROUP_ACCT_NUM field in file_grpact_fstmt.csv.
You can categorize your Oracle General Ledger accounts into specific group account numbers. The group account number is used during data extraction as well as front-end reporting. The GROUP_ACCT_NUM field in the GL Account dimension table W_GL_ACCOUNT_D denotes the nature of the General Ledger accounts (for example, cash account, payroll account). Refer to the GROUP_ACCOUNT_NUM column in the file_group_acct_names.csv file for group account codes that you can use (for example, AR, CASH, COGS). For a list of the Group Account Number domain values, see Oracle Business Analytics Warehouse Data Model Reference. The mappings to General Ledger Accounts Numbers are important for both Profitability analysis and General Ledger analysis (for example, Balance Sheets).
The logic for assigning the group accounts is located in the file_group_acct_codes_ora.csv file. The table below shows an example configuration of the file_group_acct_codes_ora.csv file.
Table 3-6 Example Configuration of file_group_acct_codes_ora.csv
| CHART OF ACCOUNTS ID | FROM ACCT | TO ACCT | GROUP_ACCT_NUM |
|---|---|---|---|
|
1 |
101010 |
101099 |
CA |
|
1 |
131010 |
131939 |
FG INV |
|
1 |
152121 |
152401 |
RM INV |
|
1 |
171101 |
171901 |
WIP INV |
|
1 |
173001 |
173001 |
PPE |
|
1 |
240100 |
240120 |
ACC DEPCN |
|
1 |
261000 |
261100 |
INT EXP |
|
1 |
181011 |
181918 |
CASH |
|
1 |
251100 |
251120 |
ST BORR |
In the table above, in the first row, all accounts within the account number range from 101010 to 101099 that have a Chart of Account (COA) ID equal to 1 are assigned to Current Asset (that is, CA). Each row maps all accounts within the specified account number range and within the given chart of account ID.
If you need to create a new group of account numbers, you can create new rows in the file_group_acct_names.csv file. You can then assign GL accounts to the new group of account numbers in the file_group_acct_codes_ora.csv file.
You also need to add a new row in the file_grpact_fstmt.csv file. This file specifies the relationship between a group account number and a Financial Statement Item code. The table below shows the Financial Statement Item codes to which Group Account Numbers must map, and their associated base fact tables.
| Financial Statement Item Codes | Base Fact Tables |
|---|---|
| AP | AP base fact (W_AP_XACT_F) |
| AR | AR base fact (W_AR_XACT_F) |
| COGS | Cost of Goods Sold base fact (W_GL_COGS_F) |
| REVENUE | Revenue base fact (W_GL_REVN_F) |
| TAX | Tax base fact (W_TAX_XACT_F) |
| OTHERS | GL Journal base fact (W_GL_OTHER_F) |
By mapping your GL accounts against the group account numbers and then associating the group account number to a Financial Statement Item code, you have indirectly associated the GL account numbers to Financial Statement Item codes as well.
Financial Statement Item codes are internal codes used by the ETL process to process the GL journal records during the GL reconciliation process against the subledgers. When the ETL process reconciles a GL journal record, it looks at the Financial Statement Item code associated with the GL account that the journal is charging against, and then uses the value of the Financial Statement item code to decide which base fact the GL journal should reconcile against. For example, when processing a GL journal that charges to a GL account which is associate to 'AP' Financial Statement Item code, then the ETL process will try to go against AP base fact table (W_AP_XACT_F), and try to locate the corresponding matching AP accounting entry. If that GL account is associated with the 'REVENUE' Financial Statement Item code, then the ETL program will try to go against the Revenue base fact table (W_GL_REVN_F), and try to locate the corresponding matching Revenue accounting entry.
Note:
When you specify the group account number, you must capitalize the letters and use the values in the
GROUP_ACCOUNT_NUM column of the file_group_acct_names.csv file.This section explains how to map Oracle General Ledger Account Numbers to Group Account Numbers.
Note: If you add new Group Account Numbers to the file_group_acct_codes_ora.csv file, you must also add metrics to the Oracle BI repository (for more information, see Section 3.2.1.4.3, "Example of Adding Group Account Number Metrics to the Oracle BI Repository").
To map Oracle GL account numbers to group account numbers
Open the file_group_acct_codes_ora.csv file with a text editor in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
For each Oracle GL account number that you want to map, create a new row in the file containing the following fields:
| Field Name | Description |
|---|---|
| CHART OF ACCOUNTS ID | The ID of the GL chart of account. |
| FROM ACCT | The lower limit of the natural account range. This is based on the natural account segment of your GL accounts. |
| TO ACCT | The higher limit of the natural account range. This is based on the natural account segment of your GL accounts. |
| GROUP_ACCT_NUM | This field denotes the group account number of the Oracle General Ledger account, as specified in the file_group_acct_names.csv file. For example, 'AP' for Accounts Payables, 'CASH' for cash account, 'GEN PAYROLL' for payroll account, etc. |
For example:
101, 1110, 1110, CASH 101, 1210, 1210, AR 101, 1220, 1220, AR
Note:
You can optionally remove the unused rows in the CSV file.Make sure that the values that you specify in the file_group_acct_codes_ora.csv file are consistent with the values that are specified in the file_group_acct_names.csv file and the file_grpact_fstmt.csv file.
For an example, see Figure 3-3, "Configuration Files for Configuring Group Accounts".
Save and close the CSV file.
If you add new Group Account Numbers to the file_group_acct_codes_ora.csv file (as described in Section 3.2.1.4.2, "How to map Oracle GL Account Numbers to Group Account Numbers"), you must also add metrics to the Oracle BI repository to expose the new Group Account Numbers. This example illustrates how you can add Group Account Number metrics using Oracle BI Administration Tool.
Example of Adding Group Account Number Metrics to the Oracle BI Repository
This example assumes that you have a new Group Account Number called 'Payroll', and you want to add a new metric to the Presentation Layer called 'Payroll Expense'.
To add a new metric in logical table Fact – Fins – GL Other Posted Transaction:
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
In the Business Model and Mapping layer:
Create a logical column named 'Payroll Expense' in logical table 'Fact – Fins – GL Other Posted Transaction'.
For example, right click on the Core\Fact - Fins - GL Other Posted Transaction\ object and choose New Object, then Logical Column, to display the Logical Column dialog. Specify Payroll Expense in the Name field.
Display the Aggregation tab, choose 'Sum' in the Default aggregation rule drop down list.
Click OK to save the details and close the dialog.
Expand the Core\Fact - Fins - GL Other Posted Transaction\Sources\ folder and double click the Fact_W_GL_OTHER_GRPACCT_FSCLYR_A source to display the Logical Table Source dialog.
Display the Column Mapping tab.
Select the Show unmapped columns check box.
Locate the Payroll Expense expression, and click on the (...) icon to open the Expression Builder dialog.
Use the Express Builder dialog to specify the following SQL statement:
CASE WHEN "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_GROUP_ACCOUNT_D"."GROUP_ACCOUNT_NUM" = 'PAYROLL' THEN "Oracle Data Warehouse"."Catalog"."dbo"."Fact_Agg_W_GL_OTHER_GRPACCT_FSCLYR_A"."OTHER_GLOBAL1_AMT" ELSE NULL END
The case condition refers to the new Group Account Number 'Payroll' and uses it as a filter.
Repeat steps d to h for each Logical Table Source. Modify the expression in step f appropriately for each LTS by using the appropriate fact table that corresponds to the Logical Table Source.
We need to repeat steps d to h for each Logical Table Source because in this example we have multiple Logical Table Sources for fact table and aggregation tables in this logical table. Modify the expression in step h appropriately for each Logical Table Source by using the appropriate fact table to which it corresponds.
Save the details.
To expose the new repository objects in end users' dashboards and reports, drag the new objects from the Business Model and Mapping Layer to an appropriate folder in the Presentation layer.
To add a new metric in logical table Fact – Fins – GL Balance:
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
In the Business Model and Mapping layer:
Create a logical column named 'Payroll Expense' in logical table 'Fact – Fins – GL Balance'.
For example, right click on the Core\Fact – Fins – GL Balance\ object and choose New Object, then Logical Column, to display the Logical Column dialog. Specify Payroll Expense in the Name field.
Select the Use existing logical columns as the source check box.
Click the (...) icon to display the Expression Builder dialog.
Use the Express Builder dialog to specify the following SQL statement:
FILTER("Core"."Fact - Fins - GL Balance"."Activity Amount" USING "Core"."Dim - GL Account"."Group Account Number" = 'Payroll')
The case condition refers to the new Group Account Number 'Payroll'.
Save the details.
To expose the new repository objects in end users' dashboards and reports, drag the new objects from the Business Model and Mapping Layer to an appropriate folder in the Presentation layer.
Note: Refer to the section Section 3.2.1.4, "About Mapping Oracle GL Natural Accounts to Group Account Numbers" for general concepts about group account number and Financial Statement Item code.
When a user maps a GL natural account to an incorrect group account number, incorrect accounting entries might be inserted into the fact table. For example, the natural account 1210 is mistakenly classified under 'AR' group account number when it should be classified under 'AP' group account number. When this happens, the ETL program will charge all the GL journal lines to account 1210 and try to reconcile these GL journal lines against subledger accounting records in the AR fact table (W_AR_XACT_F). Since these GL journal lines did not come from AR, the ETL program will not be able to find the corresponding subledger accounting records for these GL journal lines. In this case, the ETL program will insert 'Manual' records into the AR fact table because it thinks that these GL journal lines are 'Manual' journal entries created directly in the GL system charging to the AR accounts. This entire process is called the GL reconciliation process.
In order to revert these 'Manual' entries in the AR fact table, you will need to utilize the 'Group Account Number Cleanup' program provided in Oracle BI Applications. This program will revert the 'Manual' entries in the fact table (in this case, AR fact table), and then try to do the GL reconciliation process again. This time, the ETL program will try to look for the corresponding subledger accounting records in the AP fact (W_AP_XACT_F), provided that you have re-assigned the natural account 1210 to the 'AP' group account number in the file_group_acct_codes_ora.csv file.
To do group account correction
Correct the mapping of GL natural account to the group account in the input 'csv' file file_group_acct_codes_ora.csv.
For example, before correction, a CSV file has the following values:
CHART OF ACCOUNTS ID = 101
FROM ACCT = 1110
TO ACCT = 1110
GROUP_ACCT_NUM = CASH
After correction, if the account '1210' originally belonged to the 'AP' group account number and after correcting the GL natural account to the group account, the CSV file would have the following values:
CHART OF ACCOUNTS ID = 101
FROM ACCT = 1210
TO ACCT = 1210
GROUP_ACCT_NUM = AR
In DAC, do the following:
Go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Subject Areas tab.
Query for the 'Financials – General Ledger' Subject Area.
Display the Configuration Tags subtab, and verify and which of the following Configuration Tags is marked as 'Inactive':
- Financials – Calculate GL Balance
- Oracle – Extract GL Balance
By default, 'Financials – Calculate GL Balance' should be marked as inactive.
Query for the 'Financials – Group Account Number Cleanup' Subject Area and do the following:
- If in the step above, the configuration tag 'Financials – Calculate GL Balance' is marked as 'Inactive', then 'Financials – Calculate GL Balance Reverse' should be marked as 'Inactive' as well.
- If in the step above, 'Oracle – Extract GL Balance' is marked as 'Inactive', then 'Financials – Calculate GL Balance Reverse' should be marked as active (that is, the Inactive check box should be cleared).
If you need to make any change in the step above, then you need to display the Subject Areas tab and reassemble the Subject Area named 'Financials – Group Account Number Clean Up'.
Rebuild and execute the appropriate Execution Plan that you are using to load the Subject Area named 'Financials – Group Account Number Clean Up'.
For example, depending on which version of Oracle EBS you have, you might be using one of the following out-of-the-box Execution Plans such as 'Financials – Group Account Number Clean Up R12' or 'Financials – Group Account Number Clean Up R1211'.
You need to configure GL Account Hierarchies if you are deploying one of the following applications:
Oracle Financial Analytics
Oracle Procurement and Spend Analytics
Oracle Supply Chain and Order Management Analytics
The are two ways to configure general ledger account hierarchies:
Using General Ledger Accounting Flexfield Value Sets Definitions
For instructions, see Section 3.2.1.6.1, "How to Configure GL Account Hierarchies Using GL Accounting Flexfield Value Sets Definitions."
Using the Financial Statement Generator (FSG) Report Definition
This option is available only if your source system is Oracle EBS. For instructions, see Section 3.2.1.6.2, "How to Configure GL Account Hierarchies Using Financial Statement Generator (FSG) Report Definition (for Oracle EBS)."
Whichever method you choose to set up GL account hierarchies, you store the hierarchy information in the W_HIERARCHY_D table.
As an example, the hierarchy for a GL account called US Acct might have the following structure:
Node A has child nodes B and C.
Node B has child nodes D and E.
Node C has child nodes F and G.
Node D has child nodes H and I.
Node F has child nodes J and K.
The figure below shows an example of this hierarchy for US Acct.
Table 3-7 shows how the hierarchy for US Acct would be stored in the W_HIERARCHY_D table.
Table 3-7 Example of Hierarchy for US Acct Stored in W_HIERARCHY_D
| HIER_KEY | HIER_NAME | HIER1_CODE | HIER2_CODE | HIER3_CODE | HIER4_CODE | HIER5_CODE | 6 - 19 | HIER20_CODE |
|---|---|---|---|---|---|---|---|---|
|
1 |
US Acct |
A |
B |
D |
H |
H |
H |
H |
|
2 |
US Acct |
A |
B |
D |
I |
I |
I |
I |
|
3 |
US Acct |
A |
B |
E |
E |
E |
E |
E |
|
4 |
US Acct |
A |
C |
F |
J |
J |
J |
J |
|
5 |
US Acct |
A |
C |
F |
K |
K |
K |
K |
|
6 |
US Acct |
A |
C |
G |
G |
G |
G |
G |
You must configure GL account hierarchies if you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, and Oracle Supply Chain and Order Management Analytics.
Thirty segments are supported in which you can store accounting flexfields. Flexfields are flexible enough to support complex data configurations. For example:
You can store data in any segment.
You can use more or fewer segments per chart of accounts, as required.
You can specify multiple segments for the same chart of accounts.
Example of Data Configuration for a Chart of Accounts
A single company might have a US chart of accounts and an APAC chart of accounts, with the following data configuration:
Table 3-8 Example Chart of Accounts
| Segment Type | US Chart of Account (4256) value | APAC Chart of Account (4257) value |
|---|---|---|
|
Company |
Stores in segment 3 |
Stores in segment 1 |
|
Natural Account |
Stores in segment 4 |
Stores in segment 3 |
|
Cost Center |
Stores in segment 5 |
Stores in segment 2 |
|
Geography |
Stores in segment 2 |
Stores in segment 2 |
|
Line of Business (LOB) |
Stores in segment 1 |
Stores in segment 4 |
This example shows that in Chart of Account 4256, 'Company' is stored in the segment 3 column in the Oracle EBS table GL_CODE_COMBINATIONS_ALL. In Chart of Account COA4257, 'Company' is stored in the segment 1 column in GL_CODE_COMBINATIONS_ALL table. The objective of this configuration file is to make sure that when segment information is extracted into the data warehouse table W_GL_ACCOUNT_D, segments with the same nature from different chart of accounts are stored in the same column in W_GL_ACCOUNT_D.
For example, we can store 'Company' segments from COA 4256 and 4257 in the segment 1 column in W_GL_ACCOUNT_D; and Cost Center segments from COA 4256 and 4257 in the segment 2 column in W_GL_ACCOUNT_D, and so forth.
About the ETL Process for GL Accounting Flexfields
Before you run the ETL process for GL accounts, you need to specify the segments that you want to analyze using the ETL configuration file named file_glacct_segment_config_<source system>.csv, located in $PMServer\SrcFiles (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
In file_glacct_segment_config_<source system>.csv, you need to specify the segments of the same type in the same column. For example, you might store all Cost Center segments from all charts of accounts in one column, and all Company segments from all charts of accounts in another column.
For example, you might want to do the following:
Analyze GL account hierarchies using only Company, Cost Center, Natural Account, and LOB.
You are not interested in using Geography for hierarchy analysis.
Store all Cost Center segments from all COAs in ACCOUNT_SEG2_CODE column in W_GL_ACCOUNT_D.
Store all Natural Account segments from all COAs in ACCOUNT_SEG3_CODE column in W_GL_ACCOUNT_D.
Store all LOB segments from all COAs in ACCOUNT_SEG4_CODE column in W_GL_ACCOUNT_D.
In W_GL_BALANCE_A (where you store GL account balances at aggregated level), you want to store GL account balances at Company and Cost Center level instead of at GL Code Combination level.
Note: The ETL logic for populating W_GL_BALANCE_A depends on the last row in the file where CHART OF ACCOUNTS_ID = AGGREGATION. Even if you are not using GL Account Hierarchies using ValueSet definitions, you still need to have this line in the CSV file. The out-of-the-box setting for this value is Y for SEG1 through SEG6 (that is, "AGGREGATION,Y, ,Y, ,Y, ,Y, ,Y, ,Y").
How to Configure file_glacct_segment_config_<source system> for U.S. Federal Financials Analytics
For U.S. Federal Financials Analytics, the first two segments are reserved for Fund and Program segments respectively. So, to use one or both of these, configure file_glacct_segment_config_<source system>.csv in this particular order:
Put your Fund segment column name in the 'SEGMENT1' column in the CSV file.
Put your Program segment column name in the 'SEGMENT2' column in the CSV file.
If you do not have any one of these reserved segments in your source system, leave that particular segment empty in the CSV file. To configure any other segments that you may have other than Fund and Program, configure these segments starting from SEGMENT3.
To configure hierarchies with GL accounting flexfields
Configure file_glacct_segment_config_<source system>.csv, as follows:
Navigate to $PMServer\SrcFiles (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
Open file_glacct_segment_config_<source system>.csv in a text editor.
Specify the segments that you want to analyze.
In DAC, do the following:
Navigate to the Subject Areas tab, and query for 'Financials – General Ledger'.
In the Configuration Tags subtab, do the following:
Query for the tag 'Oracle – Extract Value Set Hierarchies', and make sure the Inactive check box is not selected, and query for the tag 'Oracle – Extract FSG Hierarchies', and make sure the Inactive check box is selected.
Click Assemble to reassemble the Subject Area.
Navigate to the Execution Plans tab in the Execute view, and rebuild all Execution Plans that contain the 'Financials – General Ledger' Subject Area.
For instructions on building Execution Plans, see Oracle Business Intelligence Data Warehouse Administration Console Guide.
Run the Execution Plan for General Ledger Accounts.
Make the following changes in the RPD metadata using Oracle BI Administrator tool. The metadata contains multiple logical tables that represent each GL Segment, such as Dim – GL Segment1, Dim – GL Segment2 etc. Since 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 in order 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 the ValueSet IDs that are applicable for that particular segment. The list of the ValueSet IDs would be the same as the ValueSet IDs you configured in the CSV file mentioned above.
To specify a filter in the Business Model and Mapping layer of the Oracle BI Repository, do the following:
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
Expand each logical table, for example, Dim - GL Segment1, and open the logical table source under it.
Display the Content tab.
In the 'use WHERE clause…' text 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 valuesetids>)
Enter all ValueSet IDs, separated by commas, that correspond to this segment.
Oracle Financial Analytics supports up to 30 segments in the GL Account dimension, and out-of-the-box delivers 10 GL Segment dimensions in the RPD. If you need more than 10 GL Segments, please do the following to add new segments:
In the Physical layer:
Create a new physical alias of W_GL_SEGMENT_D as "Dim_W_GL_SEGMENT_D_SegmentXX". You can do this by right-clicking on physical table W_GL_SEGMENT_D, and selecting 'New Object' and then 'Alias…'. Give the name to this new alias as 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, i.e 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. Please refer to Oracle BI Server Administration Guide for more help on how to create physical foreign key joins.
Similarly, create 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.
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 (i.e 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 Logical Table diagram, create a "New Complex 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, you can include Dim – GL Segment1 on the Logical Table diagram, right click on 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 on "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, please read this: By default, the dimension will have two levels, i.e the Grand Total Level and the Detail Level. Rename these levels to "All" and "Detail" respectively. Right click on "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 dropdown 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 etc. 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.
Drag your new "Dim - GL Segment XX" dimensions into the appropriate Presentation Catalogs. Typically you can expose these GL Segment dimensions in all presentation catalogs where GL Account dimension is exposed. You may also find all appropriate presentation catalogs by right clicking on Dim – GL Segment1 and choosing Display Related and then Presentation Catalog.
Save your changes and check global consistency.
Each GL Segment denotes a certain meaningful ValueSet(s) in your OLTP. To clearly identify each segments 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 Product segment into Segment1, you can rename logical table "Dim - GL Segment1" as "Dim – GL Segment Product" or any other appropriate name and rename the folders in Presentation layer also accordingly.
You must configure GL account hierarchies if you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, and Oracle Supply Chain and Order Management Analytics. For information about the two ways you can configure GL account hierarchies, see Section 3.2.1.6, "About Configuring GL Account Hierarchies."
If you need to define GL account hierarchies based on multiple segments within a chart of accounts, you can use the Oracle FSG report definition in Oracle EBS to define them.
You should first use the Oracle FSG form to define a row set or a column set, then Oracle BI Applications will extract the row set or column set definition and convert them into hierarchies.
Oracle FSG hierarchies are extracted from following Oracle EBS source tables:
RG_REPORT_AXIS_CONTENTS
This table defines the relationship between the FSG report axis and GL code combinations. The GL code combinations with segment values within the value range defined for that axis are categorized as children of that axis.
RG_REPORT_AXIS_SETS
This table stores the information for each of the row set or column set you defined. There is one record in this table for each row or column set you defined. Each row includes an axis set identifier, a row or column set name, and a structure identifier to assign a specific chart of accounts to the row set or column set.
RG_REPORT_CALCULATIONS
This table stores formulas for calculating each row or column in the row or column set. An example of a row calculation might be to sum up the amount from the previous five rows. An example of a columns calculation might be to calculate column five by subtracting column four from column three.
For example, in Income Statement, 'Net Income' is the calculation result of 'Gross Profit from Revenue' minus 'Total Expense'. When converting to hierarchy, Net Income becomes the parent of 'Gross Profit from Revenue' and 'Total Expense'. Therefore, hierarchy can be derived based on the information in RG_REPORT_CALCULATION.
The diagram below shows an example hierarchy, with the top level Net Income node having two child nodes, Total Expense, and Gross Profit from Revn, and the Total Expense node having two child nodes, Operating Expense, and Depreciation Expense.
The diagram below shows how an income state is derived from a hierarchy.
The hierarchy above would be converted into a flattened hierarchy and stored in W_HIERARCHY_D in the following format:
Table 3-9 Example of Flattened Hierarchy Stored in W_HIERARCHY_D
| HIER Name | HIER1 | HIER2 | HIER3 | HIER4 | HIER20 |
|---|---|---|---|---|---|
|
Income Statement |
Net Income |
Gross Profit... |
Gross Profit... |
Gross Profit... |
Gross Profit... |
|
Income Statement |
Net Income |
Total Expenses |
Operating Expenses |
Operating Expenses |
Operating Expenses |
|
Income Statement |
Net Income |
Total Expenses |
Depreciation Expense |
Depreciation Expense |
Depreciation Expense |
Fact tables join to the W_HIERARCHY_D table via the GL Account dimension table (W_GL_ACCOUNT_D).
The W_GL_ACCOUNT_D table contains six fields (HIER1_WID, HIER2_WID, HIER3_WID, ...., HIER6_WID), which are foreign keys to the W_HIERARCHY_D.row_wid. Therefore, each General Ledger Code combination can participate in up to six different hierarchies. You can decide which of the six hierarchies to drill on based on the column you use to join to W_HIERARCHY_D. For example, if you want to drill using the third hierarchy, you use W_GL_ACCOUNT_D.hier3_wid = W_HIERARCHY_D.row_wid.
Note:
Mathematical operators, such as '+', '-', '*', '/' (addition, subtraction, multiplication, division, and so on) are not extracted from the FSG definitions. For example, both A + B = C and A - B = C would give the same hierarchy, with a node C having two child nodes A and B (see diagram below).
About the ETL Process for Oracle FSG Report
Before you run the ETL process for GL accounts, you need to specify the hierarchies that you want to reference. To specify the FSG hierarchies that you want to reference, use the file file_gl_hierarchy_assignment_ora.csv, which is located in $PMServer\SrcFiles (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
In this file, for each chart of accounts, you can specify six FSG hierarchies, using axis_set_id, which is a column from the RG_REPORT_AXIS_SETS table. It is the unique ID of a row set or column set you want to store in the GL account dimension table for the code combinations that belong to that chart of accounts.
The DATASOURCE_NUM_ID field specifies the data source to which the configurations apply. If you have multiple source systems, there might be a chart of accounts across the multiple source systems with the same ID. Therefore, you need to use the DATASOURCE_NUM_ID value to distinguish between them.
For example, suppose you have an income statement FSG report and a balance sheet FSG report and you want to input both of their hierarchy structures into the data warehouse. Oracle BI Applications assumes that both reports are derived from the same set of GL accounts with CHART_OF_ACCOUNTS=101. The axis_set_id of the income statement is 1001, and for the balance sheet, it is 1003. The DATASOURCE_NUM_ID for this application is 2.
In addition, for those GL accounts that belong to the two reports, assume you want to associate their HIER1 column (in GL_ACCOUNT_D) with the income statement hierarchy structure and HIER3 column with balance sheet hierarchy structure.
In this case, you would add one row into file_gl_hierarchy_assignment_ora.csv with the following fields set as below:
CHART OF ACCOUNTS - 101
HIER1_AXIS_SET_ID - 1001
HIER3_AXIS_SET_ID - 1003
DATASOURCE_NUM_ID - 2
(Leave the other row values blank.)
This row indicates that for all of the GL accounts with CHART_OF_ACCOUNTS=101 and DATASOURCE_NUM_ID=2, assigning hierarchies with axis_set_id=1001, null, 1003, null, null, null to HIER1~HIER6 columns respectively. Therefore, after extraction and loading, for those affected GL account rows, HIER1 column will be the foreign key to the income statement hierarchy row ID in W_HIERARCHY_D, and HIER3 column will be the foreign key to the balance sheet hierarchy row ID in W_HIERARCHY_D.
Note: Axis_set_id must be specified in file_gl_hierarchy_assignment_ora.csv for Financial Analytics to load the hierarchies.
To set up hierarchies with FSG Report Definition
Configure file_gl_hierarchy_assignment_ora.csv to specify the hierarchies you want to reference for each CHART_OF_ACCOUNTS.
Navigate to $PMServer\SrcFiles (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
Open file_gl_hierarchy_assignment_ora.csv in a text editor.
Specify the segments you want to analyze.
In DAC, do the following:
Go to the Design view, and select your custom container from the drop-down list.
Display the Subject Areas tab, and query for 'Financials – General Ledger'.
In the Configuration Tags subtab, do the following:
Query for the tag 'Oracle – Extract FSG Hierarchies', and make sure that the Inactive check box is not selected.
Query for the tag 'Oracle – Extract Value Set Hierarchies', and make sure that the Inactive check box is selected.
Click Assemble to reassemble the Subject Area.
Navigate to the Execution Plans tab in the Execute view, and rebuild all Execution Plans that contain the 'Financials – General Ledger' Subject Area.
For instructions on building Execution Plans, see Oracle Business Intelligence Data Warehouse Administration Console Guide.
Run the Execution Plan for General Ledger Accounts.
Using Oracle BI Administration Tool, in the Physical layer of the Oracle BI Repository, create additional aliases or change the names of the existing alias against the table W_HIERARCHY_D.
For example, if you want to create an income statement hierarchy, create an additional alias Dim_IncomeStatement_FSGHierarchy_D against the table W_HIERARCHY_D.
Using Oracle BI Administration Tool, in the Physical layer of the Oracle BI Repository, create joins in the Physical layer from the new aliases that you created in the previous step, as follows:
Income Statement Hierarchy will join to one of the HIER1~6 columns that you have specified in file file_gl_hierarchy_assignment_ora.csv for Income Statement.
In this case, we join it to HIER1 column.Dim_W_GL_ACCOUNT_D.HIER1_WID = Dim_IncomeStatement_FSGHierarchy_D.ROW_WID
Using Oracle BI Administration Tool, in the Business Model layer of the Oracle BI Repository, create additional dimensions using the new alias.
For the Income Statement hierarchy case, we create a new logical table Dim_IncomeStatement_FSGHierarchy_D, choose Dim_IncomeStatement_FSGHierarchy_D in the Physical layer as source. Mapping ROW_WID, HIER_CODE, and HIER1~HIER20 (both name and code) from physical table to logical key.
Then, set HIER_CODE=1001 (this is the Axis_set_id of Income Statement hierarchy) in logical table to restrain the output of logical table to be Income Statement Hierarchy only (right-click logical table Dim_IncomeStatement_FSGHierarchy_D – click properties – choose Source tab – select Dim_IncomeStatement_FSGHierarchy_D – click Edit button – choose Content tab – fill ("Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_HIERARCHY_D_FSG1"."HIER_CODE" =1001) into 'use WHERE clause…' text box).
For more information about this process, refer to the preinstalled example logical table Dim - FSG Hierarchy 1 in the Oracle Business Analytics Warehouse
Using Oracle BI Administration Tool, in the Business Model layer of the Oracle BI Repository, create a new dimension based on the logical table that you created in the previous step.
Refer to 'FSG Hierarchy 1' as an example.
In the Business Model layer, look for all the logical fact table that has logical join to the logical hierarchy table Dim - FSG Hierarchy1. You will need to create a similar logical join between the new logical hierarchy dimensions that you created and these logical facts, as follows:
Under each of the logical fact table, open the logical table sources and go to the content tab. Under aggregation content, select the 'Show unmapped' check box. It will show all the hierarchies you created in the previous step. For each of these hierarchies, select the logical level to 'Detail'.
In the business model diagram, create a new complex join between each of the new logical hierarchy tables and each of the logical facts. In the join, make sure the cardinality is (0,1) on the dimension side and N on the fact side.
Using Oracle BI Administration Tool, in the Presentation layer of the Oracle BI Repository, drag the new hierarchies into the Presentation folder.
Note: You can rename the hierarchies in the Presentation layer if required.
This section contains additional configuration steps that apply to Oracle BI Applications deployed with Oracle EBS source systems. It contains the following topics:
The Make-Buy indicator specifies whether a product material is made in-house or bought from an outside vendor. Out-of-the-box, the make-buy indicator is set to 1 for M (that is, Made in-house) or 2 for B (that is, Bought from an outside vendor). If your organization uses these indicator codes, you do not need to change your out-of-the-box ETL mappings.
If your organization uses different indicator codes, you must follow the steps below to change the indicator mapping in the mapplet mplt_SA_ORA_ProductDimension. For example, you may want your indicator code to be 0 for make, and 1 for buy.
To configure the Make-Buy Indicator
In Informatica PowerCenter Designer, open the appropriate Oracle EBS adapter (for example, SDE_ORA<Version>_Adaptor), and expand the Mapplets sub-folder.
Using the Mapplet Designer, edit the mapplet mplt_SA_ORA_ProductDimension.
Double-click the Expression transformation EXP_PRODUCTS to open the Edit Transformations dialog, and display the Ports tab.
Use the Expression Editor to edit the expression for the EXT_MAKE_BUY_IND port.
For example, if you want your indicator code to be 0 for Make and 1 for Buy, change the Formula to:
DECODE (INP_PLANNING_MAKE_BUY_CODE,0,'M',1,'B',NULL)
Validate the mapplet, and save your changes to the repository.
This section contains configuration steps that apply to Oracle BI Applications deployed with PeopleSoft source systems.
This section contains the following topics:
Section 3.3.1, "Configuration Required Before a Full Load for PeopleSoft"
Section 3.3.2, "Configuration Steps for Controlling Your Data Set for PeopleSoft"
This section contains configuration steps required before a full data load that apply to Oracle BI Applications deployed with PeopleSoft source systems. It contains the following topics:
The GL Account dimension in the Oracle Business Analytics Warehouse is at a granularity of a combination of chartfields. PeopleSoft Financials provides several chartfields for GL accounts, such as account, alternate account, operating unit, department, and so on. The ETL program extracts all possible combinations of these chartfields that you have used and stores each of these chartfields individually in the GL Account dimension. It extracts the combinations of chartfields used from the following PeopleSoft account entry tables:
PS_VCHR_ACCTG_LINES (Accounts Payable)
PS_ITEM_DST (Accounts Receivable)
PS_BI_ACCT_ENTRY (Billings)
PS_CM_ACCTG_LINE (Costing)
PS_JRNL_LN (General Ledger)
The GL Account dimension (W_GL_ACCOUNT_D) in the Oracle Business Analytics Warehouse provides a flexible and generic data model to accommodate up to 30 chartfields. These are stored in the generic columns named ACCOUNT_SEG1_CODE, ACCOUNT_SEG2_CODE and so on up to ACCOUNT_SEG30_CODE, henceforth referred to as segments. These columns store the actual chartfield value that is used in your PeopleSoft application.
Mapping PeopleSoft Chartfields
A CSV file has been provided to map the PeopleSoft chartfields to the generic segments. Use this file to specify which PeopleSoft application chartfield should be populated in which segment. The file is called file_glacct_segment_config_psft.csv and is located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
The first row in the file is a header row; do not modify this line. The second row in the file is where you specify how to do the mapping. The value for the column ROW_ID is hard coded to '1'; there is no need to change this.
Note that the file contains 30 columns – SEG1, SEG2, up to SEG30. You will have to specify which chartfield needs to be populated in each of these columns by specifying one of the supported values for the chartfields. The chartfields currently supported for the PeopleSoft application are listed below.
Note:
Values are case sensitive. You must specify the values exactly as shown below.Account
Alternate Account
Operating Unit
Fund Code
Department
Program Code
Class Field
Budget Reference
Product
Project
Affiliate
Fund Affiliate
Operating Unit Affiliate
ChartField 1
ChartField 2
ChartField 3
Note:
You only need to include the chartfields in the CSV file that you want to map.Hierarchy
Oracle Business Intelligence Application supports hierarchy for all these segments. If you have created a 'tree' in PeopleSoft for any of these chartfields, you can extract these trees into Oracle Business Analytics Warehouse's hierarchy to analyze your facts at any levels of the hierarchy. Information is provided in the sections that follow on how to extract these trees and how to setup the repository file (RPD) to use the Oracle Business Analytics Warehouse's hierarchy.
GL Balance Aggregate
The Oracle Business Analytics Warehouse data model has a fact table (W_GL_BALANCE_F) that stores GL Balance for all your GL accounts. To compress this fact table for better performance, it also provides an aggregate table built on top of this fact table that stores GL Balances for up to six segments of your choice. You can configure the number of segments you want in the aggregate table and which segments you want. This configuration is done at the third line of the file_glacct_segment_config_psft.csv file. Specify a value of 'Y' under the segment column that you want to be included in the aggregate table.
Note:
You can have up to six 'Y's in the file. You do not have to use all six. For example, if you want only three segments in your aggregate, you only need to specify three 'Y's.Example CSV File Configuration
As an example for the configuration of the file_glacct_segment_config_psft.csv file, consider the following scenario:
Your system uses four chartfields: Account, Alt Account, Operating Unit, and Dept. Assume that you are interested in analyzing your data by only three of the four chartfields: Account, Operating Unit, Department, and that you typically view your GL Balances at the combination of Account and Department. Only occasionally do you want to view your GL balances as the combination of all three chartfields. In this scenario, the CSV file would like similar to the following:
Table 3-10 Example CSV Chartfield Mapping Values
| ROW_ID | SEG1 | SEG2 | SEG3 |
|---|---|---|---|
|
1 |
Account |
Operating Unit |
Department |
|
AGGREGATION |
Y |
Y |
With this configuration, W_GL_ACCOUNT_D would store the 'Account' chartfield value in SEGMENT1 column, 'Operating Unit' chartfield value in SEGMENT2 column, and so forth. The GL Balance aggregate table, W_GL_BALANCE_A would store GL Balances for each unique combination of 'Account' chartfield and 'Department' chartfield.
You can categorize your PeopleSoft General Ledger accounts into specific group account numbers. The GROUP_ACCT_NUM field denotes the nature of the General Ledger accounts.
Note:
It is critical that the General Ledger Account Numbers are mapped to the Group Account Numbers (or domain values) as the metrics in the General Ledger reporting layer uses these values.For example, Cash account, Payroll account, and so on. For a list of the Group Account Number domain values, see Oracle Business Analytics Warehouse Fusion Edition Data Model Reference. The group account number configuration is used during data extraction as well as front-end reporting. For example the group account number configuration is used heavily in both Profitability Analysis (Income Statement) and General Ledger analysis. The logic for assigning the accounts is located in the file_group_acct_codes_psft.csv file. This file is located in the $PMServer\SrcFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\SrcFiles).
Table 3-11 Layout of file_group_acct_codes_psft.csv File
| BUSINESS_UNIT | FROM_ACCT | TO_ACCT | GROUP_ACCT_NUM |
|---|---|---|---|
|
AUS01 |
101010 |
101099 |
AP |
|
AUS01 |
131010 |
131939 |
AR |
|
AUS01 |
152121 |
152401 |
COGS |
|
AUS01 |
171101 |
173001 |
OTHER |
|
AUS01 |
240100 |
240120 |
REVENUE |
|
AUS01 |
251100 |
251120 |
TAX |
In the Table above, in the first row, all accounts within the account number range from 101010 to 101099 containing a Business Unit equal to AUS01 are assigned to AP. Each row maps all accounts within the specified account number range and with the given Business Unit. If you need to assign a new group of account numbers, you can then assign GL accounts to the new group of account numbers in the file_group_acct_codes_psft.csv file.
This section contains additional configuration steps that apply to PeopleSoft. It contains the following topics:
If you only manage your resources in HRMS or create only non-employees in FSCM and have HRMS integration for employees, you will need to run the integration broker to synchronize HRMS tables with FSCM tables.For more information about implementing PeopleSoft Resource Management for employee and non-employee source data, see PeopleSoft Resource Management 9.0 PeopleBook.
Oracle Business Intelligence Applications supports PeopleSoft department-based organization hierarchy. ETL mappings extract and flatten PeopleSoft department trees into a flattened organization hierarchy. ETL parameters also enable you to flatten department trees by SetID and tree name.
Supported Tree Structures
Oracle HR Analytics supports winter tree and summer tree structure types. Winter trees have nodes but do not have detail values. Summer trees have both nodes and detail values. Nodes are grouped into levels, and Oracle HR Analytics supports only strictly enforced tree levels in which all nodes on the same level represent the same type of entity. For detailed information about tree structures, see the PeopleSoft documentation.
How Oracle HR Analytics Handles Department Trees
PeopleSoft departments and the associated department tress are supported in Oracle HR Analytics as the organization dimension (W_INT_ORG_D) and the flattened organizational hierarchy structure (W_INT_ORG_DH).
Oracle HR Analytics flattens trees up to 15 levels deep, level 0 to 14, with level 0 as the bottom node. During the tree flattening ETL process, every tree node is inserted into W_INT_ORG_DH along with the path from the top-most node on the tree. If a node is less than 15 levels deep, the node value will be repeated in all levels below the node level.
Example of How a Department Tree Is Populated
The following diagram and tables give an example of how the department tree is populated into W_INT_ORG_D and W_INT_ORG_DH. This example uses the tree name "NA Sales" and the setID "Share."
The department table (PS_DEPT_TBL) populates the Internal Organization dimension table (W_INT_ORG_D) as follows:
Table 3-12 How PS_DEPT_TBL Populates W_INT_ORG_D
| ROW_ID | ORG_NUM | ORG_NAME | HR_ORG_FLAG |
|---|---|---|---|
|
1 |
A |
American Sales |
Y |
|
2 |
B |
West Region |
Y |
|
3 |
C |
New England |
Y |
|
4 |
D |
Massachusetts |
Y |
|
5 |
E |
California |
Y |
|
6 |
F |
Boston |
Y |
The department tree populates the Internal Organization hierarchy table W_INT_ORG_DH as follows:
Table 3-13 How PS_DEPT_TBL Populates W_INT_ORG_DH
| ORG_WID | ORG_HIER11_NUM | ORG_HIER11_NAME | ORG_HIER12_NUM | ORG_HIER12_NAME | ORG_HIER13_NUM | ORG_HIER13_NAME | ORG_TOP_NUM | ORG_TOP_NAME | HIERARCHY_NAME | W_HIERARCHY_CLASS | FIXED_HIER_LEVEL | HR_ORG_FLG |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
1 |
A |
North American Sales |
A |
North American Sales |
A |
North American Sales |
A |
North American Sales |
Share ~NA Sales |
HR Org |
14 |
Y |
|
2 |
B |
West Region |
B |
West Region |
B |
West Region |
A |
North American Sales |
Share ~NA Sales |
HR Org |
13 |
Y |
|
3 |
C |
New England |
C |
New England |
C |
New England |
A |
North American Sales |
Share ~NA Sales |
HR Org |
13 |
Y |
|
4 |
D |
Massachusetts |
D |
Massachusetts |
C |
New England |
A |
North American Sales |
Share ~NA Sales |
HR Org |
12 |
Y |
|
5 |
E |
California |
E |
California |
B |
West Region |
A |
North American Sales |
Share ~NA Sales |
HR Org |
11 |
Y |
|
6 |
F |
Boston |
D |
Boston |
C |
New England |
A |
North American Sales |
Share ~NA Sales |
HR Org |
12 |
Y |
How a Summer Tree Is Flattened
The tree flattening process also supports summer trees. A summer tree is a tree with detail ranges. If a tree has detail ranges specified for the bottom nodes, the extraction process creates a number of nodes in W_INT_ORG_HIER corresponding to departments in the specified node range.
If a tree is a summer tree, the granularity of the data returned from the ETL source Qualifier is one row per specified range. The lowest parent nodes of the tree can be repeated multiple times because multiple ranges can be created. The following diagram shows how a summer tree is flattened.
The detail ranges populate the Internal Organization dimension table W_INT_ORG_D as follows:
Table 3-14 How Detail Ranges Populate W_INT_ORG_D
| ROW_WID | ORG_NUM | ORG_NAME | HR_ORG_FLG |
|---|---|---|---|
|
7 |
2334 |
Appliances |
Y |
|
8 |
2340 |
Home Theater |
Y |
|
9 |
3001 |
MP3 Players |
Y |
The summer tree detail range is populated in W_INT_ORG_DH as follows:
Table 3-15 How Detail Ranges Populate W_INT_ORG_DH
| ORG_WID | ORG_HIER (1-10) | ORG_HIER11 | ORG_HIER12 | ORG_HIER13 | ORG_TOP | FIXED_HIER_LVL | HR_ORG_FLG |
|---|---|---|---|---|---|---|---|
|
7 |
Boston |
Boston |
Massachusetts |
New England |
North American Sales |
10 |
Y |
|
8 |
Boston |
Boston |
Massachusetts |
New England |
North American Sales |
10 |
Y |
|
9 |
Boston |
Boston |
Massachusetts |
New England |
North American Sales |
10 |
Y |
How the Flattened Internal Organization Hierarchy Is Presented in Oracle BI Enterprise Edition
The Oracle HR Analytics Presentation Catalog delivers a 15-level employee organization. Employee Organization hierarchy levels are mapped to Internal Organization dimension and hierarchy tables as follows:
| RPD Presentation Layer | Physical Table Mapping |
|---|---|
| Employee Organization Number | W_INT_ORG_D.ORG_NUM |
| Employee Organization Name | W_INT_ORG_D.ORG_NAME |
| Employee Organization Hierarchy Name | W_INT_ORG_DH.HIERARCHY_NAME |
| Hierarchy Version | W_INT_ORG_DEH.CURRENT_VER_HIER_FLG |
| Employee Organization Hierarchy 1 | W_INT_ORG_DH.HIER1_NUM |
| Employee Organization Hierarchy 2 | W_INT_ORG_DH.HIER2_NUM |
| Employee Organization Hierarchy 3 | W_INT_ORG_DH.HIER3_NUM |
| Employee Organization Hierarchy 4 | W_INT_ORG_DH.HIER4_NUM |
| Employee Organization Hierarchy 5 | W_INT_ORG_DH.HIER5_NUM |
| Employee Organization Hierarchy 6 | W_INT_ORG_DH.HIER6_NUM |
| Employee Organization Hierarchy 7 | W_INT_ORG_DH.HIER7_NUM |
| Employee Organization Hierarchy 8 | W_INT_ORG_DH.HIER8_NUM |
| Employee Organization Hierarchy 9 | W_INT_ORG_DH.HIER9_NUM |
| Employee Organization Hierarchy 10 | W_INT_ORG_DH.HIER10_NUM |
| Employee Organization Hierarchy 11 | W_INT_ORG_DH.HIER11_NUM |
| Employee Organization Hierarchy 12 | W_INT_ORG_DH.HIER12_NUM |
| Employee Organization Hierarchy 13 | W_INT_ORG_DH.HIER13_NUM |
| Employee Organization Hierarchy 14 | W_INT_ORG_DH.HIER14_NUM |
The table below describes HR Organization dimension and dimension hierarchy tables:
| Table Name | Description | Source Tables |
|---|---|---|
| W_INT_ORG_DS | HR Organization dimension staging table | PS_DEPT_TBL |
| W_INT_ORG_D | HR Organization dimension table | W_INT_ORG_DS |
| W_INT_ORG_DHS | HR Organization dimension hierarchy staging table | PSTREESTRCT
PSTREENODE PSTREELEVEL |
| W_INT_ORG_DH | HR Organization dimension hierarchy table | W_INT_ORG_DHW |
The following temporary tables are used to handle the extraction and load of the trees:
Sequence 1: W_PSFT_INT_ORG_DEPT_DH_TMP
Sequence 2: W_PSFT_INT_ORG_TREE_TMP
Sequence 3: W_PSFT_INT_ORG_VERT_DH_TMP
Sequence 4: W_PSFT_INT_ORG_DTLRGE_DH_TMP
Sequence 5: W_PSFT_INT_ORG_FLAT_DH_TMP
Oracle Business Intelligence Applications supports PeopleSoft Business Unit-based organization hierarchies. ETL mappings extract and flatten PeopleSoft Business Unit trees into a flattened organization hierarchy. ETL parameters also enable you to flatten Business Unit trees by SetID and tree name.
To configure Business Unit Trees:
In DAC, display the Design view, select the appropriate PeopleSoft adapter, and display the Source System Parameters tab.
Use the $$TREE_SETID_NAME_LIST parameter to specify the tree set ID and tree name in the format of '<setid>~<tree_name>'.
Wrap the <setid>~<tree_name> value in single quotation marks. If you have multiple trees, please separate them using a comma. For example:
'SHARE~DEPT1' , 'US~DEPT1', 'EU~DEPT1'
Use the $$TREE_STRUCT_ID_LIST parameter to specify the struct IDs for which data needs to be extracted, separating each ID with a comma.
The out-of-the-box value of this parameter is 'BUSINESS_UNIT','DEPARTMENT'.
DAC provides the parameter $$TREE_SETID_NAME_LIST to configure the tree flattening ETL process. $$TREE_SETID_NAME_LIST supports two PeopleSoft tree parameters, SETID, and TREE_NAME. No DAC parameter is provided for PeopleSoft tree parameter EFFDT. The tree extract mapping has built-in logic to extract the current effectively-dated tree (excluding future date) for the specified tree name. There is no increment extraction for PeopleSoft trees and a full extraction is always performed during each ETL process.
To configure Tree SetID and Tree Name in DAC
In DAC, display the Design view, then display the Tasks tab.
Select the task SDE_PSFT_Stage_InternalOrganizationDimension_Hierarchy_Extract, and display the Parameters sub tab.
In the Parameters sub tab, use the $$TREE_SETID_NAME_LIST parameter to specify appropriate values for SETID and TREE_NAME.
Note: The $$TREE_SETID_NAME_LIST parameter is in the format '<Setid>~<Tree Name>'. A single quotation mark is required. If you have multiple trees, separate them using a comma, for example, 'SHARE~DEPT1', 'US~DEPT1'.
The Geography Country dimension is used as a roll-up dimension for the Business Location and Geography dimensions.
Oracle Business Intelligence Applications is installed out-of-the-box with a standard set of countries (in the file domainValues_GeoCountry_ISO_Country_Codes_<Type>.csv). If you add a country in your source system that is not in the file domainValues_GeoCountry_ISO_Country_Codes_<Type>.csv, you must modify the file to accommodate this data.
The file domainValues_GeoCountry_ISO_Country_Codes_<Type>.csv is a domain value lookup for the W_GEO_COUNTRY_D table for ISO Country Codes. It contains the following columns: COUNTRY_CODE, COUNTRY_NAME, ISO_COUNTRY_CODE, ISO_COUNTRY_NAME, ISO_NUMERICAL_CODE, ISO_ALPHA3_CODE, NLS_TERRITORY.
Note: The COUNTRY_CODE in domainValues_GeoCountry_ISO_Country_Codes_psft.csv should match the values from the PS_COUNTRY_TBL in the PeopleSoft application database.
To configure Country Codes for PeopleSoft:
Using a text editor, edit the domainValues_GeoCountry_ISO_Country_Codes_psft.csv located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).
Add a new row to the CSV file with appropriate values.
Save and close the file.
This section contains configuration steps that apply to Oracle BI Applications deployed with Siebel source systems.
This section contains the following topics:
Section 3.4.1, "Configuration Required Before A Full Load for Siebel Source Systems"
Section 3.4.2, "Configuration Steps for Controlling Your Data Set for Siebel Source Systems"
Not applicable to Oracle BI Applications Version 7.9.6.1.
Not applicable to Oracle BI Applications Version 7.9.6.1.
This section contains configuration steps that apply to Oracle BI Applications deployed with Oracle's JD Edwards EnterpriseOne or JD Edwards World source systems.
This section contains the following topics:
This section contains configuration steps required before a full data load that apply to Oracle BI Applications deployed with Oracle's JD Edwards EnterpriseOne source systems. It contains the following topics:
You use DAC parameters to load category codes from Oracle's JD Edwards EnterpriseOne and JD Edwards World into dimension tables. When you configure the DAC parameters, you choose which category codes to map to the 20 new columns in the dimensions listed directly below. In addition, you can also choose category codes to map to the existing columns shown in Table 3-16. In DAC, by default NULL is assigned to all DAC parameters in the name of new attributes and configurable columns in the dimension table.
The following dimensions support category codes:
W_INT_ORG_D
W_PRODUCT_D
W_CUSTOMER_ACCOUNT_D
W_PARTY_ORG_D
W_GL_ACCOUNT_D
Note: Category codes pertaining to Oracle's JD Edwards EnterpriseOne and JD Edwards World GL Accounts are mapped into the existing Account Segment columns in the W_GL_ACCOUNT_D table. Configuration files (not DAC parameters) are used to configure these category codes (for more information, see Section 5.2.4.8, "How to Configure the file_glacct_segment_config_jde.csv").
Table 3-16 lists the additional fields in the dimension tables to which you can also map category codes.
Table 3-16 Additional dimension table fields to which you can map category codes
| Table | Column |
|---|---|
|
W_CUSTOMER_ACCOUNT_DS |
ACCOUNT_TYPE_CODE |
|
W_CUSTOMER_ACCOUNT_DS |
ACCOUNT_CLASS_CODE |
|
W_INT_ORG_DS |
STATE_REGION |
|
W_INT_ORG_DS |
COUNTRY_REGION |
|
W_PARTY_ORG_DS |
LINE_OF_BUSINESS |
|
W_PARTY_ORG_DS |
REGION |
|
W_PARTY_ORG_DS |
ACCNT_AHA_NUM |
|
W_PARTY_ORG_DS |
ACCNT_CLASS |
|
W_PARTY_ORG_DS |
ACCNT_HIN_NUM |
|
W_PARTY_ORG_DS |
ACCNT_REGION |
|
W_PARTY_ORG_DS |
ACCNT_VALUE |
|
W_PARTY_ORG_DS |
CUST_CAT_CODE |
|
W_PRODUCT_DS |
CONFIG_CAT_CODE |
|
W_PRODUCT_DS |
INDUSTRY_CODE |
|
W_PRODUCT_DS |
BRAND |
|
W_PRODUCT_DS |
COLOR |
|
W_PRODUCT_DS |
UNIV_PROD_CODE |
To configure category codes in DAC
In DAC, select the Design view and then select your custom container from the drop-down list.
Display the Tasks tab, and perform a query for the task.
Table 3-17 shows Oracle JD Edwards EnterpriseOne tables that contain the category codes that you can map from and the corresponding DAC tasks that you need to edit.
Table 3-17 Oracle's JD Edwards EnterpriseOne/JD Edwards World table names and their corresponding DAC task names
| Oracle's JD Edwards EnterpriseOne/JD Edwards World Table | DAC Task |
|---|---|
|
F0006 Business Unit Master |
SDE_JDE_InternalOrganization_BusinessUnits |
|
F0101 Address Book Master, F03012 Customer Master by Line of Business |
SDE_JDE_PartyOrganisationDimension |
|
F03012 Customer Master by Line of Business (JD Edwards EnterpriseOne only) |
SDE_JDE_Customer_Account_Dimension |
|
F0301 Customer Master (JD Edwards World only) |
SDE_JDE_Customer_Account_Dimension |
|
F4101 Item Master |
SDE_JDE_ProductDimensions |
In the Detail area of the selected task, display the Parameters tab. Modify the value in the DAC parameters by overriding the NULL value with the JDE category codes column name and table name. For example:
$$FLEX_ATTRIB_2_CHAR = F0006.MCRP24
The file_udc_category_mapping_jde.csv file loads Oracle's JD Edwards EnterpriseOne or JD Edwards World user defined codes (UDCs) into the Code (W_CODE_D) dimension. To avoid loading all UDCs into the Code dimension, use this flat file to specify a particular set of UDCs that you want to load.
Before you configure the CSV file, identify the UDCs that you need depending on the functional module that you are loading. Choose only the UDCs in transactional tables that are involved in the ETL process. Oracle recommends preparing a list of UDCs that you can refer to when configuring the CSV file.
There are three columns in the CSV file. The first two columns are used to identify the system codes and user defined codes. Together, these columns are used to identify the UDCs that will be loaded into W_CODE_D. The third column is the category into which you want to load the codes in W_CODE_D.
Categories in W_CODE_D are used to group together codes intended for a similar purpose. For example, UDC 00||CN stores the country code and description. To store this under the COUNTRY category in W_CODE_D, enter the following row in the CSV file:
00 CN COUNTRY
In the CSV file, you specify the system code and user defined code and associate it with the category to which you want the UDCs loaded. This data is loaded into UDC_CATEGORY_MAP_TMP table, which leverages the data and loads the relevant codes into the Code dimension.
Table 3-18 contains the UDCs that you can map using the file_udc_category_mapping_jde.csv flat file (in the order listed in the file).
Table 3-18 List of UDCs for the file_udc_category_mapping_jde.csv Flat File
| System Code | User Defined Code | Category |
|---|---|---|
|
00 |
PY |
SUPPLIER_PAYMENT_METHOD |
|
00 |
CN |
COUNTRY |
|
01 |
GD |
GENDER |
|
01 |
LP |
LANGUAGE |
|
00 |
S |
STATE |
|
01 |
PH |
FIN_PHONE_USAGE |
|
00 |
MC |
DIVISION_TYPE |
|
00 |
TS |
FIN_ORG_STRUCTURE |
|
01 |
SC |
PROFITCNTR_GRP |
|
H00 |
TA |
TAX_CODE |
|
00 |
PY |
PAYMENT_METHOD |
|
98 |
IC |
ACCT_DOC~STATUS |
|
00 |
UM |
UOM |
|
41 |
I |
STORAGE_TYPE |
|
49 |
BX |
HAZARD_MTL |
|
41B |
PG |
PROD_GRP |
|
46 |
EQ |
CONTAINER |
|
41 |
I |
PRODUCT_TYPE |
|
42 |
FR |
FRGHT_TERMS |
|
06 |
G |
JOB |
|
07 |
MS |
MARITAL_STATUS |
|
05 |
HQ |
DISABILITY |
|
00 |
CN |
NATIONAL_ID_TYPE |
The temporary table in the data warehouse stores the UDC to Category mappings. When the ETL for Code dimension is started, all UDCs that are found in this table, along with their mappings to respective categories, are extracted from the source and loaded into the W_CODE_D table.
Determining how to associate UDCs with categories requires looking at the SILO mappings, where the descriptions are resolved through lookups in W_CODE_D. The category is hard coded in the lookups. To resolve descriptions from W_CODE_D, you must ensure that the UDCs are loaded into the proper categories.
For information on creating categories and configuring the Code dimension for Oracle's JD Edwards EnterpriseOne or JD Edwards World category codes, see the Knowledge Article posted on Metalink 3.
Oracle's JD Edwards EnterpriseOne and JD Edwards World do not have a concept of defining the quarters for a fiscal pattern or a fiscal year. Therefore, a configurable flat file is provided to populate quarter information. This configuration file enables you to feed quarter information such as Quarter Number for each period, Quarter Start Date, and Quarter End Date. For information about how to configure this flat file, see Section 5.2.4.9, "How to Configure the file_lkp_fiscal_period_Qtr_Config_jde.csv".Each fiscal pattern can have a varying number of periods as supported by the OLTP. Therefore, the quarter configuration is required for each fiscal year and for each fiscal pattern. The following is an example of the file_lkp_fiscal_period_Qtr_Config_jde.csv opened in a text editor:
| Fiscal Pattern | Year | Period | QuarterNo | QuarterStart | QuarterEnd |
|---|---|---|---|---|---|
| F | 4 | 1 | 1 | 6/1/2004 | 8/30/2004 |
| F | 4 | 2 | 1 | 6/1/2004 | 8/30/2004 |
| F | 4 | 3 | 1 | 6/1/2004 | 8/30/2004 |
| F | 4 | 4 | 2 | 9/1/2004 | 11/30/2004 |
| F | 4 | 5 | 2 | 9/1/2004 | 11/30/2004 |
| F | 4 | 6 | 2 | 9/1/2004 | 11/30/2004 |
| F | 4 | 7 | 3 | 12/1/2004 | 2/28/2005 |
| F | 4 | 8 | 3 | 12/1/2004 | 2/28/2005 |
| F | 4 | 9 | 3 | 12/1/2004 | 2/28/2005 |
| F | 4 | 10 | 4 | 3/1/2005 | 3/31/2005 |
| F | 4 | 11 | 4 | 3/1/2005 | 3/31/2005 |
| F | 4 | 12 | 4 | 3/1/2005 | 3/31/2005 |
| F | 4 | 13 | 4 | 3/1/2005 | 3/31/2005 |
| F | 4 | 14 | 4 | 3/1/2005 | 3/31/2005 |
For each fiscal year in the F0008 table, you must define the quarters for each fiscal period. The quarter information is used in the calculation of aggregates by quarter.
The W_MCAL_CONTEXT_G table in the Oracle Business Analytics Warehouse stores calendars associated with the ORG ID, Ledger ID, and Operating Unit columns. In Oracle's JD Edwards EnterpriseOne and JD Edwards World, the fiscal date patterns are associated with the company which forms the ORG_ID and LEDGER_ID.
The W_MCAL_CAL_D table stores the calendar information. Every distinct Fiscal Date Pattern stored in the Fiscal Date Pattern table (F0008) has an entry in this table. The grain of this dimension is the Date Pattern Type, which identifies the Calendar in the Oracle Business Analytics Warehouse. This dimension does not have an association with the Fiscal year for that pattern. The MCAL_CAL_WID column is a four digit number that is reset to 1000 each time the ETL is run and incremented by one for each date pattern type stored in W_MCAL_CAL_D.
Note: It is critical that the GL account numbers are mapped to the group account numbers (or domain values) because the metrics in the GL reporting layer use these values. For a list of domain values for GL account numbers, see Oracle Business Analytics Warehouse Data Model Reference.
You can categorize the General Ledger accounts in Oracle's JD Edwards EnterpriseOne or JD Edwards World into specific group account numbers. The group account number is used during data extraction as well as front-end reporting. The GROUP_ACCT_NUM field in the GL Account dimension table W_GL_ACCOUNT_D denotes the nature of the General Ledger accounts (for example, Cash account, AR account, Long Term Debt account Payroll account). For a list of the Group Account Number domain values, see Oracle Business Analytics Warehouse Data Model Reference. The mappings to General Ledger Accounts Numbers are important for both Profitability analysis and General Ledger analysis (for example, Balance Sheets).
Using the file_group_account_codes_jde.csv, you can specify which group account (among the available group accounts) the object account is associated with. The Company column in this CSV file is the actual company the object account belongs to. In addition to the From Account and To Account range, the system uses the incoming company as a parameter for the association. If the incoming company has not been configured in the group account flat file, the system inserts 00000 as the default value for Company for lookups. You can choose to not configure group accounts for any company other than 00000 if you are using a single global chart of accounts. However, if you configure group accounts for additional companies, you must configure all possible From Account and To Account ranges for these companies. In addition, you must always configure the entire range of accounts for company 00000.
The following is an example of the file_group_account_codes_jde.csv opened in a text editor:
| COMPANY | FROM ACCT | TO ACCT | GROUP_ACCT_NUM |
|---|---|---|---|
| 00000 | 4100 | 4190 | AP |
| 00000 | 1200 | 1299 | AR |
| 00000 | 2120 | 2195 | ACC DEPCN |
| 00000 | 4200 | 4211 | ACC LIAB |
| 00000 | 1100 | 1121 | CASH |
| 00000 | 4900 | 4910 | CMMN STOCK |
| 00000 | 1401 | 1469 | FG INV |
| 00000 | 3990 | 3990 | GOODWILL |
| 00000 | 4690 | 4690 | LT DEBT |
| 00000 | 3900 | 3940 | OTHER ASSET |
| 00000 | 1310 | 1400 | OTHER CA |
| 00000 | 4212 | 4550 | OTHER CL |
| 00000 | 4950 | 4950 | OTHER EQUITY |
| 00000 | 4610 | 4685 | OTHER LIAB |
The file_grpact_fstmt.csv file specifies the relationship between a group account number and a Financial Statement Item code. The table below shows the Financial Statement Item codes to which Group Account Numbers must map, and their associated base fact tables.
| Financial Statement Item Codes | Base Fact Tables |
|---|---|
| AP | AP base fact (W_AP_XACT_F) |
| AR | AR base fact (W_AR_XACT_F) |
| COGS | Cost of Goods Sold base fact (W_GL_COGS_F) |
| REVENUE | Revenue base fact (W_GL_REVN_F) |
| TAX | Tax base fact (W_TAX_XACT_F) |
| OTHERS | GL Journal base fact (W_GL_OTHER_F) |
By mapping your GL accounts against the group account numbers and then associating the group account number to a Financial Statement Item code, you have indirectly associated the GL account numbers to Financial Statement Item codes as well.
Note:
Oracle's JD Edwards EnterpriseOne and JD Edwards World adapters for Financial Analytics do not support the Tax base fact (W_TAX_XACT_F).Account dimension mappings for Oracle's JD Edwards EnterpriseOne and JD Edwards World generate hierarchies for each AID (Account ID) based on the LDA (Level of Detail). This is a relative hierarchy dependant on the order of incoming records. The following example shows a portion of the incoming data and the hierarchy that is generated:
The first five columns depict the incoming data and the hierarchy.
The ETL process uses the following logic to generate the hierarchy:
The Order By clause on CO, MCU, OBJ, and LDA source columns gives the relative position of the AID among these columns, therefore the previous records affect the current record when they belong to the same CO-MCU combination.
The flattening begins at the base (HIER9) and continues through the end (HIER20).
HIER1 and HIER2 are always Company and Business Unit.
HIER3 onward are the relative positions of AIDs based on the current level of detail relative to the previous level of detail for the same CO - Business Unit combination.
Midway gaps are filled by the previous hierarchy, for example if LDA 5 comes immediately after a LDA 3 record, HIER4 is filled with the AID at LDA 3.
A CO-MCU combination beginning with an LDA 4 and without an LDA3 reuses the account at LDA 4 for LDA 3. In logic terms, for a particular CO-MCU, the highest incoming LDA (smallest in number) encountered is treated as the highest HIER and used for all hierarchies higher than that incoming LDA. When a new LDA with an even higher rank is encountered, this LDA is promoted instead. For example, if the first record (or highest record according to the Order By clause) for a certain MCU-OBJ combination has an LDA of 5, then HIER 3 and 4 (combinations greater than 5) reuse the value at 5 until another 5 or higher than 5 (such as 3 or 4) is encountered.
The hierarchy logic is LDA driven; accounts at a particular LDA will occupy the exact hierarchy equivalent to its LDA. For example, if AID 00235684 is at LDA 5, then HIER5 will have 00235684 from where you determine where further incoming values belong in relation to this. The example above shows the AIDs occupying the hierarchy number equivalent to their LDAs. In effect, any account will occupy a hierarchy level directly matched with its original LDA.
The concept of Rate Type in Oracle's JD Edwards EnterpriseOne and JD Edwards World is different than how it is defined in the Oracle Business Analytics Warehouse. In Oracle's JD Edwards EnterpriseOne and JD Edwards World, the rate type is an optional key; it is not used during exchange rate calculations.
DAC uses the $$JDE_RATE_TYPE source system parameter to populate the Rate_Type field in the W_EXCH_RATE_GS table. By default, the $$JDE_RATE_TYPE source system parameter in DAC has a value of "Actual."
The query and lookup on W_EXCH_RATE_G will fail if the RATE_TYPE field in the W_EXCH_RATE_G table does not contain the same value as the GLOBAL1_RATE_TYPE, GLOBAL2_RATE_TYPE 2 and GLOBAL3_RATE_TYPE fields in the W_GLOBAL_CURR_G table.
Not applicable to Oracle BI Applications Version 7.9.6.1.
|
 Copyright © 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
