IFS BI Analysis Package - BI Access Views

This page deals with BI Access Views, i.e. the  read interface used by the IFS BI Analysis Package when transferring information from the IFS Applications database to the Data Warehouse in SQL Server.

Note: BI Access Views must be created before starting the ETL process.

Contents

• General
• How To Create BI Access Views
• Suggested Way of Creating BI Access Views
• Configuration of BI Access Views
• Configuration of Data Mart Sources
• Necessary BI Access Views
• View Access

General

BI Access Views are views referencing corresponding Information Source views. The BI Access Views look the same independent whether they are based on On Line or Data Mart access. The views are used as the read interface in IFS Applications when extracting data to the SQL Server database.

Note: Data Mart access is either based on Materialized Views or on incrementally loaded snapshot tables

How To Create BI Access Views

In order to populate the SQL Server data warehouse and the OLAP cubes with data, BI Access Views need to be created and configured in IFS Applications.

The BI Access Views are created in the Information Sources feature in IEE client. Select one or more Information Sources and choose option Generate BI Access Views for Selected.

In the next window, choose Data Access Type, i.e. On Line (OL) or Data Mart (DM).

Generally the following applies:

• The Data Access Type is set to On Line
• The option Create Information Source Fact Views Only is not selected, meaning that for each listed Information Source, BI Access Views will be created for all involved Facts and Dimensions.

  Selecting this option means that only Fact specific BI Access Views will be created.

• The option Recreate Existing BI Access Views is not selected, meaning that BI Access Views will not be recreated if already existing. Only select this option if it is really sure that existing views should be recreated.

  Note: Information Sources may share the same dimensions

Click OK to start creating the BI Access Views. When the views have been created a dialog will pop up displaying number of created views and the user is asked if it is required to navigate to the BI Access View window. Select Yes to see more details about created BI Access Views.

For ordinary amounts of data it is recommended to use option On Line to make maintenance easier.

The Data Mart access type should be used for all entities, facts and dimensions, that support incremental load. In this case the initial load is based on a full data transfer or a transfer based on conditions, to a dedicated incremental snapshot table. All further loads can be done more or less based on true incremental transfer, i.e. only updating the incremental snapshot table with new and modified transactions since last transfer.

Using Data Mart access for other cases means using Materialized Views. This might be an option if it is possible to refresh the Materialized Views during e.g. a nightly job before starting the transfer to SQL Server. However this might require some customization of existing Materialized View definitions in order to implement conditional parameters. The reason would be to make sure that only a sub set of all available transactions affected the Materialized View(s).

As soon as Data Mart access is used it will be required to administrate things as:

• activation of necessary Data Mart sources (Materialized Views or incremental snapshot tables)
• creating MV Refresh Categories
• setting up refresh schedules

Please refer to documentation on how to Configure Incremental Data Mart Load, how to Configure Materialized View Data Mart Load as well as the more general documentation about Data Mart Source administration.

Note: IFS Applications 9 supports On Line access for all Information Sources, Data Mart access based on Incremental Load for some of the Information Sources. In most cases existing Information Sources support Data Mart access based on Materialized Views if not Incremental Load is supported.

If an Information Source supports Data Mart access it means that snapshot data in IFS Applications database must be created first before configuring the ETL process. This is handled in Solution Manger in the IEE client.

Suggested Way of Creating BI Access Views

The most common and general scenario will be the following:

• Create BI Access Views based on On Line access for all Information Sources where the suggested access type is On Line
• Create BI Access Views based on On Line access for all Information Sources where the suggested access type is Data Mart Incremental
  • This means that all Fact and Dimension specific BI Access Views will be created based on On Line access
• Create BI Access Views based on Data Mart access for all Information Sources where the suggested access type is Data Mart Incremental, but only for Facts
  • Make sure that views are recreated
  • Make sure that only Fact views will be created
  • This means that only the Fact views will be recreated and based on Data Mart access, while the referenced dimensions will be represented by BI Access Views based on On Line access

Configuration of BI Access Views

Created BI Access Views can be found in the BI Access Views form. In most cases it is not necessary to do much more than to create the BI Access Views via the Information Source feature. The BI Access Views specific forms can however be useful. Some examples are listed below:

• Finding dimensions and facts for which BI Access Views have been created
• Finding the used dimension/fact view and the used data access type
• Current status
• Details - columns
• Validation
• Recreate options
• Conditions

  It is possible to add filter conditions to a BI Access View and then recreate view. This could be useful if it is required to reduce number of transferred transactions. But in most cases these type of conditional handling is performed on the SQL Server side.

For more information about BI Access Views, please go here >>

Configuration of Data Mart Sources

Data Mart access can be implemented in two different ways:

1. Using Materialized Views in the Oracle database as the storage for calculated/processed fact/dimension data
2. Using Incrementally Loaded storage tables, i.e. ordinary Oracle tables as the storage for calculated/processed fact/dimension data. Incremental load will in most cases only be supported for Facts and not for dimensions.

Note: It is recommended to use Data Mart access in those cases where incremental load has been implemented. This means that BI Access Views should be created based on Data Mart access where applicable.

Using Data Mart access means that it is necessary to make sure in IFS Applications that the Data Mart storage is updated on a regular basis.

For more information please refer to configurations related to Incremental Load and configurations related to Materialized Views.

Necessary BI Access Views

The BI Analysis Package contains OLAP cubes, each cube containing one or more facts. In some cases Information Sources do not cover the necessary data for the cubes so regular tables or views have been used instead. In this case the BI Access View do not exist.

The following BI Access Views are used by the BI Analysis Package in Applications 9. The column Referenced Information Source in the below tables can be used when generating BI Access Views from the Information Source feature in Solution Manager.
 

Financials

HR

Inventory

Manufacturing

Maintenance

Procurement

Project

Sales

Sales Contract

General

View Access

The executing user should be the Application Owner of IFS Applications. This will ensure that no special object security handling has to be considered since this user owns all the objects.

The BI Analysis Package will apart from BI Access Views also access the following views in IFS Applications database: