Help Guide on OLAP Operations

This section includes examples of some of the common OLAP operations you can perform on the OLAP database using ZCQL.

Example Database:

Given below is a sample table that contains the sales data of products by region, year, and quarter.

Roll-Up

Roll-up involves aggregating or summarizing the data to a higher-level hierarchy, reducing granularity. For example, summarizing the data from quarter to year, or from individual sales amount to a total.

This example query will roll up from a quarter-level sales to a yearly sales total, per region.

SELECT region, year, Sum(amount) FROM sales_fact GROUP BY region, year ORDER BY region

It will generate the following output:

Drill-Down

The drill-down operation moves down from a summary-level to a lower level in the concept hierarchy, providing a more detailed view of the data and adding new dimensions.

This example query will drill down and provide totals for each quarter, instead of yearly totals.

SELECT region, year, quarter, Sum(amount) FROM sales_fact GROUP BY region, year, quarter ORDER BY region, quarter

It will generate the following output:

Slicing

The Slice operation selects a single dimension from the cube where data is represented, and creates a slice of the cube with reduced dimensionality. This operation helps focus on a specific data slice for analysis.

This example query will provide the average Q1 sales amount per region.

SELECT region, Avg(amount) FROM sales_fact WHERE quarter = 'Q1' GROUP BY region ORDER BY region

It will generate the following output:

Dicing

The Dice operation applies multiple filters on two or more dimensions of the cube where the data is represented, to form a smaller sub-cube. It provides a multidimensional subset of the original data cube.

This example query will count the number of Laptop sales in the East and West regions during Q1 or Q2 of 2024.

SELECT region, Count(sale_id) FROM sales_fact WHERE product = 'Laptop' AND region IN ('East', 'West') AND quarter IN ('Q1', 'Q2') AND year = 2024 GROUP BY region ORDER BY region

It will generate the following output: