​
Overview

This how-to guide walks through the steps to implement serverless metrics and Business Intelligence (BI) by using Hydra’s analytics tables. Serverless analytics capabilities enable scalable and cost effective Business Intelligence reporting that do not compete for provisioned database resources.

​
Key Features

  • Analytics tables: Analytics tables use a columnstore format by default, optimized for read-heavy, complex analytical queries and generating reports.
  • Serverless processing: Compute resources scale automatically based on query demand, eliminating server resource contention
  • High Performance: Analytics tables enables efficient data compression and faster query execution for aggregations and joins with rowstore tables.

​
Set up steps

Create a project

Connect a reporting tool

Create dashboards and visualizations

​
1. Create a project

Navigate to the Hydra platform to create project: enter a project name, choose and plan, and modify hardware specs in the “scaling and storage” dropdown - then select the “Create Project” button at the bottom of the page.

​

  1. Connect a reporting tool

There are many good options for metrics and reporting tools, such as Metabase, Tableau, PowerBI, and more. In this how-to example, we will use Metabase: it’s open source, has a visual query builder, and it’s a fairly user-friendly option for creating interactive dashboards. These instructions will work with any reporting tool with a standard Postgres connector.

  • Select a reporting tool with a PostgreSQL support connector or integration.
  • Configure to connect to Hydra. Navigate to the ‘Credentials’ section in Hydra’s ‘Dashboard’ tab. In the drop down menu, select ‘env’ to retreive the host, port, database name, and credentials.
    • Host: Database endpoint. Look like ‘PGHOST=hy-009d8…’
    • Port: 5432
    • Database: Name of the target database.
    • Credentials: Username and password

Copy these credential paste them into t reporting tools’ integration UI. Then, test the connection to ensure the BI tool can query Hydra’s analytics tables.

​
3. Build metrics and dashboards

  • Reading directly from Hydra’s analytics tables for fast query performance on large datasets.
  • Create visualizations (e.g., revenue trends, regional sales) using the reporting tool’s interface. Here are Metabase’s simple guide and video on how to simply create interactive dashboards and visualizations.

Idea: create an analytics table in Hydra with your largest tables, such as the user event’s table. Data in an analytics table is automatically compressed. When queries are executed they are done by efficient serverless processes.

​
Ideal tables for reporting and metrics

  • Sales transactions table
  • Event logs
  • Financial transaction tables
  • IoT sensor telemetry
  • Customer Behavior

Table Examples:

analytics.sales_transaction

CREATE TABLE analytics.sales_transaction (
    order_id VARCHAR(50),            -- Unique identifier for the order
    customer_id VARCHAR(50),         -- Customer who made the purchase
    product_id VARCHAR(50),          -- Product purchased
    quantity INTEGER,                -- Number of items
    price NUMERIC,                   -- Price per item
    order_date TIMESTAMP,            -- Date and time of the order
    store_id VARCHAR(50),            -- Store or channel identifier
    region VARCHAR(50)               -- Geographic region of the sale
);

analytics.event_logs

CREATE TABLE analytics.event_log (
    event_id VARCHAR(50),            -- Unique identifier for the event
    user_id VARCHAR(50),             -- User who triggered the event
    event_type VARCHAR(50),          -- Type of event (click, login, etc)
    event_timestamp TIMESTAMP,       -- When the event occurred
    session_id VARCHAR(50),          -- Session identifier
    ip_address VARCHAR(45),          -- IP address of the user
    metadata JSONB                   -- Addit. event details (browser, device)
);

analytics.financial_transaction

CREATE TABLE analytics.financial_transaction (
    transaction_id VARCHAR(50),      -- Unique identifier for the transaction
    account_id VARCHAR(50),          -- Account involved
    amount NUMERIC,                  -- Transaction amount
    transaction_date TIMESTAMP,      -- Date and time of the transaction
    transaction_type VARCHAR(50),    -- Type (deposit, payment)
    status VARCHAR(20),              -- Status (pending, completed)
    currency VARCHAR(10)             -- Currency code (USD, EUR)
);

analytics.iot_sensor

CREATE TABLE analytics.iot_sensor (
    sensor_id VARCHAR(50),           -- Unique identifier for the sensor
    device_id VARCHAR(50),           -- Device generating the data
    metric_value NUMERIC,            -- Measured value (temperature, pressure)
    metric_type VARCHAR(50),         -- Type of measurement
    event_timestamp TIMESTAMP,       -- When the measurement was taken
    location GEOGRAPHY,              -- Geospatial location of the sensor
    status VARCHAR(20)               -- Sensor status (active, offline)
);

analytics.customer_behavior

CREATE TABLE analytics.customer_behavior (
    customer_id VARCHAR(50),         -- Unique identifier for the customer
    event_type VARCHAR(50),          -- Type of interaction (purchase, click)
    event_timestamp TIMESTAMP,       -- When the interaction occurred
    product_id VARCHAR(50),          -- Product involved (if applicable)
    campaign_id VARCHAR(50),         -- Marketing campaign tied to interaction
    session_id VARCHAR(50),          -- Session identifier
    page_url VARCHAR(255),           -- URL or page visited
    amount_spent NUMERIC,            -- Monetary value of the interaction
    churn_score FLOAT,               -- Predicted likelihood of churn
    lifetime_value NUMERIC           -- Estimated total value of the customer
);