Creating a successful business intelligence environment means finding a middle ground between two extremes: independent implementations driven by business units and the integrated data warehouse architecture defined by IT departments. On one hand, IT departments must accept the fact that they cannot give their customers intuitive, free-form access to information and "speed of thought" query response times without incorporating OLAP technology into their data warehouse designs. IT is never going satisfy the needs of the typical business manager simply by enabling a limited group of business analysts to execute canned queries directly against the "galactic" data warehouse using fat-client, managed query environment (MQE) tools that generate SQL directly from client workstations. ROLAP servers also generate SQL to an RDBMS, but they do so from a midtier server that sits right on top of the RDBMS, thereby taking most of the load off both the client workstation and the network. Both analytical flexibility and query response times will always suffer. On the other hand, business managers must learn to recognize the value of having a "common prayer book" to read from so that all the numbers and all the terminology match on all reports across all departments. This ensures that managers wonıt waste their time arguing about whose numbers are right. Only a central data warehouse data source can provide this consistency.
So what is OLAPıs proper place in the business intelligence architecture? In Building the Data Warehouse (John Wiley & Sons, 1996), W.H. Inmon says, "There are four levels in the architected environment ı the operational, the atomic or the data warehouse, the departmental, and the individual."
Operational systems are the source of the raw, transaction-level data that populates the data warehouse. Operational data is current, volatile, transaction-level data stored in normalized or proprietary form in an OLTP system.
The atomic data warehouse layer is a read-only archive that contains subject-oriented historical snapshots of the same transaction-level data scrubbed and rearranged into a multidimensional format more suitable for decision support. At the data warehouse layer, a single fact table may have a record for every transaction, and each record will contain a full range of statistics (such as orders, revenues, and costs) and as many as two dozen or more descriptive fields representing the entire potential universe of qualifying business dimensions (such as time, location, customer, and product) covering a broad content subject area (such as product sales data or general ledger cost data). However, once such a database grows to a size of any consequence, it becomes dangerous (from an IT resources perspective) to provide typical managers with direct access to it, and it becomes impossible to provide speed-of-thought response times to queries posed against it. This is why there is the need for the departmental level.
In his same book, W. H. Inmon writes, "The departmental level is sometimes called the ıdata martı level, the ıOLAPı level, or the ımultidimensional DBMSı level." OLAP technology should be employed at this layer in the architecture. An OLAP data mart will be limited in scope to the relatively small subset of available statistical measures and dimensions needed to study a specific, well-bounded business problem. Limiting a data martıs scope ensures that the resulting data mart will fit within the scalability limits of an OLAP database server and permits the analysis at hand to be conducted without the distractions presented by extraneous data. Using an OLAP database server, in turn, allows the use of OLAP indexing and presummarization techniques to deliver rapid response times and intuitive access.
The OLAP layer of the architecture should bear the brunt of the query load. In a well-designed business intelligence environment, 80 percent or more of the queries will be posed to the data marts and the OLAP servers ı thin clients should be the rule. The remaining queries that demand access to transaction-level data will generally be limited to "market basket" sales analysis queries ("How many customers bought both diapers and beer in a single transaction") and data mining processing or nonanalytical administrative follow-up queries ("Give me a list of transactions for these customers whose accounts are in arrears, plus their phone numbers and addresses").
When the data arrives in the warehouse it must be ready (cleaned and transformed) for immediate redistribution to subscribing data marts. Its basic dimensional structure must already be defined and reflected in the warehouseıs star-schema relational database design. It should also have a central metadata repository that catalogs the contents and status of the warehouse. The OLAP server should be able to read, directly from the warehouse tables, both the data and the metadata required to restructure and update each data mart with its required subset of measures, dimensions, and records.
Furthermore, the architecture must be comprehensive and flexible enough that new marts can be quickly created and existing marts can be quickly redefined, simply by selecting new combinations of measures and dimensions from those already existing in the warehouse "library" in response to new or redefined business requirements. When OLAP databases fail to scale up, it is usually because the architect tried to anticipate every possible business requirement for every possible subject area and then tried to stuff the contents of the entire warehouse into a single, fully presummarized OLAP "data waremart." Battle-scarred business analysts often demand such a solution because theyıre afraid (and, historically, with good reason) that if they donıt ask for it now, theyıll never be able to get it in the future. Data warehousing is necessarily a process of continual, iterative development. It is impossible to anticipate all analytical requirements up front, and you shouldnıt even try. What you must do is give yourself the ability to adjust your data mart designs rapidly in response to fluid perceptions of business needs.
In any case, all successfully designed architectures will include both a relational data warehouse layer and an OLAP data mart layer. When a data warehousing initiative fails, it is frequently because the OLAP layer has either been built as a stovepipe apart from the data warehouse (the acquiring business units are usually at fault) or left out entirely. (The IT departmentıs reluctance to embrace OLAP technology is usually to blame.)
At this point it is worthwhile to touch briefly on the MOLAP vs. ROLAP debate. MOLAP servers (also known as MDDBs) store data in multidimensional array-like data structures. ROLAP servers use metadata to map star-schema relational databases into multidimensional views.
Just as organizations use a variety of desktop query and analysis tools, most companies will also need different kinds of OLAP servers at the data mart layer of their architectures. Interactive planning applications such as demand forecasting, financial analysis, and resource allocation will typically require an MDDB database server that offers uniform speed-of-thought query response times and read-write capabilities. Read-write is a key distinction in favor of MDDBs. On the other hand, applications such as retail sales analysis or customer loyalty marketing that require databases with hundreds of thousands of continually changing products or customers and numerous attributes such as customer demographic fields or product characteristics will demand a ROLAP server. The ability to handle the concept of "attributed" well is a key distinction in favor of ROLAP. Thus, the relevant question is not "Should I buy a MOLAP or ROLAP server," but rather "How do I integrate them both into my architecture?" Look closely and youıll find that many of the companies, such as Sears, Federal Express, and Target Stores, that are walking away with data warehousing "best practices" awards are on the customer lists of both MOLAP and ROLAP vendors for precisely this reason.
At the final layer of Inmonıs architecture, data is presented to the analyst for interpretation. This usually means a temporary, graphical presentation of data created on the desktop PC (although, increasingly, this presentation is created on a remote Web server and is merely viewed on the desktop through a Web browser). Graphical ad hoc query tools (both OLAP and MQE), "big button" EIS presentations, report writers, Web browsers, data visualization tools, and spreadsheets all reside at this level of the architecture. Data warehousing experts frequently recommend limiting business analysts to the execution of canned queries when using MQE type clients to query directly against the data warehouse layer. However, nontechnical analysts using an OLAP client to query against an OLAP data mart can enjoy free reign to explore the mart on their own terms, without worrying about inaccurate responses, slow response times, or bringing the server to its knees.
Custom decision-support applications that create new information such as budgets, forecasts, recommended resource allocations, and the like also reside above the data marts at this level of the architecture, as do tools such as statistical analysis packages and data mining tools. The new information created by these processes should be written back to OLAP data marts. The "target" data marts for these outputs can thereby serve as information clearing houses for derived information that is useful across the entire enterprise, such as customer demand forecasts.
OLAP sits smack dab in the middle of the business intelligence architecture, bridging the gap between the atomic data warehouse layer and the individual presentation layer. Deploying OLAP technology effectively requires seamless integration with both adjoining layers. Building a successful business intelligence environment requires integrating a variety of OLAP servers and front-end tools. In an ideal solution, users should be able to perform their analysis without having to be aware of the physical form of the underlying database.
What did you think of this article? Send a letter to the editor.