Best Practices for Handling Calculations in a Live Data source

Published: Nov 19, 2024

The best approach to performing calculations in the live data source is to pre-aggregate fields in the backend (database). This article explains how to implement and manage pre-aggregated fields in live databases. These fields store computed aggregate values, optimizing performance for queries that require frequent aggregation.

Steps to Implement Pre-Aggregated Field

1. Create the Orders Table

First, define a table named orders with columns for storing basic information such as name, age, email, and creation timestamp.

CREATE TABLE MySQL_DataBase.orders (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Auto-incrementing primary key
    name VARCHAR(50),                  -- Name column
    age INT,                           -- Age column
    email VARCHAR(100),                -- Email column
    created_at DATETIME                -- Timestamp column
);

2. Insert Sample Data

Next, populate the table with sample records for demonstration purposes.

INSERT INTO MySQL_DataBase.orders (name, age, email, created_at) VALUES
('John Doe', 30, 'john.doe@example.com', NOW()),
('Jane Smith', 25, 'jane.smith@example.com', NOW()),
('Alice Brown', 28, 'alice.brown@example.com', NOW()),
('Bob White', 35, 'bob.white@example.com', NOW()),
('Charlie Black', 40, 'charlie.black@example.com', NOW());

3. Add a Pre-Aggregated Column

Add a new column named AggregatedField to store the calculated value.

ALTER TABLE MySQL_DataBase.orders ADD COLUMN AggregatedField INT;

4. Update Pre-Aggregated Field

Calculate and update the AggregatedField column using a derived table to avoid Error Code: 1093.

UPDATE MySQL_DataBase.orders 
SET AggregatedField = (
    SELECT TotalCount 
    FROM (
        SELECT COUNT(id) AS TotalCount 
        FROM test2.orders
    ) AS DerivedTable
);

Benefits of Using Pre-Aggregated Fields

Performance Optimization: Reduces the need to recalculate aggregate functions on large datasets.
Simplified Queries: Allows reports and dashboards to directly utilize pre-computed values.
Scalability: Enhances performance for systems that handle high query loads.

Best Practices

Indexing: Ensure appropriate indexes on fields used in calculations.
Triggers or Scripts: Automate the update of pre-aggregated fields when data changes.
Batch Updates: For large datasets, consider batch updates to minimize performance impact.

Example Query

To retrieve data from the orders table, including the pre-aggregated field, use the following query

SELECT id, name, age, email, created_at, AggregatedField 
FROM MySQL_DataBase.orders;

Connect this pre-aggregated database in Bold BI^®, and create a dashboard by adding charts, grids, or KPIs to display the pre-aggregated metrics.

For example, use the AggregatedField column to display the total count directly in a KPI widget.

By implementing pre-aggregated fields as demonstrated in this article, you can significantly enhance query efficiency and improve the overall performance of database-driven applications.

Additional References

Was this article useful?

Dislike