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.
The purpose with the Financial KPIs is to enable the possibility to within IFS EE 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 (2013 or later) 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 General Ledger cube supports Income Statement and Balance Sheet structures by providing special associated cube dimensions.
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.
In order to fully be able to use the functionality related to Financial KPIs it is necessary to install some prerequisites, i.e. ADOMD and ADO providers for SQL Server.
To do this, locate the home page of the IFS instance and use the link Add-ons that opens a page that lists different additional software. ADOMD and ADO providers exists in 32 and 64 bit versions. Choose the version that corresponds to the current client operating system, i.e. for a 64 bit client OS choose for 64 bit OS providers.
Figure 1: Providers for SQL Server on IFS Home page
Communicating from IEE with 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:
Note: There are different configuration requirements depending on if KPI Services is accessed from IEE client or from IFS Aurena. Still the configurations can only be made through IFS Installer, which is a change since the RTM release of IFS Applications.
The current setting can be viewed in the Connection Settings window.
| 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 |
If KPI Services is only accessed via IEE client, then only the following configuration keys are used:
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.
Interaction with SQL Server from IEE means accessing the SQL Server database as well as the SSAS server.
Only WIA, Windows Integrated Authentication, is supported. This means that the Windows account of the current user logged on to IEE must have been set up properly in SQL Server to enable the user to interact successfully with SQL Server.
The following must be set up on SQL Server for the Windows accounts that should access SQL Server via IEE:
A Windows account defined as an SSAS Administrator will also have read and process rights in the cubes in the SSAS database.
To define only read or processing rights, create a role on the target SSAS Cube Database that has necessary rights and make sure that the Windows account is granted this role. Allowing read and/or processing rights limits the usability of the KPI functionality for IEE users since it will not be possible to publish the KPIs to the OLAP cube,a rather big functional limitation.
As part of the Financial KPI functionality a new basic data concept called KPI Company Group has been added. 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:
In the OLAP Cube Set-up form 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:
This is the name of the target OLAP cube database on the SSAS server define in the Connection Settings window. Normally this cube database is the one created by the IFS Analysis Models Installer but it can also refer to another cube database on the SSAS server.
This is the name of the OLAP cube in the OLAP cube database. Normally this cube is the one created by the IFS Analysis Models Installer but it could also refer to another cube.
A typical 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.
List of Values support is available for the attributes OLAP Database Name and OLAP Cube Name as well as for Measure Name in the Cube Measure tab and requires that SQL Server prerequisites have been installed on the client machine.
The OLAP Cube Set-up form also provides the following interaction options:
Validates all valid Financials KPI definitions by transferring KPI definitions to the SQL Server database and then creates Multi Dimensional Expressions (MDX) that are run against the target OLAP cube.
Requires read rights on the OLAP cube.
Valid KPI definitions are transferred to the SQL Server database and then the KPIs are defined as cube measures, based on Multi Dimensional Expressions (MDX) in the target OLAP cube.
Note: This action will thus modify the target OLAP cube and will require that the windows account is defined as an SSAS administrator.
This action will make sure that the ETL database on SQL Server is updated with KPI Company Group information before the target OLAP cube is processed.
Note: Processing the cube should be done with care. Normally this is handled by a scheduled agent job on the SQL Server. Requires processing rights on the OLAP cube.
This action runs the complete ETL process related to the selected cube, i.e. data related to all supported dimensions and facts for the selected cube will be transferred from the current IFS Applications instance to the ETL database.
Note: Use this action with care since it will overrule ETL executions handled by the SQL Server administrator. If the action is executed online, it is recommended to first create a new IEE window so that other work can be done while the ETL action runs. For a small database it will take 20-30 minutes to complete. If the action is executed in batch, try to make sure that the ETL job is set up to mail the outcome of the processing to one ore more users.
Financial KPI Elements represent the building blocks that are used when defining a Financial KPI. Refer to the IEE form 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 SQL Server prerequisites have been installed on the client machine and the windows account must also have read rights on the OLAP cube.
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 RMB option Validate KPI. Validation requires that the Windows account has read rights on the OLAP cube.
This part deals with some special issues that might have to be considered.
When performing RMB options that means interaction with SQL Server there might be some problems loading a DLL that handles necessary MDX operations. If a message box pops up saying that the DLL Ifs.Application.MdxCreator.dll cannot be loaded, one tested solution can be to clear the ClickOnce cache.
On Windows 7 this can be done in the following way:
rundll32 %windir%\system32\dfshim.dll CleanOnlineAppCache
and press enter
If when performing RMB options that means interaction with SQL Server a message box pops up saying Access denied the most probable reasons are the following:
Make sure to check the privileges for the current user. Some information is found under OLAP Cube Set-up
The following things must be handled.
How to do this is described in different articles on the net. One example is provided via the following link.
By default the BI Analysis Package installer will install four SQL Server Agent Jobs related to the Financials KPI functionality. The jobs are, assuming a given prefix as KPISRV_Bi_Prod:
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 only 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:
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