IFS BI Analysis Package

IFS BI Analysis Package is 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.

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 BI Analysis package 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 BI Installer

To support an easy setup of the BI Analysis Package there is a specific IFS BI Analysis Package Installer, to be executed on  SQL Server that should host the installation, that performs the following:

• Using a standard IFS Build Home as input
• Creating a BI Instane 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

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 acessed Information Source views will also affect performence. To support different load behavior there is a configuration tool called IFS BI - 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 intitial 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

BI Services will from Applications 9 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

The BI Analysis package 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

BI Office Apps - IFS Gauges

IFS Gauges consists of a set of gadgets based on MIcrosoft Office Apps technology.

Benefits

• Adds more options to visualize data in Excel 2013 dashboards
• Includes gauges such as speedometer, thermometer, traffic lights and bullet charts
• Office Apps can be placed anywhere in the dashboard, not only in cells
• Office Apps values and control parameters can be manually entered as well as getting their values from cells in the underlying spreadsheet

SSRS Extensions

The BI Analysis Package 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

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

• Tabular Reports

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

  

• APC

  Reporting Services (SSRS) reports that are supposed to be added to the Analysis Panel in IEE. These reports are context sensitive, i.e. the context will be displayed depending on the contect in the current form.

  

• 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 eaily handled. These dashboards may also contain gadgets from BI Office Apps - IFS Gauges.

  Excel dashboards can be deployed to disk.

  

Demo Data Tool

Demo data is important when presenting good-looking BI visualizations. It is often a huge challenge to prepare demo data and in addition available data tends to be out-of-date, e.g. old orders.

Demo data is essential in many situations:

• Pre-sales
• Testing
• Conferences, e.g. IFS WoCo
• User Group meetings, presentations
• Producing documentation and presentation material

IFS BI Analysis Package includes Demo Data Toll supporting handling of Demo Data for a number of facts, with the goal to make it easy to add fetch, manipulate and transfer demo data to the Data Warehouse in SQL Server.

• Data is added only in the Data Warehouse and in the OLAP cubes
• Only used for main transactional data (facts). New customers and other basic data is entered into IFS Applications
• Excel 2013 is used for creating (if applicable) and manipulating data. One prepared Excel file with column headers will be used for each tableTemplate data can be fetched from IFS Applications for further manipulation

Note: Demo Data Tool is intended to be used primarily for IFS personnel and partners. It is not intended for use in production environments.