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.

8. The Virtual Fiscal Period

We measure business success by unit of time. Earnings and cash flow, for example, are a business’s most important measures of success and they can be expressed meaningfully only as amounts per quarter, month, year, or some other unit of time. Business is an activity and its failure or success can only be measured by the rate of activity through a period in time.

The activity of a business expands and contracts from year to year, quarter to quarter, and even day to day, however, the critical financial measures of a company’s activities is currently only available on a quarterly basis. This schedule of measurement does not allow the company to gain insight about the effects of sales and marketing campaigns, for example, which occurred during certain weeks of that quarter. Determining which weeks were the best during the quarter, or which days of the week brought the best revenues, or how much better revenues were during a particular campaign, are all critical pieces of business information that are typically not available to a company. Financial reporting is limited to a fixed fiscal period and the only unit of time that can be report on is the company’s official fiscal period (typically a three month quarter).

Almost important as the rigidity of measurable time unit is the delay in reporting. While decision-makers are desperate for information, they must often wait months for a report on the company’s earnings and cash flow to help guide them in allocating resources.

This critical limitation of financial reporting by time unit is simply not necessary and is a product of a reporting technique that is five-hundred years old and was designed to assist the manual bookkeeper who worked with quill and parchment and without the assistance of even a slide-rule. In today’s computer age, earnings can be speedometer on an executive’s dashboard that is providing him with a real-time view on the activity of the business and the changes that they are causing.

A company’s dependence upon a rigid quarterly report is based upon the use of database known as the general ledger that performs the simple algorithm of sorting transactions so that an ancient bookkeeper can manually perform addition upon the transactions that affect each account. This sorting can be done in seconds with the modern computer and the general ledger can now be a simple algorithm that provides, upon demand, the same information as the stored database (see the previous post where this algorithm is referred to as a Virtual General Ledger).

As a process, rather than a stored database, the virtual general ledger can produce earnings and cash flow statements for any day, month, year, or other period of time that the user is interested in (he can produce fiscal periods from deep in the past as well as to the current moment). In other words, the virtual general ledger provides the business executive with a perfectly accurate virtual fiscal period.

See Banking the Past.