Analysis Models - Access Views

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

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

Contents

• General
• How To Create Access Views
• Suggested Way of Creating Access Views
• Configuration of Access Views
• Configuration of Data Mart Sources
• Necessary Access Views
  • Financials - GL
  • Financials - Group Consolidation
  • HR
  • Inventory
  • Manufacturing
  • Maintenance
  • Procurement
  • Project
  • Risk
  • Sales
  • Sales Contract
  • General Access Views
• View Access
• Related Package Access

General

Access Views are views referencing corresponding Information Source views. The 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 Access Views

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

The Access Views are created via the Information Sources page. Use the command Create Access Views that will open an assistant that guides the user to create necessary Access Views.

In the first step, select the Information Sources for which Access Views should be created.

Next select generation options.

Generally the following applies:

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

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

• The option Recreate Existing Access Views is not selected, meaning that 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

Next verify the settings and start the creation process by using Finish.

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 Access View page. Select Yes to see more details about created 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 10 supports On Line access for all Information Sources and 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 the 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 Access Views

The most common and general scenario will be the following:

• Create Access Views based on On Line access for all Information Sources where the suggested access type is On Line
• Create 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 Access Views will be created based on On Line access
• Create 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 Access Views based on On Line access.
    

Configuration of Access Views

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

• Finding dimensions and facts for which 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 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 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 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 Access Views

Analysis Models 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 Access View does not exist.

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

Financials - GL

Financials - Group Consolidation

HR

Inventory

Manufacturing

Maintenance

Procurement

Project

Risk

Sales

Sales Contract

General Access Views

View Access

The preferred executing user is the <IFSINFO> user. This user will have all necessary views defined in his own schema in the database and the security filter mechanism in the views will give this user full data access (both to dimensional and fact related data).

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

Related Package Access

Analysis Models also requires access to some PL/SQL packages in the Application Owner schema. These PL/SQL packages are used by the SSIS packages in the Extract (load) step with the purpose to add information not part of the Access Views.

All needed packages are granted for access to the <IFSINFO> user during installation. In a customization case it might be necessary to grant other packages to the <IFSINFO> user. Some information how to handle this in a customization case can be found here >>