What is the article about?

This is a generalized article about what "budget automation" is, what problems this area consists of and what IT tools are used in it.



If you want to understand how BI, data warehouses (DWH), budgeting automation systems (Cognos, Anaplan, 1C: Holding Management, Bit.Finance) are interconnected and how they differ from other corporate information systems, go here.



If you are a technical architect who has never worked with the subject area of ​​business planning, this article is also for you.





I warn you right away that I tried to write the article in the simplest possible language so that it is understandable for everyone.

Why did I decide to write it?

Nowadays, there is practically no short systematic description of this area, which would give clear answers to the questions:

1. ? ?
2. (PowerBI, Tableau, Qlik, Cognos, IBM Planning analytics, Anaplan, ., 1: ) ?
3. BI – ?

The architectural difference between accounting and planning is that accounting data flows from the bottom up. To get quality reporting, you need to organize the recording of as detailed facts as possible. Then any summary information (important for managers) can be obtained by simple aggregation.



Therefore, in accounting, the Document -> Table (Register) -> Report scheme (which was used long before automation, even in medieval accounting) works optimally.





Figure: 1. Accounting scheme "Document -> Register -> Report"





The plans were initially enlarged. Therefore, it is convenient to enter them exactly "from above" (that is, in the same forms in which reports are generated).



Therefore, when trying to build a budgeting automation by analogy with conventional accounting (Fig. 3), companies immediately face three key problems.





Figure: 3



Problem 1: Administering rules . It is very inconvenient and laborious to administer the rules for data transformation (from the lowest levels of accounting to the budgeting format), written in the report code.



Problem 2: Speed ​​of "gathering fact" . Plans are displayed in reports very quickly (since they are already stored in an enlarged form), and actual data are calculated very slowly.



Problem 3: Plan entry forms... The most convenient form for entering plans is a plan-fact report. But reports in information systems usually do not allow data entry.



The first two issues are not only related to budgeting, and in general represent the basis of the entire subject area of ​​data warehousing, data integration and ETL.



The third problem is a specific planning problem. Which, in fact, is one of the important problems of ERP-systems as real-time tools (designed not only for the "posthumous" accounting of events that have occurred, but for planning and operational control of the business).

Issue 1: administering transformation rules

In fig. 1-3, all the rules for transforming actual data (from the lowest accounting level to the upper reporting levels) are written right in the report code.



This is bad:

• Rules cannot be administered without changing the code;
• They can only be used in this report.
  which means:

  – , , ,
  
  – - ,
  
  

Complexity of transformation rules

It is very important to consider here that the transformation rules can indeed be quite complex. Transformation is not always a simple aggregation of data (from day to month; from department to organization; from warehouse to region; from product line to article, etc.). This is especially evident in the CIS, where management accounting is often based on accounting. Then, from a variety of combinations of different accounting details , different values ​​for management accounting can be determined.





You can imagine how significant a problem maintaining such code is for programmers, if there are several hundred such articles, they are used in a dozen different reports, and the rules for determining them in management accounting can be adjusted every 3-4 months.

Solution to problem 1: mapping

To solve this problem, mapping - correspondence between fields of different levels and / or types of accounting and reporting - can be removed from reports, created as a separate object, configured and stored separately, and then refer to them if necessary.





Figure: 4



This has two advantages at once:

• Rules are easier to administer. They can be made interactive and configured without code, which means that even ordinary users can often do it;
• One rule can be used in different reports and / or other algorithms

This approach has no significant disadvantages.



But developing a tool for convenient mapping of large volumes of directories is not easy.

Problem 2: Speed ​​of transformation of actual data

Solution to Problem 2: Storing Transformed Data

In order not to calculate the report data "on the fly", they can be stored already in an enlarged and transformed form.



To do this, in addition to the source tables (which will still be needed in the company), you need to create tables to store the aggregated actual data. These can be separate tables, and a general table for both the "plan" and the aggregated "fact".



Of course, these tables must first be filled in somehow: for this, we will perform the same transformation procedure that was performed earlier when generating the report, but now we will move it to a separate background process.





Figure: five

DWH

Related to this issue is the Data Warehouse (DWH) domain.



Roughly speaking, DWH is a place (a table or, in practice, a set of related tables) for storing intermediate calculated (aggregated or somehow transformed) data.



What are the pros:

• Data reading speed. If reports "read" already transformed data from the table, they do it very quickly.
• Verifiability. When data is pre-stored in the warehouse, it is easier to verify it.

Minuses:

• Accuracy. In fact, this minus is rather theoretical (maximum accuracy is ensured precisely when we take data from the very primary source of information).
  On practice

  , ; , . , , , .
• Loading memory. Accordingly, in order to store aggregated data, we waste extra space on hard drives. Also, in fact, rather a theoretical disadvantage.
• Decryption. If we connect reports to aggregated tables (in which the data is not detailed according to the source documents), problems arise with the possibilities of their decryption (drill-down).

ETL processes

ETL can be called any process in which data is taken from somewhere, modified and then loaded somewhere. This is a common abbreviation for Extract, Transform, Load.



But usually this term is used precisely in cases when data after transformation is written somewhere for storage.



This approach has advantages:

• Distribution of the load on the system. The transformation process stretches over time. We can fill the aggregated table gradually (as we change / add data in the original accounting systems) or on a schedule. For example, complex calculations can be postponed overnight or other non-business hours when the server is "free". This allows you to manage the load on the system.
• One-time transformation. Once we write the summary information into an aggregated table, we can access it from many different reports. Therefore, the same transformations do not have to be performed many times.

There is only one minus:

• The complexity of controlling the integrity of the loaded data. Build an ETL process that does not lose data, that is sufficiently transparent and controllable - it is possible, but it requires a highly qualified team, user involvement and noticeable labor costs.

Problem 3: Budget Entry Form

The fact is that in the classical form, reports in software products are a means of data output. But you cannot enter data into them.





But for budgeting the classic scheme "Input form" (document) -> internal tables -> Output form (report) "is not suitable.



For example, at one time we developed a monthly procurement report (as in Fig. 2), and now we start planning, and the CFO wants to enter the procurement budget in the same form.



What is left to do? You can create a plan entry form that looks very, very similar to this report (as shown in Figure 3).

Solution to Problem 3: Interactive I / O Forms

The solution is also obvious: instead of the usual "documents" and reports ", you should create an object that will simultaneously and to read and enter data.



It is even better if in this object it will also be possible to perform calculations between the entered and / or read data - that is, to work like the way Excel allows you to work.



In this case, plans, after entering, can be “added” to the same data warehouse where the actual data are located (see figure).





Figure: 6



But such forms are much more difficult to implement than ordinary reports or documents in accounting systems.



The degree of interactivity can be different: it is easier to implement pre-configured forms, more difficult - dynamic (where the number of columns / rows is not known in advance, but their types are predefined). It is even more difficult to allow the user to “rotate” data, build new forms and set arbitrary calculation formulas, changing the structure of reports.

Solution to problem 4: cubes

There is another tool that solves a problem not indicated above.

The fact is that with large amounts of data, with high interactivity and with complex formulas, ordinary relational SQL tables, in which it is customary to store data from ERP systems, do not provide the maximum speed of data processing in real time.



To solve this problem, you can use data storage not in the form of tables, but immediately in the form of cubes.





True, if for budgeting tasks immediately organizing storage in the form of a cube is a good and suitable option, then for other business tasks a multidimensional storage model may not be suitable, and the storage is organized using a different technology. In this case, the cube can be added to the "main" storage as another link in the architecture.

TYPES OF SOFTWARE PRODUCTS IN BUDGETING

Now let's consider the types of information technologies that solve problems that are important in budgeting automation:

• Initial data systems (accounting systems, ERP systems)
• ETL tools
• Data warehouses (regular and OLAP cubes)
• BI systems
• EPM systems
• Excel of course

Each type of system has a theoretically basic function (see table):







But in reality, the boundaries blur a little, and often products "know how" to do related things. Overlapping functions very roughly looks like this:







Important: It should be noted that the overlap of functions is usually not 100%.



That is, usually a tool that offers additional functions does not perform them as well as a separate specialized tool!

Map of software types in budgeting

In general, visually the coverage of different tasks of budgeting automation by different types of information systems can be shown approximately like this:





Fig. 7



Now let's look at what kind of budgeting architecture some popular software products offer.

BUDGETING IN ERP SYSTEMS

The concept of ERP has changed over time, and new capabilities are being incorporated into ERP systems.



In my opinion, the "classic" ERP functionality includes an accounting system; report designers; functions of operational control of plans and, of course, the basic capabilities of their input.



Excludes: ability to collect data from multiple sources; building cubes and interactive analytics



There is reason to believe that EPM (like BI) was conceived as something that went beyond ERP. But now borders are blurring, and EPM functions or even entire products can be included as modules in ERP systems.

1C: SCP

The UPP implements the following scheme, but within one base.





Figure: 8. Architecture of budgeting in 1C: UPP



Advantages of budgeting in UPP:

• The SCP is very transparent and easy to modify. It is easy to verify data in it and it is quite inexpensive to develop new functionality.

Mapping - in the SCP at an average level. This is not a plus or a minus. Setting the average labor intensity.



Disadvantages of budgeting in SCP:

• Lack of interactive forms of input-output. The creation of any data is carried out through documents (entering plans; obtaining aggregated actual data; carrying out calculations), where there is no and cannot be interactivity and the ability to see the big picture.
• Lack of ETL interface. There is mapping, but the actual data is loaded directly from the document form, which is inconvenient.
• Old platform. You cannot use the 1C Managed Forms technology, which provides the user with modern possibilities for universal filtering / sorting of lists and reports. This degrades the analytical capabilities of the user.

In general, in the SCP, the automation of budgeting is most clearly implemented according to the principle of ordinary accounting: the user does not work from the general picture, but from primary documents (entering a plan; loading a fact; estimates), and the general picture can only be seen in reports in which nothing can be entered.

1C: ERP

As far as I remember, initially ERP provided only an "online" reporting model. And today, in many companies, the main scenario is exactly this. Nevertheless, now the program allows intermediate storage of the calculated values.





Figure: 9. Architecture of budgeting in 1C: ERP



Advantages of budgeting in 1C: ERP:

• Sufficiently functional forms of input-output

Disadvantages of budgeting in 1C: ERP:

• The rigidity of the model. In principle, as in most ERP systems, the budget model does not tolerate frequent changes and is rather picky about pre-setting
• Weak mapping. For some reason, the mapping functionality is worse than in the UPP
• Product hardness. Unlike SCP, it is extremely difficult and expensive to rework the methodology framework here. You need to study the existing one well, and build budgeting on 1C: ERP, if it really suits the company
• Performance. Interactive forms are quite functional, but the technical device makes them extremely slow on large amounts of data

Also in 1C: ERP there is no serious functionality in terms of setting up the organizational budgeting process (workflow) and multi-user work. For example, the approval processes are included in a separate product 1C: Workflow, which is usually implemented on top of ERP.

1C: CA

Integrated Automation is a stripped-down version of 1C: ERP, so its development follows the same path, and there is no own budgeting methodology here.

MS Axapta / MS Dynamics AX

There is only an "online" model for viewing the actual data of budgets - they are read directly from their own accounting modules, while the possibilities of serious transformation are not provided.





Figure: 10. Architecture of budgeting in MS Dynamics



Both the plus and minus of the system is its "sharpening" for its own accounting modules of Dynamics and their ready-made structure.



Pros:

• Integration with accounting modules. Setting up the receipt of actual data from various modules of the ERP system is quite simple.
• Integration. There are many opportunities for loading ready-made plans from external systems. Thus, Microsoft clearly follows the logic of separation of EPM from ERP, as a result of which EPM systems are very well "hung" on Dynamics
• Workflow. Sufficiently functional and transparent customizable organizational model of the budget process

Minuses:

• ETL. In general, the product does not provide meaningful data transformation capabilities
• Product hardness. A ready-made, but rather limited methodology is set here. And (as in the case of 1C: ERP) it is not only difficult to recycle it, but also practically pointless.

SAP S4 HANA

The main SAP product that replaced the ERP system SAP R / 3.



For budgeting, a separate EPM product is now used, which in the desktop version (SAP BPC) could still be considered a separate EPM system "on top" of ERP, but in the cloud version (SAP Analytics Cloud) it is already finally integrated into the ERP system (in SAP S4 HANA Cloud). More details about SAP BPC will be below.



It is important to say something else about S / 4 HANA itself: SAP transfers all the work of an ERP system from a relational database to a combined one (a mixture of relational, columnar and multidimensional). Such a combined database is its own SAP HANA technology (not to be confused with S / 4 HANA), which, depending on the user's actions, works either as a transactional (accounting system) or as an analytical system (cube).



Thus, SAP is moving to an architecture that is the opposite of what is now well known in "normal" practice. In it, the analytical database is not built "above" the storage (SAP BW), but is implemented "under" the ERP system. In this case, the data warehouse (SAP BW), when the user works with his data from the EPM system, transfers the data for calculations back to this original combined database.



In fact, SAP achieves the same effect for which IN-Memory OLAP was conceived in the opposite way: by maximizing calculations out of RAM.

Oracle Cloud ERP

Oracle also took the path of embedding an EPM system inside an ERP.



The company is actively moving its products to the cloud version (perhaps even more actively than SAP is doing). Therefore, for its main EPM solution, Oracle Hyperion (which we will also talk about below), the company is promoting an alternative in the form of the cloud-based Oracle EPM Cloud, which is included in the cloud-based Oracle Cloud ERP.

BI-SYSTEMS

BI-systems (Business Intellegence) in their "pure" form are a means of data output. That is, BI are report and dashboard designers, which usually also contain basic functions for restructuring and analyzing data (for example, they allow you to join tables, find averaged trends, etc.).



Popular BI systems:

• Power BI
• Tableau
• QlikView / QlikSense
• IBM Cognos TM1
• SAP BusinessObjects

Typically, BI connects to data stores (both relational and multidimensional) or raw SQL tables. Thus, you can refer to sufficiently detailed source data (to aggregate them already in BI). However, complex conditional transformations (with “if” conditions) are not about “classic” BI functionality. If you are faced with the task of building a dashboard visualization system, it is better to build a transformation before implementing BI.

EPM SYSTEMS

EPM stands for Enterprise Performance Management. Also, the terms Corporate performance management (CPM) and, less commonly, Business performance management (BPM) are encountered.



Quite a broad term that can imply related functions, but most often such systems can be considered as constructors of interactive "Plan-fact" forms. The concept of EPM has not yet become generally known, but such solutions as IBM Planning analytics, Oracle Hyperion, Anaplan, which are sometimes considered in the context of Business Intellegence, are more correctly called EPM systems.



Sometimes EPM systems are created for broader purposes (for example, SAP EPM or 1C: Holding Management), but we will consider them precisely in terms of systems for budgeting automation. Therefore, we will call SAP Business Planning & Consolidation (SAP BPC) - an EPM system, although SAP itself calls this the larger SAP EPM product, which includes SAP BPC.



As we said, BI does not allow data entry. EPMs usually include standard BI functions, and in addition provide the ability to enter and write data.



Notable EPM systems:

• Oracle Hyperion
• IBM Planning Analytics
• Anaplan
• SAP BPC
• Bit.Finance
• 1C: Holding Management

Let's start with the little ones.

Bit.Finance

Bit. Finance is based on the UPP budgeting methodology, however, unlike it, firstly, it is supported and developed, and secondly, it is implemented as an EPM system on top of ERP (thus, it allows you to consolidate factual data from various external sources).





Figure: 11. Architecture of budgeting in Bit.Finance



Advantages of budgeting automation in Bit.Finance:

• Constructors of forms for entering or reading data. Unlike the UPP, the forms of accounting documents are not fixed here, they can be customized, bringing them into a fairly convenient form.
• Interface for managing cost estimates. You can recalculate budget models here centrally, rather than manually creating a costing document.

Mapping is more developed than in SCP.



Obtaining actual data works in three ways:

• Through an evidence acquisition document (as in SCP),
• Parallel accounting. In this option, accounting documents, as they are held, create simultaneous entries in both accounting registers and budgeting registers.
• Broadcast method. In this option, the accounting ledger entries are translated into the budgeting ledger.

Disadvantages of budgeting automation in Bit.Finance:

• Work through the forms of documents. Despite the fact that the forms of documents have become flexible (see the first plus), and in general, in this aspect, a lot of progress has been made in comparison with SCP, the product still has not strayed from the document-based model of work as far as we would like. Which, as we said, is inconvenient for budgeting.
• Lack of interactive forms of input-output. Unlike 1C: ERP, there are none here.

Anaplan

A flagship product that has recently gained great popularity in the global market. Only offered in the cloud version.



Unlike other popular solutions (including Hyperion and Planning Analytics), it has a slightly specific specialization: it works best as a costing service that allows you to quickly and automatically recalculate volumetric budget models with a large number of dependencies.





Figure: 12. Anaplan budgeting architecture (popular automation scenario)



Pros:

• Costing. The product is focused on calculations, and stores all data in In-Memory OLAP, which allows all models to be recalculated online
• Teamwork (within the preparation of planning data)
• UX and freeform modeling.
• ETL. Own and quite convenient ETL tool
• Requires minimal IT support. Especially when it comes to modeling
• Cost. A bit expensive for the Russian market, but in comparison with international leaders (the same Oracle Hyperion), the total cost of ownership is lower
• Implementation speed. Compared to Hyperion and Planning Analytics, the product is easier to use and easier to implement

Minuses:

• Formatting
• Teamwork (in terms of working with events: notifications, mailings, etc.)
• Custom formula syntax. In general, using your own code is always a disadvantage from the point of view of end users.
• Hierarchies. There used to be problems with using a different directory hierarchy for different budget models. The problem is not important for many companies, but it is critical for some. Perhaps (I hope so) Anaplan has already solved this problem by now.
• Ad-hoc . , : Anaplan , .

Both Anaplan's plus and minus are its relatively clear specialization and the fact that it does not encroach on the company's IT ecosystem. The plus is that the product has clearly defined its functional purpose, and the directions of its development are quite predictable. It is a service for conducting What-If analysis, calculating and approving plans (budgets), and you need to plan the customer's architecture based on this. The downside is that the product cannot replace a full-fledged corporate data warehouse, cannot replace all the capabilities of BI, a complex corporate ETL infrastructure is not built on it, and indeed the entire corporate computing system. All this would not be a problem if the product was not offered only in the cloud version.



Unlike Oracle and SAP (both of which claim to be ecosystem), Anaplan does not emphasize the ability to easily "move" data and tools between the cloud and the company's servers. Thus, in his case, the disadvantages of the cloud product (taking into account the tariffication depending on the amount of data used on the server) are most noticeable.



Since it does not replace a universal corporate storage, in certain cases it can be used as a calculation service that “adds” planning data to the company's own DWH, from where the data is transferred to a separate BI system for building quick reports and dashboards.





Figure: 13. Anaplan budgeting architecture (alternative automation scenario)



In general, the use of both EPM and BI systems is a normal practice.

Oracle Hyperion

It comes in at least two versions: Oracle Hyperion Planning and Oracle Hyperion Financial Management.

Now actively being replaced by the new Oracle EPM Cloud product.



Due to ecosystemism, architectures can take on a variety of types, but the typical one looks something like this.





Figure: 14. Budgeting architecture in Hyperion (possible option)



In the figure, I gave a BI system as an example, since the Oracle Essbase analytical database is an excellent base for analytics of big data in BI tools.



Oracle Data Integrator is offered as an ETL solution, which acts as a universal data integration mechanism in the Oracle ecosystem.



Pros of budgeting automation in Oracle Hyperion:

• Ecosystem. In the case of Oracle, I will note it as a plus, since Oracle is one of the largest database vendors, and integration for companies who work on Oracle DBMS (and there are many such companies) really gives advantages. In particular, it is more convenient to distribute functionality between the cloud and the server. In addition, colleagues talk about quite serious advantages in terms of security in Oracle's architecture (I am not an expert in this, if there are any here, please correct).
• Ad-hoc ("reporting on demand").

Disadvantages of budgeting automation in Oracle Hyperion:

• Ecosystem. I also note as a minus, since, according to available information, Hyperion is chosen mainly by companies working on the Oracle technology stack, and from its use in a non-Oracle environment in the long term, a lesser effect is possible.
• . , Anaplan.
• . , UX ( ).

IBM Planning Analytics

Mainly intended for large businesses, not very easy to deploy and administer, but a very functional EPM system. Currently, IBM Planning analytics is built on TM1 technologies (which are at the heart of Cognos).



For ETL processes, IBM suggests using a separate product, IBM DataStage (previously used by Cognos DataManager), Turbo Integrator, Cognos Integration Server, or an external ETL tool.



Typical architecture is very similar to Hyperion.





Figure: 15. Budgeting architecture in Planning Analytics (optional)



Strengths of IBM Planning Analytics:

• Forecasting.
• Working with events.
• Flexibility. The product cannot be called non-demanding in terms of pre-configuration, but it tries to be adapted to changing models.
• Not ecosystem. What is surprising about working with IBM is that a large volume of teaching materials produced by the company is aimed at describing the best practices for interaction of IBM products with other solutions (including Oracle and SAP), and in a variety of issues. In my subjective opinion, it is good that in the long term the company keeps the trend to develop integration with third-party systems (which allows supporting a wide variety of architectures that have developed in companies), and not to reduce it.
• Product support.

Minuses

• Complexity. As with Hyperion: Requires significant user training, not the lightest infrastructure.

SAP BPC

In general, the distinctive features of SAP are ecosystems, the complexity of the architecture and infrastructure of solutions.



As I said, SAP has supported and supports different architecture options at different times; According to the most recent information, the flagship version of the architecture recommended by the vendor today looks like this:





Fig. 15. Budgeting architecture in SAP Business Planning & Consoldation (example)



Advantages of budgeting based on SAP BPC:

• Data integration. Although complex, it is functional and fast, which is essential for large companies.
• Visualization.
• Workflow.

Disadvantages of budgeting based on SAP BPC:

• UX . SAP, , .
• . , SAP . , : . , . , SAP SAP BW MS SQL Server, NetWeaver; BW/4 HANA NetWeaver; , EPM- SAP Analytics Cloud, .
• Price. In terms of total cost of ownership, it actually turns out to be one of the most expensive EPM systems in the world, which is influenced by changes in architecture.

ETL-TOOLS

Well-known ETL tools are used to build ETL processes, among which there are many products from the same vendors that produce BI / EPM solutions:

• IBM DataStage
• Informatica PowerCenter
• Talend
• Apatar
• SAP Data Services
• Oracle Data Integrator
• Microsoft Azure Data Factory
• and many others dr.

It is planned that the article will be gradually updated, possibly with the addition of information about new products and principles of developing software products for budgeting "from scratch".