Using OLAP Data to Make Better Business Decisions

If you know the names of the dimensions, levels, and measures in an OLAP cube, you can ask some fairly complex business questions of the data. Using the sample car sales database as an example, you might want to ask business questions such as

• In which quarter were car sales the greatest when viewed by payment type?

• Year over year, what was the average standard car type sale in the individual states of the West region as compared to average deluxe car type sales?

• What was the best day of the month to sell sport utility vehicles for cash in Oregon, and how much income was generated from these sales compared to sport utility sales in Washington ?

• Month over month, what were the largest and smallest sticker prices for each of the combinations of car series, car types, colors, and payment types for California car buyers?

Knowing how your data is organized enables you to determine whether the business questions that you ask can actually be answered by the data. Using the car sales data example, you wouldn’t be able to determine excise taxes collected as a portion of car sales; you could only determine total car sales.

Note

If you determine that your questions cannot be answered by the way your data is organized, you have two options: revise your business questions, or collect and enter the missing data into the database. Make sure that you are collecting, entering, storing, and organizing as much data about your business activities as possible, both from a practical and legal perspective.

How many questions can you ask of the data in the sample car sales figures? If you multiply the number of possible sale dates by the number of possible sales regions, the number of possible sales manager and salesperson combinations, and so on, you can easily come up with hundreds or thousands of questions. But it does not end there. Now take each of these questions and multiply them by the number of available measures. Fortunately, this sample cube has only a Price measure. But if it also included a Sales Tax measure, an Odometer Reading measure, a Finance Percentage measure, and so on, the number of business questions you could ask could quickly grow to a staggeringly enormous number.

When you are working with a non-OLAP database, every time you ask a new business question, the data analysis software must perform a series of possibly complex calculations and summarizations to provide the answer. The need to perform these calculations is usually not an issue when up to a few thousand records are in the mix, but processing hundreds of thousands or more records could result in considerable waiting time while the data analysis application forms the answer. Just think what would happen if data-entry employees were using the same database to enter business data? You can see the implications for database downtime pretty easily.

In an OLAP database, the OLAP software takes snapshots of the data at selected time intervals. With a process known as aggregation, OLAP software can ask business questions ahead of any request and store the answers for retrieval. When you ask a question for which the answer has been calculated, the OLAP software immediately returns the answer to you. If the OLAP application hasn’t yet addressed the question you pose, it should still be able to answer the question very quickly because it can calculate the answer from other aggregations already stored. For example, let’s say I want to know the average car sales price for the sales manager with the ID of 101. If the OLAP application did not have the answer to this question already, but it did know the sum of the car sales for each salesperson, the application could quickly determine the sum of the car sales for the salespeople that report to the specific sales manager.

Even with small databases, transitioning data to an OLAP structure makes sense, especially if the amount of the data will grow.