OLAP functions and tools, the ROLAP, MOLAP and HOLAP servers, and on-line analytical mining.
15.1 OLAP functions and tools
On-line analytical processing (OLAP) gives users fast, interactive analysis of data from many dimensions. E. F. Codd set out guidelines for OLAP tools, which include a multidimensional view of the data, transparency, accessibility, consistent performance, a client/server architecture and flexible reporting. Besides the five basic operations described in Chapter 3, OLAP tools provide several further functions, which the table below lists.
| Function | Example |
|---|---|
| Roll-up, drill-down, slice, dice and pivot | See Chapter 3, section 3.3 |
| Drill-across | Comparing "units sold" from the Sales cube with "units shipped" from the Shipping cube, month by month |
| Drill-through | Opening the individual bill lines behind the figure "Lucknow, Q4 electronics = ₹210 lakh" |
| Ranking | Listing the top 10 stores by margin this quarter |
| What-if analysis | Asking how grocery revenue and margin would change in each region if rice prices rose by 5% |
| Time intelligence | Showing year-to-date figures, the same period last year, or a moving three-month average |
Example (what-if analysis)Grocery revenue in the North region is ₹40 crore a quarter, and rice makes up 30 per cent of it, which is 0.30 × 40 = ₹12 crore. If rice prices rise by 5 per cent and customers buy the same quantity, rice revenue becomes 12 × 1.05 = ₹12.6 crore. North grocery revenue therefore rises by ₹0.6 crore to ₹40.6 crore, which is 0.6 ÷ 40 = 1.5 per cent.
Example (time intelligence)Lucknow's electronics sales were ₹60 lakh in October, ₹90 lakh in November and ₹60 lakh in December. The moving three-month average at December is (60 + 90 + 60) ÷ 3 = ₹70 lakh, and the year-to-date total at the end of December is the sum of all four quarters, 120 + 135 + 150 + 210 = ₹615 lakh.
ExplanationThese functions let a user ask a follow-up question within seconds, so the user's train of thought is not broken. Tools such as Power BI, Tableau, Oracle Essbase and Microsoft Analysis Services provide them.
15.2 OLAP servers
The figure shows that the client and the operations are the same for all three kinds of OLAP server. Only the way the data is stored underneath the server changes.
The table below compares the three kinds of OLAP server.
| Feature | ROLAP | MOLAP | HOLAP |
|---|---|---|---|
| Storage | Relational tables in a star or snowflake schema | Multidimensional arrays (cubes) | Summaries in cubes and detail in tables |
| Query speed | Slower, because SQL joins are needed | Fastest, because results are pre-computed | Fast for summaries |
| Scalability | Very high | Limited by the size of the cube | High |
| Sparse data | Handled well | Wastes space unless it is compressed | Handled well |
| Example product | MicroStrategy | Oracle Essbase | Microsoft SSAS in HOLAP mode |
Relational OLAP (ROLAP). A ROLAP server stores the data in relational tables and turns each request into SQL.
ExampleThe request "sales by category by month" is turned into a SQL GROUP BY query over the star schema, which holds 40 crore rows.
ExplanationROLAP needs no extra storage and has no limit on size. However, every question requires a join and an aggregation at the moment it is asked, so queries are slower.
Multidimensional OLAP (MOLAP). A MOLAP server stores the data in pre-computed multidimensional arrays.
ExampleA cube of 1,000 products × 200 stores × 365 days has 7.3 crore cells, and all of them are calculated in advance, so a query reads just one cell.
ExplanationMOLAP is very fast. However, the cube has 1,000 × 200 × 365 = 7,30,00,000 cells, and at 8 bytes per cell it needs 7,30,00,000 × 8 = 58,40,00,000 bytes, or about 584 MB, even before any totals are added. If only 10 per cent of the combinations of product, store and day actually have sales, then only 73 lakh cells hold data and 6.57 crore cells, or 90 per cent, are empty, which is called sparsity. Storing only the filled cells would need about 73,00,000 × 8 = 5.84 crore bytes, or about 58 MB, plus an index. Compression is therefore essential, and very large data may not fit in a cube at all.
Hybrid OLAP (HOLAP). A HOLAP server combines the two approaches.
ExampleMonthly and quarterly totals are stored in a MOLAP cube, while individual bills stay in relational tables. A dashboard reads the cube, and a drill-through to the individual bills reads the tables.
ExplanationUsers get the speed of MOLAP for common summaries and the scale of ROLAP for occasional detailed queries.
Desktop OLAP (DOLAP). A DOLAP tool downloads a small cube to the user's own computer.
ExampleA sales representative takes a cube of her territory on her laptop for a field visit where there is no network connection.
ExplanationShe can analyse her territory's figures offline, although the cube holds only a small part of the warehouse.
15.3 Data mining interface (OLAM)
On-line analytical mining (OLAM) combines OLAP navigation with data mining in a single environment.
ExampleAn analyst slices the sales cube to "North zone, Q4 2025" and drills down to individual customers. She then runs clustering on that slice of the data. One of the clusters, made up of young customers buying electronics on monthly instalments (EMI), looks interesting, so she drills into it and runs association rules to see what else these customers buy.
ExplanationOLAP narrows the data down to the part that matters, and mining then finds the patterns inside it. The results can be explored again with OLAP, so the analyst moves back and forth between the two. This works well because the warehouse already holds clean, integrated and summarised data.
Exam points
- ROLAP stores data in relational tables and generates SQL; it scales well but queries are slower.
- MOLAP stores pre-computed multidimensional arrays; it is fastest but limited by cube size and sparsity.
- HOLAP keeps summaries in cubes and detail in relational tables.
- Drill-through goes from a cube cell to the underlying detail rows; drill-across compares measures from different cubes.
- OLAM (on-line analytical mining) runs mining tasks on any slice of a cube.
Independent study notes for learning purposes. Not affiliated with or endorsed by any university, publisher or course provider. Product and company names are trademarks of their respective owners.