Capturing Data For Business Intelligence (Part One)

Earlier we touched on the idea that the format in which we capture data is not necessarily the same format in which we want to have it available for reporting. Let’s now go into more detail on this idea. When you input data into a point-of-sale (POS) system, the data are stored in a database. Such a database is typically set up in a tabular format. This means that however the bits of sale data were input, whether typed in or scanned from a bar code, they’re stored in rows and columns. Thus what we call a database table is really just an arrangement of data into rows and columns, where each row (also called a record) represents a single transaction, customer, or whatever, and each column (also called a field) represents the details of interest to us in that transaction, customer, or whatever. Think back to the general store example from several articles ago:

Jones, B., 1 lb. Coffee @ $ .01

This would represent a single transaction record in our sales database table, with fields for Customer, Quantity, Product, and Price. If a database designer were to express this as a database table it might look something like this:


This was fine in the Old West, but in the 21st century there are all sorts of potential problems. First, what if there’s more than one B. Jones in the customer database? How do you make sure the right one gets credit for the sale? The typical solution to this problem is to create a unique identifying number for each customer that’s separate from the name. So this B. Jones might become customer #325. We’ll also need to do this with the product “Coffee.” After all, nobody’s perfect when it comes to data entry. At the end of the month you may want to know how many pounds of “Coffee” you sold, but if it gets entered to the POS system as “Coffee,” “Cofee,” “Coffe,” and so on, you’re out of luck. So the system designers eliminate this problem by giving “Coffee” a unique product identifier (or just “ID” for short), say #22. Now our record looks like this:

325, 1 lb., 22, .01

To be continued . . .