This page describes how to configure specific lookup information to be used in the ETL process. In order to reduce complexity but also ambiguities when building cubes, it is in some cases necessary to provide information that will act as filters to reduce amount of data to be processed.
Ambiguities may appear when a purpose-built dimension in IFS Applications has an ID column that does not represent a unique row identifier. The standard case is of course that the ID is a unique identifier but sometimes this rule does not apply. In these cases it is rather common that there are parent keys represented as ordinary dimension attributes and thus it is necessary to filter out a portion of the data via lookup data. If this is not done, it will not be possible to find the correct technical identity of a dimension.
Complexity many times goes hand-in-hand with ambiguity. One example is Accounting Structures in Financials. The same code part may exist in many different structures in a company but the fact transactions have no information about what structure identity that applies for a given transaction row. So it is necessary to specify what structure identity to use to get a reasonable amount of structure data but it also enables the fact to connect to a structure dimension without getting multiple hits when performing a dimension lookup to find the technical key of a dimension reference.
The solution has been to introduce a set of lookup tables that must be configured to enable a successful build of the OLAP cubes. The lookup information references basic data in IFS Applications, so it is important to first define the basic data before the lookup information is supplied. Read more on how to configure basic data in IFS Applications:
Note: Lookup data is defined and stored in the SQL Server database, meaning that there are no validations that entered identities are correct.
The following information is only related to tables that reside in the SQL Server database. Each tab has predefined values that of course can be modified. Remember to make sure that the corresponding data is available in IFS Applications.
To simplify the lookup configuration a tool is available named IFS AM Config Utility. The utility can be accessed in the following ways:
Note: The tool has a tab named SSIS Package Config used for data volume configuration. For more information follow this link >>
This lookup defines the Reporting Period Definition Identity to be used by cubes related to:
Note: It is mandatory to set up a Reporting Period ID for cubes in above listed areas to make sure that the cubes are correctly created. Also refer to Basic Data Configuration.
The tab Reporting Period Lookup is used to configure this information.
Select a row and then edit the information in the section below the grid.
For each Source the Reporting Period Definition Identity should be set to the identity that applies for Information Sources related to that specific source.
For Source=Financials it is also necessary to supply a Master Company. The mapping between reporting periods and accounting periods for this company will by regarded as the master fiscal calendar, later used in the cube when building a time dimension based hierarchy. It is important to make sure that all companies connected to the Reporting Period Definition Identity assigned to Financials, have the same accounting period calendar as the master company; else it will not be possible to analyze this companies correctly, especially when it comes to time based measures as YTD, Rolling12 etc.
This lookup defines a company and the currency rate type in that company that will act as the rate source when calculating amounts in reporting currency in the cubes. It is only possible to define one single rate type.
Note: Each Information Source that supports calculation of amounts in reporting currency, will use the rates in the specified company and currency rate type
To find out what date is used when looking up the rate, please refer to the documentation of each fact, available in IFS Online documentation
Note: It is mandatory o set up the (company and) currency rate type to be used when calculating measures in reporting currency. If not, most of the IFS OLAP cubes will not be correctly created. Also refer to Basic Data Configuration.
The tab Reporting Currency Rate Type Lookup is used for this configuration.
Select a row and then edit the information in the section below the grid.
This lookup limits the possible age groups classes to only one specific class.
The information will be used by cubes in the HR area. In order to prepare for flexibility there is a Source column that enables different cubes to use different age group classes.
The tab Age Group Class Lookup is used to configure this information.
Select a row and then edit the information in the section below the grid.
This lookup limits the possible employment duration classes to only one specific class.
The information will be used by cubes in the HR area. In order to prepare for flexibility there is a Source column that enables different cubes to use different employment duration classes.
The tab Employee Duration Class Lookup is used to configure this information.
Select a row and then edit the information in the section below the grid.
This lookup defines the Accounting Attribute to be used for each one of the code parts Account, Code B to Code J, typically to be used by OLAP cubes in the Financials area. This configuration means that the OLAP cube(s) can only handle one Accounting Attribute per code part.
The tab Accounting Attr Lookup is used to configure this information.
Select a row and then edit the information in the section below the grid.
This lookup defines the Accounting Structure Identity to be used for each one of the code parts Account, Code B to Code J, typically to be used in the OLAP cubes in the Financials area.
This configuration means that the OLAP cube(s) can only handle one Accounting Structure per code part except for Account, that supports two structures:
The tab Accounting Structure Lookup is used to configure this information.
Select a row and then edit the information in the section below the grid.
This lookup defines if income statement balances in Financial related Information Sources should be reversed or not, i.e. of the sign should be reversed.
Accounts associated with the following logical account types are considered as income statement related accounts:
Cost
Revenue
Statistics
The default setup is according to the following:
The above lookup configuration means that it is possible to define if balances/amounts related to income statement accounts should be reversed (multiplied with -1) or not. The configuration can be done independently for the listed Information Sources.
Note: The Information Source with FACT_ID=FACT_GL_BAL_SET_ANALYS is represented by two facts in the configuration table. The reason is that this Information Source serves as a source for two different Facts in the Data Mart in SQL Server.
The following identities are referred to in the configuration:
This is the GL Balance Set Analysis related fact to which the dimension GL Balance Set is connected
This is the GL Balance Set Analysis related fact representing information based on configured GL Balance Sets, i.e. balance sets representing Actual, Budget, Current and Next Forecast.
The described configuration leads to the following for e.g. Information Source GL Balance
The configuration makes it possible to change the described behavior. After having changes this configuration, the only things that is needed is to rebuild the Financial cube.
This lookup defines which Organization Structures from HR that are transferred into the HR cube to be used as Analysis Structures. These structures offers drill down capabilities as well as time series analysis.
Two structures are can be configured together with reference dates that are used to extract the structure data as it looked the reference date. If no reference date is given, the structure is transferred as it looks at the time the ETL process is being run, i.e. SYSDATE.
Mostly used as the regular structure within the organization. Displayed in dimension ANALYSIS STRUCTURE - CURRENT
This structure can be used in a matix organization situation, or to compare the current structure with a historical organization structure. Displayed in dimension ANALYSIS STRUCTURE - ALTERNATIVE
The setup is according to the following:
