OLAP modeling and dashboards
What is OLAP?
OLAP is an acronym for online analytical processing. OLAP is a computing technique that enables users to extract and query data so they can analyze it from different perspectives. It’s the technical backend behind many business intelligence applications.
How can you use OLAP?
You can use OLAP for data discovery, data mining, and to understand relationships between different data points. OLAP is powerful because it enables you to perform ad hoc analysis on data, and see a new view of that data across different dimensions.
How does this translate to your everyday? Let’s say you own an orange orchard and wanted to break down how many oranges your company sold to farmer markets, grocery stores and juice manufacturers in sunny June and then compare those results with the number oranges sold in chilly January through the same channels. The result of this analysis would help you identify which channels had higher sales at which time of year. OLAP allows you to slice and dice your data and prepare it for analysis in this way.
Using OLAP technology for business queries and business intelligence helps you identify trends. OLAP technology is an indispensable tool for financial reporting, sales forecasting, budgeting, planning, and overall decision making. OLAP is often the driving force behind desirable CPM capabilities like report viewing, complex analytical calculations, and what-if scenario planning. Because OLAP technology enables you to aggregate data quickly and perform calculations on any multitude of data sets, it helps decision makers make more informed decisions faster.
How does OLAP work?
Relational databases, like Oracle or SQL, store transaction records in a two-dimensional, row-by-column format. OLAP databases, on the other hand, collect data from multiple data sources and pour them into a data warehouse. In the data warehouse, that data is cleaned. It's then sent off to a multidimensional database — known as a “data cube” in OLAP speak — where it's organized by dimensions like customer type, sales region, geography, and time period. Those dimensions are then connected hierarchically to details like customer name, country, and weeks. From here, users can then pose queries to the cube according to any dimension — an ability beyond spreadsheets or workbooks.
Types of OLAP Systems
There are three types of OLAP systems.
Multidimensional OLAP: MOLAP indexes data directly into a multidimensional database. It’s known for superior data processing.
Relational OLAP: ROLAP dynamic multidimensional analysis of data stored in a relational database. It’s known for superior data capacity.
Hybrid OLAP: HOLAP is a little bit of column a, a little bit of column b. This kind of OLAP combines the data capacity of ROLAP with the processing power of MOLAP.
What can you do to data in an OLAP database?
Drill-up: Also known as rolling-up, drill-up summarizes the data in the dimension.
Drill-down: Investigate a dimension on a deeper, more granular level. E.g. You could drill down company revenue by continent, by country, then by province or state, or even by city.
Slice: You slice the data according to a specific level, like orange revenues.
Dice: Dicing data allows you to pick and choose data from multiple dimensions for analysis. For example: orange revenues from grocery stores in June.
Pivot: Pivoting data allows you to see the data from a new angle.