Skip to content

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 Cloud database to the data storage in SQL Server.

Note:Access Views must be created before starting the data load from IFS Cloud to SQL Server. If Access Views have not been properly created, the data load will fail.

General

Access Views are views referencing corresponding Information Source (Fact/Dimension) views. The Access Views look the same independent on whether they are based on Online or Data Mart access. The views are used as the read interface in IFS Cloud when extracting data to Analysis Models specific 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 load the Analysis Models specific source tables in the SQL Server database, Access Views need to be created and configured in IFS Cloud. The Access Views are referenced by the Tabular Data Sources and are the main IFS Cloud sources transferred to SQL Server.

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. Only select this option when it is required to only create Fact-specific Access Views.
  • 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 box 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 in general only be used for all entities, Facts and Dimensions, that support incremental load. In this case the initial load is based on a full or conditional data transfer 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 as the storage object. 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 Cloud supports On Line access for all Information Sources. Data Mart access based on Incremental Load is not supported by all Information Sources. The Fact part of the Information Sources used by the IFS delivered Tabular Models will in general support Incremental Load.
Facts that do not support Incremental Load will in most cases support Data Mart access based on Materialized Views.

If an Information Source supports Data Mart access it means that snapshot data in the IFS Cloud database must be taken care of before configuring data loads for the Tabular Models. Use the following link >> for more information about Data Mart configuration.

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 for Information Sources supporting Incremental Load. This means that Access Views   (for Facts) should be created based on Data Mart access where applicable.

Using Data Mart access means that it is necessary to make sure that the Data Mart storage is in the IFS Cloud database 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 supports Tabular Models, each model based on one or more Information Sources. In some cases Information Sources do not cover the necessary data for the models. For these cases other source views are used, created during installation and maintained by the product groups in Product Development. These views act as a complement to the Access Views and it is made sure that all necessary views are available in the <IFSINFO> schema.

Access Views based on the below listed Information Sources are required by the Tabular Models in IFS Cloud.

The column Access View shows the name of the Fact related Access View for the referenced source.
The column Referenced Information Source in the below table can be used when generating Access Views from the Information Source page in Solution Manager.

Note: For a Tabular Model to function properly, all necessary Access Views must be generated. However, it is only possible to generate views based on active components, i.e. components available in the Solution Set that applies for the current IFS Cloud installation. In the tables below, the owning component can be found. If any of the listed components are not available, it means that Access Views cannot be created and that the tabular model can not be used.

Note: Always make sure to create generally required Access Views.

General Access Views

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

General Ledger

Access View Referenced Information Source Suggested
Access Type
FACT_PLAN_TRANS_VALID_BI FACT_PLAN_TRANS_VALID Data Mart
Incremental
BUSPLN
FACT_GL_BALANCE_BI FACT_GL_BALANCE Data Mart
Incremental
GENLED
FACT_GL_PERIOD_BUDGET_BI FACT_GL_PERIOD_BUDGET Data Mart
Incremental
GENLED
FACT_GL_TRANSACTION_BI FACT_GL_TRANSACTION Data Mart
Incremental
GENLED

Group Consolidation

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

Maintenance

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

Procurement

Access View Referenced Information Source Suggested
Access Type
Component
FACT_PURCHASE_ORDER_CHARGE_BI FACT_PURCHASE_ORDER_CHARGE Data Mart
Incremental
PURCH
FACT_PURCHASE_ORDER_LINE_BI FACT_PURCHASE_ORDER_LINE Data Mart
Incremental
PUCH
FACT_SPEND_ANALYSIS_BI FACT_SPEND_ANALYSIS Data Mart
Incremental
SRM

Project

Access View Referenced Information Source Suggested
Access Type
FACT_ACTIVITY_CALCULATION_BI FACT_ACTIVITY_CALCULATION Data Mart
Incremental
PROJ
FACT_INVOICING_PLANS_BI FACT_INVOICING_PLANS Data Mart
On Line
PRJREP
FACT_PROJ_ACTIVITY_INFO_BI FACT_PROJ_ACTIVITY_INFO Data Mart
On Line
PROJ
FACT_PROJ_FORECAST_ITEM_BI FACT_PROJ_FORECAST_ITEM Data Mart
Incremental
PROJBF
FACT_PROJ_FRC_ITEM_PER_BI FACT_PROJ_FRC_ITEM_PER Data Mart
Incremental
PROJBF
FACT_PROJ_FRC_SNAP_SUM_BI FACT_PROJ_FRC_SNAP_SUM Data Mart
Incremental
PROJBF
FACT_PROJ_FRC_SNAPSHOT_BI FACT_PROJ_FRC_SNAPSHOT Data Mart
Incremental
PROJBF
FACT_PROJECT_HISTORY_LOG_BI FACT_PROJECT_HISTORY_LOG Data Mart
Incremental
PROJ
FACT_PROJECT_TRANS_BI FACT_PROJECT_TRANSACTIONS Data Mart
Incremental
PRJREP

Sales

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

Suggested Way of Creating Necessary Access Views

The most common and general scenario will be according to the following:

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

View Access

All Access Views will be created in the application owner schema as well as in the <IFSINFO> user schema. The Dimension and Fact specific security filter mechanism  will make sure that the <IFSINFO> user is gives full read access (both to dimensional and fact related data).

Apart from Access Views some other non-Fact/Dimension views are also needed by the Tabular Modles and are created in the <IFSINFO> user schema by the IFS Cloud components that owns the underlying entities.

These extra views have different suffixes, described in the below table.

View Suffix Description
<name>_BIA Special BI like view created/handled by the product teams
<name>_XBI Special eXtra complementary BI views handled by TABMTM component
<name>_T<n> Dimension like BI help views handled by TABMTM. <n> is typically an integer from 1..9
<name>_TM Dimension like BI view supporting the Tabuler Models (TM), handled by TABMTM component
<name>_TC Tabular basic data Configuration views handled by component TMCONF

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 referenced Dimension/Fact source view and the current data access type.
  • Current status
  • Details - columns
  • Validation
  • Recreate options
  • Conditions It is possible to add filter conditions to an Access View and then recreate the view. This could be useful if it is required to reduce number of transferred transactions. In general the load conditions are handled when the data load to SQL Server is configured.

For more information about Access Views, please use the following link >>