Stop 07 · BI Tools & DashboardsTopics · 4/9

Connecting BI Tools to Data Sources

In short

A dashboard is only as fresh and fast as its connection. Choosing between importing a copy and querying live affects speed, cost and how stale the numbers can get.

BI tools can connect live to a database or warehouse, or import a static snapshot — understanding this distinction matters for whether your dashboard reflects real-time data or needs manual refreshing.

The jaadu connects pipes from three water tanks into one tap filling a bucket.

Think of it like this

Bucket or tap

You can fill a bucket from the tank each morning and use it all day: quick to scoop, but not updated till tomorrow. Or connect a tap straight to the tank: always fresh, but slower if the pressure is low. Import is the bucket. Live is the tap.

Watch it happen

Import or live connection?

Import / Extract

Copy stored in the BI tool
Very fast charts
Fresh only after a refresh

DirectQuery / Live

Queries the source each time
Always current data
Slower, loads the database

Default to import for most dashboards. Go live only when minutes-old data truly matters and the source can handle the queries.

The big ideas

What you need to know

Common sources

Excel and CSV files, SQL databases, cloud warehouses, SaaS apps and Google Sheets.

Use a read-only account

Ask for a database user that can only read the tables you need. Never connect with an admin login.

Gateways for on-premise data

If the database sits inside the office network, a gateway program lets the cloud service reach it for refreshes.

Bring only what you need

Filter rows and columns at the source. Ten years of logs makes every refresh slow.

See it in code

Walk through the code

sql

1-- A view made for the dashboard2CREATE VIEW reporting.daily_sales AS3SELECT4  order_date,5  city,6  category,7  SUM(amount)  AS revenue,8  COUNT(*)     AS orders9FROM sales.orders10WHERE order_date >= DATE '2024-01-01'11  AND status = 'completed'12GROUP BY order_date, city, category;

1/4A view lives in the database, so every tool and person uses the same logic.

Connect the BI tool to a small, pre-summarised view instead of the raw orders table. Faster refreshes, one agreed definition.

Save yourself the pain

Common mistakes

Oops

Pointing the dashboard at a production table with millions of raw rows.

Do this instead

Connect to a summarised view or warehouse table built for reporting.

Oops

Saving your personal password in a shared report connection.

Do this instead

Use a dedicated read-only service account that the data team manages.

Your mission

Bucket vs tap, in practice

~25 min

Quick check

A sales dashboard is viewed by 200 people daily and needs yesterday's numbers. Best connection?

Go deeper

1Connect a BI tool to both a flat file and (if possible) a live database, and compare.
2Learn the difference between 'import' mode and 'live/direct query' mode.
3Understand the trade-off: live connections stay fresh but can be slower to load.