PostgreSQL Cube

There might be situations where the data is very huge and the user would want to see summarized data involving heavy calculations. This might be simple if the number of combinations (columns) are less. But, if the number of combinations (columns) are more, then the flexibility for the user to choose various combinations will increase and this becomes difficult to handle. In such scenarios, implementing cube functionality makes it easier.

A Cube produces groupings and subtotals based on every permutation of all items in the Cube expression list and stores Pre-aggregated or summarized data, resulting in better performance (response times) than from a relational source.

If the number of columns specified in the Cube is n, then we will have 2ⁿcombinations.

Note: Cube functionality in PostgreSQL is available from PostgreSQL 9.5

Syntax:

	
SELECT
column1,
column2,
column3,
aggregate (column4)
FROM
table_name
GROUP BY
CUBE (column1, column2, column3);

Below is an example to generate cube data from a relational data source:

Consider a table (sales_data) showing weekly sales for different states

Select * from sales_data;

Create a cube using the syntax provided above

SELECT state, week, sum(sales)
FROM
    sales_Data
GROUP BY
    CUBE (state, week);

This cube has pre-aggregated data, storing State-wise and week-wise total sales.

Now, it is quite easy to get calculations for various combinations.

1. The total sales for all the states for week 1

select * from sales_data_cube
where state is null
and week is not null
and week = 1



2. The total sales for all the weeks for Uttar Pradesh state

select * from sales_data_cube
where state is not null
and week is null
and state = 'Uttar Pradesh'



3. The total sales for all the states for all the weeks

select * from sales_data_cube
where state is null
and week is null



4. The total sales for week 2 and Jharkhand state

select * from sales_data_cube
where state is not null
and week is not null
and state = 'Jharkhand'
and week = 2

In the above example, only 2 columns (State and week) were considered. More than 2 columns such as Country, State, Year, Quarter, Month, Week can also be created as a cube.

However, there is a limitation of 12 elements.

With the advantage of cube functionality, the SQL code required for the creation of reports is significantly simplified, the performance is also improved, as the base data is accessed only once.

In case if you have any queries please get us at support@helicaltech.com

Thank You
Sai Kavya Sathineni
Helical IT Solutions Pvt Ltd

Best Open Source Business Intelligence Software Helical Insight Here

A Business Intelligence Framework

Free
Download

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download