17. Ancient Data Warehousing

Although it only now has become a common term as the core of business intelligence, the practice of data warehousing has been with us for over five-hundred years. Since the early Italian renaissance, merchants have kept databases of their business transactions wherein each transaction was related to the critical dimensions that characterized its type and the effect that it had on the business.

More specifically, each transaction, representing a transfer of financial resources from one place to another, was related to the source and destination of the transfer. Each transactions was related to the place from which it was withdrawn and the place to which it was deposited in what has come to be known as double-entry bookkeeping. The record of the withdrawal was referred to as the “credit” entry and the record of the deposit was referred to as the “debit” entry.

Like all data warehouses, each of transactions was also related to the date of its occurrence, allowing the merchant to sort and sum the transactions to measure the activity of the business during given periods of time. Furthermore, the financial state of a business could be determined by summing the deposits to a given account, then summing the withdrawals from the same, and finding the account’s “balance,” or state, by subtracting the withdrawals from the deposits.

This process of relating transactions to its critical factors (“dimensions”) and summing the transactions according to these critical factors is exactly how a modern data warehouse is used. The data warehouse, the central focus of the field of business intelligence, is universally implemented as a multidimensional database. The multidimensional database, like the bookkeeper’s journal, is made up of chronologically ordered records that represent business transactions with each transaction related to the customers, products, accounts, dates, and other “dimensions” of its existence. The data warehouse is, in effect, a journal of business transactions in the same way that the accountant’s book of original entry (his “journal”) is.

So, what is the difference between a traditional bookkeeping journal and a modern data warehouse? Only the existence of the SQL language (or some equivalent database query language). The modern query language, allows the user to sum and sort transactions by any combination of its many dimensions, including the transactions date and its debit and credit accounts.

Because the renaissance bookkeeper did not have modern database automation, he needed to first sort the transactions into individual databases (called “ledger accounts”) and then sort them again into rigid time frames (called “reporting periods”). Because of these once necessary and arduous sorting tasks, the business intelligence of times past was slow, expensive, error-prone, and untimely. And because we have not integrated financial reporting and analysis into common business intelligence practices, financial information has continued to be untimely to this day.

7. The Virtual General Ledger

Each of the Entries made in the Journal must be posted twice in the Ledger, one
to the debit and the other to the credit.
Luca Paciolo, Particularis de Computis et Sripturis

The general ledger, or just “ledger,” is made up of a set of accounts, each account representing a source or destination of resource flows within a company’s accounting system. The information in each account is a repetition of the information that has already been recorded in the accountant’s journal. The duplication of information in the general ledger allows the balances of each account to being manually computed. By copying the amounts of each transaction into the account that it is related to, the human bookkeeper needs to only go to the account to be balanced and add up the sums recorded therein.

For example, a transaction is first recorded in the journal with a note that the transaction has a credit (source) effect upon the revenue account and a debit (destination) effect upon the cash account. After this initial recording, the details of the transaction’s record is “posted” to the general ledger by copying the transaction amount to the credit column of the revenue account and then copying the same amount to the debit column of the cash account. When the bookkeeper wants to find the balance of any account, he adds up its debit column and its credit column and the difference between the two sums is the account’s balance (the net balance can be a debit balance or a credit balance, depending upon which sum was greater).

However, using the fundamental principles of software engineering, the general ledger should be an algorithm, a series of steps that are performed by a computer program, rather than a separate database containing data that is already recorded in the journal. The reasons supporting this statement include the following:

1. The general ledger helps with the manual computation of balances; it serves no similar purpose for automated computation.

2. Duplicating the same information in more than one place violates the most fundamental principles of information design (keeping backups of data on tape and other media is not relevant to this argument).

3. The finding of an account balance is a matter of first sorting and then summing data. The traditional general ledger performs the function of the sorting part of this process. Modern computers can do this sorting almost instantaneously and without cost. Therefore, to have the data in a pre-sorted state is of no economic value. Determining account balance is efficiently a simple algorithm that first sorts and then sums.

4. The maintenance of a general ledger as a separate database performs no other function on the computer than to place an artificial boundary upon the data, limiting its availability in both time and semantics. The general ledger limits the data to the time of a single fiscal period and it limits the various ways that the data can be correlated with other dimensions of the transaction, such as business units, products, customers, etc. (this will be detailed in the next post here).

The general ledger should be, by this analysis, virtual data. Modern accounting practices should use the power of the automation to make the general ledger a Virtual General Ledger, implemented as a program that sorts and sums the data already stored in a powerful database.

See Banking the Past.