New Modus ETL tools for working with 1C
The platform has recently received another upgrade, which is based on advanced functions for working with 1C. Let's talk about what tools we have added and how they will affect business processes within specific companies. Let's go!
Methods of obtaining data
1C databases are usually filled with large volumes of information that can and should be used in analytics. Accounting reports, warehouse summaries, payrolls or timesheets become sources for dashboards.
There are different ways to “extract” data from 1C. The Modus ETL platform can do this in three ways:
Query in 1C language. The analyst writes a query in ETL, then checks how it is executed and creates a new rule for obtaining data. The system itself creates a table in the storage corresponding to the data structure.
Application SKD– source configuration schemes. An indispensable functionality when working with salary configurations and receiving data with complex queries/calculations on the ACS side. The analyst does not need to be able to read queries and know the data structure – he simply extracts information from the reports that employees use daily when working with 1C.
External processing. They execute the code embedded in them by 1C programmers. They are necessary to obtain information that cannot be retrieved by regular queries (registration log, data from spreadsheet documents, etc.).
To integrate 1C with Modus ETL, a special adapter has been developed – an HTTP service, installed as an extension or built into the configuration.
On average, you can connect a 1C database to Modus ETL in less than an hour. The duration of the integration depends on its complexity. If the issue is well-developed and the sources are known, it will take up to half an hour to create one rule for obtaining data.
Our portfolio includes many projects, which proves the high speed of system development. Enterprise users who had not previously worked with ETL platforms easily filled analytical warehouses with data from dozens of 1C information bases after short training courses (on average, standard training lasts up to 8 hours).
New Features in Modus ETL 1.6.9 Release
Using the 1C:Enterprise database as a source, using the 1C-ETL adapter as an HTTP service, setting up and saving data collection rules or multithreading are the basic options of the Modus ETL system.
In the latest update (Modus ETL version 1.6.9), the team focused on advanced features. Each of them significantly simplifies and speeds up the process of interaction between the two platforms.
Plus, we received the “1C:Compatible” certificate for the next two years.
Launching external processing in the source
The first important modification is the ability to launch external 1C processing.
It is very useful when you need to get data with complex extraction conditions that require several queries (practice shows that they exist in many projects). Like calculating accounts receivable by dates.
Or if you need to perform complex calculations on the source side, and you don’t want to “pull” the data for the calculation in ETL, or you need to get log data, attached files, or session data that cannot be obtained through a simple query.
Another example is calculations related to obtaining data from generated regulatory reporting and information about its signing, EDI data, and much more. In all these cases, it is impossible to do without calling the processing of arbitrary code.
External processing can be written by 1C programmers using a special software interface, disassembled in the documentation.
The processing execution service in Modus ETL is responsible for receiving external data on the source side. It calls special methods of the Adapter, which in turn launches external processing. This solution architecture allows:
execute arbitrary processing code “under the user” on whose behalf authorization for receiving data occurs;
configure the collection of the same data with different processing for varying configurations.
The big plus is that the architecture provides independence from the platform version.
Data layout systems (DLS) in reports
Another big step in the release of Modus ETL 1.6.9 was the “fine-tuning” of the SKD solution when receiving data from 1C. The 1C:Enterprise databases usually contain many custom reports on management, accounting and other types of accounting.
Using the Modus platform, you can get the same data that your colleagues working “in the system” see. In addition, we have added the option of running reports for the SKD on the 1C side and the ability to change the options for these reports.
Modus ETL can load the results of reports from the source system if the report works using the ACS. In this case, the analyst does not need to write code to load the data – he selects the data in the ETL from the available report data.
After the user has selected a report, he can configure the structure of data retrieval using the ACS settings. The last grouping will be read into the receiving table.
However, I recommend setting up a schema for loading data via the source system configurator and loading the configured schema via the loading functions. This way you will be able to get the link values via the attributes of the reference field.
Once configured, the upload rule automatically creates the required table in the storage and matches its fields to the data from the source.
For salary reports, there is the possibility of initialization by executing arbitrary code on the source side before loading the data.
The presented functionality has already found a response. The company's portfolio recently included a new case on the implementation of a salary and personnel cloud for one of the large Russian government agencies.
Using new tools for working with the ACS, we set up data collection for >20 reports from 60 databases in just 100 hours. The first 50 hours were spent by the ETL analyst responsible for migration processes. Another 50 hours were spent by the analyst ZUP to customize report options.
Other changes and upcoming plans
Despite the “1C-focus”, the release of the Modus ETL 1.6.9 system version also included related changes, for example:
increasing the stability of workflow scenarios;
improvements to the procedure for deleting scripts with all dependent objects;
reducing the number of calls to the licensing system (at the request of users who had problems of this nature);
speeding up work with the analytical subsystem of the NSI and reworking places that in exceptional cases led to the platform crashing on Linux.
The nearest plans (for 1.6.10 and 1.6.11) include the implementation of fast loading of large csv and xlsx files via Agent, adding support for Python scripts and releasing the Step Wizard for Greenplum.
To sum it up
The Modus ETL platform has become more convenient in terms of interaction with 1C. It covers at least three important processes:
Native data retrieval from 1C:Enterprise databases — in two minutes, without modifications or third-party software. You can output report results in tabular form and perform external processing.
Depersonalization of data from 1C — “on the fly”, on the source side. The key point in the issue of ensuring additional security of personal data.
Receiving freshly updated data when changes occur in 1C databases. ETL platform users see and process the same information as employees interacting with sources “in the system”.
It is difficult to overestimate the usefulness of such functionality. The corresponding options are needed if you use external BI systems when working with 1C and want to receive data from databases without the participation of 1C programmers.
In addition, the Modus ETL platform maintains the relevance of analytical data by tracking changes in 1C. It also solves problems related to the rapid delivery of big data from 1C to BI.
