In financial information systems today, three different technologies are used: spreadsheet/processor, relational database (hereinafter referred to as R-DB) and multidimensional database (hereinafter referred to as MD-DB). Each of them is a world in itself. Each is suitable for something different and it is worth taking care of their correct use when creating financial information systems. This will save a lot of nerves, work and money.
Probably the most well-known spreadsheets today are MS Excel and Libre Office Calc. Among relational databases, we can mention Oracle Database, DB2 or MS SQL Server. This also includes the office database MS Access. Representative multidimensional databases are TM1 or Essbase.
The technology used fundamentally determines the way of working with the application that is built on top of it. Each of these technologies has its own mental world (mathematical model), functions, controls, advantages and disadvantages. Choosing the right technology for a financial system can be compared to choosing a mode of transport for a holiday (car, train or plane). You don’t choose a plane for a camping trip in the Black Forest, you don’t choose a car for a trip to Mallorca.
Looking inside the technology itself, you can see the following.
In the case of a table processor, all data, including formulas, formats, and passwords, are stored in a single file that even a regular user (not an IT expert) can transfer between computers or send to other people. In (both types of) databases, the data is scattered across many directories and files of various types that only a trained IT expert can work with. Transferring databases between computers is a very complex task.
As for the infrastructure on which the technologies are run, the situation is as follows.
Looking from the outside at the user screens of individual technologies/applications, we will see approximately the following.
For a table processor, freely spaced boxes of various texts and numbers, and lots of color are typical. For an R-DB, a typical screen consists of two parts: in the upper part, freely spaced data about an object, and in the lower part, many of the same repeating lines assigning data about many occurrences of another object to a superior object (e.g., a parent and its children, an invoice and its lines). For an MD-DB, a matrix of rows and columns that can be interchanged is typical. Above that, a set of additional filters (dimensions) that can be used as columns or rows.
The basic difference is in the objects/concepts that the technology works with and the user has to think about them. The following table shows the comparison.
In the case of a table processor, the appearance of the sheet (user interface, data display) is created first, and only then are the formulas that manipulate the data created. In the case of (both) databases, data structures are created first, and only then is the data displayed. The user often looks at the data in the database through some specialized tool (so-called reporting tool), which can also be a table processor.
There is also a big difference in working with formulas. Examples of formulas are again shown in the table.
In a table processor, the user only sees cell addresses. It is not at all clear what is being calculated and from what. Databases work with general (objective) concepts that give names to table columns or dimension members. In the formula, the user can see and check what is being calculated. In a spreadsheet, any cells can be linked by a formula. In R-DB, a formula always applies only to a specific column in the table and is calculated for each row separately. In MD-DB, the formula always applies to a specific level of the hierarchy of the combination of dimensions.
In detail, the user interface for the same example looks as follows.
For a table processor (all side by side on one sheet):
For a relational database (database tables and user interface):
For a multidimensional database (different views of the data):
The above description shows the advantages and limitations of individual technologies and the appropriate way to use them.
Table processor applications are suitable for personal creation of spreadsheet documents with calculations. Each worker creates files with content for his personal work tasks. So, the typical areas of use are not specific, but the method of use – single-user with a complicated appearance (front-end). It can be used to display data from a database.
Relational database applications are suitable for transactional processing of large amounts of primary data combining the content of different subject areas (e.g. a combination of production, sales and finance). Suitable for applications where you need to freely add records. Typical areas of use are accounting, sales systems, journal entries.
Applications in multidimensional databases are suitable for analytical processing of data combining the content of different subject areas (e.g. a combination of production, sales and finance). The domains of individual dimensions must be defined in advance. Data is added rather by adding to the existing cell value. Deleting an element means losing a significant part of the data. Typical areas of use are data analysis, financial planning, quality management.
What are the impacts of using an inappropriate tool for creating a financial system:
As a result, the system does not meet the user’s expectations, does not help them in their work, and users refuse to use it. For the company, this means dissatisfied employees and underutilization of the potential of human and technical resources.