Analysis Models - Package Data Volume Configuration

This page supplies information about configurations that can be made to control the data volume to be extracted from IFS Applications and transferred to the Data Warehouse in the SQL Server database.

Contents

• General
• Configuration Tool
• Configure Load of Dimensions
• Configure Load of Facts
• Load Condition Examples per Product Area
  • Information Sources in Financials
  • Information Sources in Human Resources
  • Information Sources in Manufacturing

General

General information about Analysis Models and the ETL process can be found here.

In the ETL process, data is extracted from the IFS Applications database and transferred to the Data Warehouse in SQL Server. There are two types of entities that are transferred:

1. Dimensions
2. Facts

Information can in general be transferred in three different ways:

1. Full Load
2. Conditional Load
3. Incremental Load

The following table shows the supported combinations:

Analysis Models contains pre-defined configuration information on how to perform the load for the available sources in IFS Applications.

A source is generally a Access View representing a view that reads from the Online or Data Mart dimension or fact views. There are also a few non-standard sources represented by non-Access Views.

It is recommended to use the utility IFS AM Config Utility for all configuration modifications.

Configuration Tool

The configuration tool can be accessed in the following ways:

1. Analysis Models Package Installer provides a configuration step where the IFS AM Config Utility can be started. This is useful in order to enable configurations before the installer is used to set up agents jobs etc.
2. In the IFS AM Instance Home the configuration utility can be found in the folder  <IFS_AM_HOME>\tools\IFSBISSISConfig

Doing manual modifications in the configuration table in the SQL Server database is not recommended since the risk of removing or incorrectly modifying data is rather big.

In the grid, select the source  (Source ID) to be configured.

Configure Load of Dimensions

Dimensions will in most cases be transferred completely, i.e. each transfer will include all rows in each dimension. One of the reasons for this approach is that dimension views may contain function calls or sub selects that makes it more or less impossible to figure out if a row has changed its value or not.

Incremental load is not allowed. This is not a big issue since dimensions are small in comparison to facts.

Note: Even if true incremental support is developed for a dimension, e.g. as a customization, the configuration tool will not support incremental for dimensions

By default all dimension related sources will be loaded fully, i.e. the Load Type is defined as FULL.

There can be cases where it is of interest to reduce the number of transactions, e.g. for the general time dimension with Source ID=DIM_BI_TIME_BI or the accounting periods dimension represented by SOURCE_ID=DIM_ACCOUNTING_PERIOD_BI

The way to do this is to modify the Load Type for these sources to CONDITIONAL. The next thing to do is to add a WHERE condition that will limit the number of transferred  rows. The Conditional Where Statement field must be edited. It is necessary to include the WHERE keyword.

Two statement examples:

WHERE YEAR >= 2005

WHERE ACCOUNTING_YEAR >= 2010

Note: If dimension data is limited via CONDITIONAL load, it is important not to transfer fact information that references non-transferred dimension identities. So if the time dimension only contains information from year 2005 and onwards, it is not ok to load facts referencing the time dimension with time related dates older than 2005

One example of how it looks in the configuration tool:

Configure Load of Facts

Facts represent transactional data, i.e. large volumes, and transferring facts is in most cases rather time consuming.

By default all fact related sources will be loaded fully, i.e. the Load Type is defined as FULL.

A good idea is to test full load of involved facts in a test environment in order to find out how long time the load will take.

There is also the issue with the initial load. This phase must be carefully planned. It might be necessary to load one fact in several steps.

Note: If the fact is based on transaction tables that support removal of records, e.g. period budget in Financials, it is recommended to only use FULL load. There is no mechanism in the ETL process that can detect that source rows have been removed.

The are a few possible scenarios:

1. Scenario 1
   1. Performing each load as a FULL load
2. Scenario 2
   1. Performing the initial load as a FULL load
   2. Performing subsequent loads as INCREMENTAL loads

   This scenario is typically useful for fact sources that do not support true incremental load but where it is still possible to set up a good enough incremental WHERE condition. For this case it is important that the WHERE condition makes sure to address all new and modified transactions.

3. Scenario 3
   1. Performing the initial load and all subsequent loads using INCREMENTAL load

   This case is possible for sources that support true incremental load. Even if the load type is set to INCREMENTAL the first load will get all transactions according to the incremental where statement, normally meaning all transactions in the source table in the Oracle database. For subsequent loads, only transactions with a change date greater than the last saved change date will be transferred.

4. Scenario 4
   1. Performing the initial as well as the subsequent loads as CONDITIONAL loads where the target Data Mart table is truncated for each load
5. Scenario 5
   1. Performing the initial load as a series of CONDITIONAL loads where the target Data Mart table is NOT truncated for each load
   2. Performing subsequent loads either as CONDITIONAL or INCREMENTAL loads.

      If incremental load is used, it is either for sources that do not support the incremental framework or for sources that support true incremental load

In order to perform a conditional load it is necessary to modify the Load Type for the source to CONDITIONAL.

The next thing to do is to add a WHERE condition that will limit the number of transferred fact rows. The Conditional Where Statement field must be edited. It is necessary to include the WHERE keyword in the statement. It is also important to decide if the target Data Mart table in the data warehouse should be truncated or not. For an initial load case it might be a good idea to load one single fact in steps by using conditional conditions. In this case we want to preserve existing rows since the conditional load should only add rows to the target table.

Some examples on conditional statements:

WHERE ACCOUNTING_YEAR_KEY >= 2011

WHERE EXTRACT(YEAR FROM REPORTING_DATE) >= 2010

Some examples of how it looks in the configuration tool:

In the above case the fact source FACT_PURCHASE_ORDER_LINE_BI will be loaded conditionally with a WHERE condition based on the REPORTING_DATE column. Truncation of the target is allowed which means that target Data Mart table in the data warehouse will be emptied prior to loading the extracted data.

In the above case the fact source FACT_PURCHASE_ORDER_LINE_BI will be loaded conditionally with a WHERE condition based on the REPORTING_DATE column. Truncation of the target is NOT allowed which means that target Data Mart table in the data warehouse will NOT be emptied prior to loading the extracted data. This type of loading can be used when handling the first load of the Data Mart table, e.g. dividing the first load in a set of loads with different conditions.

In order to perform an incremental load it is necessary to modify the Load Type for the source to INCREMENTAL.

The next thing to do is to add a WHERE condition that will limit the number of transferred  fact rows. The Incremental Where Statement field must be edited. It is necessary to include the WHERE keyword in the statement. The conditional and the incremental statements are treated independently, i.e. there is no functionality that performs merge of the incremental and conditional statement during execution of the SSIS packages. So therefore it is important to write the incremental statements correctly, i.e. maybe also considering conditions used in the conditional where statement.

Note: If the source supports true incremental load, a default and suggested WHERE statement will automatically be defined in the Incremental Where field

Some examples on incremental statements:

WHERE ACCOUNTING_YEAR_KEY = 2013 AND ACC_PERIOD_FROM >= (SYSDATE-60)

WHERE EXTRACT(YEAR FROM REPORTING_DATE) >= 2013 AND REPORTING_DATE >= (SYSDATE-60)

One example of how it looks in the configuration tool, typically for a source the does not support true incremental load:

In the above case the fact source FACT_PURCHASE_ORDER_LINE_BI will be loaded incrementally with a WHERE condition based on the REPORTING_DATE column, where the year part should be greater or equal to 2014 and the reporting date is not older than 60 days.

For a source that supports true incremental load the configuration in most cases will look as follows:

The Incremental Where will be defined automatically. It is written to compare the last saved change date in the configuration table, for the current source, with the timestamp column in the source table in IFS Applications.

It is possible to modify the condition but it is not recommended.

If the statement is edited it is always possible to replace current statement with the suggested default one by clicking on <<Get Default Incremental Where

To support load of large fact tables that might be very time consuming, there is a possibility to perform the load in steps.

Assume that we want to load a fact source with transactions representing the last 3 years; 2011, 2012, 2013.  We are using the REPORTING_DATE as filter column in order to extract the year value. The approach now is to define CONDITIONAL load but to disable TRUNCATE of the information in the DM related tables in the Data Warehouse. This means that we can load the fact source conditionally in steps.

1. Make sure that the Load Type is set to CONDITIONAL
2. Make sure that Truncate Target for the source is set to NO
3. Add a conditional WHERE statement, e.g. WHERE EXTRACT(YEAR FROM REPORTING_DATE) = 2011
4. Run the ETL process for the fact source, i.e. transfer year 2011.
5. Modify the conditional WHERE statement, e.g. WHERE EXTRACT(YEAR FROM REPORTING_DATE) = 2012
6. Run the ETL process for the fact source, i.e. transfer year 2012.
7. Modify the conditional WHERE statement, e.g. WHERE EXTRACT(YEAR FROM REPORTING_DATE) = 2013
8. Run the ETL process for the fact source, i.e. transfer year 2013.
9. Modify Truncate Target for the source; set it to YES.
10. Figure out if subsequent loads should be handled conditionally or incrementally.
    1.  CONDITIONAL
       1. Set Load Type to CONDITIONAL
       2. Add a WHERE statement for subsequent loads, e.g. WHERE EXTRACT(YEAR FROM REPORTING_DATE) >= 2013
    2. INCREMENTAL
       1. Set Load Type to INCREMENTAL
       2. Add a WHERE statement for subsequent loads, e.g. WHERE EXTRACT(YEAR FROM REPORTING_DATE) >= 2013 AND REPORTING_DATE >= (SYSDATE-60)
       3. The other option is to use the default WHERE condition WHERE MVT_CREATED_DT > &LAST_MAX_INCR_LOAD_DT for sources that support true incremental load.

Load Condition Examples per Product Area

The following section provides some examples of load conditions per product area. This information is of general nature and may refer to Information Sources yet not supported by Analysis Models.

Information Sources in Financials

The below table provides some examples of conditional columns within Information Sources in the Financials area.

Note: The customer requirements on amount of data etc may leads to use of other columns than the ones listed. However in Financials it is rather convenient to use a condition on the Accounting Year since the books are closed every year and the next year will get incoming balances in period 0, meaning that there is not really any need to transfer more than a couple of years

Note: Some Information Sources do not support incremental loading. A typical example is GL Period Budget. Budget data can be removed from the source tables but the ETL process does not currently support a case where transactions in the IFS Applications' source tables are deleted.

Information Sources in Human Resources

The below table provides some examples of conditional column within Information Sources in the HR area.

Note: The customer requirements on amount of data etc may leads to use of other columns than the ones listed

Information Sources in Manufacturing