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 - Suggested¶
The suggested way to create Access Views is to use the Setup Tabular Model assistant, since it will automatically find out what Facts a model is referencing and then create the necessary Access Views. The principle how the views are created according to Suggested Way of Creating Necessary Access Views on this page will also be followed by the assistant.
The only reason for using the more general description is if there is a specific requirement to create one or more Access Views using a Fact as the starting point.
Lear more about how Access Views are created by the Setup Tabular Model assistant as described in Setup a Tabular Model.
How To Create Access Views - General¶
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.
Note: Please note disclaimer info for some if the models.
General Access Views¶
Access View | Referenced Information Source | Suggested Access Type |
---|---|---|
FACT_CURRENCY_RATES_BI | FACT_CURRENCY_RATES | On Line |
FACT_BI_TIME_BI | FACT_BI_TIME | On Line |
Cash Planning¶
Access View | Referenced Information Source | Suggested Access Type |
---|---|---|
FACT_CASH_PLAN_DETAIL_BI | FACT_CASH_PLAN_DETAIL | Data Mart Incremental |
CRM¶
Access View | Referenced Information Source | Suggested Access Type |
---|---|---|
FACT_BUSINESS_ACTIVITY_BI | FACT_BUSINESS_ACTIVITY | Data Mart Incremental |
FACT_BUSINESS_LEAD_BI | FACT_BUSINESS_LEAD | Data Mart Incremental |
FACT_BUSINESS_OPP_SNAPSHOT_BI | FACT_BUSINESS_OPP_SNAPSHOT | Data Mart Incremental |
FACT_MARKETING_CAMPAIGN_BI | FACT_MARKETING_CAMPAIGN | Data Mart Incremental |
FACT_MARKETING_CAMP_ACTION_BI | FACT_MARKETING_CAMP_ACTION | Data Mart Incremental |
FACT_MKTG_ACTION_RESPONSE_BI | FACT_MKTG_ACTION_RESPONSE | Data Mart Incremental |
FACT_BUSINESS_OPP_BI | FACT_BUSINESS_OPP\ | Data Mart Incremental |
FACT_BUSINESS_OPP_LINE_BI | FACT_BUSINESS_OPP_LINE | Data Mart Incremental |
FACT_CUSTOMER_INVOICE_BI | FACT_CUSTOMER_INVOICE | Data Mart Incremental |
FACT_CUSTOMER_ORDER_LINE_BI | FACT_CUSTOMER_ORDER_LINE | Data Mart Incremental |
FACT_RETURN_MATERIAL_LINE_BI | FACT_RETURN_MATERIAL_LINE | Data Mart Incremental |
FACT_SALES_QUOTATION_LINE_BI | FACT_SALES_QUOTATION_LINE | Data Mart Incremental |
General Ledger¶
Access View | Referenced Information Source | Suggested Access Type |
---|---|---|
FACT_PLAN_TRANS_VALID_BI | FACT_PLAN_TRANS_VALID | Data Mart Incremental |
FACT_GL_BALANCE_BI | FACT_GL_BALANCE | Data Mart Incremental |
FACT_GL_PERIOD_BUDGET_BI | FACT_GL_PERIOD_BUDGET | Data Mart Incremental |
FACT_GL_TRANSACTION_BI | FACT_GL_TRANSACTION | Data Mart Incremental |
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_REP_BALANCE_BI | FACT_CONSOL_REP_BALANCE | Data Mart Incremental |
HCM - Employee Analysis¶
Access View | Referenced Information Source | Suggested Access Type |
---|---|---|
FACT_EMPLOYEE_ANALYSIS_BI | FACT_EMPLOYEE_ANALYSIS | Data Mart Incremental |
HCM - Incident Analysis¶
Access View | Referenced Information Source | Suggested Access Type |
---|---|---|
FACT_INCIDENT_CASE_BI | FACT_INCIDENT_ANALYSIS | Data Mart Incremental |
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 |
Maintenance¶
Referenced Information Source | Access View | Suggested Access Type |
---|---|---|
FACT_JT_TASK_ACCOUNTING | FACT_JT_TASK_ACCOUNTING_BI | Data Mart Incremental |
FACT_WO_BUDGET | FACT_WO_BUDGET_BI | Data Mart Incremental |
FACT_WO_PLANNED | FACT_WO_PLANNED_BI | Data Mart Incremental |
FACT_WO_TASK_ACTUALS_SALES | FACT_WO_TASK_ACTUALS_SALES_BI | Data Mart Incremental |
FACT_WORK_ORDER | FACT_WORK_ORDER_BI | Data Mart Incremental |
FACT_WORK_TASK_ACTUALS_COST | FACT_WORK_TASK_ACTUALS_COST_BI | Data Mart Incremental |
FACT_WORK_TASK | FACT_WORK_TASK_BI | Data Mart Incremental |
### Manufacturing - Production Performance see disclaimer info
Access View | Referenced Information Source | Suggested Access Type |
---|---|---|
FACT_OPERATION_HISTORY_BI | FACT_OPERATION_HISTORY | Data Mart Incremental |
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 |
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 |
FACT_SPEND_ANALYSIS_BI | FACT_SPEND_ANALYSIS | 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_ACTIVITY_INFO_BI | FACT_PROJ_ACTIVITY_INFO | 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_HISTORY_LOG_BI | FACT_PROJECT_HISTORY_LOG | Data Mart Incremental |
FACT_PROJECT_TRANS_BI | FACT_PROJECT_TRANSACTIONS | Data Mart Incremental |
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 |
Important Disclaimer Information¶
The Tabular Models are built using different Information Sources (according to the above section). The different Information Sources, consisting of Facts and Dimensions, belong to different components in IFS Cloud. A customer will buy an IFS Cloud configuration that is based on SKUs, leading to customer specific Solution Set that defines active and inactive components.
It might be the case that a customer does not have all active components required by a Tabular Model. One typical case is that the Project (PROJ) component is inactive, leading to that e.g., the IFS_SALES model cannot be used without getting errors. There is however configuration support in the Tabular framework that can be used to make the models usable.
Suggested Way of Creating Necessary Access Views¶
The suggestion is to use On Line access for all Facts and Dimensions that do not support incremental load. For Facts that supports Incremental load, use Data Mart access, leading to the following way to set up the access views:
- Create Access Views based on On Line access for all required Information Sources.
- 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 to indicate that views should be recreated.
- Make sure to indicate that only Fact views should 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 Models 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 Tabular 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 >>