Use Power BI when:
☐ Users want to filter, slice and drill on their own
☐ Requirements change often
☐ Analysts or managers will maintain reports
☐ The audience is internal

Hand-code when:
☐ It is one or two simple charts
☐ The chart is part of the product experience
☐ Licensing is not an option for the audience

// npm install chart.js
import Chart from "chart.js/auto";

const res = await fetch("/api/reports/monthly");
const rows = await res.json();   // [{month: "2026-01", total: 1200}, ...]

new Chart(document.getElementById("revenue"), {
  type: "line",
  data: {
    labels: rows.map(r => r.month),
    datasets: [{ label: "Revenue", data: rows.map(r => r.total) }],
  },
});

CREATE VIEW v_orders_reporting AS
SELECT o.id            AS order_id,
       o.created_at::date AS order_date,
       c.city          AS customer_city,
       p.category      AS product_category,
       s.label         AS order_status,
       o.total         AS order_value
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN products p     ON p.id = o.product_id
JOIN order_status s ON s.code = o.status;
-- (PostgreSQL) Power BI connects to this view, not the raw tables

-- give Power BI its own read-only database login
CREATE ROLE bi_reader LOGIN PASSWORD 'use-a-strong-secret';
GRANT CONNECT ON DATABASE shop TO bi_reader;
GRANT USAGE ON SCHEMA public TO bi_reader;
GRANT SELECT ON v_orders_reporting TO bi_reader;

Embedded report flow for an app
1. User logs into YOUR app
2. Your backend authenticates to Power BI as an app (service principal)
3. Backend requests an embed token for the report, with the user's identity for row-level security
4. Backend returns embed URL + token to the frontend
5. Frontend renders the report using the Power BI JavaScript client
6. Tokens expire; backend issues fresh ones when needed
Note: embedding for customers requires appropriate Power BI capacity/licensing — check with your admin.

// npm install powerbi-client
import * as pbi from "powerbi-client";

const { embedUrl, token, reportId } = await (await fetch("/api/bi/embed-info")).json();
const service = new pbi.service.Service(
  pbi.factories.hpmFactory, pbi.factories.wpmpFactory, pbi.factories.routerFactory);

service.embed(document.getElementById("report"), {
  type: "report",
  id: reportId,
  embedUrl,
  accessToken: token,
  tokenType: pbi.models.TokenType.Embed,
});

Reporting Requirements
Audience: internal / customers
Key questions the report answers:
1.
2.
Data sources: tables / views / APIs
Refresh need: real-time / hourly / daily
Access rules: who sees which rows?
Location: separate dashboard / embedded in app
Owner after launch: developer / analyst / BI team

Data Contract for BI
View name: v_orders_reporting
Columns and types:
  order_id BIGINT, order_date DATE, customer_city TEXT,
  product_category TEXT, order_status TEXT, order_value NUMERIC
Guarantees: no duplicate order_id; order_date never null
Change policy: new columns allowed; renames announced 2 weeks ahead
Contact: ______________

Handover to BI team
☐ Read-only user created and credentials shared securely
☐ Reporting views documented
☐ Sample queries provided
☐ Refresh schedule agreed
☐ Row-level security rules listed
☐ Contact for schema changes named