How to Aggregate Data Based on Dimension Column

Aggregated tables are used to improve the performance of dashboard rendering for large data sets. They are created from a base table by grouping the data based on specific requirements. In this article, we will discuss how to create aggregated tables in Bold BI and in a PostgreSQL source server.

Creating Aggregated Tables in Bold BI

To create an aggregated table in Bold BI, you can use a custom query by grouping data and extracting it into Bold BI. Here’s an example:

SELECT SUM(id), date, MONTH(date) AS month
FROM test
GROUP BY date, MONTH(date)

This query returns lesser data while providing the same insights as the original data. You can use this query to extract data in Bold BI directly.

Reference: Working with Custom Query Extract Mode in Bold BI

Creating Aggregated Tables in Source PostgreSQL Server

To create an aggregated table in a PostgreSQL source server, follow these steps:

1. Create a table with the same schema as the base table.
2. Insert data into the aggregated table by selecting the data grouped by dimension fields.

Example

Consider a base table with the following schema:

• Sessions int
• Users int
• Country string
• Date datetime

We can group the sessions and users based on country and date. To do this, create a similar table and fill it with grouped data.

Aggregate Based on String

Insert table query:

INSERT INTO derived_table
SELECT COUNT(Sessions) AS Sessions, COUNT(Users) AS Users, Country, Date
FROM base_table
GROUP BY Country, Date

Aggregate Based on Date

We can also aggregate data based on date ranges, such as grouping based on year, month, or day. In this case, we need to add a column (year(date)/ month(date)/ day(date)) to the derived schema based on the requirement.

Aggregate Based on Month (Date)

Step 1: Create a table with a month column.

CREATE TABLE derived_table (
  Sessions int,
  Users int,
  Country int,
  Date datetime,
  UsageMonth int
)

Step 2: Insert data after grouping.

INSERT INTO derived_table
SELECT COUNT(Sessions) AS Sessions, COUNT(Users) AS Users, Country, Date, MONTH(Date) AS UsageMonth
FROM base_table
GROUP BY Country, Date, MONTH(Date)