| Oracle® Business Intelligence Applications Fusion Edition Installation and Configuration Guide Release 7.9.5 Part Number E12083-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications Fusion Edition Installation and Configuration Guide Release 7.9.5 Part Number E12083-01 |
|
|
View PDF |
This chapter contains configuration steps for Oracle Business Intelligence that you need to follow for any applications you deploy (for example, Oracle Financial Analytics, Oracle Human Resources).
This chapter contains the following topics:
This section contains configuration steps that apply to Oracle Business Intelligence Applications deployed with any source system.
This section contains the following topics:
This section contains configuration steps required before a full data load that apply to Oracle Business Intelligence Applications deployed with any source system, and contains the following topics:
Initial Extract Date is required when you extract data for a full load. It reduces the volume of data in the initial load. The specified initial extract date will be used as a filter on the creation date of OLTP data in the selected full extract mapping.
When you set the Initial Extract Date parameter, make sure that you set it to the beginning of an accounting period, and not a date in the middle of an accounting period. For example, if you decide to extract data from June 2005, and the June 2005 accounting period starts from 5th June, set the date to 5th June, 2005.
Extract mappings that use the Initial Extract Date parameter include:
SDE_ORA_FreightTermsDimension
SDE_ORA_GL_AP_LinkageInformation_Extract
SDE_ORA_GL_AR_REV_LinkageInformation_ Extract
SDE_ORA_GL_COGS_LinkageInformation_Extract
SDE_ORA_PayrollFact_Agg_Items_Derive
To configure the initial extract date in your warehouse:
In the DAC Client, display the Design view, and select a container from the drop down list to the right of the Execute button.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
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 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 you made a business trip to UK and filed an expense report for meal expenses in UK, the document currency of the expense report will most likely be in GBP.
Local currency. This is the base currency of your ledger, the currency in which your accounting entries are recorded in.
Global currencies. Out of the box, Oracle Business Intelligence Applications provides three global currencies, which are the common currencies used by the Data 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. In the target table, there will be two amount columns, and three 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. 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 your warehouse:
In the DAC Client, display the Design view.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
Select a container from the drop down list to the right of the Execute button.
Display the 'Source System Parameters' tab.
Locate the following parameters and set the currency code values for them in the 'Value' box:
$$GLOBAL1_CURR_CODE (for the document currency).
$$GLOBAL2_CURR_CODE (for the local currency).
$$GLOBAL3_CURR_CODE (for the global currency).
Make sure that you spell the currencies as they are spelled in your source OLTP system.
Save your changes.
When Oracle Business Intelligence 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 Business Intelligence Applications also allows you to specify the exchange rate type to use to perform the conversion. Oracle Business Intelligence Applications also provides three global exchange rate types for you to configure.
Oracle Business Intelligence 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 Application also allows you to configure the rate type that you want to use when converting the document currency to the local currency.
To configure the currency rate types:
In the DAC Client, select a container from the drop down list to the right of the Execute button.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
Display the 'Source System Parameters' tab.
Locate the following DAC parameters and set the exchange rate type values for them in the 'Value' box:
$$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 sections explains how you configure the time dimension in the Oracle Business Analytics Warehouse, and contains the following sections:
Section 7.1.1.4.1, "Overview To Setting Up The Time Dimension"
Section 7.1.1.4.3, "Setting Up Fiscal Calendar By Fiscal Week"
Section 7.1.1.4.4, "Setting Up Fiscal Calendar By Fiscal Month"
Section 7.1.1.4.5, "Process of Setting Up The Fiscal Calendar"
Section 7.1.1.4.6, "Reloading the Time Dimension Tables After Your Data Warehouse is Populated"
When you configure Time Dimension tables, W_DAY_D is the base table that represents the time dimension in the Oracle Business Analytics Warehouse. There are two lines of aggregate dimension tables built on this table, as follows:
Regular Calendar tables.
Fiscal Calendar tables.
Table 7-1 shows the tables used to store calendar information.
Table 7-1 Tables in base table W_DAY_D that are used to store calendar information
| Regular calendar tables in W_DAY_D | Fiscal calendar tables in W_DAY_D |
|---|---|
|
W_WEEK_D |
W_FSCL_WEEK_D |
|
W_MONTH_D |
W_FSCL_MONTH_D |
|
W_QTR_D |
W_FSCL_QTR_D |
|
W_YEAR_D |
W_FSCL_YEAR_D |
There are two parameters $$START_DATE and $$END_DATE for the task SIL_DayDimension that need to be setup to load the calendar data in W_DAY_D. The SIL mappings use standard time functions to create records for each calendar day falling within the boundary defined by these two parameters. Once the records are created in W_DAY_D, the aggregate calendar tables are loaded by their respective SIL mapping.
Installed out of the box, Oracle Business Intelligence Applications supports one fiscal calendar. Fiscal data is first loaded in the W_DAY_D table and then the SIL mappings read data from W_DAY_D and load data into the aggregate Fiscal Time Dimension tables such as Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year.
You may choose to provide Fiscal calendar information in terms of the Fiscal Weeks of your organization or in terms of the Fiscal months of your organization. In either case, The SIL mappings are designed to derive the Fiscal Week from the Start and End Date of a Fiscal Month by grouping into periods of seven days each.
In this option you provide data for the Fiscal Year, Fiscal Month, Fiscal Week and Start Date of Fiscal Week. The Fiscal Month information is derived using the 4-4-5 rule for grouping weeks into months. The Fiscal Week End Date is derived based on the start date of the next week that is supplied in the data file. Fiscal Months are grouped into sets of 4 months each to determine the Fiscal Quarter.
In this option you can provide data at the level of Fiscal Month. The SIL_DayDimension_FiscalMonth_Extract task divides the Fiscal Month into Fiscal Weeks of seven days each. If the number of days in the Fiscal Month is not in multiples of seven, the last week will have less number of days.
To configure Fiscal Calendar by Fiscal Week
Open the file fiscal_week.csv using a text editor in the \OracleBI\dwrep\Informatica\SrcFiles directory. Enter the Fiscal Year, Fiscal Month, Fiscal Week and the Start Date of Fiscal Week in YYYYMMDD. Records must be inserted in ascending order of Fiscal Year, Fiscal Month, Fiscal Week. Save and close the file.
Note:
Make sure you enter accurate Fiscal Data as there is no check done within the Informatica mappings.Deactivate the task SIL_DayDimension_FiscalMonth_Extract in the Data Warehouse container in the DAC. Then right click and push to references.
Activate the task SIL_DayDimension_FiscalWeek_Extract in the Datawarehouse container in the DAC. Then right click and push to references.
To configure Fiscal Calendar by Fiscal Month
Open the file fiscal_month.csv using a text editor in the \OracleBI\dwrep\Informatica\SrcFiles directory. Enter the Fiscal Year, Fiscal Month and the Start Date of Fiscal_Month in YYYYMMDD format. Save and close the file.
Note:
Make sure that you enter accurate Fiscal Data as there is no check done within the Informatica mappings.Activate the task SIL_DayDimension_FiscalMonth_Extract in Datawarehouse container in the DAC. Then right click and push to references.
Deactivate the task SIL_DayDimension_FiscalWeek_Extract in Datawarehouse container. Then right click and push to references.
The data in time dimension is loaded once during the initial full load. Subsequently, the SIL_*_UpdateFlag mappings run everyday to update the domain value codes, which indicate whether a day, week, month, quarter or year is 'Current', 'Next' or 'Previous' as of the current day. The SIL_Fiscal_UpdateFlag mappings also update the flags that indicate whether a fiscal week, month, quarter or year is 'Current', 'Previous' or 'Next' with respect to the system date.
You might want to extend the range of data that you have in your time dimension sometime after the warehouse is in production. In order to achieve this, please follow the steps below that will kick off the full load ETL run of the W_DAY_D and all the aggregate time dimension tables.
To set up the load strategy of the time dimension table
In the DAC Client, display the Setup view, display the Physical Data Sources tab and click on the connection DataWarehouse.
Go the Refresh Dates tab in the bottom panel.
The Refresh Date of all the tables will be given there. Double click on the Refresh Date of W_DAY_D and make it Null. Do the same for the following aggregate time dimension tables: W_WEEK_D, W_QTR_D, W_MONTH_D, W_YEAR_D,W_FSCL_WEEK_D, W_FSCL_QTR_D, W_FSCL_MONTH_D, and W_FSCL_YEAR_D.
In the Design view, display the Tasks tab and open the task SIL_DayDimension in DataWarehouse container.
Set the $$START_DATE and $$END_DATE parameters to the appropriate start date and end date of the new date range.
Save the task and push the changes to references.
Note:
Make sure that the new date range defined by the parameters $$START_DATE and $$END_DATE encompasses the old date range entirely. Otherwise records can be lost. Also make sure you have provided the fiscal information for the new date range also in the appropriate fiscal input file. The next time you run your ETL execution plan, the calendar dimension will be truncated and reloaded with the new date range that you have specified. Since the calendar dimensions use smart keys, none of the fact tables that refer to these tables need to be reloaded.The following notes pertain to the process of setting up the fiscal 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 irrespective of whether the month actually has 31 days or not. If the month actually has less 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 RPD, 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 RPD. Therefore, before creating any new attribute in the RPD, 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.
As installed out-of-the-box, Oracle Business Intelligence 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.
These configuration steps are required if you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, or Oracle Supply Chain and Order Management Analytics.
The are two ways to set up hierarchies in Oracle Financial Analytics:
Using General Ledger Accounting Flexfield Value Sets Definitions
Using the Financial Statement Generator (FSG) Report Definition
Whichever method you choose to set up General Ledger Account hierarchies, you store the hierarchy information in the W_HIERARCHY_D table.
As an example, the hierarchy for US Acct might have the following structure:
Node A has child nodes B and C.
Node B has child nodes D and E.
Node C has child nodes F and G.
Node D has child nodes H and I.
Node F has child nodes J and K.
The figure below shows the example hierarchy for US Acct.
The table below shows how the hierarchy US Acct would be stored in the W_HIERARCHY_D table as follows:
These configuration steps are required if you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, or Oracle Supply Chain and Order Management Analytics.
Oracle EBS supports up to 30 segments in which to store accounting flexfields. flexfields are flexible enough to support complex data configurations, for example:
You can store data in any segments.
You can use more or fewer segments per chart of account, as required.
You can specify multiple segments for the same chart of account.
An Example Data Configuration for a Chart of Accounts
A single company might have a US Chart of Account and an APAC Chart of Account with the following data configuration:
Table 7-3 Example Chart of Accounts
| Segment Type | US Chart of Account (4256) value | APAC Chart of Account (4257) value |
|---|---|---|
|
Company |
Stores in segment 3 |
Stores in segment 1 |
|
Natural Account |
Stores in segment 4 |
Stores in segment 3 |
|
Cost Center |
Stores in segment 5 |
Stores in segment 2 |
|
Geography |
Stores in segment 2 |
Stores in segment 2 |
|
Line of Business (LOB) |
Stores in segment 1 |
Stores in segment 4 |
The example shows that in Chart Of Account 4256, Company is stored in the segment3 column in the Oracle EBS table GL_CODE_COMBINATIONS_ALL. In Chart Of Account COA4257, Company is stored in segment1 column in GL_CODE_COMBINATIONS_ALL table. The objective of this configuration file is to make sure that when segment information is extracted into the 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 segment1 column in W_GL_ACCOUNT_D; and Cost Center segments from COA 4256 and 4257 in segment2 column in W_GL_ACCOUNT_D, and so forth.
About Configuring the ETL Process for GL Accounting flexfields
Before you run the ETL process for General Ledger Accounts, you need to specify the segments that you want to analyze. To specify the segments that you want to analyze, use the following ETL configuration file:
$PMSERVER/srcfile/file_glacct_segment_configur_ora.csv
In the file_glacct_segment_config_ora.csv file, you need to specify the segments of the same type in the same column. For example, you might store all Cost Center segments from all chart of accounts in one column, and all Company segments from all chart of accounts in another column.
For example, you might want to do the following:
Analyze GL account hierarchies using only Company, Cost Center, Natural Account, and LOB.
You are not interested in using Geography for hierarchy analysis.
Store all Cost Center segments from all COAs in ACCOUNT_SEG2_CODE column in W_GL_ACCOUNT_D.
Store all Natural Account segments from all COAs in ACCOUNT_SEG3_CODE column in W_GL_ACCOUNT_D.
Store all LOB segments from all COAs in ACCOUNT_SEG4_CODE column in W_GL_ACCOUNT_D.
In W_GL_BALANCE_A (where you store GL account balances at aggregated level), you want to store GL account balances at Company and Cost Center level instead of at GL Code Combination level.
The screenshot below shows how the file_glacct_segment_config_ora.csv would be configured to implement the business requirements specified above.
To Set Up Hierarchies With General Ledger Accounting flexfields
Use the /srcfile/file_glacct_segment_configur_ora.csv file to specify the segments that you want to analyze.
In the DAC Client, do the following:
For the Subject Area 'Financials – General Ledger', in the 'Configuration Tags' tab, make sure that:
The tag 'Oracle – Extract Value Set Hierarchies' is unchecked for the 'Inactive' checkbox.
The tag 'Oracle – Extract FSG Hierarchies' is checked in the 'Inactive' checkbox.
Click Assemble to redesign the subject area.
After the subject area has been redesigned, also redesign the execution plans which contain this subject area.
Run the ETL process for General Ledger Accounts.
Using the Oracle BI Administration Tool, in the RPD Physical Layer, create additional aliases or change the names of the existing alias against the table W_HIERARCHY_D.
For example, if the existing aliases are numbered 1 to 3, name the new alias Dim_W_HIERARCHY_D_ValueSetHierarchy4, and rename the existing aliases as described in the table below:
| Old alias name | New alias name |
|---|---|
| Dim_W_HIERARCHY_D_ValueSetHierarchy1 | Dim_Company_Hierarchy_D |
| Dim_W_HIERARCHY_D_ValueSetHierarchy2 | Dim_CostCenter_Hierarchy_D |
| Dim_W_HIERARCHY_D_ValueSetHierarchy3 | Dim_NaturalAccount_Hierarchy_D |
Create a new alias against W_HIERARCHY_D and name the new alias Dim_LOB_Hierarchy_D.
Using the Oracle BI Administration Tool, in the Physical Layer, create joins in the physical layer from the new aliases that you created in the previous step, as follows:
Company Hierarchy will join to the segment column in W_GL_ACCOUNT_D that stores the Company segment.
Dim_Company_Hierarchy_D. HIER20_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG1_CODE
Dim_Compnay_Hierarchy_D.HIER_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG1_ATTRIB
Cost Center Hierarchy will join to the segment column in W_GL_ACCOUNT_D that stores the Cost Center segment.
Dim_CostCenter_Hierarchy_D. HIER20_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG2_CODE
Dim_CostCenter_Hierarchy_D.HIER_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG2_ATTRIB
Natural Account Hierarchy will join to the segment column in W_GL_ACCOUNT_D, that stores the Natural Account segment.
Dim_NaturalAccount_Hierarchy_D. HIER20_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG3_CODE
Dim_NaturalAccount_Hierarchy_D.HIER_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG3_ATTRIB
LOB Hierarchy will join to the segment column in W_GL_ACCOUNT_D that stores the LOB segment.
Dim_LOB_Hierarchy_D. HIER20_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG4_CODE
Dim_LOB_Hierarchy_D.HIER_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG4_ATTRIB
Note:
Hierarchies are linked to HIER20_CODE, because it is leaf node of the hierarchyJoin the aliases of W_HIERARCHY_D to the corresponding columns in the table W_GL_BALANCE_A.
Dim_Company_Hierarchy_D.HIER20_CODE = Fact_Agg_W_GL_BALANCE_A.ACCOUNT_SEG1_CODE AND
Dim_Company_Hierarchy_D.HIER_CODE = Fact_Agg_W_GL_BALANCE_A.ACCOUNT_SEG1_ATTRIB
Note: W_GL_BALANCE_A has only 6 segment columns. So, if you have more than 6 hierarchies, join only the first 6 to W_GL_BALANCE_A but join all hierarchies to W_GL_ACCOUNT_D in the previous step.
A HIER_CODE filter should be specified in the Business Model Layer to restrain the output of logical table to be one Hierarchy only. You must set the HIER_CODE to be filtered by the Valueset IDs that are applicable for that particular hierarchy. The list of the valueset ids would be same as the valueset ids you configured in the csv file in the first set.
To specify a filter in the Business Model Layer to restrain the output of logical table, do the following:
Expand each logical table for the hierarchies, say Dim - GL ValueSetHierarchy1, and open the logical table source under it.
Go to the Content tab.
In the 'use WHERE clause…' text box, apply a filter on the corresponding physical table alias of W_HIERARCHY_D.
For example:
"Oracle Data Warehouse"."Catalog"."dbo"." Dim_W_HIERARCHY_D_ValueSetHierarchy1"."HIER_CODE" IN (<comma seperated valuesetids>)
A HIER_CODE filter should be specified in the Business Model Layer to restrain the output of the logical table to be one Hierarchy only. To specify a filter in the Business Model Layer to restrain the output of the logical table, do the following:
Right click logical table Dim_W_Hierarchy_D_ValueSet1.
Choose Properties.
Choose the Source tab.
Select 'Dim_W_Hierarchy_D_Valueset1'.
Click Edit.
Choose the Content tab.
Insert the following code into the 'use WHERE clause…' text box:
"Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_Hierarchy_D_Valueset1"."HIER_CODE" = <Value Set Hierarchy Id>
Where <Value Set Hierarchy ID> is the Value Set Hierarchy ID of the segment for which you are creating a hierarchy, for example, 1001.
Using the Oracle BI Administration Tool, in the Business Model Layer of the Oracle BI Analytics Warehouse, create additional hierarchies using the dimensions that you created in the previous step.
For more information about example hierarchies that are installed with Oracle Business Intelligence Applications, refer to the following hierarchies in the Oracle Business Analytics Warehouse:
GL ValueSetHierarchy1
GL ValueSetHierarchy2
GL ValueSetHierarchy3
In the Business Model Layer, look for all the logical fact table that has logical join to the logical hierarchy table Dim - GL ValueSetHierarchy1. You will need to create a similar logical table between the new logical hierarchy dimensions that you created and these logical facts, as follows:
Under each of the logical fact table, open the logical table sources and go to the content tab. Under aggregation content, select the Show unmapped check box. It will show all the hierarchies you created in the previous step. For each of these hierarchies, select the logical level to 'Detail'.
In the business model diagram, create a new Complex Join between each of the new logical hierarchy tables and each of the logical facts. In the join, make sure the cardinality is (0,1) on the dimension side and N on the fact side.
Using the Oracle BI Administration Tool, in the Presentation Layer of the Oracle BI Analytics Warehouse, drag the new hierarchies into the presentation folder.
Note:
You can rename the hierarchies in the Presentation Layer if required.These configuration steps are required if you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, or Oracle Supply Chain and Order Management Analytics.
If you need to define GL account hierarchies based on multiple segments within a chart of accounts, you can use the Financial Statement Generator report definition UI in Oracle EBS to define them.
You should first use the Oracle EBS Financial Statement Generator (FSG) form to define a row set or a column set, then Oracle Business Intelligence Applications will extract the row set or column set definition and convert them into hierarchies.
Oracle Financial Statement Generator hierarchies are extracted from following EBS source tables:
RG_REPORT_AXIS_CONTENTS
This table defines the relationship between the FSG report axis and GL code combinations. The GL code combinations with segment values within the value range defined for that axis are categorized as children of that axis.
RG_REPORT_AXIS_SETS
This table stores the information for each of the row set or column set you defined. There is one record in this table for each row or column set you defined. Each row includes an axis set identifier, a row or column set name, and a structure identifier to assign a specific chart of accounts to the row set or column set.
RG_REPORT_CALCULATIONS
This table stores formulas for calculating each row or column in the row or column set. An example of a row calculation might be to sum up the amount from the previous five rows. An example of a columns calculation might be to calculate column five by subtracting column four from column three.
For example, in Income Statement, 'Net Income' is the calculation result of 'Gross Profit from Revenue' minus 'Total Expense'. When converting to hierarchy, Net Income becomes the parent of 'Gross Profit from Revenue' and 'Total Expense'. Therefore, hierarchy can be derived based on the information in RG_REPORT_CALCULATION.
The diagram below shows an example hierarchy, with the top level Net Income node having two child nodes, Total Expense, and Gross Profit from Revn, and the Total Expense node having two child nodes, Operating Expense, and Depreciation Expense.
The diagram below shows how an income state is derived from a hierarchy.
The hierarchy above would be converted into a flattened hierarchy and stored in W_HIERARCHY_D in the following format:
Table 7-4 Example flattened hierarchy stored in W_HIERARCHY_D
| HIER Name | HIER1 | HIER2 | HIER3 | HIER4 | HIER20 |
|---|---|---|---|---|---|
|
Income Statement |
Net Income |
Gross Profit... |
Gross Profit... |
Gross Profit... |
Gross Profit... |
|
Income Statement |
Net Income |
Total Expenses |
Operating Expenses |
Operating Expenses |
Operating Expenses |
|
Income Statement |
Net Income |
Total Expenses |
Depreciation Expense |
Depreciation Expense |
Depreciation Expense |
Fact tables join to the W_HIERARCHY_D table via the General Ledger 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, etc) are not extracted from the FSG definitions. For example, both A + B = C and A - B = C would give the same hierarchy, with a node C having two child nodes A and B (see diagram below).
About Configuring the ETL Process for Oracle Financial Statement Generator Report
Before you run the ETL process for General Ledger Accounts, you need to specify the hierarchies that you want to reference. To specify the FSG hierarchies that you want to reference, use the following ETL configuration file:
$PMSERVER/srcfile/file_gl_hierarchy_assignment_ora.csv
In this file, for each chart of accounts, you can specify six FSG hierarchies (using axis_set_id; Axis_set_id is a column from RG_REPORT_AXIS_SETS table. It is the unique ID of a row set or column set) you want to store in the GL account dimension table for the code combinations that belong to that chart of accounts.
The DATASOURCE_NUM_ID field specifies the data source to which the configurations apply. If you have multiple source systems, there might be a chart of accounts across the multiple source systems with the same ID. Therefore, you need to use the DATASOURCE_NUM_ID value to distinguish between them.
For example, we have an Income Statement FSG report and a Balance Sheet FSG report which we want to derive both of their hierarchy structures into OLAP.
Oracle Business Intelligence Applications assumes that both reports are derived from same set of GL accounts with CHART_OF_ACCOUNTS=101. The axis_set_id of Income Statement is 1001, and for 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 (by which we used to calculate the reports), we want to associate their HIER1 column (in GL_ACCOUNT_D) with Income Statement hierarchy structure, HIER3 column with Balance Sheet hierarchy structure.
In this case, we would add one row into file_gl_hierarchy_assignment_ora.csv with the following fields set as below:
CHART OF ACCOUNTS - 101
HIER1_AXIS_SET_ID - 1001
HIER3_AXIS_SET_ID - 1003
DATASOURCE_NUM_ID - 2
(Leave the other row values blank.)
This row indicates that for all of the GL Accounts with CHART_OF_ACCOUNTS=101 and DATASOURCE_NUM_ID=2, assigning hierarchies with axis_set_id=1001, null, 1003, null, null, null to HIER1~HIER6 columns respectively. Therefore, after extraction and loading, for those affected GL Account rows, HIER1 column will be the foreign key to Income Statement hierarchy's row ID in W_HIERARCHY_D and HIER3 column will be the foreign key to Balance Sheet hierarchy's row ID in W_HIERARCHY_D.
Note: Financial Analytics will not load those hierarchies with axis_set_id not been specified in file_gl_hierarchy_assignment_ora.csv.
To Set Up Hierarchies With Financial Statement Generator Report Definition:
Use the /srcfile/ file_gl_hierarchy_assignment_ora.csv file to specify the hierarchies you want to reference for each CHART_OF_ACCOUNTS.
In the DAC Client, do the following:
Navigate to the Subject Area 'Financials – General Ledger'.
In the 'Configuration Tags' tab, make sure that the tag 'Oracle – Extract FSG Hierarchies' is unchecked for the 'Inactive' checkbox.
In the 'Configuration Tags' tab, make sure that the tag 'Oracle – Extract Value Set Hierarchies' is checked in the 'Inactive' checkbox.
Click Assemble to redesign the subject area.
After the subject area has been redesigned, also redesign the execution plans which contain this subject area.
Run the ETL process for General Ledger Accounts.
Using the Oracle BI Administration Tool, in the RPD Physical Layer, 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 Analytics Warehouse, 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 Layer of the Oracle BI Analytics Warehouse, 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 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, please refer to the following pre-installed example logical table Dim - FSG Hierarchy 1 in the Oracle Business Analytics Warehouse
Using the Oracle BI Administration Tool, in the Business Model Layer of the Oracle BI Analytics Warehouse, create new dimension based on the logical table that you created in the previous step.
Please refer to 'FSG Hierarchy 1' as an example.
In the Business Model Layer, look for all the logical fact table that has logical join to the logical hierarchy table Dim - FSG Hierarchy1. You will need to create a similar logical join between the new logical hierarchy dimensions that you created and these logical facts, as follows:
Under each of the logical fact table, open the logical table sources and go to the content tab. Under aggregation content, select the Show unmapped check box. It will show all the hierarchies you created in the previous step. For each of these hierarchies, select the logical level to 'Detail'.
In the business model diagram, create a new Complex Join between each of the new logical hierarchy tables and each of the logical facts. In the join, make sure the cardinality is (0,1) on the dimension side and N on the fact side.
Using the Oracle BI Administration Tool, in the Presentation Layer of the Oracle BI Analytics Warehouse, 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 Business Intelligence Applications deployed with any source system, and contains the following topics:
DATASOURCE_NUM_ID is a system column in the data warehouse that uniquely identifies a data source category and indicates which source systems the data comes from. For example, the value '1' indicates one of the Siebel data sources, and the value 2 indicates an Oracle 11.5.8. data source.
Oracle Business Intelligence is installed with a number of pre-defined data source templates that you can edit to specify OLTP and OLAP data sources. If you create a new data source without using one of the pre-defined templates, you must specify the correct DATASOURCE_NUM_ID for that data source category. For example, if you specify an Oracle EBS data source, you must specify the DATASOURCE_NUM_ID value '9'. For more information about specifying data sources, see Section 4.12.3, "How to Set Physical Data Sources".
The table below shows the data sources that are supported by Oracle Business Intelligence and their associated DATASOURCE_NUM_ID values.
Table 7-5 Data Sources and Associated DATASOURCE_NUM_ID Values
| Data Source Name | Data Source Number |
|---|---|
|
ORA_11_5_8 |
2 |
|
ORA_11_5_9 |
5 |
|
ORA_11_5_10 |
4 |
|
ORACLE_R12 |
9 |
|
PSFT_8_4_FINSCM |
7 |
|
PSFT_8_8_FINSCM |
8 |
|
PSFT_8_8_HCM |
6 |
|
SEBL_63 |
1 |
|
SEBL_771 |
1 |
|
SEBL_753 |
1 |
|
SEBL_78 |
1 |
|
SEBL_80 |
1 |
|
SEBL_VERT_771 |
1 |
|
SEBL_VERT_753 |
1 |
|
SEBL_VERT_78 |
1 |
|
SEBL_VERT_80 |
1 |
|
SRV_SEBL_80 |
1 |
|
UNIV |
3 |
To configure a DATASOURCE_NUM_ID value:
In the DAC Client, display the Setup view.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
Display the 'Physical Data Sources' tab.
Select a data source from the list.
On the Edit tab, edit the value in the Data Source Number field.
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 finish the steps in section Section 8.2.2.1, "How to Configure the DAC parameter for Purchase Cycle Line".
This section contains configuration steps that apply to Oracle Business Intelligence Applications deployed with Oracle EBS source systems.
This section contains the following topics:
Section 7.2.1, "Configuration Required Before A Full Load for Oracle EBS"
Section 7.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 Business Intelligence Applications deployed with Oracle EBS source systems, and contains the following topics:
Section 7.2.1.1, "Configuration of Product hierarchy (except for GL, HR modules)"
Section 7.2.1.3, "How to Map Oracle General Ledger Natural Accounts to Group Account Numbers"
Section 7.2.1.4, "How to do Group Account correction for Oracle Application"
This section contains configuration points for product hierarchy in the Product dimension table and the Inventory Product dimension table.
Oracle E-Business Suite enables users to groups the products using categories and category sets. While E-Business Suite provides pre-packaged category sets, you can also define your own category sets.
As configured out-of-the-box, the Oracle Business Analytics Warehouse extracts product categories where the CATEGORY_SET_ID is 2 or 27. However, it is likely that the categories you extract from the source system are different from these prepackaged category sets.
To configure your product category sets, do the following:
Identify the category sets you want to report (for more information, see Section 7.2.1.1.1, "How to identify Category Sets from EBS").
Configure the DAC parameters for product hierarchy (for more information, see Section 7.2.1.1.2, "Configure the DAC Source System Parameters for Product Hierarchy").
Configure the hierarchy mapping with the segments (for more information, see Section 7.2.1.1.3, "Configure the Hierarchy Mapping With the Segments").
These steps are part of the task in Section 7.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 EBS instance.
Navigate to Setup > Items > Categories > Default Category Sets.
Look for functional Area Inventory and place the cursor in the Category Set field.
Choose Help, then Diagnostics, then Examine, and specify the apps user password.
Click the Field LOV button and select CATEGORY_SET_ID, and note down the value.
Repeat steps 3-5 for the Purchasing functional area.
These steps are part of the task in Section 7.2.1.1, "Configuration of Product hierarchy (except for GL, HR modules)".
To configure the DAC Source System Parameters for product hierarchy:
In the DAC Client, display the Design view, and choose your Oracle E-Business Suite container from the container drop down list.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
Display the Source System Parameters tab.
Locate the INVPROD_CAT_SET_ID and PROD_CAT_SET_ID 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 INVPROD_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.
Click Save.
Note:
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.These steps are part of the task in Section 7.2.1.1, "Configuration of Product hierarchy (except for GL, HR modules)".
To configure the hierarchy mapping with the segments:
In Informatica PowerCenter Designer, open the appropriate Oracle Applications v11i folder, and expand the Mapplets folder.
In the Mapplet Designer, edit the mplt_SA_ORA_ProductDimension mapplet.
Double-click the EXP_PRODUCTS expression transformation to display the Edit Transformations dialog.
In the Ports tab, scroll down to find the hierarchy code ports named EXT_PROD_HIER<X>_CODE.
Hierarchy levels are named with the convention EXT_PROD_HIER<X>_CODE, where <X> denotes the level within the hierarchy. For example, if you want to edit the first level of your hierarchy, you must edit the definition for EXT_PROD_HIER1_CODE port.
The first two levels are pre-configured as follows:
EXT_PROD_HIER1_CODE = IIF(ISNULL(INP_SEGMENT1) OR ISNULL(INP_SEGMENT2), NULL, INP_SEGMENT1||'~'||INP_SEGMENT2)
EXT_PROD_HIER2_CODE = INP_SEGMENT1
Click the Expression field for the appropriate EXT_PROD_HIER<X>_CODE port, then click the down arrow to display the Expression Editor, and modify the expression.
Validate and save your changes to the repository.
Notes
The default product hierarchy level is two. If you only have one product hierarchy level, you need to make the following modifications:
In the EXP_PRODUCTS expression transformation in the mplt_SA_ORA_ProductDimension mapplet, modify the EXT_PROD_HIER1_CODE port to INP_SEGMENT1.
In the SDE_ORA_ProductDimension mapping, open the EXP_W_PRODUCT_DS transformation expression and modify the PROD_HIER1_CODE port expression to IN_PROD_HIER1_CODE.
If you have more than two hierarchy levels, then map the other columns from the appropriate source columns.
In Oracle 11i 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_Derive' has been enabled for configuration of this Master Organization based on the configuration in the OLTP. As installed out-of-the-box, the organization ID (that is set by the $$MASTER_ORG parameter) is set to 204. This organization ID 204 needs to be changed based on the individual implementation of OLTP in your deployment.
Note:
This ETL implementation supports the best practice prescribed by Oracle for the creation of Single Master Organization for defining the Product master. This ETL implementation does not support the multiple master Organizations if the same product is defined in multiple master organizations.To set the Master Inventory Organization in Product dimension Extract:
In the DAC Client, display the Design view.
For more information about logging into the DAC, see Section A.1, "How to Log Into the DAC Client".
Select a container from the drop down list to the right of the Execute button.
Display the Tasks tab, and select the SDE_ORA_ProductDimension_Derive task.
Specify or create the $$MASTER_ORG parameter with an appropriate value.
For example, $$MASTER_ORG=204.
Save your changes.
Note:
It is critical that the General Ledger Account Numbers are mapped to the Group Account Numbers (or domain values) as the metrics in the General Ledger reporting layer uses these values. For a list of domain values for General Ledger Account Numbers, see Siebel Customer-Centric Enterprise Warehouse Data Model Reference.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 the nature of the General Ledger accounts (for example, cash account, payroll account). Refer to the master_code column in the file_group_acct_names_ora.csv file for values you can use. For a list of the Group Account Number domain values, see Siebel Customer-Centric Enterprise Warehouse Data Model Reference. The mappings to General Ledger Accounts Numbers are important for both Profitability analysis and General Ledger analysis (for example, Balance Sheets).
The logic for assigning the accounts is located in the file_group_acct_codes_ora.csv file. The table below shows an example configuration of the file_group_acct_codes_ora.csv file.
Table 7-6 Example configuration of file_group_acct_codes_ora.csv
| COA ID | FROM ACCT | TO ACCT | GROUP_ACCT_NUM |
|---|---|---|---|
|
1 |
101010 |
101099 |
CA |
|
1 |
131010 |
131939 |
FG INV |
|
1 |
152121 |
152401 |
RM INV |
|
1 |
171101 |
171901 |
WIP INV |
|
1 |
173001 |
173001 |
PPE |
|
1 |
240100 |
240120 |
ACC DEPCN |
|
1 |
261000 |
261100 |
INT EXP |
|
1 |
181011 |
181918 |
CASH |
|
1 |
251100 |
251120 |
ST BORR |
In the table above, in the first row, all accounts within the account number range from 101010 to 101099 that have a Chart of Account (COA) ID equal to 1 are assigned to Current Asset. 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_ora.csv file. You can then assign GL accounts to the new group of account numbers in the file_group_acct_codes_ora11i.csv file.
You also need to add a new row in the file_grpact_fstmt.csv file. This file specifies the relationship between a Group Account Number and a Financial Statement Item Code. You must map the new Group Account Number to one of the following Financial Statement Item codes:
AP
AR
COGS
REVENUE
TAX
OTHERS
These Financial Statement Item codes correspond to the following six base fact tables in the Financial Analytics product.
AP base fact (W_AP_XACT_F)
AR base fact (W_AR_XACT_F)
Revenue base fact (W_GL_REVN_F)
Cost of Goods Sold base fact (W_GL_COGS_F)
Tax base fact (W_TAX_XACT_F)
GL Journal base fact (W_GL_OTHER_F)
By mapping your GL accounts against the Group Account Numbers and then associating the Group Account Number to a Financial Statement Item code, you have indirectly associated the GL account numbers to Financial Statement Item codes as well.
Financial Statement Item codes are internal codes used by 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
master_code column of the file_group_acct_names_ora.csv file.To map Oracle General Ledger account numbers to Group Account Numbers
Open the file_group_acct_codes_ora.csv file with a text editor in the $pmserver\srcfiles folder.
Edit the fields in the following table:
| Field Name | Description |
|---|---|
| COA ID | The ID of the General Ledger chart of account. |
| FROM ACCT and TO ACCT | The natural account range. This is based on the natural account segment of your GL accounts. |
| GROUP_ACCT_NUM | This field denotes the nature of the Oracle General Ledger accounts. For example, Cash account, Payroll account, and so on. Refer to the file_group_acct_names_ora.csv file for values you can use. |
Note:
It is important that you do not edit any other fields in the CSV files.Save and close the CSV file.
Note: Refer to the section Section 7.2.1.3, "How to Map Oracle General Ledger Natural Accounts to Group Account Numbers" for general concepts about Group Account Number and Financial Statement Item Code.
When a user mistakenly maps a GL natural account number to an incorrect Group Account Number, incorrect accounting entries might be inserted into the fact table. For example, natural account 1210 is classified as belonging to 'AR' Group Account Number when it should be classified as having 'AP' Group Account Number. When this happens, the ETL program will get all the journal lines charging to account 100 and try to reconcile these journal lines against subledger accounting records in the AR fact table (W_AR_XACT_F). Since these journal lines did not come from AR, the ETL program will not be able to find the corresponding matching subledger accounting records for these 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 GL system charging against the AR accounts. This entire process is call GL Reconciliation process.
In order to revert these 'Manual' entries in the AR fact, you will need to utilize the 'Group Account Number Cleanup' program provided in Oracle Business Intelligence 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 matching subledger accounting records in the AP fact (W_AP_XACT_F); provided that you've re-assign the natural account 1210 to the 'AP' group account number in the file_group_acct_codes_ora.csv file.
To do Group Account correction:
Correct the mapping of GL Natural account to the group account in the input 'csv' file file_group_acct_codes_ora.csv.
For example, before correction, a CSV file has the following values:
CHART OF ACCOUNTS ID = 101
FROM ACCT = 1110
TO ACCT = 1110
GROUP_ACCT_NUM = CASH
After correction, if the account '1210' originally belonged to the 'AP' Group Acct Num 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_UM = AR
In the DAC Client, do the following:
Navigate to the appropriate container.
Go to 'Design' and open up the 'Subject Areas' tab.
Open up the 'Financials – General Ledger' subject area and verify which of the following Configuration Tags is marked as 'inactive':
- Financials – Calculate GL Balance
- Oracle – Extract GL Balance
Out of the box, 'Financials – Calculate GL Balance' should be marked as inactive.
Open the 'Financials – Group Account Number Cleanup' subject area and do the following:
- If in step 3, 'Financials – Calculate GL Balance' is marked as 'inactive', then the Configuration Tags 'Financials – Calculate GL Balance Reverse' should be marked as 'inactive' as well.
- If in step 3, 'Oracle – Extract GL Balance' is marked as 'inactive', then the Configuration Tags 'Financials – Calculate GL Balance Reverse' should be unmarked in the 'inactive' check box.
If you need to make any change in step b above, then you need to reassemble the 'Financials – Group Account Number Clean Up' subject area and then redesign the one of these execution plans, depending on which version of Oracle EBS you have:
Financials – Group Account Number Clean Up ORA1158
Financials – Group Account Number Clean Up ORA1159
Financials – Group Account Number Clean Up ORA11510
Financials – Group Account Number Clean Up R12
Depending on the Oracle EBS version you have, run the corresponding Group Account Cleanup execution plan:
Financials – Group Account Number Clean Up ORA1158
Financials – Group Account Number Clean Up ORA1159
Financials – Group Account Number Clean Up ORA11510
Financials – Group Account Number Clean Up R12
This section contains additional configuration steps that apply to Oracle Business Intelligence Applications deployed with Oracle EBS source systems, and contains the following topics:
Section 7.2.2.1, "Configuring the Country Region and State Region Name"
Section 7.2.2.4, "Configuring the Configuring the Make-Buy Indicator"
For Oracle 11i, you can reconfigure the region, state, and country names. This configuration information applies only to plant, storage, and supplier locations. By default, the Region Name column (EXT_REGION_NAME) is populated using the same code value as the Region Code column (EXT_REGION_CODE). However, you can redefine the load mapping's Source Adapter mapplet to load a source-supplied region name instead of the code. If you want to reconfigure the load in this manner, you can load the region code and region name into the W_CODE_D table. For information on loading codes and code names into the W_CODE_D table, see Section 5.10, "Codes Lookup".
When you have loaded the region code and region name into the W_CODE_D table, you can remove the expression in the Source Adapter that defines the Region Name column. By making the Region Name's expression blank, the SIL looks up the Region Name in the W_CODE_D table, using the supplied region code when the load occurs. The load mapping then inserts the region name and region code into the data warehouse table.
To configure the Country Region Name:
In Informatica PowerCenter Designer, open the Configuration for SDE_ORA1158_adapter folder.
Open the mapplet you want to edit.
The following is a list of all Source Adapter mapplets that use the EXT_COUNTRY_REGION column:
mplt_SA_ORA_SupplierDimension
mplt_SA_ORA_BusinessLocationDimension_Plan
mplt_SA_ORA_BusinessLocationDimension_StorageLocation
Double-click the Expression transformation to open the Edit Transformations dialog, and click the Port tab to display the EXT_COUNTRY_REGION port.
Edit the condition by removing the assigned value if you want the lookup to occur.
Click Apply.
Validate the mapplet, and save your changes to the repository.
To configure the State Region Name
In Informatica PowerCenter Designer, open the SDE_ORA<Ver>_adapter.
Open the mapplet you want to edit.
The following is a list of all Source Adapter mapplets that use the EXT_STATE_REGION column:
mplt_SA_ORA_SupplierDimension
mplt_SA_ORA_BusinessLocationDimension_Plan
mplt_SA_ORA_BusinessLocationDimension_StorageLocation
Double-click the Expression transformation to open the Edit Transformations dialog, and click the Port tab to display the EXT_STATE_REGION port.
Edit the condition by removing the assigned value if you want the lookup to occur.
Click Apply.
Validate the mapplet, and save your changes to the repository.
For Oracle 11i, you can reconfigure the region, state, and country names that apply to the Supplier locations only. By default, the State Name column (EXT_STATE_NAME) is populated using the same code value as the State Code column (EXT_STATE_CODE). However, you can redefine the load mapping's Source Adapter mapplet to load a source-supplied state name instead of the code. If you want to reconfigure the load in this manner, you can load the state code and state name into the W_CODE_D table. For information on loading codes and code names into the W_CODE_D table, see Section 5.10, "Codes Lookup".
When you have loaded the state code and state name into the W_CODE_D table, you can remove the Expression in the Source Adapter that defines the State Name column. By setting the State Name's expression to null, the SIL looks up the state name in the W_CODE_D table using the supplied state code, during the load process. The load mapping then inserts the state name and state code into the data warehouse table.
To configure the State Name:
In Informatica PowerCenter Designer, open the SDE_ORA<Ver>_adapter.
Open one of the following mapplets:
mplt_SA_ORA_SupplierDimension
mplt_SA_ORA_BusinessLocationDimension_Plant
mplt_SA_ORA_BusinessLocationDimension_StorageLocation
Double-click the Expression transformation to open the Edit Transformations dialog, and click the Port tab to display the EXT_STATE_NAME port.
Edit the condition by removing the assigned value if you want the lookup to occur.
Click Apply.
Validate the mapplet and save your changes to the repository.
For Oracle 11i, you can reconfigure the region, state, and country names that apply to supplier locations only. By default, the Country Name column (EXT_COUNTRY_NAME) is populated using the same code value as the Country Code column (EXT_COUNTRY_CODE). However, you can redefine the load mapping's Source Adapter mapplet to load a source-supplied country name instead of the code. If you want to reconfigure the load in this manner, you can load the country code and country name into the W_CODE_D table. For information on loading codes and code names into the W_CODE_D table, see Section 5.10, "Codes Lookup".
When you have loaded the country code and country name into the W_CODE_D table, you can remove the expression in the Source Adapter that defines the Country Name column. By setting the Country Name's expression to null, when the load occurs, the SIL looks up the country name in the W_CODE_D table, using the supplied country code. The load mapping then inserts the country name and country code into the data warehouse table.
To configure the Country Name:
In Informatica PowerCenter Designer, open the SDE_ORA<Ver>_adapter.
Open one of the following mapplets:
mplt_SA_ORA_SupplierDimension
mplt_SA_ORA_BusinessLocationDimension_Plant
mplt_SA_ORA_BusinessLocationDimension_StorageLocation
Double-click the Expression transformation to open the Edit Transformations dialog, and click the Port tab to display the EXT_COUNTRY_NAME port.
Edit the condition by removing the assigned value if you want the lookup to occur.
Click Apply.
Validate the mapplet, and save your changes to the repository.
The Make-Buy indicator specifies whether a material that was used to manufacture a product was made in-house or bought from an outside vendor. By default, the indicator is set using the INP_PLANNING_MAKE_BUY_CODE. If the code is set to 1, then the indicator is set to M (for make). However, if the code is set to 2, then the indicator is set to B (for buy). Otherwise, the indicator is set to null.
Your organization may require different indicator codes. If so, you can modify the indicator logic by reconfiguring the condition in the mapplet mplt_SA_ORA_ProductDimension. For example, you may want your indicator code to be 0 for make, and 1 for buy.
To configure the Make-Buy Indicator:
In Informatica PowerCenter Designer, open the SDE_ORA<Ver>_adapter.
Open one of the following mapplets:
mplt_SA_ORA_SupplierDimension
mplt_SA_ORA_BusinessLocationDimension_Plant
mplt_SA_ORA_BusinessLocationDimension_StorageLocation
Double-click the Expression transformation to open the Edit Transformations dialog, and click the Port tab to display the EXT_COUNTRY_NAME port.
Edit the condition by removing the assigned value if you want the lookup to occur.
Click Apply.
Validate the mapplet, and save your changes to the repository.
This section contains configuration steps that apply to Oracle Business Intelligence Applications deployed with PeopleSoft source systems.
This section contains the following topics:
Section 7.3.1, "Configuration Required Before A Full Load for PeopleSoft"
Section 7.3.2, "Configuration Steps for Controlling Your Data Set for PeopleSoft"
Not applicable to Oracle Business Intelligence Applications Release 7.9.5.
This section contains additional configuration steps that apply to PeopleSoft, and contains the following topics:
Internal Organization Hierarchy extracts the hierarchy related to Business Units. The PeopleSoft Tree Name and SETID are the only settings that require modification.
Table 7-7 Internal Organization Hierarchy Mappings
| Exec Sequence | Mapping Name | Needs Customization? |
|---|---|---|
|
1 |
SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_Extract |
Y |
|
1 |
SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_GetGLBU |
N |
|
2 |
SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_GetHierarchyLevels |
N |
|
2 |
SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_DeriveRange |
N |
|
3 |
SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_Flatten |
N |
|
4 |
SDE_PSFT_InternalOrganizationDimension_CompanyHierarchy_Derive |
N |
As the above table illustrates, only the SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_Extract mapping needs to be modified (that is, there is no need to change or copy the other mappings). For example, if you want to extract a tree named 'BIZ_UNIT_HIER' with SETID 'XYZ', which uses BU_GL_UPDAT2_VW as its structure's Record Name, and BUSINESS_UNIT as its field name (that means it builds up hierarchy for BUSINESS_UNIT), do the following:
In Informatica PowerCenter Designer, create a new mapping by copying the existing SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_Extract mapping.
Name the new mapping SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy2_Extract.
Open the new mapping SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy2_Extract and edit as follows:
In SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy2_Extract, open Source Definition PSFT_TREE_VERTICAL_FLATTENING.
Under the Properties tab, change the Tree Name to 'BIZ_UNIT_HIER', and Set ID to 'XYZ'.
Save the changes in the Informatica Folder.
In Informatica PowerCenter Workflow Manager, add Corresponding Sessions and Workflows, as follows:
In Informatica PowerCenter Workflow Manager, locate the appropriate PeopleSoft folder SDE_PSFT_xx_adapter (for example, 'SDE_PSFT_89_adapter', 'SDE_PSFT_90_adapter').
Open Task Developer, and create new task for the mappings that you just created.
Using the parameters in the original sample sessions as a guide (refer to the parameter settings for SDE_PSFT_Stage_GLHierarchy_DepartmentHierarchy_Extract), modify the newly created session.
Save your changes.
In Informatica PowerCenter Workflow Manager, create a Workflow with the same name as the Session.
Drag the newly created session to the panel and connect the start with the session.
Save and Check-in all your changes in Informatica PowerCenter Designer and Informatica PowerCenter Workflow Manager.
Re-configure the DAC settings, as follows:
Login into the DAC, open your container, and display the Tasks tab.
Add the workflow that you created as a new task.
For information about what values to set task property values to, refer to the task SDE_PSFT_Stage_InternalOrganizationDimension_CompanyHierarchy_Extract.
Right click on each task and select 'Synchronize tasks'.
The Synchronize tasks process reads in the source table and target table information for these tasks from the Informatica repository. To see this information, display the 'Source Tables' tab and 'Target Tables' tab for these tasks.
Add your tasks into Configuration Tags: navigate to Configuration Tags panel (click 'Design', choose 'Configuration Tags'). Select 'PeopleSoft - Populate Business Unit Related Hierarchies' item. Select 'Tasks' tab in the panel below. Click 'Add/Remove' button; query the new mappings you just entered into the DAC. Select mappings; add them into the right panel. Click OK, then click Save.
Reassemble the Financial or Human Resources related areas: Click 'Design' button at the top tool bar of DAC, select 'Subject Areas' tab, for each of the financial-related subject areas (Cost of Goods Sold, General Ledger, Payables, Receivables, Revenue) or human resources subject areas (HR - Event Information, HR - Operation, HR - Payroll), right click on it, and choose Assemble.
Re-build related execution plan: Click 'Execute' button at the top tool bar of DAC, select 'Execution Plans' tab, find the executing plan for your PeopleSoft Financial Application (for example Financials_PeopleSoft 8.8) or PeopleSoft Human Resources Application (for example, Human Resources - PeopleSoft 8.8), right click on it, and choose Build.
This section contains configuration steps that apply to Oracle Business Intelligence Applications deployed with Siebel source systems.
This section contains the following topics:
Section 7.4.1, "Configuration Required Before A Full Load for Siebel Source Systems"
Section 7.4.2, "Configuration Steps for Controlling Your Data Set for Siebel Source Systems"
Not applicable to Oracle Business Intelligence Applications Release 7.9.5.
Not applicable to Oracle Business Intelligence Applications Release 7.9.5.
