---
title: "Help Guide on OLAP Operations"
url: https://docs.catalyst.zoho.com/en/cloud-scale/help/data-store/olap-database/olap-operations-guide/
description: "Catalyst Data Store is a highly-scalable, cloud-based relational database that stores the persistent data of your app that includes the data from the application’s backend and the data of the application’s end users."
last_updated: 2026-02-16T15:02:46Z
service: Cloud Scale
type: general
---

# 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.

<table class="content-table">
<thead>
		<tr>
			<th>sale_id	</th>
			<th>region</th>
            <th>product</th>
			<th>year</th>
            <th>quarter</th>
			<th>amount</th>
		</tr>
	</thead>
<tbody>
		<tr>
			<td>101</td>
			<td>East</td>
            <td>Laptop</td>
			<td>2024</td>
            <td>Q1</td>
			<td>900</td>
		</tr>
        <tr>
			<td>102</td>
			<td>East</td>
            <td>Laptop</td>
			<td>2024</td>
            <td>Q2</td>
			<td>1100</td>
		</tr>
        <tr>
			<td>103</td>
			<td>East</td>
            <td>Phone</td>
			<td>2024</td>
            <td>Q1</td>
			<td>500</td>
		</tr>
        <tr>
			<td>104</td>
			<td>West</td>
            <td>Laptop</td>
			<td>2024</td>
            <td>Q1</td>
			<td>800</td>
		</tr>
        <tr>
			<td>105</td>
			<td>West</td>
            <td>Phone</td>
			<td>2024</td>
            <td>Q2</td>
			<td>600</td>
		</tr>
        <tr>
			<td>106</td>
			<td>West</td>
            <td>Laptop</td>
			<td>2024</td>
            <td>Q2</td>
			<td>700</td>
		</tr>
        <tr>
			<td>107</td>
			<td>North</td>
            <td>Phone</td>
			<td>2024</td>
            <td>Q1</td>
			<td>300</td>
		</tr>
        <tr>
			<td>108</td>
			<td>North</td>
            <td>Phone</td>
			<td>2024</td>
            <td>Q2</td>
			<td>400</td>
		</tr>
    </tbody>
</table>

### 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.

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

It will generate the following output:

<table class="content-table">
<thead>
		<tr>
			<th>region</th>
			<th>year</th>
            <th>Sum(amount)</th>
		</tr>
	</thead>
<tbody>
		<tr>
			<td>East</td>
			<td>2024</td>
            <td>2500</td>
		</tr>
        <tr>
			<td>North</td>
			<td>2024</td>
            <td>700</td>
		</tr>
        <tr>
			<td>West</td>
			<td>2024</td>
            <td>2100</td>
		</tr>
    </tbody>
</table>

### 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.

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

It will generate the following output:

<table class="content-table">
<thead>
		<tr>
			<th>region</th>
			<th>year</th>
            <th>quarter</th>
            <th>Sum(amount)</th>
		</tr>
	</thead>
<tbody>
		<tr>
			<td>East</td>
			<td>2024</td>
            <td>Q1</td>
            <td>1400</td>
		</tr>
        <tr>
			<td>East</td>
			<td>2024</td>
            <td>Q2</td>
            <td>1100</td>
		</tr>
        <tr>
			<td>North</td>
			<td>2024</td>
            <td>Q1</td>
            <td>300</td>
		</tr>
        <tr>
			<td>North</td>
			<td>2024</td>
            <td>Q2</td>
            <td>400</td>
		</tr>
        <tr>
			<td>West</td>
			<td>2024</td>
            <td>Q1</td>
            <td>800</td>
		</tr>
        <tr>
			<td>West</td>
			<td>2024</td>
            <td>Q2</td>
            <td>1300</td>
		</tr>
    </tbody>
</table>

### 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.

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

It will generate the following output:

<table class="content-table">
<thead>
		<tr>
			<th>region</th>
            <th>Avg(amount)</th>
		</tr>
	</thead>
<tbody>
		<tr>
			<td>East</td>
			<td>700</td>
		</tr>
        <tr>
			<td>North</td>
			<td>300</td>
		</tr>
        <tr>
			<td>West</td>
			<td>600</td>
		</tr>
    </tbody>
</table>

### 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. 

```sql
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:

<table class="content-table">
<thead>
		<tr>
			<th>region</th>
            <th>Count(sale_id)</th>
		</tr>
	</thead>
<tbody>
		<tr>
			<td>East</td>
			<td>2</td>
		</tr>
        <tr>
			<td>West</td>
			<td>2</td>
		</tr>
    </tbody>
</table>