Introduction
Why is OLAP still relevant and important? Because an effective OLAP solution solves problems for both business users and IT departments. For business users, it enables fast and intuitive access to centralized data and related calculations for the purposes of analysis and reporting. For IT, an OLAP solution enhances a data warehouse or other relational database with aggregate data and business calculations. In addition, by enabling business users to do their own analyses and reporting, OLAP systems reduce demands on IT resources.
OLAP offers five key benefits:
- Business-focused multidimensional data
- Business-focused calculations
- Trustworthy data and calculations
- Speed-of-thought analysis
- Flexible, self-service reporting
Business-Focused Multidimensional Data
As mentioned in the first sentence of this post, OLAP uses a multidimensional approach to organize and analyze data. In a multidimensional approach, data is organized into dimensions, where a dimension reflects how business users typically think of the business. For example, business users may view their data by product, by market, and over time. Each of these is a dimension in an OLAP application. Note that business users instinctively refer to dimensions after prepositions such as by (by product/by market), over (over time), or across (across business units).
A dimension can be defined as a characteristic or an attribute of a data set. Each dimension contains members that share the common characteristic. The members are often organized hierarchically within the dimension. For example, Figure 1-1 contains a few dimensions and their members. The Time dimension, which represents a year, is divided into quarters, and each quarter into respective months. The Products dimension contains product groupings and then the individual products within each grouping. The Markets dimension demonstrates a division into geographic regions divided further into states.
The hierarchical aspect of the dimension represents the first option for aggregation. For example, Quarter 1 summarizes the data for its child members January, February, and March. Time summarizes the data for all four quarters in the year. The aggregations are inherent in the hierarchy. The metadata in an OLAP system contains the aggregation rules, freeing the application from needing to define these aggregation rules and ensuring that these rules are applied consistently for each report or analysis.
OLAP enables one to organize data in a multidimensional model that makes it easy for business users to understand the data and to use it in a business context, such as a budget.
Business-Focused Calculations in OLAP
One reason OLAP systems are so fast is that they pre-aggregate values that would need to be computed on the fly in a traditional relational database system. The calculation engine handles aggregating data as well as business calculations. In an OLAP system, the analytic capabilities are independent from how the data is presented. The analytic calculations are centrally stored in the metadata for the system, not in each report.
Here are some examples of calculations available within an OLAP system:
- Aggregations, which simply roll up values based upon levels organized in hierarchies. For example, the application may roll up sales by week, month, quarter, and year.
- Time-series calculations with time intelligence, such as percent difference from last year, moving averages, and period-to-date values.
- Matrix or simple intradimensional calculations, such as share of parent or total, variances, or indexes. For those readers used to spreadsheets, this type of calculation replaces embedded spreadsheet formulae.
- Cross-dimensional or complex interdimensional calculations, such as index of expenses for current country to revenue for total United States. Someone using only spreadsheets would need to link spreadsheets and create formulas with values from different sheets to accomplish this type of calculation.
- Procedural calculations, in which specific calculation rules are defined and executed in a specific order. For example, allocating a shared expense, like advertising across products, as a percent of revenue contribution per product is a procedural calculation, requiring procedural logic to model and execute sophisticated business rules that accurately reflect the business.
- OLAP-aware calculations, with specialized functions such as ranking and hierarchical relationships. These calculations can include time intelligence and financial intelligence. For example, an OLAP-aware calculation would calculate inventory balances in which quarter 1 ending inventory is understood to be the ending inventory of March, not the sum of January, February, and March inventories.
- User-defined expressions, allowing a user to combine previously defined calculations using any operators and multidimensional functions.
Trustworthy Data and Calculations
When electronic spreadsheets, such as VisiCalc and Lotus 1-2-3, were released in the late 1970s and early 1980s, business analysts, who were already familiar with paper-based spreadsheets, embraced these new tools. Analysts would create spreadsheets starting from raw data and spend hours formatting and massaging the data into a form they could use. They would develop dozens to hundreds of these sheets. In turn, their organizations began to rely on an inordinate number of these manually produced spreadsheets for extremely important information.
Unfortunately, as soon as data starts living in spreadsheets, users start changing the data, entering new data, and creating calculations to augment what is already there. Soon, there are multiple definitions of something as basic as sales or profit. The resulting confusion gave rise to a phenomenon that came to be known colloquially as “spreadsheet hell.” To get a sense of the depth of the problem caused by spreadsheet hell, consider the following scenario: There are ten people in a room, each with his own spreadsheet containing his own metrics, formulas, and numbers. None of the spreadsheets contains exactly the same data. It becomes exceedingly difficult, if not impossible, for management to make sound business decisions when no one can agree on the underlying facts.
The problem is not limited to just spreadsheets. Many organizations have multiple reporting systems, each with its own database. When data proliferates, it is difficult to ensure that the data is trustworthy.
OLAP systems centralize data and calculations, ensuring a single source of data for all end users. Some OLAP systems centralize all data in a multidimensional database. Others centralize some data in a multidimensional database and link to data stored relationally. Still other OLAP systems are embedded in a data warehouse, storing data multidimensionally within the database itself. Regardless of the implementation details, what is important is that OLAP systems ensure end users have access to consistently defined data and calculations to support BI.
Speed-of-Thought Analysis
Speed-of thought analysis (also referred to as ad hoc analysis) means that analysts can pose queries and get immediate responses from the OLAP system. Not needing to wait for data means fewer interruptions in the analyst’s train of thought. The analyst can immediately pose another query based on the results of the first query, then another query, and so on, leading the analyst on a journey of discovery. Fast response times, together with intuitive, multidimensional organization of data, enable an analyst to think of and explore relationships that otherwise might be missed.
For example, consider a company that experiences a sudden increase in the number of customer complaints concerning late product shipments. In investigating the issue, the analyst drills down into the financial cube and discovers that profits are at a record high. She then drills down on the average age of the company’s payable invoices to discover that the average age is growing at a very high rate. Finally, the analyst drills down into inventories and discovers that raw materials are at low levels. From this analysis, she can draw the conclusion that the finance officer started paying invoices late, which improved short-term cash flow and profits, but now the company’s vendors are upset and shipping later. Late shipments of raw materials translates into late products and an increasing number of related consumer complaints. Speed-of-thought analysis is a key component that enables this kind of drill-down investigative work across multiple functional areas.
OLAP systems respond much faster to end-user queries than do relational databases that do not capitalize on OLAP technology. Quick response times are possible because OLAP systems pre-aggregate data. Pre-aggregation means that there is no need for many time-consuming calculations when an end-user query is processed. In addition, OLAP systems are optimized for business calculations, so calculations take less time to execute.
OLAP systems make the analysis process easy for analysts by supporting tools they already use. For example, many OLAP systems support commercial spreadsheet tools such as Microsoft Excel or offer their own spreadsheet interface.
Flexible, Self-Service Reporting
The best report designers and builders usually come from within the business community itself because they know what is needed. Enabling these people to create their own reports is a hallmark of an OLAP system.
OLAP systems enable business users to query data and create reports using tools that are natural for them to use. Providing tools that are familiar to end users means that their learning curve is reduced, so they are more likely to use the system. In addition to commercial and custom spreadsheet applications, OLAP systems support other front-end reporting tools that are designed with business users in mind. For example, they include user-friendly tools that enable report designers to create and publish web-based dashboards and interactive reports using live OLAP data. The consumers of interactive reports are often able to customize their view of the data.
When business users can build their own reports, it reduces the reliance on IT resources for generating reports. Without an OLAP system, IT departments are often called upon to create a multitude of materialized views and specialized reports for business users on demand.
As with any application geared to business users, the front-end tools must be intuitive and flexible enough to be employed by casual users. That said, as with any new tool, people need to be trained on how to use these reporting facilities effectively. If end users deem the system too hard to use, they will not adopt it.
Conclusion
The main idea that drove OLAP as a “thing” was a concept affectionately referred to as Spreadsheet Hell. The advent of XL, Lotus 123, VisiCalc (for us old timers), etc. created this hellish scenario. Business users flocked to the flexibility and ease of use. Subsequently, spreadsheet “models” became the norm. Said models are a great place to start your OLAP journey. Find them. Replace them. Take advantage of these five key benefits of OLAP for analytics and reporting.
Free eBook Download
In the eBook “Having a Conversation with Data”, learn what the current BI infrastructure has been and associated challenges with the traditional approach. How important the user experience is in order to best maximize data’s value (think visualizations!!) to your organization and how to gain a competitive advantage with modern analytics platforms.
