Manage Data Mart Sources

The Information Source model in IFS Business Reporting & Analysis describes a Star Schema model consisting of Facts (transactions) and Dimensions (basic data).

If an Information Source supports Data Mart access, then the necessary storage containers, representing the Facts and the Dimensions, have to be created in the Data Mart. There are two supported Data Mart sources:

1. Materialized Views

   A Materlialized View is an object in the Oracle database representing a snapshot table.

2. Incrementally Loaded tables

   Refers to ordinary tables that are loaded with data based on incremental changes in other source tables.

Read more about general concepts

• Overview of Materialized Views
• Overview of Incremental Load of Information Sources

Note: The refresh functionality mentioned only handles Data Mart sources related to Information Sources available in IFS Business Reporting & Analysis. This means e.g. that if there are other Materialized Views in the IFS database that are not related to Information Sources, then they will have to be maintained by other means.

Contents

• Data Mart Source Overview
• Refresh Methods and Modes
• Special Data Mart Source Related Subjects
• Data Mart Source Refresh Recommendations
• Handling Incremental Load

Data Mart Source Overview

An overview of Business Reporting & Analysis related Data Mart sources can be found in the feature Data Mart Sources in Solution Manager in IEE.

Figure 1:Overview of Data Mart Sources

• A red indicator means that the source is UNUSABLE, i.e. must be activated.

  Activating a Data Mart source means creating the first snapshot.

• A yellow indicator means that the Materialized View is STALE, i.e. not up-to-date
and must be refreshed.
• A green indicator means that the state of the Materialized View is FRESH, i.e. it is up-to-date.
• The Type specifies if the source is a Materialized View (MV) or an incrementally loaded tables (Incremental)
• The details of each Data Mart Source (double-click on a source) will also display if the source is enable for In-Memory access or not.

  Note: Oracle Enterprise Edition must be used and the In-Memory option must be enabled to make it possible to enable a Data Mart source for in-memory access

Note: Some special considerations that apply to Data Mart source MV (Materialized View)
For Materialized View definitions that contains non-deterministic function calls will lead to that the Materialized View in the database is considered as having the staleness UNKNOWN. The reason is that it is not possible to trace changes from functions. A Materialized View with staleness UNKNOWN will by default be treated as FRESH, i.e. it will be marked with a green indicator. This means that Materialized Views with staleness UNKNOWN will not be refreshed, neither if ordered manually nor if being part of a scheduled refresh job. As soon as the main source tables referenced by the Materialized View are modified, the Materialized View will be marked as not up-to-date, STALE, i.e. with a yellow indicator.
If however it is required to treat a Materialized View with staleness UNKNOWN as not up-to-date, i.e. marked with a yellow indicator, this is possible by modifying the Business Reporting & Analysis parameter Refresh Materialized Views in UNKNOWN state

Use the feature to:

• List available Data Mart sources
• Look up last refreshed date for each Data Mart source
• Look up current refresh mode
• Mark selected Data Mart sources as Active
• Mark selected Data Mart sources as Incative
• Activate selected Data Mart sources
• Look up refresh status, if a Data Mart source is FRESH, STALE, UNKNOWN or UNUSABLE. If not FRESH then the Data Mart source is not up-to-date. Refresh is handled either by a scheduled job or manually.

  Note: An incrementally loaded source can never get the status UNKNOWN.

• Refresh selected Data Mart sources
• View connected Data Mart source refresh categories
• In-Memory Options
  • Enable Data Mart sources for In-Memory access
  • Disable Data Mart sources for In-Memory access
  • Remove Analytics Indexes

  These options will only be available if Oracle Enterprise Edition is used and the In-Memory option is enabled.

Refresh Methods and Modes

For a Data Mart source of type Materialized View, two refresh methods can be distinguished. The refresh method is defined by Oracle as a part of the Materialized View framework:

• FAST

  This method means that Oracle can handle incremental update of the Materialized View. It is however very difficult to use this options since it requires pure table access and not view access based on function calls and sub selects.

• COMPLETE

  This method means that the Materialized View has to be reloaded from scratch every time it is refreshed. This method can be used for complex Materialized View definitions. The IFS implementation is based on COMPLETE refreshed Materialized Views.

Note: It is recommended  to create Materialized Views with the refresh method COMPLETE and refresh mode DEMAND.

For a Data Mart source of type Incremental there is no refresh method, rather a refresh mode that can be one of the following:

• Incremental

  This mode means that all updates of the snapshot table are based on incremental changes in referenced source tables. This leads to a very efficient way of keeping the snapshot table up-to-date.

• Full

  This mode means that all updates of the snapshot table are complete, i.e. all transactions with respect to defined filter conditions, are transferred ach time a refresh is made.

Special Data Mart Source Related Subjects

Read more about special subjects related to administration of Data Mart sources here.

Data Mart Source Refresh Recommendations

Read more about general recommendations related to refresh of Data Mart sources here.

Handling Incremental Load

Read more about incremental load specifics