| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.4 Part Number E35272-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.4 Part Number E35272-01 |
|
|
PDF · Mobi · ePub |
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 must first perform the steps in Section 3.1, "Source-Independent Configuration Steps."
You must then perform the tasks in one of the following sections depending on the type of your source system:
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.
This section contains configuration steps that apply to Oracle BI Applications deployed with any source system. It contains the following topics:
Section 3.1.5, "About Mapping GL Accounts to Group Account Numbers for All Source Systems"
Section 3.1.6, "Configuration Steps for Controlling Your Data Set for All Source Systems"
Section 3.1.7, "About Support for Back-Dated Change Scenarios"
Section 3.1.8, "Supported Back-Dated Change Scenarios That Affect Position Hierarchy"
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, ensure 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, go to the Design view, and select the appropriate custom container from the drop-down list.
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.
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, go to the Design view, and select the appropriate custom container from the drop-down list.
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 first global currency).
$$GLOBAL2_CURR_CODE (for the second global currency).
$$GLOBAL3_CURR_CODE (for the third global currency).
You can specify any currency, provided an exchange rate is specified for the conversion to the local currency. Ensure 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 enables 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 enables 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, go to the Design view, and select the appropriate custom container from the drop-down list.
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 Set Up an Enterprise Calendar Using an Oracle EBS Source System"
Section 3.1.4.7, "How to Set Up an Enterprise Calendar Using a PeopleSoft Source System"
Section 3.1.4.12, "How to Use a Fiscal Calendar Loaded Through the Universal Adapter"
Section 3.1.4.13, "How to Use a PeopleSoft Summary Calendar"
Section 3.1.4.14, "Examples of Configuring the Universal Adapter"
Section 3.1.4.15, "How to Reload the Time Dimension Tables After the Data Warehouse Is Loaded"
Oracle Business Intelligence Applications Release 7.9.6.4 supports the following calendar formats:
Enterprise (Global) - fiscal reporting calendar for cross functional analysis.
Fiscal - accounting or financial calendar.
Gregorian - regular calendar that starts on January 1st and ends on December 31st.
13 Period - each year consists 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 by default. 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 initialization block errors (for example, nQSError:43059).
To enable initialization blocks for multi-calendar:
Using the Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obisn\repository
Choose Manage, then Variables to display the Variable Manager.
In the left-hand pane, select Session, then Initialization Blocks.
For each initialization block listed in this section, right-click the initialization block name in the right-hand pane, then select Enable.
Restart the Oracle BI Server.
This section describes the tables used for Time Dimension calendars (including Gregorian calendar, Fiscal calendar, and Enterprise Calendar).
W_WEEK_D
W_MONTH_D
W_QTR_D
W_YEAR_D
W_DAY_D
W_MCAL_DAY_D
W_MCAL_WEEK_D
W_MCAL_PERIOD_D
W_MCAL_QTR_D
W_MCAL_YEAR_D
W_ENT_WEEK_D
W_ENT_PERIOD_D
W_ENT_QTR_D
W_ENT_YEAR_D
Table 3-1 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 later:
W_FSCL_WEEK_D
W_FSCL_MONTH_D
W_FSCL_QTR_D
W_FSCL_YEAR_D
Note that the tables listed in the preceding list were populated through CSV files in prior releases. 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 through 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. See Section 3.1.4.4, "How to Set Up the Gregorian Calendar Date Range" for more information.
W_DAY_D is the base table that represents the time dimension in the Oracle Business Analytics Warehouse. This table must 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 must be set up 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. After 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 must 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 the 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_DATASOURCE_NUM_ID
The following sections show how to set up 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 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, 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 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, PeopleSoft and Oracle, and the Global Calendar is from Oracle source, then this parameter value should specify an Oracle data source.
Scenario 3: Using a warehouse generated calendar as the Enterprise calendar
Source System DAC Parameters for Generated Enterprise Calendars:
$$GBL_CALENDAR_ID: This parameter 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 a Generated Calendar: It should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse).
Scenario 4: Using a fiscal calendar loaded through the Universal Adapter as the Enterprise calendar
Source System DAC Parameters for Universal Enterprise Calendars:
$$GBL_CALENDAR_ID: This parameter 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 Calendar, 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 the 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, Oracle and Oracle JD Edwards EnterpriseOne, and the Global Calendar is from an 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, 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 Release 7.9.6.4 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 look up the calendar ID for a given ledger or OU (Operating Unit). This must 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 must 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 (for example, 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.
Table 3-2 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, such as 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. For example, 4-4-5, 5-4-4, 4-5-4, or 13 period. |
|
REFERENCE_DATE |
The date (in MMDD format) that the fiscal year starts. For example, if an organization's fiscal year is from October to September, then the reference_date value should be 0929. The previous fiscal year must end between three days before and three days after the date specified by REFERENCE_DATE (that is, within the range (REFERENCE_DATE - 3) to (REFERENCE_DATE + 3). In other words, if a REFERENCE_DATE is 0131 for 31st January, the previous fiscal year cannot exceed 3rd February. |
|
Other standard columns |
W_INSERT_DT, W_UPDATE_DT, TENANT_ID, X_CUSTOM, and so on. |
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 prerequisite 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, go to the Design view, and select the appropriate custom container from the drop-down list.
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. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.
This configuration step must 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 Table 3-4.
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, and so on. |
Because 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 must 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 following image shows example values in a file_summary_calendar.csv file.
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
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. Use the following EBS Source query to identify possible values for this parameter:
SELECT period_set_name || '~'|| period_type FROM gl_periods;
This query returns a list of choices for the $$GBL_CALENDAR_ID parameter. To select the Enterprise Calendar, use one of the returned values as the value for $$GBL_CALENDAR_ID. Note that the format of the parameter $$GBL_CALENDAR_ID is a concatenation of the PERIOD_SET_NAME and PERIOD_TYPE.
$$GBL_DATASOURCE_NUM_ID: This parameter is the data_source_num_id with which you have loaded the EBS calendars.
Note:
The tasks that load the Enterprise calendar will run as part of the Execution Plan for your Subject Area. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
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. Use the following PeopleSoft Source query to identify possible values for this parameter:
SELECT SETID|| '~'|| CALENDAR_ID FROM PS_CAL_DEFN_TBL;
This query returns a list of choices for the $$GBL_CALENDAR_ID parameter. To select the Enterprise Calendar, use one of the returned values as the value for $$GBL_CALENDAR_ID. Note that the format of the parameter $$GBL_CALENDAR_ID is a concatenation of the SETID and CALENDAR_ID.
$$GBL_DATASOURCE_NUM_ID: This parameter is the data_source_num_id with which you have loaded the PeopleSoft calendars.
Note:
The tasks that load the Enterprise calendar will run as part of the Execution Plan for your Subject Area. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
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. Use the following Oracle JD Edwards EnterpriseOne Source query to identify possible values for this parameter:
SELECT DISTINCT CDDTPN FROM F0008
This query returns a list of choices for the $$GBL_CALENDAR_ID parameter. To select the Enterprise Calendar, use one of the returned values as the value for $$GBL_CALENDAR_ID. In the default configuration, the default calendar is "R."
$$GBL_DATASOURCE_NUM_ID: This parameter is the data_source_num_id with which you have loaded the Oracle JD Edwards calendars.
Note:
The tasks that load the Enterprise calendar will run as part of the Execution Plan for your Subject Area. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
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. Use the following Oracle JD Edwards World Source query to identify possible values for this parameter:
SELECT DISTINCT CDDTPN FROM F0008
This query returns a list of choices for the $$GBL_CALENDAR_ID parameter. To select the Enterprise Calendar, use one of the returned values as the value for $$GBL_CALENDAR_ID. In the default configuration, the default calendar is "R."
$$GBL_DATASOURCE_NUM_ID: This parameter is the data_source_num_id with which you have loaded the Oracle JD Edwards calendars.
Note:
The tasks that load the Enterprise calendar will run as part of the Execution Plan for your Subject Area. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
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 13P_CALENDAR_ID to 10001.
Note: The task SIL_TimeDImension_McalWeek13Period will run as part of the Execution Plan for your Subject Area. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
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. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
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 Calendar: 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 must be brought in using the following 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 must be unique.
Example file_mcal_config_g.csv 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.csv Settings
Notes:
DATE columns should be of the format YYYYMMDDHHMMSS (for example, 20000202000000).
Example file_file_mcal_period_ds.csv 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 of the warehouse. Subsequently, the SIL_%_UpdateFlag mappings run during each incremental run 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. There are also other incremental tasks that run to populate the W_MCAL_% tables (Tables for Multiple Calendar Support). The range of dates for which data is loaded into the time dimension tables is determined by the parameters $$START_DATE and $$END_DATE.
Extending the data in the time dimension tables prior to the occurrence of $$END_DATE is recommended. If extension is not done prior to the occurrence of $$END_DATE, this will lead to failures, documented in technical note 1063484.1 on My Oracle Support.
In V7.9.6.3, there is an Execution Plan provided for each container in DAC, to extend the Day Dimension when required. These Execution Plans includes all time dimension related tasks and initiates a full load ETL run of W_DAY_D and all the related time dimension tables, including the W_MCAL_% tables.
To extend the Day Dimension:
A Subjects Area named 'Common dimension Extend Day Dimension' has been provided to facilitate the extension of the day dimension and related tables. By default, this Subjects Area does not include the W_MCAL_% related tasks. If you have Multiple calendar support enabled in your Day Dimension, then include the configuration tag named 'Extend Day Dimension Multiple Calendar Support.' Then, assemble this Subject Area.
Note: Only the Subjects Area 'Common dimension Extend Day Dimension' should be included for extension. Do not include any other Subjects Areas in this extension Execution Plan.
Build the corresponding Execution Plan (EP) for this Subjects Area with same name.
Choose the Task SIL_DayDimension_XTND within the Execution Plan, and set the parameter values as follows:
$$START_DATE: Set this parameter to one day more than the old $$END_DATE value ($$END_DATE +1).
$$END_DATE: Set this parameter to the new end date to which you want to extend.
Choose the SIL_TimeDimension_MCalPeriod_XTND within the Execution Plan and set the parameter values. Retain the original $$START_DATE, and choose a new $$END_DATE to which you want to extend. The $$END_DATE value should be same as the $$END_DATE value you chose for the task SIL_DayDimension_XTND in step 3. Note that this step is only needed if the configuration tag 'Extend Day Dimension Multiple Calendar Support' was included in step 1.
If necessary, change the following CSV files:
FILE_MCAL_CAL_D
FILE_MCAL_CONTEXT_G
FILE_MCAL_PERIOD_DS (if using the Universal Adapter)
FILE_MCAL_CONFIG_G
If any of these files are used as sources, they must be extended for the new date ranges.
Run this Execution Plan and verify that the dates in W_D_DAY and related tables have been extended.
Rebuild all affected fact aggregates. For more information on which aggregates to rebuild, as well as detailed steps on how to rebuild them, refer to technical note 1063484.1 on My Oracle Support. Note that technical note 1063484.1 was written for pre-7.9.6.3 customers.
Technical note 1063484 also provides steps for extending the time dimension and related tables. You can follow the steps in the technical note as an alternative to using the steps in this procedure.
This section provides information about mapping General Ledger Accounts to Group Account Numbers.
To gain a better understanding of the purpose of Group Accounts, you can look at one of the pre-built reports in the Financial Analytics General Ledger Dashboard, the Monthly Balance Sheet report. The following example focuses on the Prepaid Expenses metric and explains how it is computed.
The Prepaid Expenses metric in the report comes directly from the "Facts - Balance Sheet Statement" table within the "Financials - GL Balance Sheet" Subjects Area in the Presentation layer of the RPD file. The Presentation layer metric, in turn, comes from a corresponding item in the Business Model and Mapping layer.
Figure 3-3 shows the Monthly Balance Sheet Report and the corresponding metrics in the RPD file.
The fact table (the GL Balance fact) in the Business Model and Mapping layer joins with the GL Account Dimension and filters for the GL Accounts that belong to the Group Account Number 'PPAID EXP.'
When you look at the physical table W_GL_BALANCE_F, you can see that the metric is calculated in this case from one GL Account (1340), which is associated to the group account number 'PPAID EXP.' (Note that this example does not include other dimensionality, such as ledger, for the sake of simplicity).
To validate that account 1340 belongs to the Group Account Number PPAID EXP, you can view the configuration for the Group Account Number mapping in the file_group_acct_codes_source.csv file (for example, file_group_acct_codes_ora.csv for Oracle EBS).
Figure 3-4 shows the W_GL_BALANCE_F table and the corresponding RPD metrics.
See also the following source-specific sections on this topic:
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 at installation. 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 the 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 |
|---|---|
|
JDE_8_11_SP1_CTRL JDE_8_11_SP1_DATA JDE_8_11_SP1_DD JDE_8_11_SP1_FlatFile JDE_8_11_SP1_SYS |
17 |
|
JDE_8.12_CTRL JDE_8.12_DATA JDE_8.12_DD JDE_8_12_FlatFile JDE_8.12_SYS |
15 |
|
JDE_9.0_CTRL JDE_9.0_DATA JDE_9.0_DD JDE_9_0_FlatFile JDE_9.0_SYS |
25 |
|
JDEW_9_2_CTRL JDEW_9_2_DATA JDEW_9_2_DD JDEW_9_2_FlatFile JDEW_9_2_SYS |
24 |
|
ORA_11_5_8 ORA_11_5_8_Flatfile |
2 |
|
ORA_11_5_9 ORA_11_5_9_Flatfile |
5 |
|
ORA_11_5_10 ORA_11_5_10_Flatfile |
4 |
|
ORA_R12 ORA_R12_Flatfile |
9 |
|
ORA_R1211 ORA_R1211_Flatfile |
26 |
|
ORA_R1212 ORA_R1212_Flatfile |
27 |
|
ORA_R1213 ORA_R1213_Flatfile |
1000 |
|
PSFT_8_4_FINSCM PSFT_8_4_FlatFile |
7 |
|
PSFT_8_8_FINSCM PSFT_8_8_FlatFile |
8 |
|
PSFT_8_8_HCM |
6 |
|
PSFT_8_9_FINSCM PSFT_8_9_FlatFile |
11 |
|
PSFT_8_9_HCM PSFT_8_9_HCM_FlatFile |
10 |
|
PSFT_9_0_ELM PSFT_9_0_HCM PSFT_9_0_HCM_FlatFile |
12 |
|
PSFT_9_0_FINSCM PSFT_9_0_FlatFile |
13 |
|
PSFT_9_1_ELM PSFT_9_1_HCM PSFT_9_1_HCM_FlatFile |
28 |
|
PSFT_9_1_FINSCM PSFT_9_1_FlatFile |
29 |
|
SEBL_63 SEBL_6_3_Flatfile |
1 |
|
SEBL_753 SEBL_7_5_3_Flatfile |
1 |
|
SEBL_771 SEBL_7_7_1_Flatfile |
1 |
|
SEBL_78 SEBL_7_8_Flatfile |
1 |
|
SEBL_80 SEBL_8_0_Flatfile |
1 |
|
SEBL_811 SEBL_8_1_1_Flatfile |
1 |
|
SEBL_VERT_753 SEBL_VERT_7_5_3_Flatfile |
1 |
|
SEBL_VERT_771 SEBL_VERT_7_7_1_Flatfile |
1 |
|
SEBL_VERT_78 SEBL_VERT_7_8_Flatfile |
1 |
|
SEBL_VERT_80 SEBL_VERT_80_Flatfile |
1 |
|
SEBL_VERT_811 SEBL_VERT_8_1_1_Flatfile |
1 |
|
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."
"Back-dated changes" refer to changes occurring in OLTP records after those records have already been processed and loaded into the warehouse. Position hierarchy represents the employee and supervisor reporting relationship that is maintained in the Oracle BI Applications data warehouse. In HR, it is the Supervisor Hierarchy; and in CRM, it is the Position Hierarchy. Position Hierarchy supports historical versions of the hierarchies with the current stamp on the current hierarchy rows. Position hierarchy is delivered with history tracking enabled, and the attributes that are tracked for Type 2 slowly changing are an employee's organization and supervisor. In addition, for CRM position hierarchy, a change in an employee's position is also tracked for Type 2 slowly changing.
Starting with release 7.9.6.2, back-dated changes and deletes in the OLTP that affect the supervisor and position hierarchy are processed accordingly by the ETL to reflect the retroactive changes in the warehouse.
The Position Hierarchy is a column-flattened structure that is loaded from the Position dimension, which maintains the parent-child relationships. The scenarios described in this section illustrate the effects of how back-dated changes affect the position dimension which in turn will affect the Position Hierarchy.
In an OLTP source system such as Oracle EBS, corrections can be made to historical rows or the current rows. Corrections can be made to OLTP tables that provide the primary data source for the data warehouse or to tables that serve as auxiliary lookups in the ETL process. Corrections can affect the following attributes:
Attributes that do not capture history, which are treated as a type 1 slowly changing dimension (SCD) in the data warehouse.
Attributes that capture history, which are treated as a type 2 SCD in the data warehouse.
Position hierarchy is affected by back-dated changes in different tables that directly or indirectly influence the ETL process. For example, Division Name can be changed retroactively without corresponding employee assignment changes in the source OLTP system. This back-dated Division Name change will trigger historical updates to the Position Hierarchy.
There are three main types of OLTP tables that affect Supervisor or Position Hierarchy. The following examples represent the tables in a source OLTP system:
Type A: The primary driving table that contains all historical assignment data, for example PER_ALL_ASSIGNMENTS_F. This table is the primary driver for the employee assignment history in the data warehouse.
Type B: Auxiliary source table which keeps no history (Type 1 SCD) in OLTP but the warehouse tracks attribute changes as Type 2 SCD, for example HR_ORGANIZATION_UNITS. This table is the source for attributes that are history tracked in the warehouse.
Type C: Auxiliary source table which keeps no history in OLTP and the warehouse also keeps no historical changes for the attribute (Type 1 SCD), such as PER_JOBS. This table is the source for attributes that are not history tracked in the warehouse.
The functionality in Oracle BI Applications Release 7.9.6 assumes that:
Position hierarchy tracks history as a type 2 SCD in the data warehouse
Division Name tracks history as a type 2 slowly changing attribute in the data warehouse
Person Name (a type 1 slowly changing attribute) does not track history in the data warehouse
Job (a type 1 slowly changing attribute) does not track history in the warehouse
The following table contains an example of employee P1's assignment history in the warehouse before the back-dated changes:
Table 3-6 Example of Employee P1's Assignment History Before the Back-Dated Changes
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
|
Row 1 |
P1 |
CRM |
Mgr1 |
2006 |
2008 |
|
Row 2 |
P1 |
My Division |
Mgr2 |
2008 |
2009 |
|
Row 3 |
P1 |
My Division |
Mgr3 |
2009 |
4712 |
The following sections describe the types of OLTP back-dated changes that affect the Position Hierarchy and the solutions provided in the warehouse to address the back-dated change scenarios.
A division name changed as a correction to the current record, for example Division Name 'My Division' was renamed to 'My New Division' in 2010. The employee did not change divisions as a result of a transfer. This is a Type B change as the source table does not track Division Name history, which is a Type 2 slowly changing attribute in the warehouse.
Option 1 This option is treated as a Type 2 (SCD) change and will introduce a new Type 2 (SCD) row in the warehouse. This option is enabled if DAC parameter UPDATE_CORRECTIONS_FLG is set to 'N'.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
|
P1 |
CRM |
Mgr1 |
2006 |
2008 |
|
P1 |
My Division |
Mgr2 |
2008 |
2009 |
|
P1 |
My Division |
Mgr3 |
2009 |
2010 |
|
P1 |
My New Division |
Mgr3 |
2010 |
4712 |
Option 2 This option is treated as a correction and will change historical data only without a new Type 2 row in the warehouse. This option is enabled if DAC parameter UPDATE_CORRECTIONS_FLG is set to 'Y'.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
|
P1 |
CRM |
Mgr1 |
2006 |
2008 |
|
P1 |
My New Division |
Mgr2 |
2008 |
2009 |
|
P1 |
My New Division |
Mgr3 |
2009 |
4712 |
A division name changed as a correction to a historical record, for example Division Name 'My Division' was retroactively renamed to 'MD' 2008. The employee did not change divisions as a result of a transfer. This is also a Type B change, as Division Name history is not tracked in the source OLTP system but is tracked as a Type 2 SCD in the data warehouse.
Option Update the name change in the relevant history records.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
|
P1 |
CRM |
Mgr1 |
2006 |
2008 |
|
P1 |
MD |
Mgr2 |
2008 |
2009 |
|
P1 |
My New Division |
Mgr3 |
2009 |
4712 |
Auxiliary table change: Job information is changed in the source OLTP system that is referred to in the historical data in the warehouse, for example Job name is changed to lower case. This is a Type C change where neither the OLTP nor the data warehouse track historical changes.
Option New Job is propagated to all historical rows.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
|
P1 |
CRM |
Mgr1, job2 |
2006 |
2008 |
|
P1 |
My Division |
Mgr2, job2 |
2008 |
2009 |
|
P1 |
My Division |
Mgr3, job2 |
2009 |
4712 |
An employee changed assignment divisions as a result of a transfer, for example Employee P1 transferred to division 'GRC' reporting to Mgr4 in 2010. This is a Type A change, as the change occurs on a main OLTP driving table with history tracking.
Option The data warehouse will insert a new row for tracking the employee as having a new manager. This is the standard case.
| Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|
|
P1 |
CRM |
Mgr1 |
2006 |
2008 |
|
P1 |
My Division |
Mgr2 |
2008 |
2009 |
|
P1 |
My Division |
Mgr3 |
2009 |
2010 |
|
P1 |
GRC |
Mgr4 |
2010 |
4712 |
This is a variation of Scenario 3, for example Employee's transfer from 'CRM' to 'My Division' actually occurred in 2007, not in 2008. This is a correction to the historical assignment record. The back-dated change is to the effective_from and effective_to date of the driving OLTP history table.
Option 1 Update the historical data in the warehouse. This does not require fact table updates. This option is enabled if DAC parameter UPDATE_CORRECTIONS_FLG is set to 'Y'.
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
|
1 |
P1 |
CRM |
Mgr1 |
2006 |
2007 |
|
2 |
P1 |
My Division |
Mgr2 |
2007 |
2009 |
|
3 |
P1 |
My Division |
Mgr3 |
2009 |
4712 |
Option 2 This option introduces a new warehouse row to track changes. This option is enabled if DAC parameter UPDATE_CORRECTIONS_FLG is set to 'N'.
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
|
1 |
P1 |
CRM |
Mgr1 |
2006 |
2007 |
|
4 |
P1 |
My Division |
Mgr2 |
2007 |
2008 (NEW) |
|
2 |
P1 |
My Division |
Mgr2 |
2008 |
2009 |
|
3 |
P1 |
My Division |
Mgr3 |
2009 |
4712 |
Prior to the back-dated change, the fact table had some transactions pointing to the row 1 of the hierarchy table and some with foreign keys to the row 2. Fact rows with foreign key to row 1 will continue to have the same foreign key or the foreign key will be updated to match either row 2 or row 4 depending on the transaction date.
A back-dated change occurs in the source OLTP system that results in the splitting of the record. For example, the employee changed managers from Mgr1 to Mgr5 in 2007. In the source OLTP system, the original assignment record with Mgr1 has a new end date of 2007 and a new record was added to assign the employee to the new manager Mgr5 in 2007.
Option The warehouse will respond to the OLTP source change as described in the following table.
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
|
1 |
P1 |
CRM |
Mgr1 |
2006 |
2007 |
|
4 |
P1 |
CRM |
Mgr5 |
2007 |
2008 (NEW) |
|
2 |
P1 |
My Division |
Mgr2 |
2008 |
2009 |
|
3 |
P1 |
My Division |
Mgr3 |
2009 |
4712 |
Fact rows with foreign keys to the row 1 will continue to have the same foreign key or the foreign key will be updated to row 4 depending on the transaction date.
A back-dated change gets cascaded to all the records since a particular date, for example Mgr2 in row 3 should be Mgr4 and also change managers for all future rows since 2008. The employee's current assignment records are as follows in the data warehouse:
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
|
1 |
P1 |
CRM |
Mgr1 |
2006 |
2007 |
|
2 |
P1 |
CRM |
Mgr5 |
2007 |
2008 (NEW) |
|
3 |
P1 |
My Division |
Mgr2 |
2008 |
2009 |
|
4 |
P1 |
My Division |
Mgr3 |
2009 |
4712 |
Option Update the historical and current records in the warehouse:
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
|
1 |
P1 |
CRM |
Mgr1 |
2006 |
2007 |
|
2 |
P1 |
CRM |
Mgr5 |
2007 |
2008 (NEW) |
|
3 |
P1 |
My Division |
Mgr4 |
2008 |
2009 |
|
4 |
P1 |
My Division |
Mgr4 |
2009 |
4712 |
A back-dated change is a row delete in OLTP. Row 2 is deleted in OLTP and row 1 is updated to the end date of 2009.
Option Transactions will not be deleted in the warehouse, but rows will be updated to reflect the changed attributes in the time range, including changes initiated by auxiliary tables. This will void fact foreign key updates.
| Row | Person | Division | Manager | Start Date | End Date |
|---|---|---|---|---|---|
|
1 |
P1 |
CRM |
Mgr1 |
2006 |
2008 |
|
2 |
P1 |
CRM |
Mgr1 |
2008 |
2009 (NEW) |
|
3 |
P1 |
My Division |
Mgr3 |
2009 |
4712 |
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 by default, 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.
Figure 3-5 shows an example of the Default Category Sets screen.
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 record 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 the appropriate 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 18.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. The codes are then loaded into the W_PROD_CAT_DH table by the SDE_UNSPSC process. You must then perform manual classification by mapping the codes in FILE_ITEM_TO_UNSPSC.csv to provide the link between the product (D) and product category (DH) tables, using the load process PLP_ItemToUNSPSC_Classification.
Note the following about UNSPSC codes:
You must buy your own UNSPSC data directly from UNSPSC.org, and then map the data in file_unspsc.csv for loading into Oracle BI Applications.
The file_unspsc.csv file is used by the SDE_UNSPSC process and contains sample data. Replace the sample data with your own UNSPSC data. Make sure to perform this task before performing the manual classification steps described later in this section.
The UNSPSC_CODE column must be unique for each record in the file. This column is specific to Oracle BI Applications and does not come from the purchased UNSPSC data. You can map this column to the COMMODITY_CODE column.
The FUNCTION_CODE and FUNCTION_NAME columns are additional columns that might be included in the purchased UNSPSC data. If you do not see these columns in the purchased UNSPSC data, you can map these columns to the COMMODITY_CODE and COMMODITY_NAME columns, respectively. These columns are used to copy the levels in the hierarchy down to the base level.
Ensure 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.
To assign UNSPSC codes to products:
To identify 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, PROD_NAME, PART_NUM FROM JPOLAPUSR.W_PRODUCT_D
Note: In this example SQL statement, the INTEGRATION_ID is the product that needs classification. The PROD_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, INFA_HOME\server\infa_shared\SrcFiles).
Populate the PRODUCT_ID field with the INTEGRATION_ID values you retrieved in Step 1. Populate the UNSPSC_CODE field with the UNSPSC Code values you purchased from UNSPSC.org. Also, ensure that the DATASOURCE_NUM_ID is the same as your Oracle EBS Adapter.
In Informatica PowerCenter Designer, run the \PLP\PLP_ItemToUNSPSC_Classification workflow to update the rows in the W_PRODUCT_D table.
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 must 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 the appropriate 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 of setting up Oracle General Ledger Accounts - see Section 3.2.1.4.1, "Overview of 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 that explains 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.
See also Section 3.1.5, "About Mapping GL Accounts to Group Account Numbers for All Source Systems" for additional information.
Group Account Number Configuration is an important step in the configuration of Financial Analytics, because it determines the accuracy of the majority of metrics in the General Ledger and Profitability module. Group Accounts in combination with Financial Statement Codes are also leveraged in the GL reconciliation process, to ensure that subledger data reconciles with GL journal entries. This topic is discussed in more detail later in this section.
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_fstmt.csv - this file maps Financial Statement Item Codes to group account codes.
Before you load your data, you must ensure that the account values are mapped consistently across these three configuration files. Figure 3-6 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, Profit and Loss, Cash Flow statements).
The logic for assigning the group accounts is located in the file_group_acct_codes_ora.csv file. Table 3-7 shows an example configuration of the file_group_acct_codes_ora.csv file.
Table 3-7 Example Configuration of file_group_acct_codes_ora.csv File
| 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 Table 3-7, 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 must also 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. Table 3-8 shows the Financial Statement Item codes to which Group Account Numbers must map, and their associated base fact tables.
Table 3-8 Financial Statement Item Codes and 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)Foot 1 |
|
OTHERS |
GL Journal base fact (W_GL_OTHER_F) |
Footnote 1 Oracle EBS adapters for Financial Analytics do not support the Tax base fact (W_TAX_XACT_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. This association is important to perform GL reconciliation and to ensure the subledger data reconciles with GL journal entries. It is possible that after an invoice has been transferred to GL, a GL user might adjust that invoice in GL. In this scenario, it is important to ensure that the adjustment amount is reflected in the subledger base fact as well as balance tables. To determine such subledger transactions in GL, the reconciliation process uses Financial Statement Item codes.
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. See Section 3.2.1.4.3, "Example of Adding Group Account Number Metrics to the Oracle BI Repository" for more information.
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, INFA_HOME\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, and so on. |
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.
Ensure 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.
See Figure 3-6, "Configuration Files for Configuring Group Accounts for Oracle EBS" for an example.
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 the Oracle BI Administration Tool.
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 the logical table Fact – Fins – GL Other Posted Transaction:
Using the Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obisn\repository
In the Business Model and Mapping layer:
Create a logical column named 'Payroll Expense' in the logical table 'Fact – Fins – GL Other Posted Transaction'.
For example, right-click 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, and then 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 Show unmapped columns.
Locate the Payroll Expense expression, and click the Expression Builder button to open Expression Builder.
Use Expression Builder 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 h appropriately for each LTS by using the appropriate fact table that corresponds to the Logical Table Source.
Steps d to h must be repeated for each Logical Table Source because in this example, there are 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 the logical table Fact – Fins – GL Balance:
Using the Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obisn\repository
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 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.
In the Column Source tab, select Derived from existing columns using an expression.
Click the Expression Builder button to display Expression Builder.
Use Express Builder 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 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.
Too revert these 'Manual' entries in the AR fact table, use the 'Group Account Number Clean Up' 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 reassigned the natural account 1210 to the 'AP' group account number in the file_group_acct_codes_ora.csv file.
Note:
The Financials – Group Account Number Clean Up Subjects Area should be executed only when you need to correct group account number assignments. This Subjects Area should not be included in the standard Financials Execution Plan. You must run it separately.
To do group account correction:
Correct the mapping of GL natural account to the group account in the input CSV file called 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 Clean Up' Subject Area and do the following:
- If in the preceding step, 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 preceding step, '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 preceding step, then you must 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 installed Execution Plans, such as 'Financials – Group Account Number Clean Up R12' or 'Financials – Group Account Number Clean Up R1211'.
You must 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 Segments and Segment Hierarchies Using Flexfield Value Set 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.
Figure 3-7 shows an example of this hierarchy for US Acct.
Table 3-9 shows how the hierarchy for US Acct would be stored in the W_HIERARCHY_D table.
Table 3-9 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-10 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 5 |
|
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 ensure 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.
How to Set Up the GL Segment Configuration File
Before you run the ETL process for GL accounts, you must 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, INFA_HOME\server\infa_shared\SrcFiles).
In file_glacct_segment_config_source_system.csv, you must 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 Company segments from all COAs in ACCOUNT_SEG1_CODE column in W_GL_ACCOUNT_D.
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.
Store GL account balances at Company and Cost Center level instead of at GL Code Combination level, in W_GL_BALANCE_A (where you store GL account balances at aggregated 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 installed setting for this value is Y for SEG1 through SEG6 (that is, "AGGREGATION,Y, ,Y, ,Y, ,Y, ,Y, ,Y").
Set up the GL Segment configuration file for U.S. Federal Financial 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:
1. Put your Fund segment column name in the 'SEGMENT1' column in the CSV file.
2. 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 might have other than Fund and Program, configure these segments starting from SEGMENT3.
Steps to Configure GL Segments and Hierarchies Using Value Set Definitions
Configure file_glacct_segment_config_source_system.csv, as follows:
Navigate to $PMServer\SrcFiles (for example, INFA_HOME\server\infa_shared\SrcFiles).
Open file_glacct_segment_config_source_system.csv in a text editor.
Follow the steps in "How to Set Up the GL Segment Configuration File" to configure the file.
Perform the following steps in DAC if you are extracting the Value Set Hierarchies for your GL Segments. If you are planning to use only the GL Segments without the value set hierarchies, skip this step and proceed to step 3.
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.
Do one of the following:
If you have not created an Execution Plan, then create a new Execution Plan and include the 'Financials – General Ledger' Subject Area.
If you have created an Execution Plan that contains the 'Financials – General Ledger' Subject Area, then rebuild it.
For instructions on building Execution Plans, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.
Execute the Execution Plan that contains the 'Financials – General Ledger' Subject Area.
Make the following changes in the RPD metadata using the Oracle BI Administration Tool. The metadata contains multiple logical tables that represent each GL Segment, such as Dim – GL Segment1, Dim – GL Segment2, and so on. Because all these logical tables are mapped to the same physical table, W_GL_SEGMENT_D, a filter should be specified in the logical table source of these logical tables 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 the Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obisn\repository
Expand each logical table, for example, Dim - GL Segment1, and open the logical table source under it.
Display the Content tab.
In the 'Use this WHERE clause…' box, apply a filter on the corresponding physical table alias of W_GL_SEGMENT_D. For example:
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_D_Segment1"."SEGMENT_LOV_ID" IN (comma seperated 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 by default delivers 10 GL Segment dimensions in the RPD. If you need more than 10 GL Segments, perform the following steps to add new segments:
In the Physical layer:
Create a new physical alias of W_GL_SEGMENT_D as "Dim_W_GL_SEGMENT_D_SegmentXX". To do this, right-click the physical table W_GL_SEGMENT_D and select New Object and then Alias. Name the new alias Dim_W_GL_SEGMENT_D_SegmentXX. Similarly, create a new alias of W_HIERARCHY_D as "Dim_W_HIERARCHY_D_SegmentXX".
In the Physical Diagram, create a Physical Foreign Key between Dim_W_HIERARCHY_D_SegmentXX and Dim_W_GL_SEGMENT_D_SegmentXX similar to the one between Dim_W_HIERARCHY_D_Segment1 and Dim_W_GL_SEGMENT_D_Segment1. The direction of the foreign key should be from W_HIERACHY_D to W_GL_SEGMENT_D; for example, on a '0/1':N cardinality join, W_HIERARCHY_D will be on the '0/1' side and W_GL_SEGMENT_D will be on the 'N' side. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information about how to create physical foreign key joins.
Similarly, create a physical foreign key join between Dim_W_GL_SEGMENT_D_SegmentXX and Dim_W_GL_ACCOUNT_D, with W_GL_SEGMENT_D on the '1' side and W_GL_ACCOUNT_D on the 'N' side.
Save your changes.
In the Business Model and Mapping layer:
Create a new logical table "Dim - GL SegmentXX" similar to "Dim – GL Segment1". This logical table should have a logical table source that is mapped to the physical tables created above (for example, it will have both Dim_W_GL_SEGMENT_D_SegmentXX and Dim_W_HIERARCHY_D_SegmentXX). This logical table should also have all attributes similar to "Dim – GL Segment1" properly mapped to the respective physical tables, Dim_W_GL_SEGMENT_D_SegmentXX and Dim_W_HIERARCHY_D_SegmentXX.
In the Business Model Diagram, create a logical join from "Dim – GL SegmentXX" to all the relevant logical fact tables similar to "Dim – GL Segment1", with the GL Segment Dimension Logical table on the '0/1' side and the logical fact table on the 'N' side. To see all the relevant logical fact tables, first include Dim – GL Segment1 on the Business Model Diagram, and then right-click that table and select Add Direct Joins.
Add the content filter in the logical table source of "Dim – GL SegmentXX" as described in the previous step.
Create a dimension by right-clicking "Dim – GL SegmentXX", and select Create Dimension. Rename this to "GL SegmentXX". Make sure the drill-down structure is similar to "GL Segment1". If you are not sure how to do this, follow these steps: By default, the dimension will have two levels: the Grand Total Level and the Detail Level. Rename these levels to "All" and "Detail" respectively. Right-click the "All" level and select "New Object" and then "Child Level". Name this level as Level1. Similarly, create a level under Level1 and name it as Level2. Repeat this process until you have Level19 under Level18. Now drag the "Detail" level under "Level19" so that "Detail" is the last level of the hierarchy. Now, from the new logical table Dim - GL SegmentXX, drag Level1 Code and Level1 Name attributes to the "Level1" level of the hierarchy. Now go the Properties of the Level and from the Keys tab, create two new keys, one for Level1 Code and one for Level1 Name. When you create keys, make sure the 'Use for Drilldown' option is OFF for Level1 Code and ON for Level1 Name. Also, make sure the Primary Key drop-down is set to Level1 Code. Now, proceed similarly to all 19 levels by dragging the appropriate two attributes to the appropriate levels and create the keys similar to above. For the "Detail" level, drag the Level20 Code and Level20 Name attributes into it and create the keys similar to above.
Open the Logical Table Source of the new logical table created, Dim - GL SegmentXX. Set the Aggregation Content in the Content tab by setting the Logical Level to "Detail" level of the "GL SegmentXX" dimension/hierarchy created above.
Similarly, set the aggregation content to all relevant fact logical table sources. Open all Logical Table Sources of all the logical fact tables that are relevant to the new logical table one at a time. Go to the Content tab. If you see that it is set to the "Detail" level of other GL Segment Dimensions such as GL Segment1, GL Segment2, and so on, then set it to the "Detail" level of the "GL Segment XX" dimension/hierarchy created above. If not, skip that logical table source and go to the next one.
Drag your new "Dim - GL Segment XX" dimensions into the appropriate Subjects Areas in the Presentation layer. Typically, you can expose these GL Segment dimensions in all Subjects Areas where the GL Account dimension is exposed. You can also find all appropriate Subjects Areas by right-clicking Dim – GL Segment1 and select Query Related Objects, then selecting Presentation, and then selecting Subject Area.
Save your changes and check global consistency.
Each GL Segment denotes a certain meaningful ValueSet(s) in your OLTP. To clearly identify each segment in the report, you can rename the presentation table "GL SegmentX", logical dimension "GL SegmentX", and logical table "Dim - GL SegmentX" according to its own meaning.
For example, if you populate Product segment into Segment1, you can rename logical table "Dim - GL Segment1" as "Dim – GL Segment Product" or any other appropriate name and then rename the tables in the Presentation layer 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 following diagram 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 following diagram shows how an income state is derived from a hierarchy:
This hierarchy would be converted into a flattened hierarchy and stored in W_HIERARCHY_D in the following format:
Table 3-11 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 through 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, as shown in the following diagram:
About the ETL Process for Oracle FSG Report
Before you run the ETL process for GL accounts, you must 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, INFA_HOME\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 must 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 fields set as follows:
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 the file_gl_hierarchy_assignment_ora.csv file to specify the hierarchies you want to reference for each CHART_OF_ACCOUNTS.
Navigate to $PMServer\SrcFiles (for example, INFA_HOME\server\infa_shared\SrcFiles).
Open file_gl_hierarchy_assignment_ora.csv in a text editor.
Specify the segments you want to analyze.
Save and close the file.
In DAC, do the following:
Go to the Design view, and select the appropriate custom container from the drop-down list.
Click the Subject Areas tab, and query for all Financials Subjects Areas.
For each of these Subjects Areas, in the Configuration Tags subtab, check to see whether the Subjects Area has the following two configuration tags:
Oracle - Extract FSG Hierarchies
Oracle - Extract Value Set Hierarchies
For Subjects Areas with these two tags, do the following:
Activate the 'Oracle – Extract FSG Hierarchies' tag by deselecting the Inactive check box.
Deactivate the 'Oracle – Extract Value Set Hierarchies' tag by selecting the Inactive check box.
Click Assemble to reassemble the Subjects Area.
Do one of the following:
If you have not created an Execution Plan, then create a new Execution Plan and include the Financials Subject Areas.
If you have created an Execution Plan that contains the Financials Subject Areas, then rebuild them.
For instructions on building Execution Plans, see Oracle Business Intelligence Data Warehouse Administration Console User's Guide.
Execute the Execution Plan that contains the Financials Subject Areas.
Using the 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 the 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 the Oracle BI Administration Tool, in the Business Model and Mapping 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 the Oracle BI Administration Tool, in the Business Model and Mapping 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 and Mapping layer, look for all the logical fact tables that have logical joins to the logical hierarchy table Dim - FSG Hierarchy1. You must 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 tables, 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 logical 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 the 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 topic:
The Make-Buy indicator specifies whether a product material is made in-house or bought from an outside vendor. At installation, 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 the default ETL mappings.
If your organization uses different indicator codes, you must follow the steps provided in this section to change the indicator mapping in the mapplet mplt_SA_ORA_ProductDimension. For example, you might 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_ORAVersion_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:
Section 3.3.1.1, "About the GL Account Dimension and Chartfields for PeopleSoft"
Section 3.3.1.2, "About Mapping PeopleSoft GL Accounts to Group Account Numbers"
Section 3.3.1.3, "How to Make Corrections to the Group Account Number Configuration"
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, INFA_HOME\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 following list shows the chartfields currently supported for the PeopleSoft application.
Note:
Values are case sensitive. You must specify the values exactly as shown in the following list.
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
Statistics Code
PC Business Unit
Activity ID
Analysis Type
Resource Type
Resource Category
Resource Sub Category
Note:
You only need to include the chartfields in the CSV file that you want to map.
Configuring Projects-Specific Chartfields To use the six chartfields related to Projects (PC Business Unit, Activity ID, Analysis Type, Resource Type, Resource Category, and Resource Sub Category), you must set the DAC parameter $$INCLUDE_PROJ_CHARTFIELDS to 'Y.' You only need to perform this procedure if you want to leverage the six Projects chartfields in your analyses.
To include Projects-specific chartfields in DAC:
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Source System Parameters tab.
Set the parameter $$INCLUDE_PROJ_CHARTFIELDS to 'Y'.
Run a full ETL.
If you do not use these chartfields, then you can set the parameter $$INCLUDE_PROJ_CHARTFIELDS to a default value 'N' in DAC.
Note:
At any point, if you change this parameter value, then a full load ETL must be run.
This section explains how to map General Ledger Accounts to Group Account Numbers, and includes the following topics:
An overview of setting up General Ledger Accounts - see Section 3.3.1.2.1, "Overview of Mapping GL Accounts to Group Account Numbers."
A description of how to map General Ledger Accounts to Group Account Numbers - see Section 3.3.1.2.2, "How to Map GL Account Numbers to Group Account Numbers."
An example that explains how to add Group Account Number metrics to the Oracle BI Repository - see Section 3.3.1.2.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.
See also Section 3.1.5, "About Mapping GL Accounts to Group Account Numbers for All Source Systems" for additional information.
Group Account Number Configuration is an important step in the configuration of Financial Analytics, as it determines the accuracy of the majority of metrics in the General Ledger and Profitability module. Group Accounts in combination with Financial Statement Codes are also leveraged in the GL reconciliation process, to ensure that subledger data reconciles with GL journal entries. This topic is discussed in more detail later in this section.
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.
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_psft.csv - this file maps General Ledger accounts to group account codes.
file_grpact_fstmt.csv - this file maps Financial Statement Item Codes to group account codes.
Before you load your data, you must ensure that the account values are mapped consistently across these three configuration files. Figure 3-10 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_psft.csv and the GROUP_ACCT_NUM field in file_grpact_fstmt.csv.
Examples include Cash account, Payroll account, and so on. For a list of the Group Account Number domain values, see Oracle Business Analytics Warehouse 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, INFA_HOME\server\infa_shared\SrcFiles).
Table 3-12 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 |
TAXFoot 1 |
Footnote 1 Oracle's PeopleSoft adapters for Financial Analytics do not support the Tax base fact (W_TAX_XACT_F).
In Table 3-12, 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.
The file_grpact_fstmt.csv file specifies the relationship between a group account number and a Financial Statement Item code. Table 3-13 shows the Financial Statement Item codes to which Group Account Numbers must map, and their associated base fact tables.
Table 3-13 Financial Statement Item Codes and 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)Foot 1 |
|
OTHERS |
GL Journal base fact (W_GL_OTHER_F) |
Footnote 1 Oracle's PeopleSoft adapters for Financial Analytics do not support the Tax base fact (W_TAX_XACT_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. This association is important to perform GL reconciliation and ensure the subledger data reconciles with GL journal entries. It is possible that after an invoice has been transferred to GL, a GL user might adjust that invoice in GL. In this scenario, it is important to ensure that the adjustment amount is reflected in the subledger base fact as well as balance tables. To determine such subledger transactions in GL, the reconciliation process uses Financial Statement Item codes.
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.
This section explains how to map General Ledger Account Numbers to Group Account Numbers.
Note:
If you add new Group Account Numbers to the file_group_acct_codes_psft.csv file, you must also add metrics to the Oracle BI repository. See Section 3.3.1.2.3, "Example of Adding Group Account Number Metrics to the Oracle BI Repository" for more information.
To map PeopleSoft GL account numbers to group account numbers:
Open the file_group_acct_codes_psft.csv file with a text editor in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).
For each GL account number that you want to map, create a new row in the file containing the following fields:
| Field Name | Description |
|---|---|
|
BUSINESS_UNIT |
The ID of the BUSINESS UNIT. |
|
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 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, and so on. |
For example:
AUS01, 1110, 1110, CASH AUS01, 1210, 1210, AR AUS01, 1220, 1220, AR
Note:
You can optionally remove the unused rows in the CSV file.
Ensure that the values that you specify in the file_group_acct_codes_psft.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.
See Figure 3-10, "Configuration Files for Configuring Group Accounts for PeopleSoft" for an example.
Save and close the CSV file.
If you add new Group Account Numbers to the file_group_acct_codes_psft.csv file (as described in Section 3.3.1.2.2, "How to Map 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 the Oracle BI Administration Tool.
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 the logical table Fact - Fins - GL Other Posted Transaction:
Using the Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obisn\repository
In the Business Model and Mapping layer:
Create a logical column named 'Payroll Expense' in the logical table 'Fact - Fins - GL Other Posted Transaction.'
For example, right-click 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, and then 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.
Locate the Payroll Expense expression, and click the Expression Builder button to open Expression Builder.
Use Expression Builder 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 h appropriately for each LTS by using the appropriate fact table that corresponds to the Logical Table Source.
Steps d to h must be repeated for each Logical Table Source because in this example, there are 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 the logical table Fact - Fins - GL Balance:
Using the Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obisn\repository
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 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.
In the Column Source tab, select Derived from existing columns using an expression.
Click the Expression Builder button to display Expression Builder.
Use Express Builder 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 Section 3.3.1.2, "About Mapping PeopleSoft GL 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.
To revert these 'Manual' entries in the AR fact table, use the 'Group Account Number Clean Up' 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 reassigned the natural account 1210 to the 'AP' group account number in the file_group_acct_codes_psft.csv file.
Note:
The Financials – Group Account Number Clean Up Subjects Area should be executed only when you need to correct group account number assignments. This Subjects Area should not be included in other Financials Execution Plans. You must run it separately.
To do group account correction:
Correct the mapping of GL natural account to the group account in the input CSV file called file_group_acct_codes_psft.csv.
For example, before correction, a CSV file has the following values:
BUSINESS_UNIT = AUS01
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:
BUSINESS_UNIT = AUS01
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 Clean Up' Subject Area and do the following:
- If in the preceding step, 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 preceding step, '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 preceding step, then you must 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'.
Oracle Business Intelligence Applications 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. See also Section 3.3.2.2, "About Configuring Department Trees" for information about extracting trees, Section 18.4, "How to Set Up Product Category Hierarchies" for information about including the trees as hierarchies in the RPD file.
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 (W_GL_BALANCE_A) 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 look similar to the values shown in Table 3-14.
Table 3-14 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.
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.
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-15 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-16 How PS_DEPT_TBL Populates W_INT_ORG_DH
| ORG_WID | ORG_HIER(1-11)_NUM | ORG_HIER(1-11)_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-17 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-18 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 |
11 |
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_DH.HIERARCHY_VERSION |
|
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 following table 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_DHS |
Table 3-19 shows the temporary tables and their corresponding sessions that are used to handle the extraction and load of the trees.
Table 3-19 Temporary Tables and Corresponding Sessions for Extracting and Loading Trees
| Sequence | Temporary Table Name | Session |
|---|---|---|
|
Sequence 1 |
W_PSFT_INT_ORG_DEPT_DH_TMP |
SDE_PSFT_Stage_InternalOrganizationDimension_DepartmentHierarchy_GetDept |
|
Sequence 2 |
W_PSFT_INT_ORG_TREE_TMP |
|
|
Sequence 3 |
W_PSFT_INT_ORG_VERT_DH_TMP |
SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_GetHierarchyLevel |
|
Sequence 4 |
W_PSFT_INT_ORG_DTLRGE_DH_TMP |
SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_DeriveRange |
|
Sequence 5 |
W_PSFT_INT_ORG_FLAT_DH_TMP |
SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_Flatten |
Oracle Business Intelligence Applications supports both PeopleSoft Department organization hierarchies, and Business Unit-based organization hierarchies. ETL mappings extract and flatten PeopleSoft Department or Business Unit trees into a flattened organization hierarchy. ETL parameters also enable you to flatten Department or Business Unit trees by SetID and tree name.
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 the 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 Department and Business Unit Trees:
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab.
Select the task SDE_PSFT_Stage_InternalOrganizationDimension_Hierarchy_Extract, and display the Parameters subtab.
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, 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 default value of this parameter is 'BUSINESS_UNIT','DEPARTMENT'.
Repeat steps 3 - 5 for the SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_Extract task.
The Geography Country dimension is used as a roll-up dimension for the Business Location and Geography dimensions.
Oracle Business Intelligence Applications is installed 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, INFA_HOME\server\infa_shared\LkpFiles).
Add a new row to the CSV file with appropriate values.
Save and close the file.
There are no mandatory or additional configuration steps that apply to Oracle BI Applications deployed with Siebel source systems.
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 topic:
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 shown in the following list. In addition, you can also choose category codes to map to the existing columns shown in Table 3-20. 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-20 lists the additional fields in the dimension tables to which you can also map category codes.
Table 3-20 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, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab, and perform a query for the task.
Table 3-21 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-21 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-22 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-22 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 on My Oracle Support.
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. Table 3-23 shows an example of the file_lkp_fiscal_period_Qtr_Config_jde.csv opened in a text editor.
Table 3-23 Example of file_lkp_fiscal_period_Qtr_Config_jde.csv
| 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.
This section explains how to map Oracle JD Edwards EnterpriseOne or JD Edwards World General Ledger Accounts to Group Account Numbers, and includes the following topics:
An overview of setting up General Ledger Accounts - see Section 3.5.1.4.1, "Overview of Mapping GL Accounts to Group Account Numbers."
A description of how to map General Ledger Accounts to Group Account Numbers - see Section 3.5.1.4.2, "How to Map GL Account Numbers to Group Account Numbers."
An example that explains how to add Group Account Number metrics to the Oracle BI Repository - see Section 3.5.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.
See also Section 3.1.5, "About Mapping GL Accounts to Group Account Numbers for All Source Systems" for additional information.
The Group Account Number Configuration is an important step in the configuration of Financial Analytics, as it determines the accuracy of the majority of metrics in the General Ledger and Profitability module.
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_jde.csv - this file maps General Ledger accounts to group account codes.
file_grpact_fstmt.csv - this file maps Financial Statement Item Codes to group account codes.
Before you load your data, you must ensure that the account values are mapped consistently across these three configuration files. Figure 3-13 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 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.
Table 3-24 shows an example of the file_group_account_codes_jde.csv opened in a text editor.
Table 3-24 Example of file_group_account_codes_jde.csv
| 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. Table 3-25 shows the Financial Statement Item codes to which Group Account Numbers must map, and their associated base fact tables.
Table 3-25 Financial Statement Item Codes and 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)Foot 1 |
|
OTHERS |
GL Journal base fact (W_GL_OTHER_F) |
Footnote 1 Oracle's JD Edwards EnterpriseOne and JD Edwards World adapters for Financial Analytics do not support the Tax base fact (W_TAX_XACT_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.
This section explains how to map General Ledger Account Numbers to Group Account Numbers.
Note:
If you add new Group Account Numbers to the file_group_acct_codes_jde.csv file, you must also add metrics to the Oracle BI repository. For more information, see Section 3.5.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_jde.csv file with a text editor in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).
For each GL account number that you want to map, create a new row in the file containing the following fields:
| Field Name | Description |
|---|---|
|
COMPANY |
The ID of the COMPANY. |
|
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 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, and so on. |
For example:
10000, 1110, 1110, CASH 10000, 1210, 1210, AR 10000, 1220, 1220, AR
Note:
You can optionally remove the unused rows in the CSV file.
Ensure that the values that you specify in the file_group_acct_codes_jde.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.
See Figure 3-13, "Configuration Files for Configuring Group Accounts for JD Edwards" for an example.
Save and close the CSV file.
If you add new Group Account Numbers to the file_group_acct_codes_jde.csv file (as described in Section 3.5.1.4.2, "How to Map 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 the Oracle BI Administration Tool.
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 the logical table Fact - Fins - GL Other Posted Transaction:
Using the Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obisn\repository
In the Business Model and Mapping layer:
Create a logical column named 'Payroll Expense' in the logical table 'Fact - Fins - GL Other Posted Transaction.'
For example, right-click 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, and then 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 Show unmapped columns.
Locate the Payroll Expense expression, and click the Expression Builder button to open Expression Builder.
Use Expression Builder 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 h appropriately for each LTS by using the appropriate fact table that corresponds to the Logical Table Source.
Steps d to h must be repeated for each Logical Table Source because in this example, there are 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 the logical table Fact - Fins - GL Balance:
Using the Administration Tool, open OracleBIAnalyticsApps.rpd.
The OracleBIAnalyticsApps.rpd file is located at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obisn\repository
In the Business Model and Mapping layer:
Create a logical column named 'Payroll Expense' in the logical table 'Fact - Fins - GL Balance.'
For example, right-click 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.
In the Column Source tab, select Derived from existing columns using an expression.
Click the Expression Builder button to display Expression Builder.
Use Express Builder 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.
In Oracle's JD Edwards EnterpriseOne adapter, the account segments are mapped as:
CO.BU.OBJ.SUB.Sub Ledger.Sub Ledger type.Category Code(x).Category Code(n). Category Code(y)
Segment hierarchies are only supported for the Business Unit (BU) segment. Note that because the Business Unit hierarchy is already captured in W_INT_ORG_DH, there is no extraction (ETL) for populating W_HIERARCHY_D.
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.
|
 Copyright © 2009, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|