Skip to content

Analysis Models - Data Sources

This page provides information about Tabular Data Sources specific functionality in IFS Cloud.

Use this page to learn more about what a tabular data source represents and what it consists of, available commands etc.

General

A Tabular Data Source is the entity that connects an IFS model representation of an SSAS Tabular Model with IFS Cloud sources such as Information Sources, configurations and other help sources. A Tabular Data Source also defines objects to be created in the target database in SQL Server/Azure SQL.

A Tabular Data Source typically defines the following:

  • Name of target table in SQL Server database.
  • Name of a read-from source:
    • Either an Oracle source (view) that provides the source data.
    • Or a SQL Server specific view, normally created by an other dependent Tabular Data Source
  • Column overrides, i.e. definition of columns that in Oracle database has a data type that cannot be automatically translated to correct data type in SQL Server/Azure SQL database. Applies only if the read source is an Oracle source.
  • Indexes to be created on the SQL Server/Azure SQL target table.
  • SQL Server specific views that represent transformation views, normally defined on top of the target table.
  • Dependent Tabular Data Sources are referenced by Tabular Model source types as expressions and table partitions in the IFS Model representation.

The Tabular Data Sources are used when:

  1. Deploying necessary objects in the SQL Server/Azure SQL database that are needed by an IFS Tabular Model.
  2. Creating a Data Load Configuration that is used when ordering a load of the data layer in the target SQL Server/Azure SQL database.

A Tabular Data Source supports configuration, i.e. the possibility to configure an existing core/base source or to create a completely new configured source.

Data Sources Overview

The Data Sources page provides an overview for avaiable Tabular Data Sources.

Attribute Description
Data Source ID Identity of the Tabular Data Source
Description Description of the source
Source View Name Name of the source view
Source Type Source type, either Oracle or SQL Server/Azure SQL
Target SQL Server Table Name of the target table in the SQL Server database
Last Refreshed Timestamp when a referesh of the data source was last run.
Last Max Change Date Max change/create timestamp of handled transactions in the last/latest run.
Configuration State The configuration state of the data source. For more information, please refer to Custom Configurations.

Available commands:

Command Description
Create Configuration Available if the Configuration State is Base. Will define the data source as configured by setting Configuration State as Configured. It is now possible to configure the source, e.g. creating a detail configuration that adds a new detail, modifies a Base configuration, removes a configuration detail.
Exclude Excludes the data source, i.e. the data source will not be included when a data load definition is created.

Data Source Details

Details for a Tabular Data Source are found in the Data Source page. There are different detail groups.

In the above image all available detail groups can be seen,

The two expanded groups are:

  • Tabular Data Source
    Contains general information about the Tabular Data Source.
  • Oracle Source
    Contains information about an Oracle specific (read) source.

The above image shows the following expanded groups:

  • SQL Server Source
    Contains information about a SQL Server/Azure SQL specific read source
  • SQL Server Destination
    Contains information about the SQL Server/Azure SQL destination table
  • SQL Server Destination Column Data Type Overrides
    Contains information about column data type overrides related to the SQL Server/Azure SQL destination table
  • SQL Server Destination Indexes
    Contains information about the indexes to be created on the SQL Server/Azure SQL destination table


The above image shows the expanded groups:

  • SQL Server Source Views
    Contains information about special SQL Server/Azure SQL views that can be used for basic transformations of the stored data, to make sure that data is fit for purpose for the target tabular models.
  • Required Data Sources
    Contains information about Tabular Data Sources that are required to ensure that the actual source can be used properly. A typical case is when one Tabular Data Source defines one or more SQL Server/Azure SQL views that require that other SQL Server/Azure SQL views are available.
Attribute Description
Tabular Data Source
Data Source ID Identity of the Tabular Data Source
Description Description of the source
Last Refreshed Timestamp when a refresh of the data source was last run
Configuration State The configuration state of the data source. For more information, please refer to Custom Configurations.
Oracle Data Source
View Name Name of source view in the Oracle database
Type Type of source view
  • Fact
    Fact specific source view
  • Dimension
    Dimension specific source view
  • Config
    Data configuration source view
  • Other
    Other type of source view
Supports Incremental Load Specifies if the source is supported by the incremental framework or not. Generally only Fact sources support this functionality.
Load Type Defines the load type that applies for the source. The value is suggested when creating a Data Load Definition where the Tabular Data Source is included.
  • Full
    The source table in SQL Server is loaded fully
  • Conditional
    The source table in SQL Server is loaded conditionally
  • Incremental

  • The source type in SQL Server is loaded incrementally. Can only be defined if the Tabular Data Source supports incremental loading.
Conditional Where Where condition to be used if the Load Type is Conditional.
The value is when creating a Data Load Definition where the Tabular Data Source is included.
SQL Server Source
View Name Name of the source view in SQL Server. If this type of view is defined, then the loading of the target SQL Server table is done be reading from the SQL Server source view.
SQL Server Destination
Table Name Name of the target table in the SQL Server database.
Truncate Table Defines if the target table should be truncated or not
Create Primary Key on ID Defines if a primary key index should be created on the ID column.
SQL Server Destination Column Data Type Overrides
Column Name Name of a column in the target SQL Server table
Data Type SQL Server data type
Length Data type length. Typically defined for string data types.
Precision Total number of digits in a number.
Scale Number of digits to the right of the decimal point in a number.
Configuration State The configuration state of the column override. For more information, please refer to Custom Configurations.
SQL Server Destination Indexes
Name Name of index.
Column List List of columns in the index, Suggested to use the following syntax:
[column_name1],[column_name2],...,[column_nameN]
Index Param Indicates type of index. 1 = Unique index, 2 = Primary Key, NULL = Non-unique index.
Configuration State The configuration state of the destination index definition. For more information, please refer to Custom Configurations.
SQL Server Source Views
View ID SQL Server view identifier
Name Name of the view
Definition View definition
Order Global deployment order. Important attribute that needs to be defined such that necessary dependent views are deployed before the current view.
Configuration State The configuration state of the SQL Server source view. For more information, please refer to Custom Configurations.
Required Data Sources
Required Data Source ID Identity of a dependent Tabular Data Source
Sequence Sequence number
Configuration State The configuration state of the required data source info. For more information, please refer to Custom Configurations.

Available commands:

Command Description
Create Configuration Available if the Configuration State is Base. Will define the data source as configured by setting Configuration State as Configured. It is now possible to configure the source e.g., creating a detail configuration that adds a new detail, modifies a Base configuration, removes a configuration detail.
Remove Configuration Removes the data source configuration and sets the Configuration State to Base.
Exclude Excludes the data source, i.e. the data source will not be included when a data load definition is created.
Include Includes the data source, typically moving from Extended to Base.
View Table Definition Opens a dialog which displays the column details of the Oracle access view connected to the data source along with the table creation script for SQL Server/Azure SQL deployment. Useful when adding or modifying a data source based on an Oracle source, since the output can be used to review the column data types that might lead to addition/modification of column override, specified in SQL Server Destination Column Data Type Overrides section.

Using the command View Table Definition opens a dialog like shown in the following image.