OLAP in Data Warehouse

What is OLAP?

OLAP, an acronym for 'Online Analytical Processing' is a technique by which the data sourced from a data warehouse or data mart is visualized and summarized to provide perspective multidimensional view across multiple dimensions. Generally OLAP refers to OLAP Tools(e.g Cognos, Business Objects etc.,) that help to accomplish these tasks. Since data warehouse is designed using a dimensional data model, data is represented in the form of data cubes enabling us to aggregate facts, slice and dice across several dimensions. OLAP tools provide options to drill-down the data from one hierarchy to another hierarchy.

For example sales amount can be calculated for a particular year or it can be drilled down to its next hierarchies like month, week, day etc. In the same way, data can be rolled up for summarization from product to product group, product group to product sub-class then from product sub-class to product class. Thus with this cube structure, data can be viewed from multiple points providing the data analysts, a greater insight into data.

There are many OLAP hybrids or variants like MOLAP(Multidimensional OLAP), HOLAP(Hybrid OLAP), ROLAP(Relational OLAP), DOLAP(Desktop OLAP or Database OLAP) available in the market and can be used depending on the needs and requirements of an organization

OLAP - Examples

Topmost executives of an organization are really interested in aggregated facts or numbers to take decisions rather than querying several databases (that are normalized) to get the data and do the comparison by themselves. OLAP tools visualize the data in an understandable format, like in the form of Scorecards and Dashboards with Key Performance Indicators enabling managers to monitor and take immediate actions. In todays business life, OLAP plays a vital role by assisting decision makers in the field of banking and finance, hospitals, insurance, manufacturing, pharmaceuticals etc., to measure facts across geography, demography, product, and sales.

OLAP can be performed in data warehouses that undergo frequent updates and that do not. Following are some of the examples to show how OLAP solves complex queries involving facts to be measured across company’s best-interested dimensions.

• Comparison of sales (fact) of a product (dimension) over years (dimension) in the same region (dimension).

• How may members (fact) have opened a savings account (dimension), in USA branch (dimension), over a period (dimension)?

• How many mortgage loans (fact) have been approved in fixed mortgage (dimension) or Adjustable Rate Mortgage (dimension) in New York City (dimension), over a period (dimension)?

• What is the total sales value (fact) of a particular product (dimension) in a particular grocery store (dimension), over a period (dimension)?

• What is the amount spent (fact) for a particular product promotion (dimension) in a particular branch (dimension) or in a particular city (dimension), over a period (dimension)?