choose language:    gb   ru   kz   ua
 

Integration with accounting systems

To date, flexible integration connectors to all sorts of information systems are integral parts of the robust BI-solution. This is due to numerous applications are employed by the company for various business functions (accounting software, ERP and CRM-systems, WMS, etc.). All this bound to lead to large amounts of heterogeneous data generation and defines primary goal for any BI-application as consolidation of heterogeneous data arrays for subsequent analysis. However, it is important to understand that pieces of data collected this way can often be found in contradiction one with another, due to different sources of information, and can have different analytical characteristics, which fact can lead to biased and flawed analysis and to erroneous management decisions in turn.

Implemented in KPI MONITOR technology allows to import data directly from MS SQL Server, which is used to store enterprise data, and from transport files in Microsoft Excel format. In addition, the system supports automatic synchronization of analytical dictionaries to the corporate information system and provides a unified analytics system to the company, forming a common informational landscape.

KPI MONITOR integration is performed by importing the data into a KPI MONITOR database in the form of data marts that contains indicator values in different analytical dimensions. System provides a facility to store templates, which maps respective fields of basic indicators of KPI MONITOR to field names in foreign application database or import file. Spreadsheet format used for imports is very simple:

import_file


Importing data into KPI MONITOR from MS Excel (Export data from 1C program)

For better understanding of the data import routine in KPI MONITOR, you can can download the template for 1C, for accounting forms import (balance sheet and income statement (Form 2)) to obtain values for financial analysis.

1

Launch 1C Enterprise / 1C Accounting. Open the template through File / Open menu.

2

Set the flag "export" to reports. By default, all reporting forms (of the income statement), which are available in the database are checked.

3

Push the button "Execute". Selected statements are exported to MS Excel, created file is opened on the desktop.

1C template exports data of the income statement in the following format, where:

Field “Date”- the date of report execution

Field “Item Code” - code of the balance item of income statement line. One can also export names of the items. In this case, on the first import, the analytical dictionary “Balance Items”, created in KPI MONITOR, is automatically filled with imported values.

Field "Form" - the name of the reporting form. On the first import, analytical dictionary "Reporting Forms" created in KPI MONITOR is automatically filled with imported values.

field "Us" - name of holding subsidiary that owns the balance sheet or income statement.

4

Before importing data into KPI MONITOR, it is necessary to create analytical dictionaries for basic indicators, which will store data used in estimated/target indicators calculation.

  1. Analytical dictionary, "Balance Items", which contains field "code" – code of the item, and in the field "Name", the item title.
  2. Analytical Dictionary "Reporting Forms", which contains field "code" – code of the reporting form, and field "Name" - the name of the reporting form. In our case, code and name will have same values.
  3. Analytical Dictionary "Us", which contains field "Code"- individual tax number of the enterprise, and field "Name"- the name of the company.
5

Create basic indicator. Establish three analytical dimensions for its values ("Balance Item", "Reporting Form", "Us"). Set "Period", for example - quarterly, for quarterly reporting

6

Set up field matching between spreadsheet fields and indicator parameters, using KPI MONITOR data import service. Save settings in the import template for future use.

7

Launch the import routine, or schedule its execution using built-in KPI MONITOR Scheduler.

Importing data into KPI MONITOR from MS SQL Server (export data from the Turbo 9)

For better understanding of the data import routine in KPI MONITOR one can download the Turbo 9 procedure for accounting forms import - balance sheet and income statement (Form 2) for the financial indicators calculation.

Unlike the previous example, this demonstrates the ability to import data directly from MS SQL SERVER database of the accounting system.

1

Description of a new table in Turbo 9 – Accounting database for the data export

Create table of the arbitrary table structure. For example, we use will use values of financial statements (balance sheet and income statement) to create basic indicator, on the date of the report. Here is query to create table in MS SQL SERVER.

Описание таблицы в MS SQL

Description of fields in the Turbo 9 is provided on the following figure, where:

field “Date”- the date of report execution

field “Item Code” - code of balance sheet item or income statement line. One can also export names of the items. In this case, on the first import, analytical dictionary “Balance Items”, created in KPI MONITOR, is automatically filled with imported values.

Field "Form" - the name of the reporting form. On first import, imported analytical dictionary "Reporting Forms" created in KPI MONITOR, is automatically filled with imported values

field "Us" - name of holding subsidiary that owns the balance sheet or income statement.

2

Before importing data into KPI MONITOR it is necessary to create analytical dictionaries for the basic indicators, which will store data used in evaluated/target indicators calculation..

  1. Аналитический справочник «Статья», содержащий в поле «Код»-код статьи, а в поле «Наименование» -название статьи.
  2. Analytical dictionary, "Balance Items", which contains the field "code" – code of the item, and in the field "Name", the item title.
  3. Analytical Dictionary "Us", which contains the field "Code"-individual tax number of the enterprise, and the field "Name"- name of the company.
3

Create basic indicator. Establish three analytical dimensions for its values ("Balance Item", "Reporting Form", "Us"). Set "Period", for example - quarterly, for quarterly reporting

4

Set up database connection and field matching between table fields and indicator's parameters, using KPI MONITOR data import service. Save settings in the import template, for future use.

One can also use custom SQL query for data retrieval.

5

Launch the import routine, or schedule its execution using built in KPI MONITOR Scheduler.

Based on basic indicators, which contains data from balance sheet/income statement one can set up any financial indicators using the built in library of mathematical functions. Ability to customize indicator calculation logic, using different formulas and filters by any analytical parameters allows to create flexible hierarchy of indicators for companies with any organizational structure, with minimal effort, and program interface allows responsible users not only monitor current values, but also analyze calculation logic, uncovering casual relationships.

Similarly, it is possible to develop 1C template or export procedure for another applications, which can output data in MS EXCEL format, where values will be matched to analytical parameters and lately used for indicators/KPI creation, monitoring and analysis.

For example, following are valid spreadsheet templates:

  1. Revenue, branch, retail outlet, responsible, product groups, brand, etc.
  2. Accrued salary, given on hand salary, personal income from salary, Accrued bonuses, given on hands bonus, personal income from bonus, insurance premiums, employee, division, etc.
  3. Planned budget, factual budget execution, amount of adjustment of the budget, branch, division, CFA, Budget, Executive, Goods / Service