What is OLAP?
OLAP (online analytical processing) is a technology that processes multidimensional data which allows for users to view data from multiple perspectives. The essence of OLAP is that it stores data in multiple dimensions rather than tabular relations found in relational databases, which enables users to analyze data from any point of view.
OLAP is the foundation for many business intelligence tools. It offers the capabilities for complex analysis such as drill-down or slice-and-dice functions as well as trend modeling through multidimensional databasing in OLAP cubes. For example, if you want to compare the sales of your product in Washington, D.C., in March compared to New York at the same date, OLAP allows you to process your data in OLAP cubes to retrieve and visualize your data at any level of your data hierarchy.
3 types of OLAP
- Multi-dimensional (MOLAP): MOLAP is a term used to specify multidimensional-OLAP, which is the classic form of OLAP described above.
- Relational (ROLAP): This does not use a multidimensional database, but relies on queries to a relational database to extract data to answer questions in an OLAP fashion. It features similar features to MOLAP with some limitations.
- Hybrid (HOLAP): HOLAP works by holding large sets of data in relational databases while holding more aggregate data information in OLAP cubes in order to attain the value of the other two OLAP models.
An OLAP cube is a term used to refer to a multi-dimensional arrays of data. Multi-dimensional data works similar to having a subject with various related descriptions. For example, you may have a product with dimensions such as time, region, and revenue where product, time, location, and revenue are the dimensions. Each dimension has a hierarchy such as your location having country, state, and county level data.
4 Main methods of data manipulation
- Slicing: A user “slices” out a column of data out of a specific dimension with one fewer dimension. For example, you may have data organized in a cube by product type, location and revenue by year and you want to know the sales figures of a specific product type by year. You would slice out the product type dimension to extract data about the revenue by year, which would also leave out the location dimension (one less dimension).
- Dicing: Dicing acts like a zoom feature allowing you to select a specific subcube to view the specific values of multiple dimensions. Similar to the example above, you may have data organized by product type, location, and revenue by year. You could “zoom” into a subcube of a specific product type to reveal more detailed information of the product type, regional information of the location, as well as quarters of the year.
- Drill-down: Users may traverse the hierarchy of data by moving from summary sets of data to more detailed sets. For example, you can drill-down a broad category of location such as country to the state level to reveal information at that level for the other dimensions.
- Roll-up: This allows you to summarize data by combining all the levels of the hierarchy. For example, you can roll-up the data about sales in many states to form a summary of data at the national level.
Benefits of OLAP
- Fast performance through multidimensional indexing and caching.
- Efficient data extraction and computation through structured data aggregation.
- Compacted data through compression allowing for smaller disk storage sizes.
- OLAP (online analytical processing) is a technology that processes multidimensional data which allows for users to view data from multiple perspectives.
- There are three types of OLAP techniques: MOLAP, ROLAP, and HOLAP.
- OLAP processes multidimensional data queries through the storage of information in OLAP cubes.
- OLAP cubes enable the various methods of analysis such as drill down.