What is OLAP?
Most times used interchangeably, the terms Online Analytical Processing (OLAP) and data warehousing apply to decision support and business intelligence systems. OLAP systems help data warehouses to analyze the data effectively. The dimensional modeling in data warehousing primarily supports OLAP, which encompasses a greater category of business intelligence like relational database, data mining and report writing.
Data Warehouse Tutorial Video
Many of the OLAP applications include sales reporting, marketing, business process management (BPM), forecasting, budgeting , creating finance reports and others. Each OLAP cube is presented through measures and dimensions. Measures refers to the numeric value categorized by dimensions. In below diagrams, dimensions are time, item type and courtiers/cities and the values inside them (605, 825, 14, 400) are measures.
The OLAP approach is used to analyze multidimensional data from multiple sources and perspectives. The three basic operations in OLAP are:
- Roll-up (Consolidation)
- Drill-down
- Slicing and dicing
Roll-up or consolidation refers to data aggregation and computation in one or more dimensions. It is actually performed on an OLAP cube. For instance, the cube with cities is rolled up to countries to depict the data with respect to time (in quarters) and item (type).
On the contrary, Drill-down operation helps users navigate through the data details. In the above example, drilling down enables users to analyze data in the three months of the first quarter separately. The data is divided with respect to cities, months (time) and item (type).
Slicing is an OLAP feature that allows taking out a portion of the OLAP cube to view specific data. For instance, in the above diagram, the cube is sliced to a two dimensional view showing Item(types) with respect to Quadrant (time). The location dimension is skipped here. In dicing, users can analyze data from different viewpoints. In the above diagram, the users create a sub cube and chose to view data for two Item types and two locations in two quadrants.
Get 100% Hike!
Master Most in Demand Skills Now!
Multidimensional model (MOALP)
The databases that are configured for OLAP use multidimensional data model, enabling complex analysis and ad hoc queries at a rapid rate. The multidimensional data model is analogous to relational database model with a variation of having multidimensional structures for data organization and expressing relationships between the data. The data is stored in the form of cubes and can be accessed within the confines of each cube. Mostly, data warehousing supports two or three-dimensional cubes; however, there are more than three data dimensions depicted by the cube referred to as Hybrid cube.
As per the formal definition, “Each cell within a multidimensional structure contains aggregated data related to elements along each of the dimensions.” The multidimensional analytical databases are helpful in providing data-related answers to complex business queries quickly and accurately. Further, unlike other data models, OLAP in data warehousing enables users to view data from different angles and dimensions, thereby presenting a broader analysis for business purposes.
It has been observed that the OLAP cubes answers a query in 0.1% of the time consumed for the similar query by an OLTP (Online Transaction Processing) relational database.
OLAP systems are mainly classified into three :
- MOLAP (Multi-dimensional OLAP)
- ROLAP (Relational OLAP) : works with relational databases
- HOLAP (Hybrid OLAP): database divides data between relational and specialized storage
