Financials KPIs

Financials KPIs represent a sub set of the KPI Services functionality. The purpose with this functionality is to provide a way to define typical financial KPI definitions and then transform them to MDX (Multi Dimensions Expressions) before transfer them to a target OLAP cube, thus enabling the possibility to rather instantly start performing financial analyses using the General Ledger or the Group Consolidation OLAP cube.

This section provides some more details relates to the KPI Services functionality.

Contents

 

General Functional Overview

The purpose with the Financial KPIs functionality is to enable the possibility from the IFS client to define typical financials KPIs or measures based on accounting structures and then publish the KPIs in a target SSAS (SQL Server Analysis Services) OLAP cube. Publishing means adding new measures to the OLAP cube meaning that the OLAP cube can now directly be used in e.g. Excel for further analysis.

Financials KPIs are based in accounting structures since most KPIs can be defined by using account based Income Statement or Balance Sheet accounting structures. This approach provides a big flexibility. The framework only supports accounting structures based on Account.

The IFS standard cubes related to General Ledger and Group Consolidated support Income Statement and Balance Sheet structures by providing special associated cube dimensions.

Analysis Models Installer

The Analysis Models installer supports Financial KPIs by installing a set of necessary SQL Server Agent Jobs if the technical component KPISMS is part of the build. These Agent Jobs are necessary to make the interaction between IFS Enterprise Explorer and SQL Server work properly.

For more details about the installer, please follow this link.

 

Connection Settings

Communication between IFS Aurena and SQL Server requires some connection settings to be defined. The configuration settings can only be defined through the IFS Installer. The configurations are done for KPI Services and Cube access via IFS Lobby via the same installation step. For KPI Services it is necessary to define:

  1. SSAS OLAP Cube Parameters
  2. SQL Server Data Warehouse Parameters

The current setting can be viewed in the Connection Settings page.

Configuration Key Configuration Value Description
MS_SQL_SERVER_DB_SERVER_NAME SQLSRVDB This is the name of server where the MS SQL Server database installation has been made. This is thus the server chosen to host the Analysis Models related to the current IFS Applications instance.
MS_SQL_SERVER_DB_NAME Apps10_Prod The name of the database on the MS SQL Server db server that will contain the ETL data related to Analysis Models for the current IFS Applications instance
MS_SQL_SERVER_USERNAME bisqlssuser Microsoft SQL Server, (SQL Server Authenticated) Username
MS_SQL_SERVER_PASSWORD zVRVgdf5MVa5sk4l5YK75l98ln+mLswYekx6ANu Microsoft SQL Server, (SQL Server Authenticated) User Password
MS_SQL_SERVER_CON_MAX_EXE 60000 Microsoft SQL Server, Maximum Amount of Time (ms) a Connection should wait for Query Execution
MS_SQL_SERVER_CON_MAX_ACT 30 Microsoft SQL Server, JDBC Connection Pool No of Max Active Connections
MS_SQL_SERVER_CON_MAX_TOT 40 Microsoft SQL Server, JDBC Connection Pool No of Max Concurrent Connections
MS_SQL_SERVER_CON_MAX_WAIT 60000
Microsoft SQL Server, Maximum Amount of Time (ms) a Request should wait for a Connection from JDBC Connection Pool before throwing an Error.
MS_SQL_SERVER_JDBC_CON_STR_FMT jdbc:sqlserver://{SERVER_NAME};databaseName={DATABASE_NAME} Microsoft SQL Server, JDBC Connection String Format
SSAS_SERVER_NAME SQLSRVSSAS The name of the server where MS SQL Server Analysis Services (SSAS) has been installed. Many times the SSAS server name is the same as the db server name. The SSAS server will host the OLAP cubes deployed by the Analysis Models.
SSAS_SERVER_HTTP_URL http://SQLSRVSSAS/OLAP/msmdpump.dll SQL Server Analysis Services, HTTP endpoint
SSAS_USERNAME domain\bissasuser SQL Server Analysis Services, Domain Username of a SSAS Administrator
SSAS_PASSWORD vHUbcfhjtN9hYa7bKeNnWkWRfl3ksT65e45tQr4 SQL Server Analysis Services, Password of {SSAS_USERNAME} user
SSAS_CON_MAX_EXE 60000 SQL Server Analysis Services, JDBC Connection Pool Max Execution Time
SSAS_CON_MAX_ACT 30 SQL Server Analysis Services, JDBC Connection Pool No of Max Active Connections
SSAS_CON_MAX_TOT 40 SQL Server Analysis Services, JDBC Connection Pool No of Max Concurrent Connections
SSAS_CON_MAX_WAIT 60000 SQL Server Analysis Services, JDBC Connection Pool Max Wait Time

Note: The configuration values above are only examples

Note: Installation of Analysis Models on SQL Server means setting up an ETL process, SSRS reports etc. based on one single IFS Applications instance as source. The configuration settings for Financial KPIs should reflect this situation. The Analysis Models Installer will also set up necessary SQL Server Agent Jobs that can only relate to one IFS Applications instance.

Refreshing SSAS Info

In the Connection Settings page, that is read-only, it is necessary to use the command Refresh SSAS Database Info in order to refresh stored information about existing SSAS databases in the SSAS server provided in the settings. If the command is not executed, it will not be possible to define a OLAP cube definition in KPI Services.

Permissions

Interaction with SQL Server from IFS Aurena means accessing the SQL Server database as well as the SSAS server.

The following must be set up on SQL Server for the user accounts configured to access SQL Server via IFS Aurena:

KPI Company Groups

KPI Company Group is a company basic data related functionality that has been added to the Financial KPI functionality (and not to IFS Accounting Rules). The purpose is to enable the possibility to group companies with the same follow-up structure. By doing this it will be possible to use the standard GL OLAP Cube for different analysis scenarios. KPI Company Groups can be used without having to define any Financial KPIs.

To use KPI Company Groups in the GL Cube, the following is required:

OLAP Cube Set-up

In the OLAP Cube Setup page a cube identity is defined, supposed to represent a target OLAP Cube in the SSAS database.

The target OLAP cube is defined by the following attributes:

A possible case when the cube database is not the same as the one created by the IFS Analysis Models Installer would be if it is required to test Financials KPI definitions in a separate cube, e.g. test, before the KPI definitions are published to the real target cube, e.g. production. Note that this case has some limitations related to ETL jobs.

List of Values support is available for the attributes OLAP Database Name and OLAP Cube Name but it requires that cube database information related to the configured SSAS server has been refreshed in the Connection Settings page.

Once the cube has been defined, it will be necessary to use the command Fetch/Refresh Cube Info to refresh stored information about available measure, measure groups, dimensions and hierarchies.

The Measure Name field in the CUBE MEASURES tab supports List of Values once the cube information has been refreshed.

The OLAP Cube Set-up page also provides the following interaction options:

Financial KPI Elements

Financial KPI Elements represent the building blocks that are used when defining a Financial KPI. Refer to the page Financial KPI Definition.

An element is defined in the scope of a cube and a company group and must be based on an accounting structure. In most cases this is either the Balance Sheet or Income Statement structure.

A cube source is defined for  the element, referring to a suitable OLAP cube measure in the context of a node in a structure hierarchy. A cube dimension is defined representing an accounting structure specific OLAP cube dimension, e.g. ACC STRUCT BS CG or ACC STRUCT IS CG, a hierarchy in that dimension (BSHy or ISHy) and a node in the referenced accounting structure.

List of Values against the target OLAP cube is possible and requires that a refresh has been made in the OLAP Cube Setup page and that the configured SSAS domain account has read rights on the OLAP cube.

Financial KPI Definitions

Financial KPI Definitions represent the measures to be created in the target OLAP cube.

Each KPI must have a unique name in the OLAP cube.

A KPI is defined by creating a set of calculation items, i.e. using the items KPI Element, KPI Operator, Factor, KPI ID to define the KPI. One example could be to create a KPI by using the element OPRES (operational result) and add it to the element FINRES (financial result).

When the definition is done it can be validated against the target OLAP cube via the command Validate KPI. Validation requires that the configured SSAS domain account has read rights on the OLAP cube.

Special Considerations

This part deals with some special issues that might have to be considered.

Problem Connecting to SQL Server

If, when performing commands related to interaction with SQL Server, a message box pops up saying Access denied the most probable reasons are the following:

  1. The configured Microsoft SQL Server user account does not have privileges to access the configured SQL Server.

    Make sure to check the privileges for that user. Some information is found under OLAP Cube Set-up

 

Problems Encountered When Running ETL for GL or Group Consolidation Only

By default the Analysis Models installer will install some SQL Server Agent Jobs related to the Financials KPI functionality. The jobs are, assuming a given prefix as KPISRV_Bi_Prod:

  1. KPISRV_Bi_Prod_Process_Cube
  2. KPISRV_Bi_Prod_Run_ETL
  3. KPISRV_Bi_Prod_Run_Cube_GL_ETL
  4. KPISRV_Bi_Prod_Run_Cube_Cons_ETL
  5. KPISRV_Bi_Prod_Transfer_KPIs

Please also refer to general information about installing KPI Services.

By default only three of these agent jobs will be active and used by the Financial KPI functionality. When it comes to processing the ETL data, the job KPISRV_Bi_Prod_Run_Cube_GL_ETL or KPISRV_Bi_Prod_Run_Cube_Cons_ETL is used while the job KPISRV_Bi_Prod_Run_ETL is not used; it is only there as a backup job.

The job KPISRV_Bi_Prod_Run_GL_ETL only transfers General Ledger cube specific data and the job KPISRV_Bi_Prod_Run_Cube_Cons_ETL only transfers Group Consolidation cube specific data from IFS Applications to the data warehouse on SQL Server. This is relevant since the Financials KPI functionality only affects the General Ledger and Group Consolidation cubes. The time to transfer the data is also reduced compared to transferring data for all available ETL sources.

If for some reason a cube specific job fails or if the data does not seem to be transferred correctly then there is a possibility to switch to the job KPISRV_Bi_Prod_Run_ETL, a job that transfers all data for all available ETL sources,  e.g. a full ETL job. This job can be more reliable but at the same time it takes much more time to execute.

Note: Before switching agent job, please check log files in the Log folder under the SSIS folder in the instance home. Also try running the agent job manually, as Administrator, a couple of times and investigate the log files to see if there are errors or if some ETL sources are not processed.

 To switch agent job for GL Cube do the following:

  1. Open up SQL Server Management Studio
  2. In the Databases folder, locate the database that is the target for the IFS Installation
  3. Select from the table SSIS_JOB_CONFIG_TAB

  4. Update the row with JOB_ID=<prefix>:Run_Cube_GL_ETL to <prefix>:Run_ETL

Note: Switching from the cube specific ETL to the full ETL transfer job means the ETL process might take a long time to execute and the job will transfer data for all ETL sources from IFS Applications to SQL Server