The sacred CRUD (Create, Read, Update, Delete). It has been my daily bread.

However, things break when a company grows and they ask for "a sales report crossed by geographical area and product family for the last three years".

Last week, naive as I am, I threw a SELECT with I-don't-know-how-many JOINs and several GROUP BY clauses directly against the production database (OLTP). The result? The system locked up completely for ten minutes. The time has come to accept that transactional systems are useless for analytics. Welcome to the world of Business Intelligence (BI).

From OLTP to OLAP: A mindset shift

The fundamental problem is that a database designed to capture rapid transactions (OLTP) is highly normalized (to avoid redundancies) and optimized for writing. But when you want to analyze trends (OLAP), you need to read millions of historical records.

The solution I'm implementing involves setting up a Data Warehouse: a separate database, updated early in the morning through an ETL (Extract, Transform, and Load) process.

Getting our hands dirty: A handcrafted mini-ETL

Instead of installing super heavy suites like Pentaho or Informatica, I have started to orchestrate a basic ETL process using SQL scripts that run via cron.

The idea is to denormalize the transactional data and load it into a "Fact Table" that is much wider and optimized for fast reading.

-- 1. EXTRACTION AND TRANSFORMATION (Staging Environment)
-- We join data from multiple normalized transactional tables
CREATE TEMPORARY TABLE temp_ventas_transformadas AS
SELECT 
    v.id_venta,
    v.fecha,
    c.region_comercial,
    p.categoria_producto,
    v.cantidad * p.precio_unitario AS importe_total
FROM db_transaccional.ventas v
JOIN db_transaccional.clientes c ON v.id_cliente = c.id_cliente
JOIN db_transaccional.productos p ON v.id_producto = p.id_producto
WHERE v.fecha >= DATE_SUB(CURDATE(), INTERVAL 1 DAY);

-- 2. LOAD (Data Warehouse)
-- We insert the data into our denormalized analytics table
INSERT INTO db_analitica.fact_ventas_diarias (
    id_venta, 
    fecha_sk, -- Time surrogate key
    region, 
    categoria, 
    importe
)
SELECT 
    id_venta,
    REPLACE(CAST(fecha AS CHAR), '-', ''), -- YYYYMMDD format for time dimension
    region_comercial,
    categoria_producto,
    importe_total
FROM temp_ventas_transformadas;

Now, when the report is needed, the query attacks db_analitica.fact_ventas_diarias directly. The reports that used to crash the server for ten minutes now run in two seconds.

Reflection: The gold is in the data

Going from programming interfaces to input data to designing architectures to understand that data has blown my mind. BI seems like black magic to managers, but deep down, it's pure data engineering and smart modeling.

I firmly believe that Business Intelligence will stop being a luxury of Fortune 500 companies. With increasingly powerful open-source databases, any SME is going to start demanding its own Data Warehouses. The future lies in knowing what to do with the terabytes of information we are accumulating. Leave Java and start studying BI.