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

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 in the Information Sources feature in IEE client. Select one or more Information Sources and choose option Generate 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:

Click OK to start creating the 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 Access View window. 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:

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 Access Views

The most common and general scenario will be the following:

Configuration of Access Views

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

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

Access View Referenced Information Source Suggested
Access Type
FACT_GL_BAL_SET_ANALYS_BI FACT_GL_BAL_SET_ANALYS Data Mart
Incremental
FACT_GL_BALANCE_BI FACT_GL_BALANCE Data Mart
Incremental
FACT_GL_PERIOD_BUDGET_BI FACT_GL_PERIOD_BUDGET On Line
FACT_GL_PROJECT_BALANCE_BI FACT_GL_PROJECT_BALANCE On Line
FACT_GL_TRANSACTION_BI FACT_GL_TRANSACTION Data Mart
Incremental
FACT_PLAN_TRANS_VALID_BI FACT_PLAN_TRANS_VALID Data Mart
Incremental

 

Financials - Group Consolidation

Access View Referenced Information Source Suggested
Access Type
FACT_CONSOL_BAL_SET_ANALYS_BI FACT_CONSOL_BAL_SET_ANALYS Data Mart
Incremental
FACT_CONSOL_BALANCE_BI FACT_CONSOL_BALANCE Data Mart
Incremental
FACT_CONSOL_PAR_BALANCE_BI FACT_CONSOL_PAR_BALANCE Data Mart
Incremental
FACT_CONSOL_REP_BALANCE_BI FACT_CONSOL_REP_BALANCE Data Mart
Incremental

HR

Access View Referenced Information Source Suggested
Access Type
FACT_EMPLOYEE_ANALYSIS_BI FACT_EMPLOYEE_ANALYSIS On Line

 

Inventory

Access View Referenced Information Source Suggested
Access Type
FACT_INVENT_TRANS_HIST_BI FACT_INVENT_TRANS_HIST Data Mart
Incremental
FACT_INVENTORY_TURNOVER_BI FACT_INVENTORY_TURNOVER Data Mart
Incremental
FACT_INVENTORY_VALUE_BI FACT_INVENTORY_VALUE Data Mart
Incremental

Manufacturing

Access View Referenced Information Source Suggested
Access Type
FACT_SHOP_ORDER_BI FACT_SHOP_ORDER Data Mart
Incremental
FACT_SHOP_ORDER_MATERIAL_BI FACT_SHOP_ORDER_MATERIAL Data Mart
Incremental
FACT_SHOP_ORDER_OPERATION_BI FACT_SHOP_ORDER_OPERATION Data Mart
Incremental
FACT_OPERATION_HIST_BI FACT_OPERATION_HISTORY On Line

 

Maintenance

Access View Referenced Information Source Suggested
Access Type
FACT_JT_TASK_ACCOUNTING_BI FACT_JT_TASK_ACCOUNTING Data Mart
Incremental
FACT_WO_BUDGET_BI FACT_WO_BUDGET Data Mart
Incremental
FACT_WO_PLANNED_BI FACT_WO_PLANNED Data Mart
Incremental
FACT_WO_TASK_ACTUALS_SALES_BI FACT_WO_TASK_ACTUALS_SALES Data Mart
Incremental
FACT_WORK_ORDER_BI FACT_WORK_ORDER Data Mart
Incremental
FACT_WORK_TASK_BI FACT_WORK_TASK Data Mart
Incremental
FACT_WORK_TASK_ACTUALS_COST_BI FACT_WORK_TASK_ACTUALS_COST Data Mart
Incremental

 

Procurement

Access View Referenced Information Source Suggested
Access Type
FACT_PURCHASE_ORDER_CHARGE_BI FACT_PURCHASE_ORDER_CHARGE Data Mart
Incremental
FACT_PURCHASE_ORDER_LINE_BI FACT_PURCHASE_ORDER_LINE Data Mart
Incremental

 

Project

Access View Referenced Information Source Suggested
Access Type
FACT_ACTIVITY_CALCULATION_BI FACT_ACTIVITY_CALCULATION Data Mart
Incremental
FACT_INVOICING_PLANS_BI FACT_INVOICING_PLANS On Line
FACT_PROJ_FORECAST_ITEM_BI FACT_PROJ_FORECAST_ITEM Data Mart
Incremental
FACT_PROJ_FRC_ITEM_PER_BI FACT_PROJ_FRC_ITEM_PER Data Mart
Incremental
FACT_PROJ_FRC_SNAP_SUM_BI FACT_PROJ_FRC_SNAP_SUM Data Mart
Incremental
FACT_PROJ_FRC_SNAPSHOT_BI FACT_PROJ_FRC_SNAPSHOT Data Mart
Incremental
FACT_PROJECT_CONN_DET_BI FACT_PROJECT_CONN_DET Data Mart
Incremental
FACT_PROJECT_HISTORY_LOG_BI  FACT_PROJECT_HISTORY_LOG Data Mart
Incremental
FACT_PROJECT_TRANSACTIONS_BI FACT_PROJECT_TRANSACTIONS Data Mart
Incremental

 

Risk

Access View Referenced Information Source Suggested
Access Type
FACT_RISK_ACTION_BI FACT_RISK_ACTION On Line
FACT_RISK_ANALYSIS_HEADER_BI FACT_RISK_ANALYSIS_HEADER On Line
FACT_RISK_CONSEQUENSE_BI FACT_RISK_CONSEQUENCE On Line
FACT_RISK_RESPONSE_BI FACT_RISK_RESPONSE On Line

 

Sales

Access View Referenced Information Source Suggested
Access Type
FACT_CUSTOMER_INVOICE_BI FACT_CUSTOMER_INVOICE Data Mart
Incremental
FACT_CUSTOMER_ORDER_CHARGE_BI FACT_CUSTOMER_ORDER_CHARGE Data Mart
Incremental
FACT_CUSTOMER_ORDER_LINE_BI FACT_CUSTOMER_ORDER_LINE Data Mart
Incremental
FACT_RETURN_MATERIAL_CHARGE_BI FACT_RETURN_MATERIAL_CHARGE On Line
FACT_RETURN_MATERIAL_LINE_BI FACT_RETURN_MATERIAL_LINE On Line

 

Sales Contract

Access View Referenced Information Source Suggested
Access Type
FACT_SALES_CONTRACT_ITEM_BI FACT_SALES_CONTRACT_ITEM On Line

 

General Access Views

Access View Referenced Information Source Suggested
Access Type
FACT_CURRENCY_RATES_BI FACT_CURRENCY_RATES On Line

 

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:

View Component
ACCOUNTING_ATTRIBUTE_CON_BIA Accounting Rules (ACCRUL)
ACCOUNTING_STRUCTURE_ITEM_BIA General Ledger (GENLED)
BI_GL_BAL_SET_CUBE_CONFIG_BIA Budget Process (BUDPRO)
FIN_KPI_BIA KPI Services (KPISRV)
FIN_KPI_DETAIL_BIA KPI Services (KPISRV)
FIN_KPI_DOMAIN_BIA KPI Services (KPISRV)
FIN_KPI_DOMAIN_COMPANY_BIA KPI Services (KPISRV)
FIN_KPI_DOMAIN_STRUCT_BIA KPI Services (KPISRV)
FIN_KPI_ELEMENT_BIA KPI Services (KPISRV)
SSAS_CUBE_BIA KPI Services (KPISRV)
SSAS_CUBE_DOMAIN_BIA KPI Services (KPISRV)
SSAS_CUBE_MEASURE_BIA KPI Services (KPISRV)
USER_VIEWS system view

 

Related Package Access

Analysis Models also requires access to some PL/SQL packgaes 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 >>