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:
- Create configurations that excludes tables according to the previous list with data sources and tables per modes and dynamic component.
- 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:
- Deploy the configured tabular model to Analysis Services.
- Create a data load definition
- 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:
- Create configurations that excludes BUSPLN related tables from the model.
- Create configurations that excludes all relationships related to BUSPLN.
- Create configurations on tabular data source FACT_TRANS_BAL_SET_CONF
- Exclude a dependency
- 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 |
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:
- Deploy the configured tabular model to Analysis Services.
- Create a data load definition
- 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.