Materialized Views

The Information Source model for IFS Business Reporting & Analysis describes a star schema model consisting of Facts (transactions) and Dimensions (basic data). If an Information Source is supposed to support a Data Mart solution, then the necessary storage containers, representing the Facts and the Dimensions, have to be created in the Data Mart.

This section deals with the case when the Data Mart implementation is based on Materialized Views. Data Mart access can also be implemented as ordinary tables based on the Incremental Load framework. For more information, please refer to Incremental Load of Information Sources.

Contents

• Data Mart
• Materialized View Basics
• Refresh Options
• Build Options
• Considerations

Data Mart

 Some words about the Data Mart:

• Materialized Views are used as the storage containers for Dimensions and Facts.

  A Materialized View is an Oracle feature that means creating a snapshot table.

• The Materialized Views are created in the application owner schema. It is possible to use separate tablespaces for these Oracle objects.
• There will almost in most case exist more Materialized Views than Dimensions and Facts.

  This since the collection of data is made through definition of several Materialized Views and the information in these Materialized Views is joined together in one way or another to create the final Dimension and Fact definition.

• Keeping the data related to IFS Business Reporting & Analysis separated from all other information has the following advantages:

• Data can be collected in different ways but always according to the needs of the dimensions and facts to be created. This might lead to restructuring of existing data, but without modifying existing core tables.
• The Materialized Views contain calculated/evaluated data which means that during retrieval of information the calculations are made, thus enhancing the performance when querying the data.
• Since Materialized Views are tables it is possible to create indexes that are customer/installation specific without having to change indexes in core tables. The latter is always risky business since a rather big analysis has to be done each time a core table is modified with respect to indexes.
• Since Materialized Views are snapshot tables this actually means that it will be possible to freeze the information in the Data Mart, leading to consistent analysis handling. It will be up to the administrator of an installation to define the refresh method for the Materialized Views.
• The Materialized Views can be created with grouped data, also leading to better performance when retrieving information.
• It might be the case that it is not necessary to transfer all data to from the source tables/views to the target Materialized View, meaning that the transaction volume can be reduced,

Materialized View Basics

The above picture shows the basic principle for defining of a Materialized View:

• A Materialized View is created by selecting data from one or more source tables.
• The select statement can be an advanced one, e.g. with sub selects, UNION (ALL), outer joins etc.
• It is also possible to add function calls to the Materialized View definition, which means that data can be calculated before it is stored in the Materialized View.
  Functions that return information dependent on the time of access, like e.g. date related calculations, should never be part of the Materialized View to avoid inconsistency problems.

  Note: Function calls should be avoided as much as possible since the thay may affect performance in a nagative way.

• The state of a Materialized View is called staleness and many values are possible. In Business Reportiner & Analysis only the following values will be displayed, mainly to reduce complexity:

• UNUSABLE
  The Materialized View is empty and must first be activated, i.e. creating the first snapshot.
• STALE
  The Materialized View is not up-to-date and must be refreshed to be up-to-date.
• UNKNOWN
  The Materialized View contains non-deterministic functions and the state is unknown. It does not matter if the Materialized View is refreshed, it will still be considered as being in an unknown state.
  More information can be found in the documentation about Materialized View Management.
• FRESH
  The Materialized View is up-to-date.

A typical definition of a Materialized View looks as follows:

CREATE MATERIALIZED VIEW &MV
TABLESPACE &IFSAPP_DATA
BUILD DEFERRED
USING NO INDEX
REFRESH COMPLETE ON DEMAND
AS
SELECT <columns>
FROM   <table(s)>
WHERE  <conditions>

Refresh Options

When a Materialized View is created a refresh option is specified. The general options are:

• REFRESH FAST ON COMMIT
• REFRESH FAST ON DEMAND
• REFRESH COMPLETE ON COMMIT
• REFRESH COMPLETE ON DEMAND

The REFRESH FAST options means incremental update of the Materialized View.
The REFRESH COMPLETE options means that the Materialized View is completely recreated from the current state of the involved source tables.

It is possible to define that the refresh should be performed on COMMIT, i.e. as soon as changes in the involved source tables are made permanent or on DEMAND, i.e. either manually ordered or according to a scheduled task.

IFS only supports completely refreshable Materialized Views. Even if Oracle support refresh on COMMIT this option should NEVER BE USED.
This means that refresh of a MV is defined as:

REFRESH COMPLETE ON DEMAND

It is up to the administrator to define how often Materialized Views should be refreshed and this is preferable done by scheduling of a refresh category.

Build Options

There are different build options available when creating a Materialized View.

IFS recommends using the option BUILD DEFERRED, which means that the Materialized View is defined in the Oracle dictionary when installed/deployed BUT it will be created empty.
The first snapshot is created by activating the Materialized View.

Considerations

There are several areas to consider when creating a Materialized View.

• Purpose of the Materialized View
  It is important to find out what the purpose of the Materialized View is. Should it represent basic data or transaction data? Does it directly correspond to a Dimension or a Fact or does it have a common purpose (like a reusable object)? Is the purpose to store special information like translations? Really try to find out what information that is needed and how it should be made available.
• Columns to be used
  Find out which of the source table columns that has to be available in the created Materialized Views.
  Consider the following:

• Key columns 
• Parent columns/values
• Unique identities
• Dimension identities
• Information to be published, single columns as well as columns needed for function calls.
• Consistency aspects; adding columns that initially are there only to be consistent with the Online views, since Data Mart and Online support means one Star Schema model but different sources (Materialized Views, views).

• Pre-grouping data
  A Materialized View provides the possibility to store grouped data. The Materialized View definition can of course perform grouping of the selected information, which can be very beneficial for performance when accessing information in the Data Mart. The original source tables can also be stored in Materialized Views, thus enabled a possibility to drill down from a grouped source to the transaction source.
• Limit number of transactions
  Reducing number of transactions transferred to a Materialized View is one important way to make the Data Mart smaller and more performance efficient. It might be the case that only transactions with a specific status or timestamp etc needs to be present in the Data Mart.
• One or many Materialized Views
  Consider filtering of source data into more than one Materialized View. The source tables can be to general and cover to many cases, so it can sometimes be a good idea to create purpose built Materialized Views that of course can be used to create purpose built Information Sources.
• Function calls
  The Materialized View definition can contain function calls. Generally function calls should be avoided and if possible the data should be derived via pure SQL.
  Consider the following:

• Information that is dependent of the time of execution, the current user, language etc should never be calculated and stored in a Materialized View. This has to be done at a later stage; when retrieving the data from the Materialized View.
• Function calls can affect the refresh performance. It is important to find out how the refresh is affected by added functions if a lot of data is processed and if the refresh time is believed to be acceptable in a real customer scenario.

• Translation handling
  Translated texts should normally not be stored in a Materialized View, since the language is decided at the time of execution, not when the Materialized View is refreshed. An exception is if the translations in the different available languages are stored in a Materialized View that is later used as the translation source. A suggestion is then to make the Materialized View as small as possible to, containing only a small subset of all available translations.
• Row Level Security
  Row Level Security should note be implemented as a part of the Materialized View definition. This of course due to that the security  has to be evaluated at the time of access with respect to e.g. current user.
• Indexes
  A big advantage with Materialized Views is that purpose built indexes can be added without too much of concern. Adding indexes to core tables is much worse.
  Do not add too many indexes as a part of the Materialized View creation since it's difficult to find out in advance how different customers will access the Materialized View.
  Add indexes on columns expected to be the most commonly used ones.
  Use concatenated indexes, e.g. combining two or more columns.
• Refresh Time
  Generally it is very important to find out how long time it takes to refresh a Materialized View, given a scenario where the involved tables have large amounts of data. If the time used is considered as not acceptable, then the Materialized View definition must be modified. Some suggestions are found in previous bullets.