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 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.
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.
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:
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.
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.
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:
A specified domain account that is 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 configured domain account is granted this role. Allowing read and/or processing rights limits the usability of the KPI functionality for Ihe end users, since it will not be possible to publish the KPIs to the OLAP cube, a rather big functional limitation.
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:
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:
This is the name of the target OLAP cube database on the SSAS server define in the Connection Settings page. 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 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:
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 configured SSAS domain 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 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 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.
This part deals with some special issues that might have to be considered.
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:
Make sure to check the privileges for that user. Some information is found under OLAP Cube Set-up
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:
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:
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