Capturing Data For Business Intelligence (Part Two)

In "Capturing Data (Part One)" we discussed a bit about how transaction data are stored in database tables and began to develop the idea that such data are designed (or "architected") to get data in as easily and accurately as possible. This article expands on these concepts. If you haven't already, you should read "Capturing Data (Part One)" before continuing.

When we left our point-of-sale (POS) transaction system in the previous article, we had taken the customer and product names and replaced them with unique identifying codes, so that

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

became

325, 1, 22, .01

But who can make sense of that just by looking at it?! (OK, maybe a geek like me could, but please!) In order to preserve descriptive customer and product information, the database designer for the POS system ends up moving them to their own tables and linking these tables to the sales transaction table. Now instead of one table there are three.


The technical term for this process is “database normalization.” The advantage of doing this in the data entry process is that we don’t have to type in “B. Jones” or “Coffee” every time we sell coffee to him/her. We type the descriptive information about them into the Customer or Product tables the right way one time and then have the computer look them up when we need them for a sale. If we take this to the next step, suppose we want to know the supplier for a particular product. Using normalization, we would create a “Supplier” table and separate it from the “Product” table. And on it goes. Putting only the information we need in each table and repeating it as little as possible in other tables leads to a table structure that looks like this:


This kind of a table structure is often called a “snowflake.” The good news about this snowflake "schema" (as the structure is called in more technical terms) is that it’s easy and efficient to get transaction data into the system. This is because being able to look up information about customers and products cuts down on data entry errors and makes the process go more quickly and smoothly. The bad news comes when you try to get data out of the system, because in order to make the information you get out descriptive and/or meaningful you have to pull data from a lot of tables. Most POS system designers understand this and try to help you out by providing basic reporting. But if you’re trying to get data out of all of those tables at the same time sales clerks are trying to enter orders for customers, the system can really bog down. This why we create a separate data warehouse space for business intelligence purposes, and that is where processing comes in.

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 . . .

Data Architecture 101

Someone who lays out the plan for a building is called an “architect.” The building plan as the architect lays it out is called the building’s “architecture.” In a similar way, we commonly call the layout of data for use by a business or other organization its “data architecture.”

The architecture of a building depends a lot on how the building is going to be used. For example, you wouldn’t expect an office building to be designed exactly the same as a factory or a grocery store. The same is true with data. The type of data architecture that works best to get a lot of data into a system, like a point of sale terminal, isn’t necessarily the best one to get the data out of that system. If you try to get the data out during business hours you risk slowing down the ongoing process of getting data in. The last thing you would want is to make a customer wait because you’re pulling a report from the same system you’re using to sell them something.

To make matters worse, consider what happens when, like our specialty retail store (for background see the article on the data integration imperative), you now have two different places where you have sales data (one in-store and one online). Now you have to pull sales reports from two different places. Do you want information by customer? Are you sure all the customers in the two systems are different? How do you know? Maybe you can just settle for sales by postal code, if you capture the postal code information in the store at the point of sale. And let’s hope the clerk (or you) didn’t fat-finger any of the Zip Codes. Of course, if you don’t have a choice you don’t have a choice. But this is why sooner or later growing businesses take a long look at data governance, data integration, and the data warehouse concept.

About The Data Warehouse Concept

In recent articles we've been talking about data governance and data integration and why these are so important to your business. One of the key best practices for successful governance and integration is to keep business intelligence data separate from transaction data (such as the data in your accounting or point of sale systems).

Once you grow your business to the point where you need the concepts discussed here, you’ll probably want a technical guru to head up the implementation. Remember as we go that my idea is not to make you that guru, but to give you enough information to be an intelligent consumer of the products and services that make a sustainable BI program possible.

One very important principle behind that sustainable BI program is that we want a separate place for the data we’re going to consume, away from the sources from which we captured the data in the first place. Different people may give different names to this separate place. For our purposes right now let’s use the term “data warehouse.” This isn’t completely accurate, because the term “data warehouse” has a very specific meaning for BI professionals. So we’ll come back to this later.

But for now, as a simplification, we’ll say that we take data from where it was captured, do some processing with it, and load it into the data warehouse. There are good reasons for this. First, the systems designed to capture the data are not typically designed to get data out as easily as it gets in. Second, as we’ve hinted at previously there may be two or more sources of data that need to be combined in a way that is meaningful for your business.

The Data Integration Imperative

Consultants and experts in the BI field use the terms “data governance” and “data integration” to talk about how to approach the kinds of problems we’ve been discussing in this section of the material. These fundamental concepts lie at the heart of the “integrated and coordinated” part of the definition we gave earlier for BI as a whole. Previously, we introduced data governance in two articles. This article introduces the concept of data integration.

Whereas data governance (in my opinion, at least) is really a people concept that requires a human touch to manage properly, data integration is more a technical concept for implementing the parts of data governance policy that call for BI to reflect the business as a whole. Put another way, data integration is the activity of pulling together data from systems all over the business and tying it all together so it says something meaningful about the whole business.

If you’re just starting your own business, or you’re in a business unit of a company where there is little or no data governance in place, data integration is simply not a high priority. You may only have one set of data to work with, as was the case in “'Real' World Story #1.” In that case the output was meant strictly for internal consumption by the sales force. In the case of the fictional specialty retail store we discussed earlier, the only data available in the beginning might be point of sale data plus some cost data.

But as the business grows in size and data accumulate in more and different places, data integration becomes more and more important. As our specialty retail store develops, sales are collected both in the store and online. Also, there may now be shipping data sitting in a completely different place. Without some way of tying all of this together it becomes difficult to impossible to get the big picture of how the business is doing. So whether you need it or not to begin with, it’s never too early to start thinking about and planning for data integration in your business.

Data Governance (Part Two)

This is the second of two articles introducing the concept of data governance.

Now we’re going to introduce a theme that will return over and over again in different contexts throughout this blog: if you’re the boss, you need to be on board for any of this to work well. By “the boss” I mean the owner if it’s a small business, and a non-IT C-level executive (Chief Executive Officer, Chief Operating Officer, Chief Financial Officer in that order of preference) if it’s a larger business.

Why this order of preference? The CEO by definition has the most clout: typically the COO and Chief Information Officer (CIO) both report to her, and she can easily delegate responsibility for the data governance process jointly through them. The COO, having direct responsibility for day-to-day operations, is the next best choice. The CFO is more of a dicey choice because in most mature businesses IT grew up as an arm of finance and accounting and was later split off into a separate unit. Because of this, the relationship between IT and finance and accounting is often either too cozy (if the split was friendly) or too adversarial (if it wasn’t).

And why do we need the boss on board in the first place? Especially in larger companies, politics can play a huge role in the governance process. It’s only natural that stakeholders in the governance process will fight tooth and nail for the definitions, policies, and procedures that are most favorable to their business units. The boss needs to firmly and consistently champion the process as a whole, and to set guidance that places the overall business strategy for data governance above departmental politics.

Data Governance (Part One)

This is the first of two articles introducing the concept of data governance.

Consultants and experts in the BI field use the terms “data governance” and “data integration” to talk about how to approach the kinds of problems we’ve been discussing in this section of the material. These fundamental concepts lie at the heart of the “integrated and coordinated” part of the definition we gave earlier for BI as a whole.

When we defined BI we said that both the data and the definitions of what the data mean need to be shared. “Data Governance” is the term that BI professionals use for the process of making sure that happens. This includes things like setting policies and procedures for how data are to be captured, processed, and stored, defining what terms like “revenue” mean for the purpose of making business decisions, defining what is acceptable for knowledge workers to do with data that are being used for decision purposes, and more. In short, what is being governed is the BI life cycle itself.

How data governance works depends mainly on the number of people with a stake in the process. In a small business, the owner may be the only person with a stake. So that owner will call the shots. In a larger business there may be many people of relatively equal clout with a stake in the governance process, and a significant amount of time and effort may need to be expended to make sure each voice is heard. In many larger companies today you may actually find a data governance board, made up of representatives of the IT and non-IT business units, whose mission it is to set corporate standards, policies and procedures for data governance.

Next time: The importance of top management buy-in for successful data governance.

Drill Down On: Capturing and Processing Data

Previously, we touched on the Capture and Process stages of the BI data life-cycle. This article begins a more in-depth look at these two stages.

When we introduced the concept of BI we put it in the context of a life cycle. The first two steps of the life cycle we defined as capturing and processing data. These two pieces are tied together because the better job we can do in the first step of capturing data, the less work we have to do in the second step of processing it for consumption (which is the third step in our life cycle). All of that sounds wonderful, but it’s never as easy as it sounds, and the bigger a business becomes the more difficult it gets to capture and process the data smoothly.

To illustrate what we’re talking about, think about a specialty retail store. When the store first opens for business, let’s say it starts out with a cash register or point of sale (POS) terminal. If the specialty items that the store sells are rare or interesting enough, in time word will spread beyond the local community in which the store operates. The store owner may create a presence on a social networking site such as Facebook and begin seeing demand from other parts of the country or even the world. This may lead to the creation of an e-commerce web site.

Soon there are sales coming from two sources (one online, the other in-store). While most of the online customers are not local customers, some of them may be. As the business grows bigger and bigger it becomes harder to tell which is which. You may end up with an account for the same customer on each of your sales systems.

This may happen even with just one system, if you’re not very careful about how you set it up and train the people who run the system. For example, an existing customer named Jones wants to buy something. The clerk does a search for “JONES.” Nothing comes up, so the clerk sets up a new account. Nothing came up in the search because the POS system is case-sensitive. So now you have Mr. Jones twice. Good luck later when you try to figure out if he’s a good customer or not.

Next time: A sad but true tale from the "real" world.

BI Data Life-Cycle: Capture

Previously, I described a life-cycle of business intelligence data with four main stages: Capture, Process, Consume, and Archive. This article deals with the Capture stage of the cycle.

In some respects, a business treats data a lot like a factory treats raw material. And in order to do anything with the raw material you first have to get it. Let’s define “capture” as the process of getting raw data. How do we capture the data? The answer to that depends on how automated your business is. Our Old West general store owner from earlier in the chapter captured data in his sales ledger. Today, we might capture the same data in a point-of-sale (POS) terminal or from the Web if we do e-commerce. Or if we don’t do a lot of transactions, we may still issue a written receipt to the customer and then re-enter the data into a computer if we want to put it in electronic format. And of course, that’s not the only data we capture. We also capture data from purchase orders, shipping receipts, vendor invoices, tax forms, and many other sources.

Even though we don’t pay too much attention to data capture in the business intelligence processes that follow, it is still critical to BI. The reason for this is simple: if you don’t capture it in the beginning, you can’t analyze it later. Have you been to a retail store lately and as you go to check out, the sales clerk asks for your postal code? There may be any number of reasons for this. Maybe there are other stores nearby and they want to see if any of their markets are being over-served or under-served. Or maybe they want to see what postal codes their sales are coming from so they can better target mailings. (Or maybe they’re just nosy busybodies, but that’s probably not it. Probably not.) The key takeaway is this: if you want to analyze or report or mine the data (more about data mining later), you must capture it first.

Next time we'll discuss processing the captured data to prepare it for consumption.