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
Export Data Source	Export the Data Source to an XML file. This can be used to share the Data Source with other environments. To read more on Export Import of Data Sources >>
Import Data Source	Import an exported Data Source from other environments To read more on Export Import of Data Sources >>
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.