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:
- Deploying necessary objects in the SQL Server/Azure SQL database that are needed by an IFS Tabular Model.
- 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
|
| 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.
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.
