IFS Analysis Models

IFS Analysis Models consists of a framework that supports an ETL process, transferring data from IFS Applications to a Data Warehouse hosted in MS SQL Server.

ETL means Extract, Transform and Load and it is a standard definition for the process where data is transferred from a source system to a data warehouse in a target system. The idea is to transfer information with rather high complexity into a data warehouse where data is calculated, transformed etc. to define a pure star schema model. The star schema model is perfect as a source when building OLAP cubes.

Contents

• ETL Processing
• IFS Analysis Models Installer
• Configuration Tool
• Support for Incremental Load
• OLAP Cubes
• SSRS Extensions
• Prepackaged Content

ETL Processing

The ETL processing is handled by SSIS packages. SSIS means SQL Server Integration Services and is a MS framework that servers as a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool for ETL processing.

The Analysis Models consists of SSIS packages handling:

• Extract of dimensional data from IFS Applications to the staging area
• Extract of fact data from IFS Applications to the staging area
• Transfer of dimensional data from the staging area to the DW area
• Transfer of dimensional data from the DW area to the DM (Data Mart) area, including creation of new unique identifiers
• Transfer of fact data from the staging area to the DW area, including lookup if dimension identities
• Transfer of fact data from the DW area to the DM (Data Mart) area
• Execution of all Facts related packages in the Extract step, per component
• OLAP Cube processing
• Execution of the complete ETL process

IFS Analysis Models Installer

To support an easy setup of the data warehouse part of IFS Analysis Models, there is a specific IFS Analysis Model Installer, running on SQL Server that acts as a host for the installation. The following is performed:

• Using a standard IFS Build Home as input
• Creating an Instance Home
• Creating an ETL database in SQL Server
• Configuring all files with correct login/access information
• Executing all database scripts, creating tables, views, stored procedures etc
• Deploying OLAP Cubes in a Analysis Services
• Deployment of sample contents in Reporting Services
• Creating an Agent job that runs the complete installation process
• Creating KPI Services specific agent jobs

Configuration Tool

The ETL process can be executed in different ways. Each load can be performed as a full load which is also the default load type. 

The ETL process can however be configured to use full, conditional or incremental load for most of the supported sources, except for dimensions that do not support incremental load.

In most cases it is enough to perform a full load of all involved dimensions.

For Facts it is a bit different since the facts represent transactional data and that might mean transfer of large number of transactions. The performance of the accessed Information Source views will also affect performance. To support different load behavior there is a configuration tool called IFS Analysis Models - SSIS Config Utility.

For facts this means that it is possible to run the first initial load in the following ways:

1. Perform a full load
2. Perform a conditional load where only a sub set of all transactions are transferred. One example can be to only transfer financial data that is max 2 years old.
3. Perform conditional loading in steps, e.g. loading transactions per year. In this case the DM (table) will not be truncated which is the case for the first two load scenarios.

After having performed the initial load, subsequent loads can be performed as a incremental load, meaning that only a part of the source transactions are transferred to the Data Warehouse and all existing transactions in the DM (Data Mart) are kept as is, leading to update or add of transactions.

Note: Incremental load in this case will only be based on an assumption, i.e. an incremental criteria has to be supplied but it can be rather tricky to define an accurate criteria.

If true incremental load is supported then the tool displays the last max changed data for the entities, normally facts, that support true incremental load.

Support for Incremental Load

Business Reporting & Analysis support true incremental load. This means that it will be possible to track only updated or new records and to transfer the corresponding Information Source transactions to the Data Warehouse.

This means that the initial transfer can be time consuming, depending on number of transactions etc., but the following loads can then be rather efficient since on most cases rather few transactions will be transferred.

OLAP Cubes

Analysis Models supports a set of pre-packaged OLAP cubes.

OLAP means On-Line Analytical Processing and a cube supports multi dimensional analysis with high end user performance.

Cube specifics

• Based on the star schema model in DM (Data Mart) area in the Data Warehouse
• Consists of one or more facts containing
  • Measures from Information Sources
  • Calculated measures and KPIs

    Calculated measures bring additional values such as

    • YTD values, R12 values, Differences, Quotes
    • Recalculation into reporting currencies
• Consists of 2 or more dimensions
  • Dimensions are used to organize and filter data
  • Dimensions are based on Information Sources
  • Dimensions often contains hierarchies that adds additional value and allows for interactive drill-down

SSRS Extensions

Analysis Models contains a set of Reporting Services (SSRS) extensions,

The main purpose with extensions is to support different authentication methods but also to support using IFS Applications as a custom data source in Reporting Services.

• Authentication Extension
• Authorization Extension
• Data Processing Extension
• Data Source Extension

Prepackaged Content

Analysis Models contains pre-packaged content. This content is not supported but is still maintained to make it easy to use and demo developed functionality. The following is included:

• Tabular Reports

  Ordinary Reporting Services (SSRS ) reports. Example reports exist in all product areas that are supported.

  

• Excel Dashboards

  The Excel dashboards are based on Excel 2013 in combination with Analysis Services. Since Excel is a familiar tool, modifications and customizations are rather easily handled.

  IFS Applications only support deployment of Excel dashboards to disk.
  Example Excel dashboards can be downloaded from the respective Analysis Models component on the IFS Applications 9 track.