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:
- Using Materialized Views in the Oracle database as the storage for calculated/processed fact/dimension data.
- 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 >>