3. Common data operations in OLAP

Let’s round things off with a sample OLAP query. Most OLAP queries use the following set of operations:

1. Joins: To combine information from different tables using the common keys
2. Filter: Identify rows that match certain criteria
3. Aggregate: Calculate aggregate statistics across different groups in the data. For instance, find the average sales by region.

Let’s use an example to explore these common OLAP operations. Assume that we want to understand how sales relate to the average income of the states they are located in using the tables shown in the illustration, but we only want to include stores that are located in cities with more than a million people.

The fact table only contains information for the daily sales for different stores. The stores dimension table contains the name of the city and state that a store is located in, while the city dimension includes the name of the city and state along with the population and average income.

The first task is to combine the store and cities tables. This would give us a table with each store, along with information on the population and average income for the city in which it is located.

The next step is to filter the rows in the table so that we only keep those that are in cities with a population greater than one million.

Now this smaller table can be combined with the larger sales table.

Next, we can group this table on the states to calculate the average store sales and income for each state.

OLAP encompasses a lot more than we can cover here. If you would like to learn more, I highly recommend these free courses that are available through EdX here. Finally, we only touched on OLAP solutions that use the relational data model where tables are connected to each other using keys. There is an entire space of database analytics called NoSQL that uses non-tabular data models to store and analyze data. You can learn more about them here.