7 ways to download data from 1C for business analytics

The 1C ecosystem is a set of application solutions for process automation based on the 1C: Enterprise platform. The system is used by more than 1.5 million Russian companies.

Information from 1C is valuable for business analytics, since platform configurations store data on different business areas:

• 1C: Accounting – accounting and tax data

• 1C: ERP Management – ​​data on production processes

• 1C: UT – on wholesale, wholesale-retail or distance trading

• Dalion and Astor – accounting data in retail chains

• 1C: WMS – information about logistics and warehouse processes

1C offers its own product for business analytics – the BI system 1C: Analytics. However, the built-in configuration has significant limitations in functionality, flexibility of settings and variety of visualizations.

In addition, only data from 1C will be available for analysis, and if reports require information from other sources, it cannot be loaded into 1C: Analytics.

Difficulties arise when loading data from 1C to an external BI platform. 1C is a closed proprietary system, and it is impossible to connect to it directly and simply download data without violating the licensing policy.

Let's look at the 7 most popular methods of extracting data from 1C for business analytics, their pros and cons.

1. Connect directly to the SQL database in 1C

Information in 1C is stored in linked tables of MS SQL or PostgreSQL DBMS. If you understand the structure of the tables, then in the future you can receive metadata from 1C by connecting to the working database without using third-party software.

This method has its disadvantages:

Connection may lead to changes in the normal operation of the platform, destruction of the database and refusal of 1C technical support.

• Physical tables and fields in the working database are encoded, and for further work it is necessary to “translate” their names into a readable format

For this purpose, there are free developments and paid view generators, or you will have to look for a 1C programmer with such skills.

Each time you make changes, you will have to repeat the query to the database and download everything in full.

2. Build your own analytical SQL database

The data is uploaded to SQL tables in the intermediate database using pre-created queries to the 1C database.

The method is suitable for processing big data, in addition, the analytical base allows combining data from different sources and performing calculations of any complexity on them.

Disadvantages of this method:

• We need 1C specialists and analysts who can write unique queries and prepare SQL tables for each object – document, register, reference book

• The most technically complex and financially expensive method; budgets for large companies with large volumes of data can be in the millions

Whether to use the first two methods, which violate the 1C licensing policy, or not is everyone’s own choice, but we urge you to approach the issue taking into account all the listed risks.

3. Manually upload XLS/CSV/XML files

The method will help you quickly and easily get data from 1C, this will help “Report Distribution” function. For unloading, a Universal Report is generated, where all the necessary fields and period are configured.

In the Report Distribution tool, you specify the required report, format (Excel, CSV or XML), distribution schedule and download location: local folder or FTP server.

Disadvantages of this method:

• Manual labor that takes up employee time and increases the likelihood of errors related to formatting and data loss

• If the required report is not available in 1C, you will have to involve a 1C programmer to generate it in the system before uploading.

• When updating information in 1C, you will have to unload an Excel file each time, which is overwritten over the old one. Archive versions are not saved, it becomes impossible to ensure data versioning

• Incremental loading is not possible with this method; a large volume of data is unloaded at once, and it is necessary to separately organize the unloading schedule for different periods.

• This method may be suitable as a temporary solution for small amounts of data. As the number and variety of reports grows, it is likely to lead to errors.

4. Load data using OData protocol

OData (Open Data Protocol) — is a protocol that allows you to perform operations on data using HTTP commands of the form

http://<имя сервера>:<порт>/<имя публикации>/odata/standard.odata/<имя ресурса><формат полученных данных

and receive files in XML or JSON formats with all the details and values ​​of the directory.

The method is suitable for operational tasks, unloading small amounts of data. With sufficient knowledge of 1C, you can use special commands (convert links to values) or set filters in queries (for example, when you need to unload data for a certain period).

Disadvantages of this method:

When publishing data on the Internet, there is always a risk of leakage, and this method is not suitable for companies with strict security policies.

The service will not allow processing requests with complex logic or a large number of filters

And the larger the volume of data, the slower the tool works. With a large volume of data and a large number of reports, the method will not be effective.

5. Use COM connection

You can connect to 1C databases using COM connections (COMConnector) – a software service originally designed to transfer data from one 1C configuration to another.

Disadvantages of this method:

• To connect to the database, you need to install a 1C client of the same bit depth as the bit depth of the source application/server

• To write the code, we need specialists with programming skills in the 1C language and an understanding of the syntax of the language used in BI

• The connection and unloading time depends on the size of the 1C database, since at the moment of connection the database is loaded completely

6. Implement 1C:Bus

1C offers its own software solution of the Enterprise Service Bus (ESB) class for exchanging messages between various information systems – 1C:Bus.

The bus contains interfaces for interaction with different information systems via various protocols: SOAP, AMQP, JDBC, FTP, HTTP, JMS, RabbitMQ.

The bus can reduce the cost of managing and supporting a large number of integrations in a company, since they are all collected in one place.

Disadvantages of using 1C:Bus:

• The costs of purchasing, implementing and supporting the Bus are quite high and are justified only if the number of integrations in the company is more than ten.

• The bus does not have a magic button “Integrate everything!” All the mechanics of exchange and the nuances of data flow from one system to another are still implemented and supported by developers, which requires additional time and budgets

7. Use a connector or extractor for 1C

There are also third-party ready-made solutions on the market for automatic data upload from 1C to the ClickHouse, PostgreSQL or Microsoft SQL database, which speed up integration and reduce database support costs.

1. ATK BIView – connector for generating a data model from 1C and further use in BI systems and data warehouses.

• Provides automatic generation of SQL views with transformations required for BI

• Works with non-file versions of 1C from 8.1 and higher and with any BI system with the ability to connect to an SQL server

• Saves time on searching for matches between table and field names in 1C and SQL and renaming them

• Almost no support required, launch is only needed when making changes to 1C

The connector violates the current 1C licensing policy, as it interacts with the root working base of the system.

2. Extractor 1C in BI from Denvik – a tool that automates the receipt of all data from 1C and its delivery to ClickHouse or MS SQL database tables without using Excel tables or external services.

• Works with both client-server and file databases 1C versions 8.2 and 8.3 and any BI systems

• It is an extension to the familiar 1C interface and does not require programming skills.

• Allows you to work not only with 1C queries, but also with additional calculations on a data set

• Provides the ability to incrementally load – tracks and automatically uploads all changes in the 1C database to an external storage. You can set up scheduled uploads

• Provides fast data unloading – 4346 records per second (according to the vendor) due to the multi-threading mode

• Included in the register of Russian software

So, we have looked at the main methods of extracting data from 1C for using it in BI.

Exporting in Excel format or connecting via the OData protocol are good and easy-to-implement methods, but they will not solve the problem when using different 1C configurations, a large number of 1C databases, a large volume of data, and a variety of reports.

For large companies, the best option may be to use 1C:Bus or an extractor that automates continuous data unloading from 1C.