Seeking the Source

We have seen that business intelligence is important for effective decision making in our organizations. This, however, leads to a big question. Just where is this business intelligence going to come from? Is business intelligence a form of corporate espionage? Do we need to send up spy satellites to watch our competitors and tap the phone lines of our clients? Should we be hiring secret agents to infiltrate our rivals' facilities? Of course not!

Does business intelligence require us to take the pulse of the people? Do we need to commission large studies of our potential customers? Do we need to conduct a survey to determine what people are thinking about our products or services? While some business intelligence may come from customer satisfaction surveys or market research, the customer's buying behavior is a better gauge of their tendencies and satisfaction. At any rate, this is not what we are going to focus on in this book.

The bulk of business intelligence for most organizations comes from something they already have, their transactional data.

Transactional Data

Most organizations need to keep track of the things they do to conduct their business. Orders taken, products produced, services rendered, payments received from clients, and payments made to vendors are all interactions that usually result in one or more entries in some type of data store. Each of these interactions is a business transaction, so we refer to this as transactional data.

Definition 

Transactional Data is the information stored to track the interactions, or business transactions, carried out by an organization.

The business transactions of an organization need to be tracked for that organization to operate. Payments must be collected for products and services. Payments must be made for goods and services received. Orders and service requests need to be fulfilled. In general, the organization needs to keep track of what it has done and what it needs to do. When these transactions are stored on and managed by computers, we refer to this as online transaction processing or OLTP.

Definition 

Online Transaction Processing (OLTP) systems record business interactions as they happen. They support the day-to-day operation of an organization.

The sum of these transactions stored in OLTP systems is the history of an organization. This transactional data contains the raw numbers necessary to calculate the measures we discussed in the previous chapter. Here, then, is the data we need to create our business intelligence.

Difficulties Using Transactional Data for Business Intelligence

OLTP systems are the treasure chests holding the raw data we need to calculate measures and create business intelligence. Problems arise, however, when we try to extract these nuggets of raw data from our OLTP systems. Let's take a look at some of the difficulties.

The Nature of the Beast Well-designed OLTP systems are optimized for efficiently processing and storing transactions. This means breaking data up into small chunks using the rules of database normalization. This allows OLTP systems to process a number of transactions at the same time without one transaction getting in another's way. Information of this type is best stored in a relational database.

The measures we are using for business intelligence, on the other hand, are not designed to reflect the events of one transaction, but to reflect the net result of a number of transactions over a selected period of time. Business intelligence measures are often aggregates of hundreds, thousands, or even millions of individual transactions. Designing a system to provide these aggregates efficiently requires an entirely different set of optimizations.

Definition 

An Aggregate is a number that is calculated from amounts in a number of detail records. An aggregate is often the sum of many numbers, although it can also be derived using other arithmetic operations or even from a count of the number of items in a group. For example, the total amount invoiced to a client in a given year is the aggregate sum of all the invoice amounts for that client in the given year.

OLTP systems, because of the way they are designed, are usually not good at delivering large aggregates. This is not what they were designed to do. We need to look to a different type of data storage optimization to make these aggregates work efficiently.

Interfering with Business Operations OLTP systems are used by our organizations to support their daily operations. In many cases, the organization's operation depends on the performance of these systems. If the order processing system or the client management system becomes too bogged down, our organizations can grind to a halt.

We've already discussed the fact that OLTP systems are not good at delivering the aggregates needed for business intelligence. When OLTP systems are called on to produce such aggregates, they typically use a large amount of processing power and take a long time to produce a result. It is also possible that a large number of records will be locked while the aggregate is being produced, rendering those records unavailable to participate in transactional processing. Either of these two events can have a serious impact on transactional processing efficiency.

In other words, requiring an OLTP system to create business intelligence aggregates can tax the system. This can have a detrimental effect on our organizations' daily operations.

Archiving Because OLTP systems are concerned with the day-to-day, they aren't too concerned with data from the distant past. These systems may only save data for a relatively short period of time. The data may be saved for a year, and then a year-end process may remove it from the database. It may be archived in another format, a text file or a database backup file, or it might simply be deleted. Whether deleted or archived, the data is no longer easily accessible.

OLTP systems use this archive process to insure that the system continues to operate efficiently. If a transaction table contains too many records, the OLTP system can become bogged down and begin to operate slowly. Archiving allows an OLTP system to stay lean and mean.

This archiving causes problems for business intelligence. When we are looking for trends in our measures, we want to compare last year's numbers to this year's numbers. We may even want to compare numbers over several years of operation. This is hard to do when the data from past years has been archived or deleted.

Divided They Stand Our organizations probably use a number of different OLTP systems to manage different aspects of their operations. One system is used for order processing, a different system for accounting, another for manufacturing, and still another for personnel. As we saw in the previous chapter, Maximum Miniatures, Incorporated has six different systems that can provide data for business intelligence. Even with the move toward integrated Enterprise Resource Planning (ERP) systems, it is unlikely that all of an organization's transactional data will be in one location.

The measures used to provide business intelligence, on the other hand, do not respect these lines of separation. Instead, they treat the organization as a whole. For example, a reasonable measure to require is the profit margin for a particular product. To calculate this measure, we need the list of raw materials from the manufacturing system, the cost of those materials from the accounting system, the cost of labor required to produce the product from the time entry system, and the amount paid for the product from the order processing system. To calculate this type of a measure, then, we need to combine data across systems to get what we need.

Aside from the necessity for communication between systems, this need to cross systems leads to another problem. Each of these systems maintains its own set of product numbering schemes, codes, and calendars. The same product may be known as "12593" in the manufacturing system and "SD125RDS" in the order processing system. The payroll system may work on two-week pay periods, while the accounting system works on fiscal months. When data from these disparate systems is brought together, we need to find some common ground.