DEV Community

nash9
nash9

Posted on

Supabase with PowerBI Dashboard

#postgres #analytics #microsoft #tutorial

Real-Time Sales & Inventory Dashboard

A full-stack Business Intelligence demo integrating Supabase (PostgreSQL) as the cloud backend and Microsoft Power BI for frontend data visualization. This project demonstrates how to track sales revenue and monitor inventory levels in real-time.

Project Overview

  • Backend: Supabase (PostgreSQL) hosted on AWS.
  • Frontend: Microsoft Power BI Desktop.
  • Goal: Visualize sales trends, category performance, and low-stock alerts using cloud data.

Tech Stack

Setup Instructions

Part 1: Supabase Setup (Backend)

  1. Create a new project on Supabase.
  2. Navigate to the SQL Editor in the left sidebar.
  3. Run the following SQL script to create the schema and seed dummy data: Run schema.sql in SQL editor. 
-- 1. Create Tables
CREATE TABLE public.products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE public.orders (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES public.products(id),
    quantity INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE
);

-- 2. Insert Dummy Data
INSERT INTO public.products (name, category, unit_price, stock_quantity)
VALUES 
    ('Wireless Mouse', 'Electronics', 25.50, 150),
    ('Mechanical Keyboard', 'Electronics', 85.00, 40),
    ('Gaming Monitor', 'Electronics', 300.00, 15),
    ('Ergonomic Chair', 'Furniture', 150.00, 10),
    ('Desk Lamp', 'Furniture', 45.00, 80),
    ('USB-C Cable', 'Accessories', 12.00, 200);

INSERT INTO public.orders (product_id, quantity, total_amount, order_date)
VALUES 
    (1, 2, 51.00, CURRENT_DATE - INTERVAL '3 days'),
    (2, 1, 85.00, CURRENT_DATE - INTERVAL '3 days'),
    (1, 1, 25.50, CURRENT_DATE - INTERVAL '2 days'),
    (3, 1, 300.00, CURRENT_DATE - INTERVAL '2 days'),
    (5, 4, 180.00, CURRENT_DATE - INTERVAL '1 day'),
    (4, 1, 150.00, CURRENT_DATE),
    (6, 10, 120.00, CURRENT_DATE);
Enter fullscreen mode Exit fullscreen mode

Part 2: Power BI Connection (Frontend)

  1. To connect Power BI to Supabase, you must use the Connection Pooler (IPv4) to avoid connectivity issues. Get Credentials: Go to Supabase > Project Settings > Database.
  2. Enable "Use connection pooling". Set Mode to "Session".
  3. Copy the Host (e.g., aws-0-us-east-1.pooler.supabase.com) and User.
  4. Connect in Power BI: Get Data > PostgreSQL database. Server: Paste the Pooler Host URL. Database: postgres. Data Connectivity Mode: Import. Auth: Use Database authentication (User/Password).

Dashboard Visuals :

KPI Card: Displays Total Sales Revenue (Sum of total_amount).
Pie Chart: Sales distribution by Category (Electronics vs. Furniture).
Table: A specific list for Low Stock Alerts (Items with stock_quantity < 20).

first load

schema

Troubleshooting : (I have faced this issue ,fix and error details added)

"The remote certificate is invalid" Error
Supabase uses SSL, and Power BI may reject the certificate by default.
Fix: Go to File > Options and settings > Data source settings. Select the data source, click Edit Permissions, and uncheck "Encrypt connections".
"Host not found"
Ensure you are using the Pooler URL (port 5432 or 6543) found in Supabase Database settings, not the direct connection string.

Thanks for reading !!!

Top comments (0)