OLAP (Online Analytical Processing) technology brings the data in a business intelligence system to life. OLAP “cubes” serve data to interactive reports, and empower the user of the report by allowing them to interact with the data they see on the screen. Through actions like “drilling” or “slicing and dicing”, OLAP reports give business people a way to see not only that something is happening, but what is causing it. When a sales manager views an OLAP report showing declining sales in a region, they can dive into that region straight from the report to see which salespeople are not making quotas. Optionally, from the same report, the reader could also see which customers are not buying or which products are not selling in that region.

OLAP technology works closely with data warehouses to serve both summarized and historical information. With OLAP technology and a data warehouse working together, that product report from 2004 doesn’t languish in a filing cabinet or someone’s spreadsheet; it remains a valuable contributor to your organization’s ongoing knowledge and awareness.

OLAP technology also results in fast reports. Your monthly accounting trial balance report that runs directly against your company’s General Ledger may be slow; your accountants may have to wait while accounting reports are processed, slowing the delivery of each month-end close in the process. These same reports in OLAP are available online all the time, and offer excellent response time without impacting your General Ledger system. Since OLAP technology stores both your business’s detailed information (such as accounting entries) and aggregated information, the results aggregated to the highest level of large multinational corporations are available quickly.

Of course, the uses of OLAP are not limited to accounting. OLAP can be used to provide reports and analysis over any of your business data that generate aggregated information. Other examples of OLAP uses are sales data, purchasing data, budgets, and forecasts, to name a few.

Technology Overview

Online Analytical Processing (OLAP) reporting enhances BI solutions by making up for many of the inefficiencies of reporting from a relational database. Like relational reports, OLAP reports show data at various levels of detail, for instance the individual lines of sales invoices as well as summarized sales data for the whole company. The difference between relational and OLAP reporting is in speed and flexibility.

Application databases store granular data. To build a report that shows less detailed data (for instance sales data for a region), you must group and aggregate. Optionally, you could build and update aggregate tables to service that particular report. Either approach is inefficient; the first has relatively slow response time, while the second requires extra development, support, and administration. OLAP databases work with relational databases to make “rolled up” business data available without incremental development or administration.

OLAP databases come in different flavors to enable maximum flexibility in analytical reporting scenarios:

• Multidimensional OLAP (MOLAP) databases store data aggregations directly in a multidimensional database, providing very quick report response time.
• Relational OLAP (ROLAP) databases have an OLAP structure for reporting flexibility, but fetch data at runtime through dynamic SQL queries, resulting in very quick build times and very low latency.
• Hybrid OLAP (HOLAP) cubes are a mixture of MOLAP and ROLAP, storing data that is used most often and querying less used information only when needed. This provides a mix of quick response time, short build time, and low latency.

Since data aggregation is resource-intensive, you would expect the amount of aggregation that OLAP databases perform to require a lot of time and storage space. OLAP technology benefits from a multidimensional data structure that provides excellent aggregation and storage performance through matrix math and dimensional indexing. OLAP therefore provides superior report response times with quick build times. The latest OLAP platforms, such as SQL Server Analysis Services 2005, have options to make the OLAP database available nearly 24x7. As such, OLAP technology complements the raw data storage capabilities of relational databases to provide quick response times over a large volume of data.

Traditional reports tend to be static, not easily enabling the user to “drill into” the data or easily change the structural layout of a report. OLAP databases provide data through a flexible framework that facilitates the “drilling” and “slicing and dicing” of a grid of data. This framework is multidimensional in nature, and is modeled after the dimensions of a business rather than by referential rules. Using OLAP, reports showing product information by customer over a year can be modified to show the quarterly sales of a product group very quickly. OLAP queries also draw from a single source of metrics and calculations; business calculations can be defined in OLAP, and reused for each query that requires them, rather than being rewritten in each report.

Northridge Systems implements OLAP exclusively using SQL Server Analysis Services 2005. With the introduction of the Unified Dimensional Model (UDM), Analysis Services 2005 provides an industry leading platform for OLAP with a level of analytical flexibility that simply was not available in prior platforms. Analysis Services 2005 also provides flexibility in data storage modes (MOLAP, ROLAP, & HOLAP) that spans the capabilities often available only with multiple BI products from other vendors, and data modeling capabilities that allow analytical data to be refreshed in near real-time.

For more detailed information about Reporting and Decision Support services offered by Northridge Systems, please send us an information request or email sales@northridge.com