| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Version 7.9.5.1 Part Number E13766-01 |
|
|
View PDF |
| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Version 7.9.5.1 Part Number E13766-01 |
|
|
View PDF |
This section contains mandatory configuration tasks that apply to Oracle BI Applications deployed with any source system as well as mandatory tasks that are specific to the various source systems.
To configure Oracle BI Applications, you first need to perform the steps in Source-Independent Configuration Steps.
You then need to perform the tasks in one of the following sections depending on the type of your source system:
This section contains configuration steps that apply to Oracle BI Applications deployed with any source system. It contains the following topics:
Initial Extract Date is required when you extract data for a full load. It reduces the volume of data in the initial load. The specified initial extract date will be used as a filter on the creation date of the transactional data in the selected full extract mapping.
When you set the Initial Extract Date parameter, make sure that you set it to the beginning of an accounting period and not a date in the middle of an accounting period. For example, if you decide to extract data from June 2005, and the June 2005 accounting period starts on June 5, set the date to June 5, 2005.
To configure the initial extract date
In DAC, go to the Design view, and select your custom container from the drop-down list to the right of the Execute button.
Click the Source System Parameters tab.
Edit the value of the $$INITIAL_EXTRACT_DATE parameter.
Save your changes.
Currency conversions are required because your business might have transactions involving multiple currencies. To create a meaningful report, you have to use a common currency. The Oracle Business Analytics Warehouse stores amounts in the following currencies:
Document currency. The document currency is the currency of the transaction. For example, if you purchase a chair from a supplier in Mexico, the document currency is probably the Mexican peso. Or, if you made a business trip to the United Kingdom and filed an expense report for meal expenses in the UK, the document currency of the expense report will most likely be in GBP.
Local currency. The local currency is the base currency of your ledger, or the currency in which your accounting entries are recorded in.
Global currencies. Oracle BI Applications provides three global currencies, which are the common currencies used by the Oracle Business Analytics Warehouse. For example, if your organization is a multinational enterprise that has its headquarters in the United States, you probably want to choose US dollars (USD) as one of the three global currencies. The global currency is useful when creating enterprise-wide reports. For example, a user might want to view enterprise-wide data in other currencies. For every monetary amount extracted from the source, the load mapping loads the document and local amounts into the target table. It also loads the exchange rates required to convert the document amount into each of the three global currencies. 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 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.
Select your custom container from the drop-down list to the right of the Execute button.
Click the Source System Parameters tab.
Locate the following parameters, and set the currency code values for them in the Value field:
$$GLOBAL1_CURR_CODE (for the document currency).
$$GLOBAL2_CURR_CODE (for the local currency).
$$GLOBAL3_CURR_CODE (for the global currency).
Make sure that you spell the currencies as they are spelled in your source OLTP system.
Save your changes.
When Oracle BI Applications converts your transaction records' amount from document currency to global currencies, it also requires the exchange rate types to use to perform the conversion. For each of the global currencies, Oracle BI Applications also allows you to specify the exchange rate type to use to perform the conversion. Oracle BI Applications also provides three global exchange rate types for you to configure.
Oracle BI Applications also converts your transaction records' amount from document currency to local currency. Local currencies are the base currencies in which your accounting entries and accounting reports are recorded. In order to perform this conversion, Oracle BI Applications also allows you to configure the rate type that you want to use when converting the document currency to the local currency.
To configure exchange rate types
In DAC, select your custom container from the drop-down list to the right of the Execute button.
Click 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
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 3-1 shows the tables used to store calendar information.
Table 3-1 Tables in Base Table W_DAY_D 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 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. Once the records are created in W_DAY_D, the aggregate calendar tables are loaded by their respective SIL mapping.
By default, Oracle BI 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 four months each to determine the fiscal quarter.
To set up the fiscal calendar by fiscal week
Using a text editor, open the file fiscal_week.csv, which is located in the \OracleBI\dwrep\Informatica\SrcFiles directory. Enter the fiscal year, fiscal month, fiscal week and the start date of the 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 because there is no check done within the Informatica mappings.In DAC, do the following:
Go to the Design view, and select your custom container from the drop-down list.
Click the Tasks tab.
Query for the task SIL_DayDimension_FiscalMonth_Extract, and inactivate it by deselecting the Inactive check box.
Query for the task SIL_DayDimension_FiscalWeek_Extract, and activate it by selecting the Inactive check box.
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 a fewer number of days.
To configure the fiscal calendar by fiscal month
Using a text editor, open the file fiscal_month.csv, which is located 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 because there is no check done within the Informatica mappings.In DAC, do the following:
Go to the Design view, and select your custom container from the drop-down list.
Click the Tasks tab.
Query for the task SIL_DayDimension_FiscalWeek_Extract, and inactivate it by deselecting the Inactive check box.
Query for the taskSIL_DayDimension_FiscalMonth_Extract, and activate it by selecting the Inactive check box.
The data in time dimension tables is loaded once during the initial full load. Subsequently, the SIL_*_UpdateFlag mappings run everyday to update the domain value codes, which indicate whether a day, week, month, quarter or year is 'Current', 'Next' or 'Previous' as of the current day. The SIL_Fiscal_UpdateFlag mappings also update the flags that indicate whether a fiscal week, month, quarter or year is 'Current', 'Previous' or 'Next' with respect to the system date.
You might want to extend the range of data that you have in your time dimension tables sometime after the data warehouse is in production. In order to achieve this, follow the steps below to initiate the full load ETL run of W_DAY_D and all the aggregate time dimension tables.
To set up the load strategy of the time dimension table
In DAC, go to the Setup view, click the Physical Data Sources tab, and then click on the connection DataWarehouse.
Select the Refresh Dates subtab in the bottom pane.
The Refresh Dates subtab displays the refresh dates for all of the tables. Double-click on the refresh date for W_DAY_D, and make it null. Do the same for the following aggregate time dimension tables: W_WEEK_D, W_QTR_D, W_MONTH_D, W_YEAR_D,W_FSCL_WEEK_D, W_FSCL_QTR_D, W_FSCL_MONTH_D, and W_FSCL_YEAR_D.
In the Design view, select your custom container from the drop-down list.
Click the Tasks tab, and query for the task SIL_DayDimension.
Set the $$START_DATE and $$END_DATE parameters to the appropriate start date and end date of the new date range.
Save the task.
Note:
Make sure that the new date range defined by the parameters $$START_DATE and $$END_DATE encompasses the old date range entirely. Otherwise, records can be lost. Also make sure you have provided the fiscal information for the new date range also in the appropriate fiscal input file. The next time you run your ETL execution plan, the calendar dimension will be truncated and reloaded with the new date range that you have specified. Since the calendar dimensions use smart keys, none of the fact tables that refer to these tables need to be reloaded.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 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'.
Configuring GL account hierarchies is required 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 How to Configure GL Account Hierarchies Using GL Accounting Flexfield Value Sets Definitions.
Using the Financial Statement Generator (FSG) Report Definition
This option is available only if your source system is Oracle EBS. For instructions, see How to Configure GL Account Hierarchies Using Financial Statement Generator (FSG) Report Definition (for Oracle EBS).
Whichever method you choose to set up GL account hierarchies, you store the hierarchy information in the W_HIERARCHY_D table.
As an example, the hierarchy for a GL account called US Acct might have the following structure:
Node A has child nodes B and C.
Node B has child nodes D and E.
Node C has child nodes F and G.
Node D has child nodes H and I.
Node F has child nodes J and K.
The figure below shows an example of this hierarchy for US Acct.
Table 3-2 shows how the hierarchy for US Acct would be stored in the W_HIERARCHY_D table.
Table 3-2 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-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 |
This example shows that in Chart of Account 4256, "Company" is stored in the segment 3 column in the Oracle EBS table GL_CODE_COMBINATIONS_ALL. In Chart of Account COA4257, "Company" is stored in the segment 1 column in GL_CODE_COMBINATIONS_ALL table. The objective of this configuration file is to make sure that when segment information is extracted into the data warehouse table W_GL_ACCOUNT_D, segments with the same nature from different chart of accounts are stored in the same column in W_GL_ACCOUNT_D.
For example, we can store "Company" segments from COA 4256 and 4257 in the segment 1 column in W_GL_ACCOUNT_D; and Cost Center segments from COA 4256 and 4257 in the segment 2 column in W_GL_ACCOUNT_D, and so forth.
About the ETL Process for GL Accounting Flexfields
Before you run the ETL process for GL accounts, you need to specify the segments that you want to analyze using the ETL configuration file named file_glacct_segment_config_<source system>.csv, located in \OracleBI\dwrep\Informatica\SrcFiles.
In file_glacct_segment_config_<source system>.csv, you need to specify the segments of the same type in the same column. For example, you might store all Cost Center segments from all charts of accounts in one column, and all Company segments from all charts of accounts in another column.
For example, you might want to do the following:
Analyze GL account hierarchies using only Company, Cost Center, Natural Account, and LOB.
You are not interested in using Geography for hierarchy analysis.
Store all Cost Center segments from all COAs in ACCOUNT_SEG2_CODE column in W_GL_ACCOUNT_D.
Store all Natural Account segments from all COAs in ACCOUNT_SEG3_CODE column in W_GL_ACCOUNT_D.
Store all LOB segments from all COAs in ACCOUNT_SEG4_CODE column in W_GL_ACCOUNT_D.
In W_GL_BALANCE_A (where you store GL account balances at aggregated level), you want to store GL account balances at Company and Cost Center level instead of at GL Code Combination level.
Figure 3-3 shows how file_glacct_segment_config_<source system>.csv would be configured to implement the business requirements specified above.
Configuring file_glacct_segment_config_<source system> for U.S. Federal Financials Analytics
For U.S. Federal Financials Analytics, the first two segments are reserved for Fund and Program segments respectively. So, to use one or both of these, configure file_glacct_segment_config_<source system>.csv in this particular order:
Put your Fund segment column name in the 'SEGMENT1' column in the CSV file.
Put your Program segment column name in the 'SEGMENT2' column in the CSV file.
If you do not have any one of these reserved segments in your source system, leave that particular segment empty in the CSV file. To configure any other segments that you may have other than Fund and Program, configure these segments starting from SEGMENT3.
To configure hierarchies with GL accounting flexfields
Configure file_glacct_segment_config_<source system>.csv.
Navigate to OraceBI\dwrep\Informatica\SrcFiles.
Open file_glacct_segment_config_<source system>.csv in a text editor.
Specify the segments you want to analyze.
In DAC, do the following:
Navigate to the Subject Areas tab, and query for 'Financials – General Ledger'.
In the Configuration Tags subtab, do the following:
Query for the tag 'Oracle – Extract Value Set Hierarchies', and make sure the Inactive check box is not selected.
Query for the tag 'Oracle – Extract FSG Hierarchies', and make sure the Inactive check box is selected.
Click Assemble to reassemble the subject area.
Navigate to the Execution Plans tab in the Execute view, and rebuild all execution plans that contain the 'Financials – General Ledger' subject area.
For instructions on building execution plans, see Oracle Business Intelligence Data Warehouse Administration Console Guide.
Run the execution plan for General Ledger Accounts.
A HIER_CODE filter should be specified in the Business Model layer to restrain the output of the logical table to have only one hierarchy. 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 the same as the valueset IDs you configured in the CSV file in the first set.
To specify a filter in the Business Model layer of the Oracle BI Repository to restrain the output of logical table, do the following using the Oracle BI Administration Tool:
Expand each logical table for the hierarchies, for example, 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_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.
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.
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 About Configuring GL Account Hierarchies.
If you need to define GL account hierarchies based on multiple segments within a chart of accounts, you can use the Oracle FSG report definition in Oracle EBS to define them.
You should first use the Oracle FSG form to define a row set or a column set, then Oracle BI Applications will extract the row set or column set definition and convert them into hierarchies.
Oracle FSG hierarchies are extracted from following Oracle EBS source tables:
RG_REPORT_AXIS_CONTENTS
This table defines the relationship between the FSG report axis and GL code combinations. The GL code combinations with segment values within the value range defined for that axis are categorized as children of that axis.
RG_REPORT_AXIS_SETS
This table stores the information for each of the row set or column set you defined. There is one record in this table for each row or column set you defined. Each row includes an axis set identifier, a row or column set name, and a structure identifier to assign a specific chart of accounts to the row set or column set.
RG_REPORT_CALCULATIONS
This table stores formulas for calculating each row or column in the row or column set. An example of a row calculation might be to sum up the amount from the previous five rows. An example of a columns calculation might be to calculate column five by subtracting column four from column three.
For example, in Income Statement, 'Net Income' is the calculation result of 'Gross Profit from Revenue' minus 'Total Expense'. When converting to hierarchy, Net Income becomes the parent of 'Gross Profit from Revenue' and 'Total Expense'. Therefore, hierarchy can be derived based on the information in RG_REPORT_CALCULATION.
The diagram below shows an example hierarchy, with the top level Net Income node having two child nodes, Total Expense, and Gross Profit from Revn, and the Total Expense node having two child nodes, Operating Expense, and Depreciation Expense.
The diagram below shows how an income state is derived from a hierarchy.
The hierarchy above would be converted into a flattened hierarchy and stored in W_HIERARCHY_D in the following format:
Table 3-4 Example of Flattened Hierarchy Stored in W_HIERARCHY_D
| HIER Name | HIER1 | HIER2 | HIER3 | HIER4 | HIER20 |
|---|---|---|---|---|---|
|
Income Statement |
Net Income |
Gross Profit... |
Gross Profit... |
Gross Profit... |
Gross Profit... |
|
Income Statement |
Net Income |
Total Expenses |
Operating Expenses |
Operating Expenses |
Operating Expenses |
|
Income Statement |
Net Income |
Total Expenses |
Depreciation Expense |
Depreciation Expense |
Depreciation Expense |
Fact tables join to the W_HIERARCHY_D table via the GL Account dimension table (W_GL_ACCOUNT_D).
The W_GL_ACCOUNT_D table contains six fields (HIER1_WID, HIER2_WID, HIER3_WID, ...., HIER6_WID), which are foreign keys to the W_HIERARCHY_D.row_wid. Therefore, each General Ledger Code combination can participate in up to six different hierarchies. You can decide which of the six hierarchies to drill on based on the column you use to join to W_HIERARCHY_D. For example, if you want to drill using the third hierarchy, you use W_GL_ACCOUNT_D.hier3_wid = W_HIERARCHY_D.row_wid.
Note:
Mathematical operators, such as '+', '-', '*', '/' (addition, subtraction, multiplication, division, and so on) are not extracted from the FSG definitions. For example, both A + B = C and A - B = C would give the same hierarchy, with a node C having two child nodes A and B (see diagram below).
About the ETL Process for Oracle FSG Report
Before you run the ETL process for GL accounts, you need to specify the hierarchies that you want to reference. To specify the FSG hierarchies that you want to reference, use the file file_gl_hierarchy_assignment_ora.csv, which is located in \OracleBI\dwrep\Informatica\SrcFiles.
In this file, for each chart of accounts, you can specify six FSG hierarchies, using axis_set_id, which is a column from the RG_REPORT_AXIS_SETS table. It is the unique ID of a row set or column set you want to store in the GL account dimension table for the code combinations that belong to that chart of accounts.
The DATASOURCE_NUM_ID field specifies the data source to which the configurations apply. If you have multiple source systems, there might be a chart of accounts across the multiple source systems with the same ID. Therefore, you need to use the DATASOURCE_NUM_ID value to distinguish between them.
For example, suppose you have an income statement FSG report and a balance sheet FSG report and you want to input both of their hierarchy structures into the data warehouse. Oracle BI Applications assumes that both reports are derived from the same set of GL accounts with CHART_OF_ACCOUNTS=101. The axis_set_id of the income statement is 1001, and for the balance sheet, it is 1003. The DATASOURCE_NUM_ID for this application is 2.
In addition, for those GL accounts that belong to the two reports, assume you want to associate their HIER1 column (in GL_ACCOUNT_D) with the income statement hierarchy structure and HIER3 column with balance sheet hierarchy structure.
In this case, you would add one row into file_gl_hierarchy_assignment_ora.csv with the following fields set as below:
CHART OF ACCOUNTS - 101
HIER1_AXIS_SET_ID - 1001
HIER3_AXIS_SET_ID - 1003
DATASOURCE_NUM_ID - 2
(Leave the other row values blank.)
This row indicates that for all of the GL accounts with CHART_OF_ACCOUNTS=101 and DATASOURCE_NUM_ID=2, assigning hierarchies with axis_set_id=1001, null, 1003, null, null, null to HIER1~HIER6 columns respectively. Therefore, after extraction and loading, for those affected GL account rows, HIER1 column will be the foreign key to the income statement hierarchy row ID in W_HIERARCHY_D, and HIER3 column will be the foreign key to the balance sheet hierarchy row ID in W_HIERARCHY_D.
Note: Axis_set_id must be specified in file_gl_hierarchy_assignment_ora.csv for Financial Analytics to load the hierarchies.
To set up hierarchies with FSG Report Definition
Configure file_gl_hierarchy_assignment_ora.csv to specify the hierarchies you want to reference for each CHART_OF_ACCOUNTS.
Navigate to OraceBI\dwrep\Informatica\SrcFiles.
Open file_gl_hierarchy_assignment_ora.csv in a text editor.
Specify the segments you want to analyze.
In DAC, do the following:
Go to the Design view, and select your custom container from the drop-down list.
Click the Subject Areas tab, and query for 'Financials – General Ledger'.
In the Configuration Tags subtab, do the following:
Query for the tag 'Oracle – Extract FSG Hierarchies', and make sure the Inactive check box is not selected.
Query for the tag 'Oracle – Extract Value Set Hierarchies', and make sure the Inactive check box is selected.
Click Assemble to reassemble the subject area.
Navigate to the Execution Plans tab in the Execute view, and rebuild all execution plans that contain the 'Financials – General Ledger' subject area.
For instructions on building execution plans, see Oracle Business Intelligence Data Warehouse Administration Console Guide.
Run the execution plan for General Ledger Accounts.
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 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 layer of the Oracle BI Repository, create a new dimension based on the logical table that you created in the previous step.
Refer to 'FSG Hierarchy 1' as an example.
In the Business Model layer, look for all the logical fact table that has logical join to the logical hierarchy table Dim - FSG Hierarchy1. You will need to create a similar logical join between the new logical hierarchy dimensions that you created and these logical facts, as follows:
Under each of the logical fact table, open the logical table sources and go to the content tab. Under aggregation content, select the 'Show unmapped' check box. It will show all the hierarchies you created in the previous step. For each of these hierarchies, select the logical level to 'Detail'.
In the business model diagram, create a new complex join between each of the new logical hierarchy tables and each of the logical facts. In the join, make sure the cardinality is (0,1) on the dimension side and N on the fact side.
Using 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 any source system. It contains the following topics:
DATASOURCE_NUM_ID is a system column in the Oracle Business Analytics Warehouse that uniquely identifies a data source category and indicates from which source systems the data comes. For example, the value '1' indicates one of the Siebel data sources, and the value 2 indicates an Oracle 11.5.8. data source. Table 3-5 shows the data sources that are supported by Oracle BI Applications and their associated DATASOURCE_NUM_ID values.
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. 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 Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
Table 3-5 Data Sources and Associated DATASOURCE_NUM_ID Values
| Data Source Name | Data Source Number |
|---|---|
|
ORA_11_5_8 |
2 |
|
ORA_11_5_9 |
5 |
|
ORA_11_5_10 |
4 |
|
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 DAC, go to the Setup view, and click the Physical Data Sources tab.
Select the appropriate data source from in list.
On the Edit subtab, 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 perform the steps in section How to Configure the DAC Parameter for Purchase Cycle Line.
This section contains configuration steps that apply to Oracle BI Applications deployed with Oracle EBS source systems.
This section contains the following topics:
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:
Configuration of Product Hierarchy (Except for GL, HR Modules)
How to Map Oracle GL Natural Accounts to Group Account Numbers
This section contains configuration points for product hierarchy in the Product dimension table and the Inventory Product dimension table.
Oracle EBS enables users to group the products using categories and category sets. While Oracle EBS provides pre-packaged category sets, you can also define your own category sets.
By default, 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 How to Identify Category Sets from EBS).
Configure the DAC parameters for product hierarchy (for more information, see How to Configure DAC Source System Parameters for Product Hierarchy).
Configure the hierarchy mapping with the segments (for more information, see How to Configure the Hierarchy Mapping with the Segments).
These steps are part of the task in Configuration of Product Hierarchy (Except for GL, HR Modules).
To find out the category sets that your organization is using
Log in to the Oracle EBS instance.
Click Setup, then Items, then Categories, and then Default Category Sets.
Look for the functional area Inventory and place the cursor in the Category Set field.
Choose Help, then Diagnostics, then Examine, and specify the user password for the application.
Click the Field LOV button and select CATEGORY_SET_ID, and note down the value.
Repeat steps 3-5 for the Purchasing functional area.
These steps are part of the task in Configuration of Product Hierarchy (Except for GL, HR Modules).
To configure the DAC source system parameters for product hierarchy
In DAC, go to the Design view, and select your custom container from the drop-down list.
Click 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 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 11i 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 box.
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. By default, 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 the Product dimension extract
In DAC, go to the Design view, and select your custom container from the drop-down list.
Click the Tasks tab, and query for 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 GL account numbers are mapped to the group account numbers (or domain values) because the metrics in the GL reporting layer use these values. For a list of domain values for GL account numbers, see Oracle Business Analytics Warehouse Data Model Reference.You can categorize 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 GROUP_ACCOUNT_NUM column in the file_group_acct_names.csv file for values you can use. For a list of the Group Account Number domain values, see Oracle Business Analytics Warehouse Data Model Reference. The mappings to General Ledger Accounts Numbers are important for both Profitability analysis and General Ledger analysis (for example, Balance Sheets).
The logic for assigning the accounts is located in the file_group_acct_codes_ora.csv file. The table below shows an example configuration of the file_group_acct_codes_ora.csv file.
Table 3-6 Example Configuration of file_group_acct_codes_ora.csv
| CHART OF ACCOUNTS ID | FROM ACCT | TO ACCT | GROUP_ACCT_NUM |
|---|---|---|---|
|
1 |
101010 |
101099 |
CA |
|
1 |
131010 |
131939 |
FG INV |
|
1 |
152121 |
152401 |
RM INV |
|
1 |
171101 |
171901 |
WIP INV |
|
1 |
173001 |
173001 |
PPE |
|
1 |
240100 |
240120 |
ACC DEPCN |
|
1 |
261000 |
261100 |
INT EXP |
|
1 |
181011 |
181918 |
CASH |
|
1 |
251100 |
251120 |
ST BORR |
In the table above, in the first row, all accounts within the account number range from 101010 to 101099 that have a Chart of Account (COA) ID equal to 1 are assigned to Current Asset. 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_ora.csv file.
You also need to add a new row in the file_grpact_fstmt.csv file. This file specifies the relationship between a group account number and a Financial Statement Item code. 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 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.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 folder (for example, \PowerCenter8.1.1\server\infa_shared\SrcFiles).
Edit the fields in the following table:
| Field Name | Description |
|---|---|
| CHART OF ACCOUNTS ID | The ID of the GL 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.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 How to Map Oracle GL 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 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 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 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.
Query for the 'Financials – General Ledger' subject area. Click the Configuration Tags subtab and verify which of the following Configuration Tags is marked as 'inactive':
Click the Configuration Tags subtab, and verify which of the following Configuration Tags is marked as 'Inactive':
- Financials – Calculate GL Balance
- Oracle – Extract GL Balance
By default, 'Financials – Calculate GL Balance' should be marked as inactive.
Query for the 'Financials – Group Account Number Cleanup' subject area and do the following:
- If in the step above, the configuration tag 'Financials – Calculate GL Balance' is marked as 'Inactive', then 'Financials – Calculate GL Balance Reverse' should be marked as 'Inactive' as well.
- If in the step above, 'Oracle – Extract GL Balance' is marked as 'Inactive', then 'Financials – Calculate GL Balance Reverse' should be active, that is, the check box should not be selected.
If you need to make any change in the step above, then you need to reassemble the 'Financials – Group Account Number Clean Up' subject area and then rebuild the appropriate 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 BI Applications deployed with Oracle EBS source systems. It contains the following topics:
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 Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
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 Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
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 Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
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 BI Applications deployed with PeopleSoft source systems.
This section contains the following topics:
Not applicable to Oracle BI Applications release 7.9.5.1.
This section contains additional configuration steps that apply to PeopleSoft. It 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 3-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 the 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.
Reconfigure the DAC settings, as follows:
In DAC, go to the Design view, select your custom container from the drop-down list, and click 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, go to the 'Source Tables' tab and 'Target Tables' tab for these tasks.
Add your tasks into Configuration Tags: In the Design view, click the Configuration Tags tab. Select 'PeopleSoft - Populate Business Unit Related Hierarchies' item. Select the Tasks subtab. Click Add/Remove. Query for the new mappings you just entered into DAC. Select the mappings, and add them into the right panel. Click OK, then click Save.
Reassemble the Financial or Human Resources related subject areas: In the Design view, click the Subject Areas tab. Query for the appropriate subject area, and click Assemble. Repeat this for each of the Financial-related subject areas (Cost of Goods Sold, General Ledger, Payables, Receivables, Revenue) and human resources-related subject areas (HR - Event Information, HR - Operation, HR - Payroll).
Rebuild all execution plans that contain the subject areas you reassembled: Go to the Execute view, and click Execution Plans. Query for the execution plan for your PeopleSoft Financial Application (for example Financials_PeopleSoft 8.8), and click Build.
This section contains configuration steps that apply to Oracle BI Applications deployed with Siebel source systems.
This section contains the following topics:
Configuration Required Before A Full Load for Siebel Source Systems
Configuration Steps for Controlling Your Data Set for Siebel Source Systems
Not applicable to Oracle BI Applications release 7.9.5.1.
Not applicable to Oracle BI Applications release 7.9.5.1.
|
 Copyright © 2009, Oracle. All rights reserved. Legal Notices |
|