May 27, 2015 at 11:48Management reporting according to 1C: how it works
In this article we will tell you how to analyze 1C data using QuBeQu: where does this task come from, how does it work, and what are the alternatives.
Let you have one or more 1C, in which accounting is carried out primarily. In order to control the business and make decisions, you need managerial accounting and data analysis. Wherein:
But the costs of compiling data from several 1C systems are often greater than the benefits of the project, and the financial director or analyst cannot quickly and easily build new reports in 1C - Excel is used instead, which leads to its problems: manual data compilation, sending outdated reports by mail, etc.
We propose to solve this problem by overloading data into a QuBeQu data collection and analysis system. We configure the loading and synchronization of data from 1C using a connector that transfers data over an encrypted connection. By loading the primary data, they can then be analyzed in various sections without thinking about the necessary reports in advance. Reducing several databases (the same or different configurations) is almost as simple, and does not require the involvement of 1C developer.
It is convenient to keep all the missing data directly in QuBeQu, for example: additional costs, set correspondence between different articles, enter additional classification of goods, etc. - All this data can be easily entered or downloaded from Excel. As a result, each system fulfills its role: 1C performs the functions of an accounting system and is a “source of truth”, and in QuBeQu analysis is performed and accounting auxiliary to analysis. This eliminates the irreproducible and immediately outdated Excel reports and the resulting mess.
After all the necessary data has been downloaded, their analysis, construction of reports and graphs are not a problem - without any programming you can answer any question that arises, and the reports and visualizations will always be relevant.
We shot a short video that shows the main steps for configuring the connector and data analysis:
The result of the settings can also be viewed on the open demo stand at demo1c.qubequ.com - keep in mind that the data on it is visible to everyone who has logged on to it, and the database is periodically reset to its initial state.
The connector is an exe-file that must be running all the time on the same computer where the 1C server is running. In the connection settings, you need to set the path to the 1C database, user and password. After starting, the connector connects from 1C, and also establishes a secure connection to the server where QuBeQu is running, and listens for data download requests. All further settings are made in the QuBeQu interface: all directories, documents and registers from the 1C database are available for download, you can also make arbitrary requests to 1C. In any table available for download, you can select only those fields that are needed during operation, do additional filtering or program processing. In practice, in order to determine exactly what data needs to be downloaded, it is often useful to peek at the report settings in the 1C configurator.
It often happens that there are several branches or individual organizations with their own 1C databases, and you want to analyze the data as a whole. In this case, the connectors are placed on the server to each 1C and connected to the same server with QuBeQu. When setting up data loading, it is necessary to additionally mark the “Organization” field for downloading, which is already in all 1C configurations, and will help to distinguish data by various organizations. The second important issue is the actual data reduction: almost always in different 1C databases there are some directories for which you need to compare data from different databases, for example, the nomenclature of goods sold or employees. For such directories, the setting is slightly different, there are two options for downloading them. The simplest but slightly limited is to use “natural” keys, which match in all databases - for example, name or code. The disadvantage of this method is that such keys must be immutable (or change at the same time), and this is not always the case. If there are no such natural immutable keys, then it is necessary to set the correspondence between the keys in each of the databases.
After that, you can work with data in the report designer, easily and quickly filtering and grouping data, including by “chains”, i.e. according to the signs of the characteristics of the source data. In a few minutes, without any programming, you can get interactive dashboards that always display relevant data, or build a new analysis. Any formulas that you create do not have to “stretch” or update anywhere when new data appears. The data does not lie in free form, as in Excel, but in “indicators” that have the specified “analytical features”, from which the report is drawn. You can specify formulas for indicators in a language similar to Excel / DAX formulas, and then this indicator can be used in any reports and graphs without thinking about where it comes from.
Of course, QuBeQu is not the only way to solve these problems. In our opinion, our main advantages are fast implementation, high-quality and reproducible result, ad-hoc data analysis, the ability to enter additional information. But in some cases other solutions are better suited.
Excel is suitable if there is not much data, it does not need to be summarized and easy to unload, the data analysis is episodic and is done by one person who is ready for manual work. But you probably already know Excel, and you don't have to pay extra for it.
Refining 1C is suitable if 1C already has all the data for analysis, and you need “just another report”, and you know exactly which one.
1C ConsolidationA good solution for converting several 1C databases, and is suitable if you are ready for a long and expensive implementation, and the range of tasks is not limited to analytics, and the required reports are known in advance and do not change.
QlikView is an excellent solution if you are comfortable with the price, you do not need to enter auxiliary data, and you are ready for the fact that setting up new calculations will require programming.
Let you have one or more 1C, in which accounting is carried out primarily. In order to control the business and make decisions, you need managerial accounting and data analysis. Wherein:
- Not all data is in 1C itself: not all expenses are reflected, accounting items of expenses differ from managerial ones.
- I want to control the business from a browser or from a tablet and see what is happening on visual graphs, and not climb in 1C.
- New reports or changes to existing ones are constantly required.
- There are several 1C systems that need to be somehow reduced.
But the costs of compiling data from several 1C systems are often greater than the benefits of the project, and the financial director or analyst cannot quickly and easily build new reports in 1C - Excel is used instead, which leads to its problems: manual data compilation, sending outdated reports by mail, etc.
We propose to solve this problem by overloading data into a QuBeQu data collection and analysis system. We configure the loading and synchronization of data from 1C using a connector that transfers data over an encrypted connection. By loading the primary data, they can then be analyzed in various sections without thinking about the necessary reports in advance. Reducing several databases (the same or different configurations) is almost as simple, and does not require the involvement of 1C developer.
It is convenient to keep all the missing data directly in QuBeQu, for example: additional costs, set correspondence between different articles, enter additional classification of goods, etc. - All this data can be easily entered or downloaded from Excel. As a result, each system fulfills its role: 1C performs the functions of an accounting system and is a “source of truth”, and in QuBeQu analysis is performed and accounting auxiliary to analysis. This eliminates the irreproducible and immediately outdated Excel reports and the resulting mess.
After all the necessary data has been downloaded, their analysis, construction of reports and graphs are not a problem - without any programming you can answer any question that arises, and the reports and visualizations will always be relevant.
How it works?
We shot a short video that shows the main steps for configuring the connector and data analysis:
The result of the settings can also be viewed on the open demo stand at demo1c.qubequ.com - keep in mind that the data on it is visible to everyone who has logged on to it, and the database is periodically reset to its initial state.
The connector is an exe-file that must be running all the time on the same computer where the 1C server is running. In the connection settings, you need to set the path to the 1C database, user and password. After starting, the connector connects from 1C, and also establishes a secure connection to the server where QuBeQu is running, and listens for data download requests. All further settings are made in the QuBeQu interface: all directories, documents and registers from the 1C database are available for download, you can also make arbitrary requests to 1C. In any table available for download, you can select only those fields that are needed during operation, do additional filtering or program processing. In practice, in order to determine exactly what data needs to be downloaded, it is often useful to peek at the report settings in the 1C configurator.
It often happens that there are several branches or individual organizations with their own 1C databases, and you want to analyze the data as a whole. In this case, the connectors are placed on the server to each 1C and connected to the same server with QuBeQu. When setting up data loading, it is necessary to additionally mark the “Organization” field for downloading, which is already in all 1C configurations, and will help to distinguish data by various organizations. The second important issue is the actual data reduction: almost always in different 1C databases there are some directories for which you need to compare data from different databases, for example, the nomenclature of goods sold or employees. For such directories, the setting is slightly different, there are two options for downloading them. The simplest but slightly limited is to use “natural” keys, which match in all databases - for example, name or code. The disadvantage of this method is that such keys must be immutable (or change at the same time), and this is not always the case. If there are no such natural immutable keys, then it is necessary to set the correspondence between the keys in each of the databases.
After that, you can work with data in the report designer, easily and quickly filtering and grouping data, including by “chains”, i.e. according to the signs of the characteristics of the source data. In a few minutes, without any programming, you can get interactive dashboards that always display relevant data, or build a new analysis. Any formulas that you create do not have to “stretch” or update anywhere when new data appears. The data does not lie in free form, as in Excel, but in “indicators” that have the specified “analytical features”, from which the report is drawn. You can specify formulas for indicators in a language similar to Excel / DAX formulas, and then this indicator can be used in any reports and graphs without thinking about where it comes from.
Why not X?
Of course, QuBeQu is not the only way to solve these problems. In our opinion, our main advantages are fast implementation, high-quality and reproducible result, ad-hoc data analysis, the ability to enter additional information. But in some cases other solutions are better suited.
Excel is suitable if there is not much data, it does not need to be summarized and easy to unload, the data analysis is episodic and is done by one person who is ready for manual work. But you probably already know Excel, and you don't have to pay extra for it.
Refining 1C is suitable if 1C already has all the data for analysis, and you need “just another report”, and you know exactly which one.
1C ConsolidationA good solution for converting several 1C databases, and is suitable if you are ready for a long and expensive implementation, and the range of tasks is not limited to analytics, and the required reports are known in advance and do not change.
QlikView is an excellent solution if you are comfortable with the price, you do not need to enter auxiliary data, and you are ready for the fact that setting up new calculations will require programming.