Skip to content

Analysis Models - Disclaimer Information Details

The tabular models are based different Information Sources. To provide as much functionality as possible for the end users, the models have been based on Facts an Dimensions from one or more technical components.

The drawback with this approach is that some of the components that are referenced in a model might not be active in the customer's Solution Set definition.

This leads to issues when trying to activate the Tabular Models.

This section describes affected models, dynamic component dependencies and how to use the Tabular Framework to configure a model to make it possible to use even if some dependent components are not active.

There are two separate cases described, one related to models except IFS_GENERAL_LEDGER and one related to the IFS_GENERAL_LEDGER model.

Case 1

This case refers to a number of models, listed below, but not to IFS_GENERAL_LEDGER model that is described in Case 2.

Below table shows components in each model that are considered dynamic i.e., they might not be active in the customer's Solution Set, controlled by bought SKUs.

Tabular Model Dynamic Component Component Name
IFS_CRM CFGCHR Configuration Characteristics
IFS_CRM PROJ Project
IFS_MAINTENANCE PROJ Project
IFS_PROCUREMENT PROJ Project
IFS_PROCUREMENT SRM Supplier Relationship Management
IFS_PRODUCTION_PERFORMANCE CFGCHR Configuration Characteristics
IFS_PRODUCTION_PERFORMANCE PROJ Project
IFS_SALES CFGCHR Configuration Characteristics
IFS_SALES PROJ Project

The below table shows for each component the identity of the Dimensions or Facts that are referenced.

Component Dimension ID Fact ID Data Source ID Table ID
CFGCHR DIM_CONFIGURATION_SPEC DIM_CONFIGURATION_SPEC CONFIGURATION_SPEC
PROJ DIM_ACTIVITY DIM_ACTIVITY ACTIVITY
PROJ DIM_PROJECT DIM_PROJECT PROJECT
PROJ DIM_SUB_PROJECT DIM_SUB_PROJECT SUB_PROJECT
SRM DIM_PROC_CAT_ASSORTMENT DIM_PROC_CAT_ASSORTMENT CAT_ASSORTMENT
SRM DIM_PROC_CAT_ASSORTMNT_NODE DIM_PROC_CAT_ASSORTMNT_NODE CAT_ASSORTMENT_NODE
SRM FACT_SPEND_ANALYSIS FACT_SPEND_ANALYSIS SPEND_ANALYSIS

For IFS_PROCUREMENT there are tables that refer to general dimensions that ONLY have relations to FACT_SPEND_ANALYSIS, meaning that those tables also have to be considered.

Below is a more complete table covering all models.

Tabular Model Dynamic
Component
Data Source ID Table ID
IFS_CRM CFGCHR DIM_CONFIGURATION_SPEC CONFIGURATION_SPEC
IFS_CRM PROJ DIM_ACTIVITY ACTIVITY
IFS_CRM PROJ DIM_PROJECT PROJECT
IFS_MAINTENANCE PROJ DIM_ACTIVITY ACTIVITY
IIFS_MAINTENANCE PROJ DIM_PROJECT PROJECT
IFS_MAINTENANCE PROJ DIM_SUB_PROJECT SUB_PROJECT
IFS_PROCUREMENT PROJ DIM_ACTIVITY ACTIVITY
IFS_PROCUREMENT PROJ DIM_PROJECT PROJECT
IFS_PROCUREMENT PROJ DIM_SUB_PROJECT SUB_PROJECT
IFS_PROCUREMENT SRM DIM_ACCOUNT ACCOUNT
IFS_PROCUREMENT SRM DIM_CODE_B CODE_B
IFS_PROCUREMENT SRM DIM_CODE_C CODE_C
IFS_PROCUREMENT SRM DIM_CODE_D CODE_D
IFS_PROCUREMENT SRM DIM_CODE_E CODE_E
IFS_PROCUREMENT SRM DIM_CODE_F CODE_F
IFS_PROCUREMENT SRM DIM_CODE_G CODE_G
IFS_PROCUREMENT SRM DIM_CODE_H CODE_H
IFS_PROCUREMENT SRM DIM_CODE_I CODE_I
IFS_PROCUREMENT SRM DIM_CODE_J CODE_J
IFS_PROCUREMENT SRM DIM_BUSINESS_REP SALES_REPRESENTATIVE
IFS_PROCUREMENT SRM DIM_PROC_CAT_ASSORTMENT CAT_ASSORTMENT
IFS_PROCUREMENT SRM DIM_PROC_CAT_ASSORTMNT_NODE CAT_ASSORTMENT_NODE
IFS_PROCUREMENT SRM DIM_SUPPLIER_ASSORTMENT SUPPLIER_ASSORTMENT
IFS_PROCUREMENT SRM FACT_SPEND_ANALYSIS SPEND_ANALYSIS
IFS_PRODUCTION_PERFORMANCE CFGCHR DIM_CONFIGURATION_SPEC CONFIGURATION_SPEC
IFS_PRODUCTION_PERFORMANCE PROJ DIM_ACTIVITY ACTIVITY
IFS_PRODUCTION_PERFORMANCE PROJ DIM_PROJECT PROJECT
IFS_SALES CFGCHR DIM_CONFIGURATION_SPEC CONFIGURATION_SPEC
IFS_SALES PROJ DIM_ACTIVITY ACTIVITY
IFS_SALES PROJ DIM_PROJECT PROJECT

The problem appears when setting up a Tabular Model and leads to the the deployment of objects to the SQL Server/Azure SQL database will fail.

The error can be like below where the error message states that there are missing columns.

Another error that can appear is that there are Access Views missing, typically those Access Views related to the Dimensions and Facts in the inactive components.

To make sure that the model can be used it will be necessary to do two things:

  1. Create configurations that excludes tables according to the previous list with data sources and tables per modes and dynamic component.
  2. Create configurations that removes all relationships from the model where the related tables are involved.

As an example the IFS_SALES_models will be used.

The dynamic component PROJ is not active but the dynamic component CFGCHR is active in the customer's Solution Set. That means we have to perform configurations that excludes the effect of the PROJ related tables in the model. From the above list we find that we need to consider tables ACTIVITY and PROJECT.

Start by navigating to the Tabular Models page, select IFS_SALES and navigate to the details page.

Go to the Tables in the model group.

Select the tables (in the example tables ACTIVITY and PROJECT) and use the Exclude command.

Next go to the Relationships in the model group.

Filter on To Table ID to only get relationships associated with the removed tables.

Use Exclude command to create a configuration for all relationships.

The necessary configurations are now done.

For others models listed in Case 1, the procedure is the same as described in the example. But please note that for IFS_PROCUREMENT model, if the component SRM is not active, the fact table SPEND_ANALYSIS will appear as the From Table ID in the Relationships in the model group.

Next steps:

  • Deploy objects to the target database, either via Setup Tabular Model assistant or the Deploy to SQL Server Db assistant. If the assistant ends with success it means that all necessary objects have been deployed.
  • Next steps can either be performed by the Setup Tabular Model assistant or done step-by-step using separate dialogs and assistants. The steps are:
    1. Deploy the configured tabular model to Analysis Services.
    2. Create a data load definition
    3. Run the data load definition in order to transfer data from IFS Cloud db to the SQL Server/Azure SQL database.
  • Process the tabular model will be last step before the model can be used.

Case 2

The second case deals with the IFS_GENERAL_LEDGER tabular model.

This models is based on two main components, GENLED (General Ledger) and BUSPLN (Business Planning).

If component BUSPLN is set to inactive in the customer's Solution Set (due to that the customer has not bought the SKU related to Business Planning) then it will not be possible to successfully deploy the GL model.

The problem appears when deployment of objects to the SQL Server/Azure SQL database takes place.

The reason is that Access Views related to component BUSPLN have not been created since the component is inactive.

To make sure that the model can be used it will be necessary to do the following things:

  1. Create configurations that excludes BUSPLN related tables from the model.
  2. Create configurations that excludes all relationships related to BUSPLN.
  3. Create configurations on tabular data source FACT_TRANS_BAL_SET_CONF
    1. Exclude a dependency
    2. Modify a transformation view

The mentioned steps will be exemplified.

First thing will be to remove tables related to BUSPLN component according to the below table. | Table ID | Data Source ID | | ------------------------- | ---------------------- | | BUSINESS_PLAN | DIM_BUSINESS_PLAN | | BUSINESS_PLANNING | FACT_PLAN_TRANS_VALID | | BUSINESS_PLANING_MEASURES | | | BUSINESS_PLAN_RLS | BUSINESS_PLAN_RLS | | CODE_M | DIM_CODE_M | | CODE_N | DIM_CODE_N | | CODE_O | DIM_CODE_O | | CODE_P | DIM_CODE_P | | CODE_Q | DIM_CODE_Q | | CODE_R | DIM_CODE_R | | CODE_S | DIM_CODE_S | | CODE_T | DIM_CODE_T | | PLANNING_STRUCTURE | DIM_PLANNING_STRUCTURE | | PLANNING_UNIT | DIM_PLANNING_UNIT | | PLANNING_UNIT_RLS | PLANNING_UNIT_RLS |

Start by navigating to the Tabular Models page, select IFS_GENERAL_LEDGER and navigate to the details page.

Go to the Tables in the model group.

Select the tables in the model according to the above table and use the Exclude command.

Next go to the Relationships in the model group.

Filter on From Table ID=BUSINESS_PLANNING as well as From Table ID=BUSINESS_PLAN_RLS

Select all relations and use the Exclude command.

Next go to the Roles in the model group, select the GL Model role and go to details.

In the Table Permissions group on Tabular Role page, make sure to use the command Exclude to Filter Expressions related to tables BUSINESS_PLAN and PLANNING_UNIT.

Next navigate to the Data Sources page and select the Data Source ID = FACT_TRANS_BAL_SET_CONF

Go to details page.

In the Required Data Sources group, make sure to exclude the Required Data Source ID = FACT_PLAN_TRANS_VALID_BAL_SET_CONF

On the same page, in the SQL Server Source Views group, select the view with View ID = FACT_TRANS_BAL_SET_CONF_TM, create a configuration.

Press edit and modify the view definition as described below:

Remove the SELECT part related to FACT_PLAN_TRANS_VALID_BAL_SET_CONF_TM, exemplified below:

SELECT ..........
FROM FACT_GL_TRANS_BAL_SET_CONF_TM f ~~UNION ALL~~ ~~SELECT~~ ~~f.id as [id],~~ ~~f.COMPANY_KEY as [company_key],~~ ~~f.BALANCE_SET_ID_KEY as [balance_set_id_key],~~ ~~f.ACCOUNTING_YEAR_KEY as [accounting_year_key],~~ ~~f.VOUCHER_TYPE_KEY as [voucher_type_key],~~ ~~f.VOUCHER_NO_KEY as [voucher_no_key],~~ ~~f.ROW_NO_KEY as [row_no_key],~~ ~~f.business_plan_id_key as [business_plan_id_key],~~ ~~f.planning_unit_id_key as [planning_unit_id_key],~~ ~~f.unit_version_id_key as [unit_version_id_key],~~ ~~f.transaction_id_key as [transaction_id_key],~~ ~~f.period_id_key as [period_id_key],~~ ~~f.AMOUNT_DOM as [amount_dom],~~ ~~f.DEBET_AMOUNT_DOM as [debet_amount_dom],~~ ~~f.CREDIT_AMOUNT_DOM as [credit_amount_dom],~~ ~~f.AMOUNT_OB_DOM as [amount_ob_dom],~~ ~~f.AMOUNT_CURR as [amount_curr],~~ ~~f.DEBET_AMOUNT_CURR as [debet_amount_curr],~~ ~~f.CREDIT_AMOUNT_CURR as [credit_amount_curr],~~ ~~f.AMOUNT_OB_CURR as [amount_ob_curr],~~ ~~f.BALANCE_SET_ID as [balance_set_id],~~ ~~f.LEDGER_ID as [ledger_id],~~ ~~f.SOURCE_ORIGIN as [source_origin],~~ ~~f.COMPANY_BASE_CURRENCY as [company_base_currency],~~ ~~f.TRANSACTION_TEXT as [transaction_text],~~ ~~f.VOUCHER_NO as [voucher_no],~~ ~~f.TRANSACTION_ROW_NO as [transaction_row_no],~~ ~~f.VOUCHER_DATE as [voucher_date],~~ ~~f.DIM_ACCOUNT_ID as [dim_account_id],~~ ~~f.DIM_ACCOUNTING_PERIOD_ID as [dim_accounting_period_id],~~ ~~f.DIM_ACCOUNTING_PROJECT_ID as [dim_accounting_project_id],~~ ~~f.DIM_BALANCE_SET_ID as [dim_balance_set_id],~~ ~~f.DIM_CODE_B_ID as [dim_code_b_id],~~ ~~f.DIM_CODE_C_ID as [dim_code_c_id],~~ ~~f.DIM_CODE_D_ID as [dim_code_d_id],~~ ~~f.DIM_CODE_E_ID as [dim_code_e_id],~~ ~~f.DIM_CODE_F_ID as [dim_code_f_id],~~ ~~f.DIM_CODE_G_ID as [dim_code_g_id],~~ ~~f.DIM_CODE_H_ID as [dim_code_h_id],~~ ~~f.DIM_CODE_I_ID as [dim_code_i_id],~~ ~~f.DIM_CODE_J_ID as [dim_code_j_id],~~ ~~f.DIM_COMPANY_ID as [dim_company_id],~~ ~~f.DIM_CURRENCY_CODE_ID as [dim_currency_code_id],~~ ~~f.DIM_PROJECT_ID as [dim_project_id],~~ ~~f.DIM_REPORTING_DATE_ID as [dim_reporting_date_id],~~ ~~f.DIM_RPD_COMPANY_PERIOD_ID as [dim_rpd_company_period_id],~~ ~~f.DIM_VOUCHER_TYPE_ID as [dim_voucher_type_id]~~ ~~FROM FACT_PLAN_TRANS_VALID_BAL_SET_CONF_TM f~~ UNION ALL SELECT

Now all needed configurations have been done.

Next steps:

  • Deploy objects to the target database, either via Setup Tabular Model assistant or the Deploy to SQL Server Db assistant. If the assistant ends with success it means that all necessary objects have been deployed.
  • Next steps can either be performed by the Setup Tabular Model assistant or done step-by-step using separate dialogs and assistants. The steps are:
    1. Deploy the configured tabular model to Analysis Services.
    2. Create a data load definition
    3. Run the data load definition in order to transfer data from IFS Cloud db to the SQL Server/Azure SQL database.
  • Process the tabular model will be last step before the model can be used.