Advertisements

DAC Execution Plan Failure – “No active subject areas for execution plan”

When your Oracle Business Intelligence Applications (OBIA) DAC Execution Plan fails with a message that looks like this …

“Retrieving execution plan informationMESSAGE:::com.siebel.analytics.etl.execution.exceptions.EmptyExecutionPlanException: No active subject areas for execution plan were found
EXCEPTION CLASS::: com.siebel.analytics.etl.execution.ExecutionPlanInitializationException

com.siebel.analytics.etl.execution.ExecutionParameters.(ExecutionParameters.java:111)
com.siebel.analytics.etl.execution.ExecutionParameterHelper.(ExecutionParameterHelpe”

It is likely that you have a missing Subject Area in your Execution Plan in DAC.  You need to attach the Subject Area to the Execution Plan and rebuild the Plan.

After doing this your job should run successfully.

 

Advertisements

Creating a Custom Landing Page or Custom Home Page for your OBIEE / OBIA environment

Your organization may want to have a custom home page or landing page for your OBIEE or OBIA environment.  (I will use the term “Landing Page” going forward to not confuse it with the OBIEE delivered “Home Page”).  When users log in, they need to be automatically taken to this custom landing page instead of to the delivered OBIEE Home Page.

This post describes some of the reasons you may want a custom landing page, the content that could be on the page, how to automatically navigate users to the page, and security associated with the page.

Why would you want to create a Custom Landing Page?  The reasons will vary by organization, but these could be some of the reasons:

  1. Deliver the look and feel that your company or users desire.
  2. Allow for a place that serves as a central location for the content you want to emphasize, in the way you want to display it.
  3. Provide a central place for messages of any kind for your users.

What content will be on this Custom Landing Page?  Some of the possibilities are:

  1. Create a page with your custom logos, images, and colors that are in line with your company’s or department’s branding.
  2. A section with messages for your user community. This information could include things such as:
    1. The date/time of the last data load?
    2. The sources of the information displayed on your dashboards
    3. Information about recent dashboard releases
    4. Upcoming downtime
    5. Upcoming events such as user training events
    6. Action needed by the user community
  3. A section that lists links to useful resources, such as:
    1. user’s guides or tutorials
    2. dashboard and report glossary
    3. analysis/report request forms
    4. Security/Access Request forms
    5. general OBI information
  4. A section with Contact Information – containing information about who, what, when, how to contact people for help or information, or how to submit new requests for data/analyses/reports, maybe by functional area, etc.
  5. An area to display your company’s or division’s top key performance indicators (KPIs). These should be limited to just a few – I would say not more than 5 – and they should be relevant company-wide or “OBI user community-wide”.
  6. Links to dashboards. You may create an area or areas of links to various dashboards. Your dashboard list may include many of your dashboards or just a select few that you know are frequently used or that you want to emphasize.

All users that are authorized to use the OBI system will have access to this page.  So, maybe BI Consumer role will be provided access.

However, you will need to set security on the sections containing links to dashboards to allow access only to those authorized for the each set of dashboards.

Once your custom landing page is ready, you will then need to set it as the default page for users (or a subset of users).  To do this you will need to create an initialization block that sets the PORTALPATH built-in OBI variable to point to the new landing page dashboard page.

One final note … you can have multiple custom landing pages if you desire, for example, a different page for each division or a different page for each major group of users.  You would then need to set the PORTALPATH variable based on the user’s profile.

Good luck with your custom landing page project.

OBIA Financial Analytics – SIL_APTransactionFact_DiffManLoad performance issues

We are on Oracle Business Intelligence Applications (OBIA) 7.9.6.3 and had been experiencing performance issues with the SIL_APTransactionFact_DiffManLoad workflow/mapping. We tried a number of things but only had minimal improvements.   Eventually, I found a solution for the poor performance on Oracle Support.  This change resulted in a drastic improvement of this workflow.

The solution can be found on Oracle Support (http://support.oracle.com – Oracle Doc ID: 1446397.1), but for your convenience I have included the content below.  There are other mappings that have a similar problem.

————————————————————————

OBIA 7963: SIL_APTransactionFact_Diffmanload Mapping And Performance Issue (Doc ID 1446397.1)

In this Document
Symptoms
Cause
Solution

 Applies to:
Informatica OEM PowerCenter ETL Server – Version 7.9.6.3 [AN 1900] and later Information in this document applies to any platform.

Symptoms
The OBIEE application (7.9.6.3) ETL task “SIL_APTransactionFact_DiffManLoad” has run over 68 hours during full load execution.

Cause

  1. The size of these columns (DOC_HEADER_TEXT and LINE_ITEM_TEXT )  in DAC is 255 (except AP where its 1020 in DAC and Infa). But in Informatica the size for these two columns is 1020. Ideally it should be 255. This is a known performance issue.
  2. The cause of the problem has been identified in unpublished  Bug 12412793- PSR: B16 INCREMENTAL: SIL_GLREVENUEFACT,

Solution
Below are the steps you will follow to modify the size of the fields in the lookup.

  1. Take a backup of existing Lookups ( LKP_W_AP_XACT_F and LKP_W_AR_XACT_F ).
  2. Login to Informatica Designer >Transformations
  3. Open the lookup and modify the size of the fields. The port lengths for the DOC_HEADER_TEXT and LINE_ITEM_TEXT were changed to 255 .
  4. Save the changes
  5. Rerun the test and confirm the performance issue is resolved  and migrate the changes in PROD.

————————————————————————

Oracle Business Intelligence Applications (OBIA) Fact Tables

Dimensionally modeled (star-schema designed) data warehouses are primarily made up of two types of tables – Fact and Dimension.  Fact tables store the measurements generated by business events (# of orders, amount of dollars, etc.); and Dimension tables store the descriptive attributes that provide context to the measurements (product [product name], customer [customer type], date, etc.).

This post describes the types of Fact tables found in Oracle Business Intelligence Applications (OBIA) data warehouse – Oracle Business Analytics Warehouse (OBAW).  There will be future posts that describe in detail the other table types in OBIA (Dimension, Internal, etc.).

The 5 types of Fact tables used in the OBAW are:

  1. Transactional
  2. Aggregate
  3. Cycle Lines
  4. Snapshot
  5. State Transition.

The Transactional Fact Table is the main type of fact table. It stores the lowest-level of information from transactional sources. An example of a Fact table in OBIA (Financial Analytics) is: W_GL_BALANCE_F
Note: Fact tables in OBIA end with “_F”.
This table stores the current balance for GL accounts by GL_ACCOUNT and other dimensions.

The Aggregate Fact Table is typically used for performance improvements.  It is a summarized or rolled-up version of the Transactional fact table.  Instead of querying the data at the transactional level – which is the most detailed level and the level with the most records, the Aggregate table allows you to query the data at a more rolled up level when appropriate.  One of the most frequent roll-ups is time – for example, a transactional table at a day level is rolled up to the month level.
Aggregate tables can be tens of times less (or even hundreds) than their transactional versions.  These types of tables are also very common in OBIA and in data warehousing in general.

An example of an Aggregate Fact Table in OBIA (Financial Analytics) is: W_GL_BALANCE_A
Note: Aggregate Fact tables in OBIA end with “_A”.
This table stores the GL account balances aggregated by GL Account Segment and other dimensions. Instead of having data at the GL_ACCOUNT level as in the Transactional fact table, the data is at the GL Account Segment level in the Aggregate table.  Aggregate Fact tables are derived from Transactional Fact  Tables or other Aggregate Fact tables. This table is derived from the transactional fact table mentioned above: W_GL_BALANCE_F.

The Snapshot Fact Table stores “snapshots” of measurements taken at well-defined, predetermined time intervals – such as daily, monthly, annually, etc.  Examples include Inventory and Account Balance snapshots, and AR/AP aging snapshots.  Common items such as financial reports or bank statements are examples of reports from Snapshot Fact tables.

An example of a Snapshot table in OBIA(Supply Chain Analytics) is: W_INVENTORY_DAILY_BAL_F
Oracle’s description of this table will help to clarify its makeup and purpose.
The W_INVENTORY_DAILY_BAL_F fact table is used to represent at a point in time information of all inventory balances and inventory values related to products whose inventory is maintained by the business organization, these would typically include all inbound (purchased from external entities) products as well as outbound (sold to external entities) products. The inventory balance information is trended by copying historical snapshot information from this table at periodic points in time into history table W_INVENTORY_MONTHLY_BAL_F.
The W_INVENTORY_MONTHLY_BAL_F table stores a snapshot of inventory balance.
There is one row for each product and product storage location whose point in time inventory quantity and value information is maintained. The storage location could represent a warehouse or further divisions within a warehouse. This aspect is configurable within the product. All the dimension key links to the other Oracle Business Analytics Warehouse dimension tables, such as W_DAY_D, W_BUSN_LOC_D, W_PRODUCT_D, W_INVENTORY_PRODUCT_D, and so on, represent information associations at that point in time for that product inventory information. The DATE_WID column represents the date on which the inventory balance information is valid.

These tables can also have Aggregate versions:
As mentioned in the description for the W_INVENTORY_DAILY_BAL_F table above, there is an aggregate version.  However, snapshot tables are not necessarily aggregated like transactional tables, because many times the measures are non-additive or semi-additive. For example, you would not take your account monthly balance in January and add it to your account monthly balance in February to determine how much money you have – that would be wrong.

The W_INVENTORY_MONTHLY_BAL_F fact table is used to represent the monthly information of all the inventory balances and the inventory values related to products whose inventory is maintained by the business organization. This information includes all inbound (purchased from external entities) products and outbound (sold to external entities) products. The aggregation period is configurable, and has a preconfigured value of Monthly.
There is one row for each product and product storage location whose point in time (as of a month) inventory quantity and value information is maintained. All the dimension key links to the other Oracle Business Analytics Warehouse dimension tables such as W_DAY_D, W_BUSN_LOC_D, W_PRODUCT_D, W_INVENTORY_PRODUCT_D, and so on, and represents information and associations at that point in time for that product inventory information. The PERIOD_START_DT_WID and PERIOD_END_DT_WID column represents the aggregation bucket start and end dates. The column INV_BALANCE_DT_WID represents the date within this aggregation period on which the inventory balance information is valid.

The Cycle Lines Fact Table store measurements for multiple related business events and are therefore typically derived from multiple fact tables. They typically store process cycle times or provide the ability to easily determine process cycle times.  These tables are also called Accumulating Snapshot Fact tables because they are snapshots of different events accumulated on each other.  An example of a Cycle Lines Fact table is W_PURCH_CYCLE_LINE_F.

Here is Oracle’s description of the table which should help clarify its purpose: W_PURCH_CYCLE_LINE_F table tracks the time duration of all events pertaining to the purchase process commencing with a requisition. Information in this table enables analysis of the direct spend process within an organization beginning with a purchase requisition, its approval, the creation of an approved purchase order, its submission to a supplier, the creation of a purchase schedule and ending with its receipt of the products. It can be used to calculate the time taken to receive products that have been ordered, the time between the first receipt and last receipt of products that have scheduled for delivery. The W_PURCH_CYCLE_LINE_F table contains all the various dates associated with the processes such as submission, approval, ordering and receiving as well as quantities and amounts. While Other spend related fact tables capture individual process such as requesting, ordering, scheduling this table combines all the in one place for ease of analysis and reporting.

These Cycle Lines tables can also have aggregate versions. For example: W_PURCH_CYCLE_LINE_A This is an aggregate table of W_PURCH_CYCLE_LINE_F at a higher level of dimensionality. The Product dimension is replaced by a Product type dimension to give a high level analysis of the sourcing data. It stores Purchase Cycle Line records aggregated over a preconfigured Monthly time period and product types.

State Transition Fact Tables store state-transition metrics based on business events, such as customer state – new, top, dormant, lost, etc – based on the customer order activity.  These tables store or allow you to easily derived counts of the various states.  State Transition Fact tables are derived from Transactional or Snapshot fact tables.

Below are two examples of State Transition Fact tables in OBIA (Marketing Analytics):

The Customer Status History Fact: W_CUSTOMER_STATUS_HIST_F
This is a fact table that tracks the status of customers based on the frequency of orders they place with the organization. Possible statuses are NEW, RECENT, DORMANT and LOST. The time duration for each status bucket is configurable, out of the box being a calendar year.
The grain of this table is at a Customer, Customer Status and the Status Start Date level. Other important columns in this table include the Sold to and the Ship to location for the customer. These are derived based on the status bucket start date against the Customer Locations dimension table.

The Loyalty Member Status History Fact: W_LOY_MEMBER_STATUS_HIST_F
W_LOY_MEMBER_STATUS_HIST_F Fact table stores status changes of Loyalty members. Grain: One record for each member status changed.

That’s it for OBIA fact tables.  Understanding the types of fact tables and their purpose helps us to make better design choices when we set out to build new fact tables to represent business events, and it also helps us to quicker recognize and better analyze the data in these tables.
I hope you found this information useful. If you have information about other fact table types, please share.

Informatica Unconnected Transformations

There are 3 Informatica transformations (External Procedure, Lookup, and Stored Procedure) that can be unconnected in a valid mapping.  An unconnected transformation is one that is not connected to the pipeline – that is, it is not connected to any other transformation via a link.  Unconnected transformations, especially unconnected lookup transformations, are widely in OBIA mappings.

Unconnected transformations are defined in a mapping and are called / invoked from another transformation in the mapping.  They can be called from any transformation that supports the transformation expression language.

Below is a list of the 3 Informatica Unconnected Transformations and how they are called or invoked from another transformation:

 Transformation  How its called / invoked
 External Procedure  :EXT.external_procedure_transformation(argument1, argument2, …)
 Lookup  :LKP.lookup_transformation(argument1, argument2, …)
 Stored Procedure  :SP.stored_procedure_transformation( argument1, argument2, [, PROC_RESULT])

Any mapping with an unconnected transformation must also include another transformation that calls the unconnected transformation.

All of the 3 Unconnected Transformations can also be used in connected mode.  However, when you want to execute the transformation logic for a subset of the rows passing through the mapping (especially when it’s a small percentage of the rows), you will want to consider using the transformations in unconnected mode and with conditional logic.  The conditional logic will cause the unconnected transformation to execute only when necessary, and therefore, may improve perfiormance.

The example below shows a call to an unconnected lookup transformation (lkp_item_id) that is invoked only when the desired item_id value is NULL.  In a case where only a small percentage of the rows have an item_id that is NULL, then the lookup would only be invoked for a small number of rows, as opposed to all rows if the transformation was connected.

                       IIF  ( ISNULL(item_id),   :LKP.lkp_item_id ( item_name ),   item_id )

As a side note, the Sequence Generator transformation can also be invoked from a function call, but it is a connected transformation.

 Transformation  How its called / invoked
 Sequence Generator  :SEQ.sequence_generator_transformation.CURRVAL

Informatica Transformations Frequently used in OBIA

These are some of the Informatica transformations that are frequently used in Oracle Business Intelligence Applications (OBIA).  The OBIA SDE and SIL mappings used to load the Oracle Business Analytics Warehouse (OBAW) are built using these and other transformations.

1. Source Qualifier
The Source Qualifier transformation is used to bring data from one or more tables from the same source into the mapping.  If being used for more than one table, then a join condition needs to be defined between the tables.  The typical naming convention for a Source Qualifier transformation is SQ_* or sq_*.

2. Joiner
The Joiner transformation is used to join tables in different data sources.  The typical naming convention for a Joiner transformation is JNR_* or jnr_*.

3. Expression
The Expression transformation is used to perform simple row-based calculations or derivations.  The typical naming convention for an Expression transformation is EXP_* or exp_*.

4. Filter
The Filter transformation is similar to a where clause in SQL – it adds a conditional filter to the data passing through the mapping.  The typical naming convention for a Filter transformation is FIL_* or fil_*.

5. Aggregator
The Aggregator transformation is used to perform aggregate calculations on the data passing through the mapping, for example, performing a sum or max.  The typical naming convention for an Aggregator transformation is AGG_* or agg_*.

6. Lookup
The Lookup transformation is used to lookup values based on another known/submitted value, and pass the looked up value into the mapping.  There are 2 types of Lookups – connected and unconnected.  The typical naming convention for a Lookup transformation is LKP_* or lkp_*.

7. Update Strategy
The Update Strategy transformation is used to determine and perform the appropriate course of action for data in the mapping.  Based on the determined state of the data, the transformation is used to insert, update, delete or reject records.  The typical naming convention for an Update Strategy transformation is UPD_* or upd_*.

Components of Oracle Business Intelligence Applications (OBIA)

The Oracle Business Intelligence Applications (OBIA) is made up of a number of components that are brought together to create a great prebuilt BI solution.  The components can be categorized into 4 major components.

1. Prebuilt reports and dashboard content + Embedded dashboard/report building tool
This prebuilt content is contained in the Oracle BI Presentation Services Catalog, and some of the content is built on the Oracle BI Repository metadata.
The tools include Dashboard Editor and Answers.

2. Prebuilt metadata content (Oracle BI Server Repository) + Administration Tool      
This metadata content is contained in the Oracle Business Intelligence Applications repository file (EnterpriseBusinessAnalytics.rpd).
This content is built and administered using the BI Administration Tool, and is built from the metadata in the OBAW.

3. Oracle Business Analytics Warehouse
The prebuilt data warehouse that holds data extracted, transformed, and loaded from the transactional sources.  The OBAW contains best-practice star-schemas and conforming dimensions.

4. Prebuilt ETL processes and tools
Prebuilt Informatica content + Embedded Informatica ETL Tool
+ Prebuilt DAC metadata repository files + Embedded DAC Tool
Informatica is a third-party application that performs the extract, transform, and load operations for the Data Warehouse.  The Informatica content includes Extract-Transform-Load (ETL) repository objects, such as mappings, sessions, and workflows, and is contained in the Informatica repository file (Oracle_BI_DW_Base.rep).
The DAC is a tool that is used for setup, configuration, administration, and monitoring of data warehouse processes.  The DAC content includes repository objects such as tables, subject areas, execution plans, and tasks, and is contained in XML files.
These tools and processes together extract data from sources, such as Oracle EBS or PeopleSoft, and load the data into the OBAW.