Skip to content

Analysis Models - Admin & Config - Tabular Models

This page provides information about available Tabular Model specific client functionality in IFS Cloud.

Use this page to learn more about available Tabular Models, model details as well as of assistants and dialogs that support the user with actions as e.g. database object deployment, Tabular Model deployment, model import.

Tabular Models

Tabular Models Overview Page

The Tabular Models page gives an overview of available models.

Models having Configuration State set to Base, represent core delivered models.

Attribute Description
Model ID The IFS identity of the Tabular Model
Name The name of the model. This is also the name of the SSAS model name, Name.bim, when deployed to a SSAS database.
Description The description of the model. Will appear as the description of the SSAS model Name.bim
SSAS Database ID Identity of the SSAS database that will contain the model Name.bim when deployed to SSAS.
SSAS Database Name The name of the SSAS database that will contain the model Name.bim when deployed to SSAS.
Configuration State The configuration state of the model. For more information, please refer to Custom Configurations.

The overview page provides a number of useful commands once a Tabular Model has been selected as shown in the below picture.

Available commands:

Command Description
Create Configuration Available if the Configuration State is Base. Will define the model as configured by setting Configuration State as Configured. It is now possible to configure model details, e.g. creating a detail configuration that adds a new detail, modifies a Base configuration, removes a configuration detail.
Remove Configuration Removes the configuration and sets the Configuration State to Base.
Exclude Excludes the model or the customization of the model. Leads to a Configuration State change.
Include Includes the model, typically moving from Excluded to Base or from Configured or Dependent to Base.
Import Starts the Tabular Models Import Assistant that will import an existing tabular model from a target SSAS database.
Deploy DW Starts the Data Warehouse Deployment Assistant that supports the user to deploy all Tabular Data Sources referenced by one Tabular Model.
Deploy Model Starts the Tabular Model Deployment Assistant that supports the user to deploy a Tabular Model to the target SSAS database as defined by the IFS model.
Process Database Starts the Tabular Model Processing Assistant that supports the user to process the tabular SSAS model in the SSAS database as defined by the IFS model.
Change SSAS Database Opens a dialog that can be used to define the values for SSAS Database ID and SSAS Database Name.

Selecting on or more models gives the possibility to open the Tabular Model details page.

Tabular Model Detail Page

The Model Details page has the same attributes/fields as the overview page but also shows the associated Expressions, Tables, Relationships and Roles in the model as detail lists.

The Model Details page also provides the following command:

Command Description
New Data Load Definition Opens a dialog that supports the user in creating a new Data Load Definition containing directly referenced and dependent Tabular Data Sources.
Only input needed is a Data Load Definition ID
When ready, the dialog will close and then open the Data Load Configurations page.

Expressions in the model

This detail group lists all expressions in the model.

Attribute Description
Expression ID The IFS identity of the expression in current model ( Model ID )
Name The name of the of the expression.
Data Source ID Identity of Tabular Data Source that specifies the expression source.
Configuration State The configuration state of the expression. An expression can have its own configuration, i.e. a child configuration of the Tabular Model. For more information, please refer to Custom Configurations.

The Expressions in the model group provides the following commands for a single expression:

Command Description
Create Configuration Available if the Configuration State is Base. Will define the expression as configured by setting Configuration State as Configured. It is now possible to configure the expression.
Remove Configuration Removes the expression configuration and sets the Configuration State to Base.
Exclude Excludes the expression or the customization of the expression from deployment. Leads to a Configuration State change.
Include Includes the expression in deployment, typically moving from Excluded to Base or from Configured or Dependent to Base.

Tables in the model

This detail group lists all tables in the model.

Attribute Description
Table ID The IFS identity of the table in current model ( Model ID )
Name The name of the of the table.
Reference Expression ID Identity of expression that the source refers to.
Reference Table ID Identity of table that the source refers to.
Data Source ID Identity of Tabular Data Source that specifies the table source.
Configuration State The configuration state of the table. An table can have its own configuration i.e. a child configuration of the Tabular Model. For more information, please refer to Custom Configurations.

The Tables in the model group provides the following commands for a single table:

Command Description
Create Configuration Available if the Configuration State is Base. Will define the table as configured by setting Configuration State as Configured. It is now possible to configure the table (details).
Remove Configuration Removes the table configuration and sets the Configuration State to Base.
Exclude Excludes the table or the customization of the table from deployment. Leads to a Configuration State change.
Include Includes the table in deployment, typically moving from Excluded to Base or from Configured or Dependent to Base.
Process Table Starts the Tabular Model Processing Assistant that provides the possibility to process the current table but also to process a number of tables or the complete model.

Relationships in the model

This detail group lists all relationships in the model.

Attribute Description
From Table ID The IFS identity of the from table in a table relation in current model ( Model ID )
From Table Column ID The IFS identity of the from table column in a table relation
To Table ID The IFS identity of the to table in a table relation in current model ( Model ID )
To Table Column ID The IFS identity of the to table column in a table relation
From Cardinality Cardinality in the from table, e.g. many in a many-to-one relationship
To Cardinality Cardinality in the to table, e.g. one in a many-to-one relationship
Filtering behavior Indicates how relationships influence filtering of data. Typically OneDirection, BothDirections, Automatic
Configuration State The configuration state of the relationship. For more information, please refer to Custom Configurations.

The Relationships in the model group provides the following commands for a single relationship:

Command Description
Create Configuration Available if the Configuration State is Base. Will define the relationship as configured by setting Configuration State as Configured. It is now possible to configure the relationship.
Remove Configuration Removes the relationship configuration and sets the Configuration State to Base.
Exclude Excludes the relationship or the customization of the relationship from deployment. Leads to a Configuration State change.
Include Includes the relationship in deployment, typically moving from Excluded to Base or from Configured or Dependent to Base.

Roles in the model

This detail group lists all roles defined for the model.

Model roles are normally defined in SSMS (SQL Server Management Studio) and specifies the permissions applicable for the role, included members and table specific filters. If roles are added to a base model in SSMS, then when importing the model to IFS Cloud the role definitions will be defined as a configuration.

The other way to handle roles is to use the Aurena client and to create a configuration of a base model, then defining the role definitions and finally deploying the configured model to SSAS.

The GL Model has a predefined role definition. It contains Table Permissions related to GL Company, Business Plan and Planning Unit users. The role definition will be added in SSAS when deploying the model. Role Members can be added either in Aurena client or in SSMS after having deployed the model.

Attribute Description
Name The name of the role as defined in SSMS (SQL Server Management Studio).
Description Description of the role.
Model Permission Role permission.
Can be one of the following.
Administrator
Members can make modifications to the model schema and can query all data in the model designer, reporting client, and SSMS. Row filters do not apply. All data can be queried in this role.
None
Members cannot make any modifications to the model database schema and cannot query data. Row filters do not apply. No data is visible to users in this role.
Read
Members are allowed to query data (based on row filters) but cannot see the model database in SSMS, cannot make any changes to the model database schema, and the user cannot process the model. Row filters can be applied. Only data specified in the row filter DAX formula is visible to users.
Read Refresh
Members are allowed to query data (based on row-level filters) and run process operations by running a script or package that contains a process command, but cannot make any changes to the database. Cannot view the model database in SSMS. Row filters can be applied. Only data specified in the row filter DAX formula can be queried.
Refresh
Members can run process operations by running a script or package that contains a process command. Cannot modify the model database schema. Cannot query data. Cannot query the model database in SSMS. Row filters do not apply. No data can be queried in this role.
Configuration State The configuration state of the role. An role can have its own configuration, i.e. a child configuration of the Tabular Model. For more information, please refer to Custom Configurations.

The Roles in the model group provides the following commands for a single role:

Command Description
Create Configuration Available if the Configuration State is Base. Will define the role as configured by setting Configuration State as Configured. It is now possible to configure the role.
Remove Configuration Removes the role configuration and sets the Configuration State to Base.
Exclude Excludes the role or the customization of the role from deployment. Leads to a Configuration State change.
Include Includes the role in deployment, typically moving from Excluded to Base or from Configured or Dependent to Base.

Tabular Table Detail Page

From the Tables in the model group in the Tabular Model page, select a table and go to details to navigate to the Tabular Table page. This page provides detail information about:

  1. Partitions in the table
  2. Columns in the table
  3. Measures in the table
  4. Hierarchies in the table

The Table group in the detail page has the same attributes and actions as in the Tables in the model group in the Tabular Model detail page.

Partitions

The Partitions in the table list shows all partitions in the table. Currently only one partition per table is supported.

Attribute Description
Partition ID The IFS identity of the table partition.
Name Name of the partition.
Description Description of the partition.
Data Source Identity of the Tabular Data Source that specifies the table source.
Source Type The type of source used by this partition. Most common type is m which refers to that the partition is using an M expression. For other values, please refer to Microsoft documentation.
Configuration State The configuration state of the table partition. For more information, please refer to Custom Configurations.

Available commands for table partition.

Command Description
Create Configuration Available if the Configuration State is Base. Will define the table partition as configured by setting Configuration State as Configured. It is now possible to configure the table partition.
Remove Configuration Removes the table partition configuration and sets the Configuration State to Base.
Exclude Excludes the table partition from deployment. Leads to a Configuration State change.
Include Includes the table partition in deployment, typically moving from Excluded to Base.

Columns

The Columns in the table group shows all columns in a table.

Attribute Description
Column ID The IFS identity of the table column
Name Name of column
Description Description of column
Data Type Column data type
Display Folder Name of display folder that column should appear in
Hidden Yes if the column should be hidden in client tools.
Configuration State The configuration state of the table column. A table column can have its own configuration, i.e. a child configuration of the tabular table/model. For more information, please refer to Custom Configurations.

Available commands for table column.

Command Description
Create Configuration Available if the Configuration State is Base. Will define the table column as configured by setting Configuration State as Configured. It is now possible to configure the table column
Remove Configuration Removes the table column configuration and sets the Configuration State to Base.
Exclude Excludes the table column from deployment. Leads to a Configuration State change.
Include Includes the table column in deployment, typically moving from Excluded to Base.

Measures

The Measure in the table group shows all measures in a table. Applies to tables based on Facts. (note that the below image is not relevant for the shown tabular table detail.)

Attribute Description
Measure ID The IFS identity of the table measure
Name Name of measure
Description Description of measure
Display Folder Name of display folder that measure should appear in
Hidden Yes if the measure should be hidden in client tools.
Expression The DAX expression for the measure
Format String Format string that applies
Configuration State The configuration state of the table measure. A table measure can have its own configuration, i.e a child confguration of the tabular table/model. For more information, please refer to Custom Configurations.

Available commands for table column.

Command Description
Create Configuration Available if the Configuration State is Base. Will define the table measure as configured by setting Configuration State as Configured. It is now possible to configure the table measure
Remove Configuration Removes the table measure configuration and sets the Configuration State to Base.
Exclude Excludes the table measure from deployment. Leads to a Configuration State change.
Include Includes the table measure in deployment, typically moving from Excluded to Base.

Hierarchies

The Hierarchies in the table group shows all hierarchies in a table.

Attribute Description
Hierarchy ID The IFS identity of the table hierarchy
Name Name of hierarchy
Configuration State The configuration state of the table hierarchy. A table hierarchy can have its own configuration, i.e. a child configuration of the tabular table/model. For more information, please refer to Custom Configurations.

Tabular Role Detail Page

From the Roles in the model group in the Tabular Model page, select a role and go to details to navigate to the Tabular Role page. This page provides detail information about:

  1. Table Permissions
  2. Role Members

The Table Permissions list shows the filter expression that will be applied to a table in the model. These filters are mainly used to support RLS (Row Level Security).

Attribute Description
Table The IFS identity of a table in the model with an associated filter expression that is used to control table permissions.
Filter Expression A DAX expression used as a table filter to control table permission. The filter is typically used for handling of RLS, where members of the role gets access to different table details. The filter expression normally includes a DAX function to retrieve the user identity. This function should in most cases be the USERPRINCIPLENAME(), a function that return the user principle name. If the USERNAME() function is used the user identity is often a domain user. It is important to make sure that the correct function is applied. The result of the user function must return a user name that is defined as AD User Identity in the AD User Mappings page.
Configuration State The configuration state of the role permission. For more information, please refer to Custom Configurations.

Available commands for role permissions.

Command Description
Create Configuration Available if the Configuration State is Base. Will define the role permission as configured by setting Configuration State as Configured. It is now possible to configure the role permission.
Remove Configuration Removes the role permission configuration and sets the Configuration State to Base.
Exclude Excludes the role permission from deployment. Leads to a Configuration State change.
Include Includes the role permission in deployment, typically moving from Excluded to Base.

The Role Members list shows the members of the role.

Attribute Description
Member Name The identity of a role member. If the member is a user then it should be related to the AD User Identity on the AD User Mappings page. When defined via Aurena, only users defined in the AD User Mappings page will be possible to choose. Upon save the related Fnd User identity will be displayed.
During import of a model for which the role definitions have been handled in SSMS, it might be so that the Member Name is not recognized. In this case the Fnd User will not be displayed. When this happens,please make sure to update the mappings on the AD User Mappings page.
Fnd User This is the IFS user identity. Mappings of valid users is made on the AD User Mappings page. This is a read-only field.
Identity Provider The name of the provider that handles user identification. Not a mandatory field. Will in most cases only have a value after import of a role defined in SSMS.
Member Type Defines the member type.
Auto
Group
The member is a user group, typically an AD group.
User
The member is a user, typically an AD user.
Not a mandatory field. Will in most cases only have a value after import of a role defined in SSMS.

Note: Adding or removing members from role is not considered as a configuration.

Tabular Model Explorer page

The Tabular Model Explorer page can be used to explore the details of a tabular model. The explorer page contains more information than can be seen in the Tabular Model detail page.

All available models are presented in a selector.

The Model Details group shows details information for the selected Tabular Model.

The page also has three overview lists:

  1. Expressions in the model
  2. Tables in the model
  3. Relationships in the model

As can be seen from the above image, the model IFS_SALES appears twice.The reason is that this model has been configured, i.e. there is one base version of the model and one configured version.

Exploring the base, non-configured, version lets us show available expressions, tables, relationships, table partitions, table columns etc.

Now, selecting the configured version of the model.

We can see that there is one configured Table.

However, in the explore navigator we will see all details,i.e. both the base and the configured version for all expressions, tables, relationships etc.

Use the navigator to explore available expressions, tables and relationships. The following icons are used:

Data Warehouse Deployment Assistant

The Data Warehouse Deployment Assistant assists in deploying all objects defined by Tabular Data Sources referenced by a Tabular Model in the target SQL Server database.

Before deploying a Tabular Model to SSAS and processing it, all objects referenced by the model specific Tabular Data Sources must be deployed in the target database in SQL Server.

First select the model, either in the Tabular Models overview page or the Tabular Model detail page.

Choose the command Deploy DW to start the assistant. It opens with introduction page.

Use Next.

A list of objects and the actions for each object to be handled will be generated.

If there are errors, use the Script icon to open the script for the source action that reports error and investigate why there are errors before continuing.

In most cases no action is needed in this step. However, there can be a case where it is necessary to only run the actions for a few of the sources. This can be handled by first using the Toggle Deploy Flag at the bottom of the dialog to disable all actions. Next edit the list and enable Deploy for all object actions that should be executed.

Press Next.

A confirmation dialog appears. Choose YES to start the deployment in the SQL Server database.

If the deployment went well, this will be displayed in the assistant.
If there are deployment error, a list of errors will be presented in the assistant. These errors will have to be examined and taken care of before trying to deploy the objects again. If there are only a few errors, then one option is to only deploy actions related to objects that reported errors.

A successful deployment means that all database objects defined by the Tabular Data Sources referenced by the model have been successfully deployed in the SQL Server database. When this is done the next step could by to either deploy the IFS model to the target SSAS database or to set up and run a data load.

Tabular Model Deployment Assistant

The Tabular Model Deployment Assistant assists in deploying an IFS Cloud tabular model into a target SSAS database.

It is possible to deploy a model before having deployed necessary database objects in the SQL Server database, but it is NOT recommended.

First select the model to be deployed, either in the Tabular Models overview page or the Tabular Model detail page.

Choose the command Deploy Model to start the assistant. It opens with an introduction page.

Press Next.

The Tabular Model in SSAS format, i.e. as a .bim file, is presented in this step. The step provides the possibility to download the .bim file and e.g. investigate the created file by opening it in a tool like Tabular Editor, before deployed to the SSAS database.

If ok so far, press Next.

The so-called .tmsl script that will be used to deploy the model in SSAS is presented. The step provides the possibility to download both the .bim file and the .tmsl file if needed.

If ok so far, press Next.

The message box lets you decide to continue with the model deployment or to quit.

If YES is selected, the deployment in the target SSAS datbase will start.

The assistant will present if the deployment went well or not. Deployment errors will have to be investigated.

Tabular Model Processing Assistant

The Tabular Model Processing Assistant assists in processing a deployed Tabular Model in the target SQL Server database.

Before starting the processing, make sure that the:

  1. Tabular Model framework environment has been set up correctly.
  2. Database objects related to the Tabular Model have been successfully deployed in the SQL Server database.
  3. Tabular Model has been sucessfully deployed in the target SSAS database.

First select the model, either in the Tabular Models overview page or the Tabular Model detail page.

Choose the command Process Database to start the assistant. It opens with introduction page.

Press Next.

This page displays the possible processing options. There are two Process Modes. Either the complete tabular database is processed or a sub set of tables in the tabular database are processed. Also select the Process Type. Please refer to public information about SSAS Tabular Models for more info about processing options.

In above example a section has been made to process the complete Database and to use Process Mode=Full.

In above example the Process Mode=Table has been selected. This option leads to that included Tabular Data Sources are listed and the Process property is set to No for all sources. The value of Process can be modified by:

  • Using the button Toggle Process Flag to toggle the value for all sources.
  • Using edit mode and changing value of Process for a few Tabular Data Sources.

The message box lets you decide to continue with the processing of the model or to quit

If YES is selected, the processing of the model with respect to processing selections model will start in the target SSAS database.

If the processing was successful, no error log is supplied, and you can just Finish the assistant.

If there are errors, the error log will be available and can be downloaded for further error analysis.

Scheduled Tabular Model Processing

Processing of a tabular model related database can be scheduled. This is the main processing option in practical scenarios.

Note: Scheduling processing of a tabular database requires that the executing user has access to all necessary functionality. It will be necessary to make sure that special Routing Rules and Routing Addresses are available in IFS Connect. For more information see Remarks - Functionality Access.

Start the New Database Task Schedule assistant.

Provide an easily recognized Schedule Name and then lookup the Database Task named Process Tabular Database.

Go to Next step.

Provide parameters:

  • MODEL_ID_
    Identity of tabular model to be processed.
  • PROCESS_TYPE_
    Defines how the processing should be done.
    • automatic
      Correponds to Default processing.
    • full
    • clearValues
      Clear all tables and load fully.
    • calculate
      Updates and recalculates hierarchies, relationships, and calculated columns.

Go to Next step.

Provide scheduling options/info.

The last step can normally be skipped. Use Finish to create the schedule and open up the Database Task Schedule page.

From the page, it is possible to navigate to the associated background job to get more information about the execution status.

Initially the background job will be in state Posted and is set to Ready when a successful execution has taken place.

Note: Currently the Status of the background job does not show when the complete database processing is ready. Posted indicates when the job has been posted to the queue and Ready indicates that the job execution has been successfully handed over to IFS Connect.

Once the job has finalized, the status and details of the model processing can be found by using the SSIS Custom Event Logs page.

The status of the IFS Connect execution can be found by looking up the Message Function named IFS_TABULAR_MODEL_PROCESS_DATABASE_HANDLING and then view details.

Remarks - Functionality Access

The user that handles scheduling of tabular database processing, needs to be granted a permission set that gives access to the scheduling functionality but also to functionality that is used when executing a schedule such as e.g. I IFS Connect. The built-in permission set FND_ADMIN has the needed privileges.
This means access to:

  • Scheduled Tasks
  • Background Jobs
  • Application Messages
  • Routing Rules (IFS Connect)
    Specific Tabular Models routing rules:
    • IFS Tabular Model Process Database Handling
  • Routing Addresses (IFS Connect)
    Specific Tabular Models routing addresses:
    • IFS Tabular Model Model Process Database Handling

Tabular Model Import Assistant

The Tabular Model Import Assistant is started from either the Tabular Models overview page or the Tabular Model detail page. It will assist in importing an SSAS tabular model to IFS Cloud as an IFS Tabular Model representation.

There are different scenarios:

  1. Create a new IFS Tabular Model by registering a new model in IFS Cloud and then importing an existing SSAS model. The imported model gest the Configuration State set to New.
  2. An IFS Cloud model with state Base already exists when importing an SSAS Tabular Model.
    1. If the imported model has no differences to the existing model, the Configuration State will still be Base.
    2. If there are differences between the existing and imported model, the Configuration State will be Configured, i.e. a customization layer has been added.
  3. An IFS Cloud model with state New already exists when importing an SSAS tabular model.
    1. The import will maintain the Configuration State as New, since New means that it is already a configured model.
  4. An IFS Cloud model with state Configured or Dependent already exists when importing an SSAS Tabular Model.
    1. The existing state will be maintained.

Note: To be able to successfully import a SSAS Tabular Model to IFS Cloud, the required Tabular Data Sources must be available. Delivered standard models will always be supported by required Tabular Data Sources but if e.g. a standard model is modified or if a completely new model is supposed to be imported, it is necessary to add and deploy all needed Tabular Data Sources first. More info about Tabular Data Sources can be found here >>

Note2: New functionality has been released to export and import the BIM Definition of a Tabular Model. Follow here to read up more

Example on creating a new model.
First creating a new record in the Tabular Models overview page, calling it NEW_SALES.

Use the command Import to start the assistant. It starts with an introduction page.

The next page provides a list of available tabular databases in SSAS. Select the database to be imported.

Note the two toogle buttons in the assistant header. By default they are not selected.

  • If Overwrite ID is selected, the source SSAS Database ID will overwrite the current value in the IFS Tabular Model representation.
  • If Overwrite Name is selected, the source SSAS Database Name will overwrite the current value in the IFS Tabular Model representation.

In the next step the model specific .bim file will be retrieved. This might take a while.

Note the two toogle buttons in the assistant header. By default they are not selected.

  • If Overwrite Name is selected, the source model name will overwrite the current value in the IFS Tabular Model representation.
  • If Overwrite Description is selected, the source model description will overwrite the current value in the IFS Tabular Model representation.

When the file has been retrieved, by expanding the Tabular Model (.bim) group, the complete model file is made available.

By using the Download.bim button, the file can be downloaded and then opened in some preferrable tool, e.g. Tabular Editor,and reviewed (if needed).

Next will open a confirmation dialog.

Choose YES to start the import to the IFS Cloud database.

If the import is successful it is displayed in the last step. In this step it is also possible to download the .bim file and also to download and view the import log.

The import log can also be opened later using the Import Logs page.

Change SSAS Database ID Dialog

The Change SSAS Database ID dialog is started from either the Tabular Models overview page or the Tabular Model detail page.

Select a model and then choose the command Change SSAS Database to start the dialog.

The dialog provides the possibility to change both the SSAS Database ID and the SSAS Database Name.
For a predefined base model the SSAS Database ID and the SSAS Database Name are set to the same as the IFS Model ID.

If an already deployed model is to be deployed once again, it can be given a new unique identity and name, to ensure that it can be deployed successfully.

If the SSAS Database ID and the SSAS Database Name are given different values, a warning will be raised.

New Data Load Definition Dialog

Once all necessary db objects have been deployed in the SQL Server database, the next natural step is to create a Data Load Definition that when executed loads data from the IFS Cloud source database to the target SQL Server database.

The easiest way to create a definition that contains all required Tabular Data Sources, is to use the New Data Load Definition dialog that is started from the Tabular Model detail page.

Select a model and then choose the command New Data Load Definition to start the dialog.

In the dialog, the Data Load Definition ID will by default be set to the IFS Model ID. Change the suggested name if needed and then press OK.

A new Data Load Definition will be created and it will be made sure that all directly referenced Tabular Data Sources as well as all dependent sources are included in the definition.

When finished, the Data Load Definitions page will be launched. Select the created Data Load Definition ID and if needed change the description.

Go to details, i.e. navigation to the Data Load Configurations page will take place.

The presented data sources will have Load Type, Where Condition and Truncate SQL Table set to the values as defined for each individual Table Data Source.

It is of course possible to edit the settings for the load configuration.

Next step would be to run the load configuration. This is done either as a direct execution or as a scheduled job.

For more information, please read about Data Load Definition and Volume Configuration.

Copying a Tabular Model

Creating a copy of a Tabular Model has been simplified. There are now two methods in which one can copy a Tabular Model.

Read further on Copying a Tabular Model >>