This month we stray from MDX query basics as we try to explain why OLAP is such a hot technology. Also, we answer some important questions about OLAP database maintenance. And as always, we end with the MDX Puzzle section.
Why Bother with OLAP?
OLAP technology is a tool that solves certain types of problems. Like any other tool, it's hard to fully appreciate and understand OLAP until you understand this tool's purpose. For example, you probably know that the TV remote control is one of the most important inventions of the century. But could a time traveler from the pre-remote days appreciate the remote control's true greatness? Of course not, because the time traveler wouldn't understand the remote control's purpose.
OLAP might not be as cool as a remote control, but it's almost impossible to read a computer trade magazine today without reading about OLAP. Why is OLAP a hot topic, and who needs OLAP solutions? OLAP solutions help people make better decisions.
Many people confuse OLAP with online transaction processing (OLTP). However, you use OLAP and OLTP for different purposes.
Consumer banks need to conduct customers' transactions quickly and accurately. So, OLTP systems must guarantee that a bank's systems and employees apply transactions correctly, and such systems must process transactions quickly and support countless users.
In a generic way, OLTP systems accept a stimulus, perform processing, and respond. If Joe BankGuy transfers money from his checking account to his savings account, the transaction is successful if all the money ends up in the right place. The success of a transactional system is based on whether the system detects and correctly responds to an event.
As Figure 1 shows, OLTP systems help you detect and correctly respond to problems because a transaction isn't complete without some type of reporting capability. For example, when Joe BankGuy uses an ATM to transfer money from his checking account to his savings account, he requests a receipt for his records. Another simple type of reporting occurs when Joe calls the bank to ask about his checking account balance. As Figure 2 shows, these requests, operational reports, help you determine a transaction's current status by measuring where the transaction is, what it's doing, and how well it's performing.
Almost any system can potentially benefit from an OLAP approach to answering important business questions. OLAP solutions (remember that the "A" in OLAP stands for analytical) help you make better decisions, not by creating reports, but by generating new insights. This distinction is important. An operational report is functionally correct if the information accurately measures the state of a transaction. Did the ATM receipt show the correct date, time, and amount of money Joe transferred from checking to savings? The report (i.e., the ATM receipt) is correct if the information is correct.
OLAP systems are successful if they give you a new insight to use to make a better decision, as Figure 3 illustrates. In other words, OLAP systems convey information and understanding by letting users explore highly aggregated information and not individual transactions. OLAP systems help users find trends or relationships in historic business information or venture into the future with what-if scenarios.
For example, in banking systems, management-level decision-makers can answer: Which loan officers tend to write the most risky auto loans? What are the cash withdrawal patterns at busy ATM machines? What account activity patterns can you detect immediately before customers close their accounts? What customer service action can you take to head off this event and keep customers? Which customers fit a certain demographic profile that would qualify them for a new loan product that you're about to launch?
Using traditional reporting techniques to answer such questions is sometimes difficult and often impossible. To make matters worse, some developers classify report writing as boring, unchallenging work that is best left for junior developers. But such questions are important, and many developers or analysts would be challenged by creating an OLAP system that can effectively answer these questions.
Companies can greatly benefit from reporting systems. However, most companies are using the wrong type of system. Instead of using OLAP systems, they are using traditional OLTP systems to try to answer complex questions.
So why is OLAP such a hot topic? OLAP is hot because, for the first time, technology and mass-market tools are offering decision-makers better ways to understand available information. Understanding it creates insights for better decisions.
Does your organization want to make better decisions? Does your organization have untapped data? If you answered yes to these questions, you'll benefit from an OLAP solution.