Business intelligence for dummies

Want to know what BI is but were always afraid to ask?

Here is a use case that covers 90% of Business Intelligence software use in the real world.

Imagine an Excel table containing banking transactions that you exported from your online banking system.

Once they are exported, you may be tempted to construct pivot tables, to view e.g.

  • The bank accounts you credited for the last 12 months;
  • the ratio between credit and debit by month;
  • the expenses from your banking cards compared to those of your wife.

All of these pivot tables will be constructed for you in a matter of seconds, or even milliseconds.

Unfortunately, the construction of pivot tables does not scale up. A bank manager trying to construct a pivot table to identify the most debiting accounts or to identify the accounts performing most of transactions over a month is likely to wait many hours until his pivot tables are calculated. This is because the amount of transactions he is analyzing is likely to be in the hundreds of thousands, and more. He will also try to lump together much more information by adding columns with addresses, information on insurance policies of account holders, etc, thus growing the table to tens, sometimes even hundreds of columns, aggravating the performance issue.

Fortunately, there is a solution in the form of the OLAP databases. They essentially precalculate combinations of the pairs of columns in order to simplify the calculation of any possible pivot table for any given table in such a way that the construction of pivot tables is possible within seconds even on big and large tables.

This is it. Business intelligence is about constructing arbitrary pivot tables in a matter of seconds on big, multicolumn tables.

P.S. Care to know what the OLAP cube is? This is a grouping of X and Y axes of a pivot table with the data area. A multidimensional OLAP cube is a collection of such groupings for all menaingful combinations of columns in the original table. For instance, given a table containing bank account numbers, amounts and dates, the most likely pivot table (and thus the OLAP cube) is

  • account numbers in rows
  • dates in columns
  • amounts aggregated by date and account in the data area