cloudparts

Dashboards and BI

Business intelligence tools let people who don’t write SQL explore a warehouse through charts, filters and dashboards: authors define datasets and metrics once, and the tool turns every click into a query, caches the answer and limits rows to what each viewer may see.

5 technologies on this page ↓

How it works

A BI tool starts from a connection to a warehouse, an OLAP database or another SQL source. Superset, for example, talks to any database with a Python DB-API driver and a SQLAlchemy dialect. An author then registers a dataset: a table or a saved SQL query, plus a thin model on top, such as metrics (SUM(amount)), calculated columns, joins and friendly names. Power BI calls this a semantic model, Looker writes it in LookML, and Superset keeps it in its dataset editor. Viewers pick a chart type, a metric and the columns to group and filter by, and the tool generates the SQL, usually a GROUP BY query, in the source’s dialect. A dashboard is a page of such charts with shared filters, and a SQL editor lets analysts query the source directly. Data reaches the charts in one of two ways. Live queries send SQL to the source on each view, with a cache in front: Looker reuses results for identical queries for an hour by default, and Superset keeps chart data in Redis or another Flask-Caching backend. Or the tool imports a copy into its own compressed in-memory engine on a schedule: Power BI Import mode (VertiPaq), Tableau extracts (.hyper) and Amazon Quick Sight’s SPICE. Row-level security adds a filter per user or role to every generated query, so one dashboard serves every region or customer. To embed dashboards in another product, the product’s backend asks the BI tool for a short-lived token that names the dashboard and the viewer’s row filters, and the page shows it in an iframe.

Think of it as

A car’s dashboard. Engineers decide which sensors feed which gauge and how to scale them; the driver just reads speed and fuel at a glance, and nobody opens the bonnet to check the oil.

Reach for it when

  • Many people who don’t write SQL need the same numbers every day or week: revenue against plan, sign-ups, orders by region
  • Self-service: people want to filter, drill down and build their own charts on curated datasets without asking an analyst each time
  • Customer-facing analytics in a product: embed dashboards with per-tenant row-level security instead of building charts yourself
  • Scheduled reports and alerts on warehouse data, run by the BI tool’s workers

Think twice when

  • One-off exploration of unfamiliar data: a notebook or SQL editor is quicker than building a dataset and a dashboard first
  • Watching services in production (latency, errors, CPU): an observability tool such as Grafana reads metrics and log stores and alerts on them
  • Live dashboards on a per-scan-billed warehouse that many people open: each uncached view can become a query, so use caching, an imported copy or rollups
  • Metrics that several tools and apps must agree on: define them in a semantic layer that the BI tool reads, not only inside one BI tool’s model

A real example

A retail chain loads the day’s sales into its warehouse every night. An analyst registers the orders table as a dataset, adds the metrics revenue = SUM(amount) and orders = COUNT(*), and builds one dashboard: revenue by week, top products and a map of stores, with a region filter. Forty store managers open it each morning. Chart results are cached until the next load, so the warehouse runs each chart’s query once per role instead of once per manager. A row-level security rule gives the North managers’ role the filter region = 'North', so each manager sees only their own stores; finance has no rule and sees everything. Later the company puts the same dashboard in its supplier portal: the portal’s backend requests a short-lived token for each supplier with a filter on their supplier ID, and the page embeds the dashboard in an iframe.

Technologies

Specific products, and what each does differently.

Also used for dashboards and bi

Products of other kinds that are often used this way, grouped by what they are.

What each provider calls it

AWS
Amazon Quick Sight, the BI part of Amazon Quick (formerly Amazon QuickSight)
Google Cloud
Looker (governed BI on LookML models), Data Studio (formerly Looker Studio; free, plus Data Studio Pro)
Azure
Power BI, part of Microsoft Fabric
Open source & others
Apache Superset, Metabase (AGPL); BI as code: Evidence, Observable Framework; commercial: Tableau (Salesforce), Hex

Live queries or an imported copy?#

Most BI tools offer both, per dataset or even per table. The choice decides freshness, speed and who pays for each view.

Live queriesImported copy
How it worksEach chart or filter change sends SQL to the source; the tool may cache the resultThe tool loads the data into its own compressed in-memory engine and answers from there
FreshnessAs fresh as the source, minus the cache timeoutAs fresh as the last refresh; Power BI allows 8 scheduled refreshes a day on shared capacity and 48 on Premium
Speed and sizeDepends on the source and the query; good on a fast warehouse or OLAP databaseFast once loaded; Power BI Import models compress about 10 times, but the whole model must fit in memory
Cost on per-query sourcesEvery uncached view can scan dataThe source is queried when the copy is created and refreshed; Quick Sight reuses SPICE data without further source charges
ExamplesSuperset, Looker, Power BI DirectQuery, Quick Sight direct query, Tableau live connectionsPower BI Import (VertiPaq), Tableau extracts (.hyper), Quick Sight SPICE
  • The two mix: Power BI composite models set Import, DirectQuery or Dual per table, and a hybrid table keeps older partitions imported and the newest rows live.

Row-level security, and where it leaks#

Each tool turns “who is looking” into a filter, but the rules differ. Superset assigns a filter clause such as department = 'finance' to datasets and to users, groups or roles, and adds it to the generated query’s WHERE clause; Regular filters apply to the listed subjects, Base filters to everyone except them, and filters with the same group key are combined with OR. Power BI defines roles in the semantic model as DAX expressions such as [Region] = "West" or [UserEmail] = USERPRINCIPALNAME(). Quick Sight attaches a permissions dataset that lists a user or group and the values they may see in each restricted column.

The edges matter more than the happy path. Power BI applies row-level security only to workspace Viewers: Admins, Members and Contributors can edit the model and see every row. Quick Sight dataset owners also see all data, users with no rule see nothing, and rules work only on text columns. When a Power BI report is embedded with a service principal, USERPRINCIPALNAME() returns the app, not the person, so the embedding app must pass an effective identity in the embed token. Superset embedding works the same way: the host app’s backend requests a guest token, which can carry rls rules, and the embedded SDK shows the dashboard in an iframe.

In code · python

import sqlite3

warehouse = sqlite3.connect(":memory:")
warehouse.executescript("""
    CREATE TABLE orders (region TEXT, product TEXT, amount REAL);
    INSERT INTO orders VALUES ('North', 'tea', 12), ('North', 'cake', 30),
                              ('South', 'tea', 8),  ('South', 'cake', 25);
""")

# 1. The dataset: a table plus named metrics, defined once by an author
dataset = {"table": "orders",
           "metrics": {"revenue": "SUM(amount)", "orders": "COUNT(*)"}}

# 2. Row-level security: each role adds a WHERE clause (None = sees all rows)
rls = {"north_manager": "region = 'North'", "finance": None}

cache = {}  # generated SQL -> rows

def chart(metric, group_by, role):
    """What a chart does on every view: build SQL, check the cache, query."""
    sql = f"SELECT {group_by}, {dataset['metrics'][metric]} FROM {dataset['table']}"
    if rls[role]:
        sql += f" WHERE {rls[role]}"
    sql += f" GROUP BY {group_by} ORDER BY {group_by}"
    if sql not in cache:  # the key is the final SQL, so roles never share rows
        print("  warehouse <-", sql)
        cache[sql] = warehouse.execute(sql).fetchall()
    return cache[sql]

print(chart("revenue", "product", "north_manager"))  # runs the query: [('cake', 30.0), ('tea', 12.0)]
print(chart("revenue", "product", "north_manager"))  # same SQL: from the cache
print(chart("revenue", "product", "finance"))        # no filter, other SQL: [('cake', 55.0), ('tea', 20.0)]

Often confused with

Semantic layersData notebooksReal-time analytics databasesData warehousesStatic read modelsObservability
Where it came from1958–2026

The term is older than the products. In October 1958 Hans Peter Luhn of IBM published “A Business Intelligence System” in the IBM Journal of Research and Development, describing an automatic system to deliver information to the people in an organisation who need it, “a communication facility serving the conduct of a business”. In November 1991 Business Objects filed a patent, granted in 1996, for letting end users query relational databases without knowing their structure or SQL, through business terms mapped onto the tables. In January 2003 Pat Hanrahan, Chris Stolte and Christian Chabot founded Tableau to turn Polaris, a visualisation research project from Stanford, into a product.

The cloud brought BI as a service priced per user. In July 2015 Microsoft made the new Power BI generally available, with a free tier and Power BI Pro at $9.99 per user per month. In October 2015 AWS previewed QuickSight, built around SPICE, its “Super-fast, Parallel, In-memory Calculation Engine”. The same year Maxime Beauchemin started Superset at an Airbnb hackathon as a fast front end for the Druid database; Airbnb open-sourced it in March 2016 under the name Caravel, it became Superset that November, and it graduated to a top-level Apache project in January 2021. The big vendors consolidated: Salesforce completed its purchase of Tableau on 1 August 2019, and Google completed its acquisition of Looker in February 2020.

The latest shift wraps BI in AI assistants. In October 2025 AWS folded QuickSight into Amazon Quick Suite, an agentic workspace, and its BI features became Quick Sight. In April 2026 Google renamed Looker Studio back to Data Studio, keeping Looker as its governed enterprise platform built on a central semantic model. Superset now ships an MCP server that lets AI clients list dashboards, query datasets and create charts.

Why it’s built this way

A model between the tables and the charts
People think in metrics and business terms, not table structures. Business Objects’ 1991 patent mapped business objects onto SQL, and today’s tools keep the idea as datasets, semantic models or LookML, so viewers pick “revenue” and the tool writes the query.
Copy the data into memory, or query it live
Querying the source on every click can be slow, or costly on per-query sources, so Power BI (Import), Tableau (extracts) and QuickSight (SPICE) can keep a compressed copy that answers quickly. The copy is only as fresh as its last refresh, so the same tools also offer live queries.
Security in the query, not in the dashboard
Copying a dashboard per region or per customer doesn’t scale. Adding a per-user filter to every generated query lets one dashboard serve everyone, including customers who see it embedded in another product.

Timeline

  1. 1958

    Hans Peter Luhn publishes “A Business Intelligence System” at IBM

  2. 1991

    Business Objects files its patent for querying databases through business terms

  3. 2003

    Tableau is founded to commercialise Stanford’s Polaris research

  4. 2015

    Power BI becomes generally available; AWS previews QuickSight with its SPICE engine

  5. 2016

    Airbnb open-sources Superset, first released as Caravel

  6. 2019

    Salesforce completes its acquisition of Tableau

  7. 2025

    QuickSight becomes Quick Sight, part of Amazon Quick Suite

  8. 2026

    Google renames Looker Studio back to Data Studio

Sources15

Improve this page on GitHub