Part 1. Introduction How 1C Platform Stores Data: Accumulation Registers
Our website https://1c-dn.com now has a significant amount of material that describes how the 1C platform and its mechanisms operate, and some of the articles feature sample program code of wide-ranging complexity. However, all of those materials have two drawbacks: platform mechanisms are described in isolation from their program implementations, and existing samples of program code provide no explanation as to how the mechanism works.
Today we're launching a series of articles that will address this deficiency. Every article will include some of the theory involved in the function of a mechanism within the 1C platform and some practice tasks to help you learn how to write a piece of code.
Let's start with the basics, i.e., how the 1C platform stores data. But first, I'd like to remind you that 1C is not a universal programming language like Java but rather a specialized tool used to rapidly design business applications. Sometimes it's stated that the 1C platform is a specialized framework for the design of business applications. That's why the platform already has mechanisms that save the developer from thinking about the number of SQL tables to create (one or multiple tables). The platform makes this decision instead. In this case, the 1C developer works at a higher level, that is, the level of business logic where they don't work with database tables (SQL tables). Instead, they work with such objects (or entities) as Document, Catalog, etc. So, I'll also use these objects in our publications but will occasionally "drop" to a lower level, meaning storage of data on a SQL server.
Let's assume we need to automate operations in a commercial organization that buys products from suppliers, stores them in a warehouse, and then sells them at a markup. Naturally, the organization's managers need to have the latest information about the current situation: warehouse inventory, sales value for a given period, accounts payable, and, most importantly, profit. We'll break the task down into parts and start with warehouse inventory accounting.
Let's now digress a little to imagine how a commercial firm with manual accounting operates. It must be something like this. A clerk receives a Supplier Invoice as proof of goods delivery to a warehouse and files it into a designated folder. When a manager asks how much inventory they have at the warehouse, the clerk takes the invoices, calculates the number of products specified in them, and hands the report over to the manager. So, we can simplistically present the flow of data in the following way.
First, the clerk receives a document. Then, at the manager's request, the clerk uses the documents to generate a report. In other words, in this arrangement, all data is stored in the documents, and when we need to analyze it (to determine the stock on hand, for example), we have to call up and total all of the documents.
Let's see how this approach can be implemented in SQL. In this case, Invoice corresponds to INVOICES SQL table.
It seems easy enough. We can always sum up the indicators we want (stock on hand in this case) using SQL server tools and generate the required report.
But let's now assume that our organization receives hundreds of supplier invoices every day, and we've been operating for years. As a result, SQL table INVOICES will always grow in size, and reports will take more and more time to generate. It'll happen because the SQL server will always have to sum up data for the entire period of the company operations to calculate stock on hand. You must admit that it's pretty impractical. It seems like a dead-end, but 1C platform can offer a solution of its own to optimize data handling. In the flow above, we will need to add another object between Documents and Reports, which is Registers.
The idea is that the clerk will continue to work with supplier documents filing them in a folder. However, from time to time — for example, when they have some free time — they'll take finalized documents from the folder, calculate what products and what quantities are currently at the warehouse, and note down the numbers in a particular book. When the manager asks the clerk to produce stock on hand numbers, the clerk doesn't have to anxiously calculate all documents but simply opens the book and takes the latest number. Thus, first, the report generation rate goes up, and second, it does not depend on the number of finalized documents.
In other words, we can see that the clerk has an intermediate book to store the data, which is then used to generate new reports (in place of data from the documents).
The principle of storing data in an intermediate space is the foundation 1C platform. We'll only have to replace the book the clerk uses to note down stock on hand data with an object called the Accumulation Register.
Note: 1C platform has some other objects called Registers: Accounting Registers, Calculation Registers, and Information Registers. They're important objects too, and we'll discuss working with them later. However, Accumulation Registers are the most significant ones as they ensure data handling by platform mechanisms.
Thus, the 1C platform has a mechanism allowing to store data in isolation from documents. You might have some questions. What data can be stored in a register? How does data end up in a register? How many registers could there be in a system? These are my answers.
Let's assume we have a Supplier Invoice 1C Document with multiple attributes: document date and number, delivery date, warehouse that entered the product in the books, supplier and corresponding contract, shipping company that transported the products to our warehouse. What attributes might we need to see what products and what quantities we have? It's a simple question as we only need to know product name and quantity. We'll actually arrive at the following data storage structure:
I want to clarify once again. To know what quantity of what product we currently have at the warehouse, we don't need to know anything else. This data is enough. So we have now actually designed the structure for our future register. Indeed the actual register will be a little more complex than the one below, but this enough to demonstrate how data storage will be set up. Generally, the answer to the question about what data can be stored in a register could be as follows: theoretically, you can store any data in any classifications, but there's a rule — the fewer dimensions (attributes, fields) a register has, the higher read and write capacity it will have. In other words, if we only need two fields (Product and Quantity) to store stock on hand, we must use only these two fields and avoid adding the Supplier field to this register.
The next question is how data ends up in a register? Since registers are independent objects within 1C platform, data is not added automatically. The developer must design program code that will write required data into the appropriate register. To write data, the platform will use a process (or mechanism) called Posting.
Once again, Posting is a mechanism that writes data into a register (or registers) after a document is recorded.
In a real case scenario, the clerk first creates a Supplier Invoice document in the program, then fills it up with appropriate data and clicks Post. At this point, the document is first recorded (meaning that relevant lines are added to DBMS tables), then the pre-defined posting procedure is triggered. Here's a part of the algorithm code:
In this procedure, the developer must design program code that will write data into the appropriate registers. In fact, posting a document comprises two parts: recording a document and writing data into a register.
A discriminating reader might notice that we actually duplicate data as we write it into both the document and the register and consider it to be a bad thing. In fact, it's true. Data duplication might theoretically result in a situation where document data and register data differ. For example, when posting a supplier invoice, we need to increase product quantity at the warehouse, but the developer makes a mistake and decreases this quantity instead during the posting procedure. This is why the developer has a great responsibility to design correct and well-behaved code. On the other hand, using registers to store data and generate reports allows us to massively raise system performance as registers store normalized rather than redundant data. Do you remember that we use just two attributes (fields) to store stock: Product and Quantity?
The next question of how many registers a system might contain is a logical extension of the previous question. Let's assume we have already implemented storage of stock in the register with the structure described above. This is when the manager sets the task of accounting for accounts payable. What's the easiest way to complete this task? Can we simply add new fields to the register? Keep in mind that we have designed this register specifically to store stock, not to account for payments to suppliers. What would be the way out here? Is there any?
Indeed, there is! We'll create another register with a different (completely different, I stress) set of attributes (fields) that will store data regarding payments to suppliers. Based on data from this register, the manager can obtain the latest data on accounts payable. As noted above, data can't just be added to the new register automatically. This task is to be completed by the developer. They must modify the posting procedure to write data to the new register when recording a document.
As for the question on the number of registers a system might have, the answer is simple. It can have as many as required by the task. The main rule is that a register structure (in fact, data stored in the register is an SQL table where the server stores data) must be normalized for optimal writing and reading. This is the key factor of high capacity.
Let's now go back to the register that stores stock. We have designed its structure, but, as noted above, a structure of an actual register would be a little more complex. An actual register would contain additional service fields (attributes). Here's how a document-movement table in a register would look like:
Let's have a closer look at these service attributes (fields). It is obvious that Period field (Date type) stores the date when information is written to the register.
The Recorder field contains a link to a document that was a source of data added to the register. This field is essential as we can always use the link to navigate from the register to the document.
Another important field is Record Type. To understand why we need to remember that we can either receive products from suppliers or sell products to our customers. In the former case, the 1C platform needs to increase product quantity in the register, while in the latter case, quantity needs to be reduced. As you can see from the context, the Record Type field stores type of movement for the current record. For supplier documents this will be recorded as Receipt, and for sales documents this will be Expense.
The last service field is Line Number, which is simply a line number in the source document. To put it simple, a portion of the Supplier Invoice is a table with data that is used to fill in the register. Line Number is a line number in such a table.
Now let us take a break and revert to data storage and retrieval tasks. Generally, there are two ways of accumulating data. Option 1 is to answer the question of how much has been accumulated up to a specific date, and the option 2 is how much has been accumulated over a given period.
Thus, the first approach will work for storing and retrieving stock, such as stock on hand. At that, stock can be entered in books (positive movement or receipt) or written off (sales, negative movement or expense). This is precisely the storage method we used when designing this register.
But there can be a task of another type. Imagine we need to know the quantity of products sold in the last month. In this case, we're not interested in the stock at a specific date. Instead, we need to know the quantity of products sold over a given period, i.e., turnover. To store this type of information, 1C uses a register of a different type.
Technically, the Accumulation Register object on the 1C platform will be either of two types: Balances (storing balances) or Turnovers (storing turnovers). The developer will select the type of register to be used when creating a register:
Based on this information, the register we're now designing (as you know we need to store product balances) must be of Balances type. This type of register has the Record Type field with one of two values: Receipt and Expense.
Turnover type register is limited to turnovers and has no Record Type field. Below, we will see how to work with this type of register; now, let's go back to the Balances.
We have seen that capacity boost on the 1C platform is ensured by storing data in registers, and the data storage structure of a standard register looks as follows:
In fact, it's a SQL flat table. "Now wait!" you might say. "Where's the capacity boost in that? Does it make any difference if you read data from SQL table in a document or a SQL table in a register when generating a report? Read and write capacity for both cases will be almost the same!"
Here we gradually move to the second key rule of data storage in 1C (the first one being data storage in registers). Thus, to ensure fast reading of data from registers (and here I specifically mean reading, not writing), the platform will automatically create an additional DBMS table to store intermediate balances for each created register (which is, in fact, a standard DBMS flat table).
What is this, and why do we need it? See the diagram below:
On top is the document-movement table, which is the main table in the register. This is the table where document posting data is written. The second table is the table of balances automatically created by 1C platform as soon as the developer creates a new register. The developer can't write to the table because only 1C can. What kind of data is written?
It's very important to know that this table stores total balances for each register dimension (field, attribute) at the end of each month. See the diagram again: the system calculated end-of-month balances for Item 1 and Item2 products. I stress once again that 1C platform automatically calculates balances for the register. Neither the programmer nor the user is involved in this process. It's vital to know and understand this.
OK, we've calculated total balances for each product, but how will we benefit in terms of capacity? Let's assume we need to know Item 1 balances for February 6, 2020. If we have no table of balances, we'll have to add up all receipts of this product over the entire period. As shown above, this approach would negatively impact system capacity. However, if we use the table of balances, the data-retrieval process will be dramatically streamlined. As we now have calculated monthly data, we can take the Item 1 total at the end of January 2020 (i.e., 29) and add the movements recorded between February 1 and 6 (i.e., 3 and 10). The result is 42:
It's a straightforward example, but it's also a good illustration of the data storage principle and, most importantly, of data reading from accumulation registers. With large amounts of data, the storage of intermediate totals means a considerable benefit in terms of capacity.
Remember that all intermediate totals are automatically calculated by the system, with no involvement of the developer. Data reading happens the same way. We simply request data, such as Item 1 balances for February 6. The 1C platform auto-retrieves intermediate totals from the totals table and adds up the movements from the movements table, doing so in a process that's unseen by the developer. It is this principle that makes 1C platform so powerful a capable of handling vast amounts of data.
As noted above, we have the second type of accumulation, which is the Turnovers register. We'll discuss it later. For now, just bear in mind that this register has no totals table (which is surprising, right?). Instead, the 1C platform creates a turnover table for the register. As with the totals table, the turnovers table stores monthly turnovers. When we need to retrieve the turnover for a given period, the platform first retrieves calculated data from the turnovers table and adds up data for the uncalculated period. The algorithm is the same as with the Balances register.
Well, we've had enough theory for starters. It is time to move to practical tasks. In the next article of the series, we'll create a Supplier Invoice document, Catalogs to store additional data, an Accumulation Register, and we'll write program code that will create the required movements in the register. In other words, we'll do the things we've discussed today.