Management reporting according to 1C: how it works
In this article we will explain how to analyze 1C data using QuBeQu: where the problem comes from, how it works and what alternatives are there.
Suppose you have one or more 1C, which is primarily accounting. In order to control the business and make decisions, you need management accounting and data analysis. Wherein:
But the costs of converting data from several 1C systems often turn out to be greater than the benefits of the project, and the CFO or analyst cannot quickly and easily build new reports in 1C - Excel is used instead, which leads to its problems: manual data reduction, transfer outdated mail reports, etc.
We propose to solve this problem by overloading data into the system for collecting and analyzing QuBeQu data. We configure the download and synchronization of data from 1C using a connector that transmits data over an encrypted connection. Due to the loading of primary data, they can then be analyzed in various sections in advance without thinking about the necessary reports. Mixing several databases (of 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 expenses, set the correspondence between different articles, introduce additional classification of goods, etc. - All this data can be easily entered or loaded from Excel. As a result, each system performs its role: 1C performs the functions of the accounting system and is the “source of truth”, while QuBeQu performs analysis and accounting, which is auxiliary for analysis. This eliminates non-reproducible and immediately outdated Excel reports and the resulting clutter.
')
After all the necessary data is loaded, their analysis, the construction of reports and graphs do not pose a problem - without any programming, you can answer any question that has arisen, and reports and visualizations will always be relevant.
We shot a small video that shows the main stages of setting up the connector and analyzing data:
The result of the setting can also be viewed on an open demonstration stand at demo1c.qubequ.com - keep in mind that the data on it is visible to all who entered it, and the base is periodically reset to its initial state.
The connector is an exe-file that should 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 database 1C, user and password. After launch, the connector connects with 1C, and also establishes a secure connection to the server where QuBeQu is running, and listens to requests for data download. All further configuration is done 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 loading, you can select only those fields that you will need during your work, make additional filtering or program processing. In practice, in order to determine exactly which data to load, it is often useful to look at the report settings in the 1C configurator. For downloading, it is usually better to choose the most detailed source data, since this will allow you to analyze them in more sections in the future, and thus more conclusions can be drawn from the data.
It often happens that there are several branches or separate organizations with their own 1C databases, and I want to analyze the data as a whole. In this case, the connectors are placed on the server for each 1C and are connected to the same server with QuBeQu. When setting up data loading, you need to additionally check the “Organization” field, which already exists in all 1C configurations, and help distinguish data from different organizations. The second important question is the actual data reduction: almost always in various 1C databases there are some reference books 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 easiest but a bit limited is to use “natural” keys that match in all bases — for example, a name or code. The disadvantage of this method is that such keys must be unchanged (or changed at the same time), and this is not always the case. If there are no such natural unchanged keys, then it is necessary to set the correspondence between the keys in each of the bases.
After that, you can work with the data in the report designer, easily and quickly filtering and grouping data, including by “chains”, i.e. according to the signs of the original data. In just a few minutes, without any programming, you can get interactive dashboards, always showing actual data, or build a new analysis. Any formulas that you create, do not need to “pull” or update when new data appears. The data is not in free form, as in Excel, but in “indicators” that have specified “analytical features”, from which the report is typed. You can set 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 results, 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 little data, it does not need to be reduced and easy to upload, data analysis is episodic and is done by one person who is ready for manual work. But then you probably already know Excel, and you don’t need to pay extra for it.
Revision 1C is suitable, if 1C already has all the data for analysis, and you need to “just another report”, and you know exactly which one.
1C-Consolidation is a good solution for converging 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 satisfied 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.
Suppose you have one or more 1C, which is primarily accounting. In order to control the business and make decisions, you need management 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 the browser or from the tablet and see what is happening on the visual graphs, and not climb in 1C.
- Constantly require new reports or changes to existing ones.
- There are several 1C systems that need to be somehow reduced.
But the costs of converting data from several 1C systems often turn out to be greater than the benefits of the project, and the CFO or analyst cannot quickly and easily build new reports in 1C - Excel is used instead, which leads to its problems: manual data reduction, transfer outdated mail reports, etc.
We propose to solve this problem by overloading data into the system for collecting and analyzing QuBeQu data. We configure the download and synchronization of data from 1C using a connector that transmits data over an encrypted connection. Due to the loading of primary data, they can then be analyzed in various sections in advance without thinking about the necessary reports. Mixing several databases (of 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 expenses, set the correspondence between different articles, introduce additional classification of goods, etc. - All this data can be easily entered or loaded from Excel. As a result, each system performs its role: 1C performs the functions of the accounting system and is the “source of truth”, while QuBeQu performs analysis and accounting, which is auxiliary for analysis. This eliminates non-reproducible and immediately outdated Excel reports and the resulting clutter.
')
After all the necessary data is loaded, their analysis, the construction of reports and graphs do not pose a problem - without any programming, you can answer any question that has arisen, and reports and visualizations will always be relevant.
How it works?
We shot a small video that shows the main stages of setting up the connector and analyzing data:
The result of the setting can also be viewed on an open demonstration stand at demo1c.qubequ.com - keep in mind that the data on it is visible to all who entered it, and the base is periodically reset to its initial state.
The connector is an exe-file that should 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 database 1C, user and password. After launch, the connector connects with 1C, and also establishes a secure connection to the server where QuBeQu is running, and listens to requests for data download. All further configuration is done 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 loading, you can select only those fields that you will need during your work, make additional filtering or program processing. In practice, in order to determine exactly which data to load, it is often useful to look at the report settings in the 1C configurator. For downloading, it is usually better to choose the most detailed source data, since this will allow you to analyze them in more sections in the future, and thus more conclusions can be drawn from the data.
It often happens that there are several branches or separate organizations with their own 1C databases, and I want to analyze the data as a whole. In this case, the connectors are placed on the server for each 1C and are connected to the same server with QuBeQu. When setting up data loading, you need to additionally check the “Organization” field, which already exists in all 1C configurations, and help distinguish data from different organizations. The second important question is the actual data reduction: almost always in various 1C databases there are some reference books 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 easiest but a bit limited is to use “natural” keys that match in all bases — for example, a name or code. The disadvantage of this method is that such keys must be unchanged (or changed at the same time), and this is not always the case. If there are no such natural unchanged keys, then it is necessary to set the correspondence between the keys in each of the bases.
After that, you can work with the data in the report designer, easily and quickly filtering and grouping data, including by “chains”, i.e. according to the signs of the original data. In just a few minutes, without any programming, you can get interactive dashboards, always showing actual data, or build a new analysis. Any formulas that you create, do not need to “pull” or update when new data appears. The data is not in free form, as in Excel, but in “indicators” that have specified “analytical features”, from which the report is typed. You can set 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 results, 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 little data, it does not need to be reduced and easy to upload, data analysis is episodic and is done by one person who is ready for manual work. But then you probably already know Excel, and you don’t need to pay extra for it.
Revision 1C is suitable, if 1C already has all the data for analysis, and you need to “just another report”, and you know exactly which one.
1C-Consolidation is a good solution for converging 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 satisfied 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.
Source: https://habr.com/ru/post/290970/
All Articles