Functional Configurations for OBIA Financial Analytics

This is a continuation of articles under OBIA.In the previous article I had mentioned about domain load. Once the domain load is complete the next step is to complete the set of tasks for the implementation module selected. This can be accomplished either through BIACM or FSM.

                      

Some of the important configurations to be completed for financials are:

Data load Parameters

 Data load parameters are used to control the nature of data loaded from the transactional system into the Business Analytics Warehouse. Fo financial analytics implementation 3 keys configurations are :

FSG enabled

Oracle provides the FSG hierarchy dimensions and you can view it from the seeded RPD in BMM layer.If you are not planning to load the FSG tables (GL Hierarchy tables) it is advisable to leave this option set to N. If not data load will take much longer to complete with FSG enabled. Some of the tables loaded for FSG are:

W_GL_ACCOUNT_DH

W_ORA_GL_HIER_AXCONTENT_DH_TMP

W_ORA_GL_HIER_DH_TMP

W_ORA_GL_HIER_NORM_HIER_DH_TMP

W_ORA_GL_HIER_RGE_DERV_DH_TMP

W_ORA_GL_HIER_CCID_DH_TMP

W_ORA_GL_HIER_CCID_REF_DH_TMP

The temporary table has to be set to a greater value since the ETL process with FSG consumes more table space.Here is a screen shot of the FSG hierarchy dimension provided by oracle.

Some customizations to RPD and dashboards would be needed to bring over those dimensions to get FSG report in BI.

Fed financial enabled

This option is for federal financials and it is suggested to turn it off if not using since the ETL is going to go over the packages related to Federal financials.

Initial extract date

It determines the date from which the data from the source system has to be loaded into your warehouse tables.

 Reporting parameters

Some of the reporting parameters are to be set for financials analytics though it may seem to belong to other modules. This is defined in the task list as well.Master Organization, inventory caterogy set, purchasing category set are some of the reporting columns . If you do not have these setup, during the financial data load fails since product and inventory dimension tables are loaded. Now for such configurations you would need input from your inventory control SMEs.

An important note on Product Category Sets:Product category set having multiple categories assigned for an item is not supported. It causes duplicates in Product Dimensions during data load of financial module.

Domain Mappings and hierarchies

Group Account Configuration.

Domains are pre-seeded dimensional values that help define business metrics. For example, the data in domain Source Group Account (BI_GROUP_ACCOUNT) extracts and loads into the domain Group Account (W_GL_GROUP_ACCOUNT).

Group Account Number Configuration is an important step in the configuration of Financial Analytics, because it determines the accuracy of the majority of metrics in the General Ledger and Profitability module.

 

Group account to financial statement Item

By mapping your GL accounts against the group account numbers and then associating the group account number to a Financial Statement Item code, you have indirectly associated the GL account numbers to Financial Statement Item codes as well. This association is important to perform GL reconciliation and to ensure the subledger data reconciles with GL journal entries.

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.

 

Why we cannot extend the target domain codes.

Since BIAPPS product supports multiple source systems – that have a set of values which will cater to all sources and those are the conformed domains. 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.

In the next article I will continue on full data load

Oracle Business Intelligence Applications(OBIA)11.1.1.8.1 with ODI

What is OBIA

Oracle BI Applications is a suite of prebuilt business intelligence solutions that deliver role-based intelligence. Oracle BI Applications support prebuilt aggregation and analysis of information from a variety of transactional applications, including Oracle E-Business Suite, Oracle Siebel, PeopleSoft, JD Edwards EnterpriseOne, as well as non-Oracle applications. These transactional applications are referred to source systems, because they provide the underlying data that source analyses.

 Business case for OBIA

In the above introduction it is mentioned BI solution is pre built and it means:

• A prebuilt data warehouse schema, the Oracle Business Analytics Warehouse, with associated extract, transform, and load (ETL) metadata and data-movement infrastructure to support aggregation and transformation for the analysis of data from all transactional sources.
• A pre-built Oracle BI Applications metadata repository to support analysis of the data in the Oracle Business Analytics Warehouse.
• A suite of metrics pertaining to how organizations measure performance that your company can select from and apply to your particular line of business.

Benefits of a packaged application are quicker time to deploy, less overall cost, an enterprise view of data, role-based views for all employees, and built-in best practices. For any organization or company  who own the transactional source system of Oracle Ebusiness Suite,Siebel,PeopleSoft ,JDEdwards  OBIA can drastically reduce the time and cost to deploy a BI solution It also provides the flexibility to introduce and implement the analytics one module at a time, for eg: You can implement Financials and then move on HR analytics.

OBIA ERP Analytics consists of the following modules:

Oracle Financial Analytics

Oracle U.S. Federal Financial Analytics

Oracle Human Resources Analytics

Oracle Manufacturing Analytics

Oracle Procurement and Spend Analytics

Oracle Project Analytics

Oracle Supply Chain and Order Management Analytics

Oracle Student Information Analytics

OBIA Version

I will be going over the new release of OBIA 11.1.1.7.1/11.1.1.8.1 with ODI.  Earlier version that is OBIA  7.9.6 had Informatica as its ETL architecture. The topics I will be covering will be based only the new release of OBIA 11.1.1.8.1 and not an upgrade from 7.9.6 .

The oracle site has only 11.1.1.8.1 for download .So I will be referring to only 11.1.1.8.1 installation and configuration steps. The upgrade path from 11.1.1.7.1 to 11.1.1.8.1 is available on the OTN site. Both have the same BI components but the latest version 11.1.1.8.1 has some bug fixes and enhancements to 11.1.1.7.1 version.

The most significant difference between OBIA 11.1.1.7.1 and 11.1.1.8.1 that I have noticed are

• New Data Lineage Tool allows customers and implementers to track lineage from source to target - improving information transparency across their enterprise 
• Configuration Management expands System Setup and Load Plan definition capabilities, including the registration of multiple instances for the same Product Line Version, UI enhancements to support DW connection configuration and Load Plan definition, and Load Plan definition migration

The different components of OBIA 11.1.1.8.1 are

• Admin console
• BIACM(Business Intelligence Applications Configuration Manager)
• FSM(Functional Setup Manager)
• ODI Console(Oracle Data Integrator Console)
• RPD(Repository)
• Catalog

Success Factors for implementing OBIA 11.1.1.8.1 with ODI Successful BI implementation is a collaborative effort of functional and technical teams in an organization. BI implementation is successful and useful only when the data on the dashboards are meaningful.  And when does data become meaningful in dashboards?  Look at the diagram below:

This diagram depicts the different components that are responsible for the display of meaningful data on your dashboards. Functional configurations is an important task at each and every stage of BI implementation since accurate configurations and mappings translate to accurate data on dashboards. These steps need input from the subject matter experts (SMEs) or the super users of your transactional source system to get the data right. And when the technical folks show the BIACM and FSM UIs to functional SMEs for their input, the questions they would be asked are:

• This UI does not make sense.
• What is all that Mapping about?
• Why you need that input?

The answer that would help:

These functional setups, based on either business requirements or on transactional source system settings or configurations, direct the manner in which relevant data is moved and transform d from source database to target database. Functional setups are also called functional configurations which provides accurate and successful movement of data from (source system) EBS to (database to target Business Analytics Warehouse) Dashboard and they Control the manner in which data is displayed.

 Implementation Phases of OBIA 11.1.1.8.1 with ODI

I will be going over the implementation phases for Oracle BI Applications. A typical product lifecycle consists of the following process, some of which may happen concurrently.

• Installation
• OBIA architecture and components
• Configurations
• Data Load-Domain Load/Data Load/Incremental Load/Issues in Data Load
• Customization to RPD
• Customization to Dashboards
• Migration

Creating target table from Designer

We can create target table from Designer itself using the below way:

1) In target designer first create a target with all the necessary columns.
2) Then click Target--> Generate/Execute SQL.
3) A popup like below is shown:

4) Here we give the filename which will contain the create DDL query.
5) Then we connect to the database where we need to create the table using "Connect" button.
6) Then select the tables for which we need to generate and also select "Generation options" as required.
7) Click the "Generate SQL file " button . This will put the DDL in the filename we mentioned.
8) We can click "Edit SQL file" to edit the file..ex: changing the database name and making small changes to the column definitions.
9) Then click "Execute SQL file". In the output window you will see the result of the execution.
10) We can even click "Generate and Execute" if we want to generate and directly execute the DDL.

Sorter Transformation

Sorter Transformation:

• Active and Connected. Active as when we use distinct option the number of rows might reduce.
• We can use sorter to sort data in ascending or descending order according to a specified sort key.
• You can sort data from relational or flat file sources. We can also use it to pass data to aggregator transformation configured for sorted input.
• The Sorter transformation contains only input/output ports.
• Sort key could be single or multiple columns. When multiple columns are used the order the ports appear in the Ports tab determines the
  succession of sort operations.

• Sorter transformation properties:

1. Sorter Cache Size: The Integration Service uses the Sorter Cache Size property to determine the maximum amount of memory it can allocate to perform the sort operation. The Integration Service passes all incoming data into the Sorter transformation before it performs the sort operation. You can configure a numeric value for the Sorter cache, or you can configure the Integration Service to determine the cache size at run time. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to allocate to the cache.
    

If the Integration Service runs a partitioned session, it allocates the specified amount of Sorter cache memory for each partition.

If it cannot allocate enough memory, the Integration Service fails the session.

Sorter cache size is set to 16,777,216 bytes by default.

2. Case Sensitive: When you enable the Case Sensitive property, the Integration Service sorts uppercase characters higher than lowercase characters.
3. Work Directory: You must specify a work directory the Integration Service uses to create temporary files while it sorts data. After the Integration Service sorts the data, it deletes the temporary files. By default, the Integration Service uses the value specified for the $PMTempDir process variable.

4. Distinct Output Rows: You can configure the Sorter transformation to treat output rows as distinct. You can configure the Sorter transformation to treat output rows as distinct.

5. Tracing Level: Configure the Sorter transformation tracing level to control the number and type of Sorter error and status messages the Integration Service writes to the session log.

6. Null Treated Low: Enable this property if you want the Integration Service to treat null values as lower than any other value when it performs the sort operation. Disable this option if you want the Integration Service to treat null values as higher than any other value.

7. Transformation Scope:

♦ Transaction . Applies the transformation logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction, but does not depend on rows in other transactions.

♦ All Input. Applies the transformation logic on all incoming data. When you choose All Input, the PowerCenter drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source.

Aggregator Transformation

                                                     Informatica Transformations

Aggregator Transformation  

• Active and Connected.
• The Aggregator transformation performs aggregate calculations, such as averages and sums.
• The Aggregator transformation makes use of aggregator cache to perform aggregations.
• What is Difference between Aggregator and Expression Transformation: Aggregator transformation to perform calculations on groups. The Expression transformation permits we to perform calculations on a row-by-row basis only.
• What is incremental aggregation option:

After we have used the aggregator transformation in our mapping we can can enable the session option, Incremental Aggregation. Note that this is done using session properties option. This means this can be done only through session and not When the Integration Service performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculations incrementally.

Explain Components of aggregator transformation:

The Aggregator transformation has the following components and options:

• Aggregate cache. The Integration Service stores data in the aggregate cache until it completes aggregate calculations. It stores group values in an index cache (when we are using group by) and row data in the data cache.

• Aggregate expression. Enter an expression in an output port. Note that we need to create a new output port and then enter the expression. We cant do that on input-output port.The expression can include nont-aggregate expressions and conditional clauses.

• Group by port. Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.

• Sorted input. Select this option to improve session performance. To use sorted input, we must pass data to the Aggregator transformation sorted by group by port, in ascending or descending order.

 Explain Aggregator Transformation properties:

Property	Description.
Cache Directory	Local directory where the Integration Service creates the index and data cache files. By default: $PMCacheDir. If we have enabled incremental aggregation, the Integration Service creates a backup of the files each time we run the session. The cache directory must contain enough disk space for two sets of the files.
Tracing Level	Amount of detail displayed in the session log for this transformation.
Sorted Input	Indicates input data is presorted by groups. Select this option only if the mapping passes sorted data to the Aggregator transformation.
Aggregator Data Cache Size	Data cache size for the transformation. Default cache size is 2,000,000 bytes. we can configure the Integration Service to determine the cache size at run time, or we can configure a numeric value. If we configure the Integration Service to determine the cache size, we can also configure a maximum amount of memory for the Integration Service to allocate to the cache.
Aggregator Index Cache Size:	Index cache size for the transformation. Default cache size is 1,000,000 bytes. we can configure the Integration Service to determine the cache size at run time, or we can configure a numeric value. If we configure the Integration Service to determine the cache size, we can also configure a maximum amount of memory for the Integration Service to allocate to the cache.
Transformation Scope	Specifies how the Integration Service applies the transformation logic. Transaction: Applies the transformation logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction, but does not depend on rows in other transactions. Traansaction commit points can be created using Transaction control transformation. All Input: Applies the transformation logic on all incoming data. When we choose All Input, the PowerCenter drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source. This is what it generally should be.

All the above properties can be further overridden at the session level:

• How does aggregator use Aggregator cache:

1. The Integration Service creates index and data caches in memory to process the transformation. If the Integration Service requires more space, it stores overflow values in cache files. Note that only if more space is required cache files are created.
2. We can configure the index and data caches in the Aggregator transformation or in the session properties.
3. Why no cache required for Sorted inputs:

The Integration Service uses memory to process an Aggregator transformation with sorted ports. It does not use cache memory. We do not need to configure cache memory for Aggregator transformations that use sorted ports

• Explain Aggregate expressions:

1. The Designer allows aggregate expressions only in the Aggregator transformation. An aggregate expression can include conditional clauses and non-aggregate functions.
2. It can also include one aggregate function nested within another aggregate function, such as: MAX( COUNT( ITEM ))

• What Aggregate functions can be used:

1. Following functions can be used with aggregator:

♦ AVG

♦ COUNT

♦ FIRST

♦ LAST

♦ MAX

♦ MEDIAN

♦ MIN

♦ PERCENTILE

♦ STDDEV

♦ SUM

♦ VARIANCE

2. We can nest one aggregate function within another aggregate function.

3. We can include multiple single-level or multiple nested functions in different output ports in an Aggregator transformation. However, we cannot include both single-level and nested functions in an Aggregator transformation. Therefore, if an Aggregator transformation contains a single-level function in any output port, we cannot use a nested function in any other port in that transformation. When we include single-level and nested functions in the same Aggregator transformation, the Designer marks the mapping or mapplet invalid. If we need to create both single-level and nested functions, we should create separate Aggregator transformations.

4. We can Use conditional clauses in the aggregate expression to reduce the number of rows used in the aggregation. The conditional clause can be any clause that evaluates to TRUE or FALSE.

Ex: SUM( COMMISSION, COMMISSION > QUOTA )

5. We can also use non-aggregate functions in the aggregate expression.

6. When we configure the Integration Service, we can choose how we want the Integration Service to handle null values in aggregate functions. we can choose to treat null values in aggregate functions as NULL or zero. By default, the Integration Service treats null values as NULL in aggregate functions.

Define a default value for each port in the group to replace null input values. This allows the Integration Service to include null item groups in the aggregation. 

• Explain how to use Group By Ports:

1. The Aggregator transformation lets we define groups for aggregations, rather than performing the aggregation across all input data.
2. To define a group for the aggregate expression, select the appropriate input, input/output, output, and variable ports in the Aggregator transformation. we can select multiple group by ports to create a new group for each unique combination.

3. When we group values, the Integration Service produces one row for each group. If we do not group values, the Integration Service returns one row for all input rows. The Integration Service typically returns the last row of each group (or the last row received) with the result of the aggregation. (unless we some function like FIRST).

4. When selecting multiple group by ports in the Aggregator transformation, the Integration Service uses port or der to determine the order by which it groups.

5. Use non-aggregate expressions in group by ports to modify or replace groups. For example, if we want to replace ‘AAA battery’ before grouping, we can create a new group by output port, named CORRECTED_ITEM, using the following expression:

IIF( ITEM = 'AAA battery', battery, ITEM )

• Explain how to Use Sorted Input:

1. We can improve Aggregator transformation performance by using the sorted input option. When we use sorted input, the Integration Service assumes all data is sorted by group and it performs aggregate calculations as it reads rows for a group.
2. Sorted input uses only memory and no cache is involved and hence it is beneficial as far as performance is concerned.
3. This prevent aggregator to wait for entire data to be first read.

When we do not use sorted input, the Integration Service performs aggregate calculations as it reads. Since the data is not sorted, the Integration Service stores data for each group until it reads the entire source to ensure all aggregate calculations are accurate.

4. If we use sorted input and do not presort data correctly, we receive unexpected results.
5. Do not use sorted input if either of the following conditions are true:

♦ The aggregate expression uses nested aggregate functions.

♦ The session uses incremental aggregation. Very imp to note that when using incremental aggregation we cannot use sorted input.

6. Data must be sorted in the following ways:

♦ By the Aggregator group by ports, in the order they appear in the Aggregator transformation.

♦ Using the same sort order configured for the session.

7. For relational and file sources, use the Sorter transformation to sort data in the mapping before passing it to the Aggregator transformation.

If the session uses relational sources, we can also use the Number of Sorted Ports option in the Source Qualifier transformation to sort group by columns in the source database. Group by columns must be in the same order in both the Aggregator and Source Qualifier transformations.

Note: We cannot add expressions to existing input-output ports. So we need to create new output or variable ports.

Tips:

Limit the number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.