Grafana Fundamentals: Connecting Data to Dashboards

Install · Import transactions into MySQL · Build a transaction-risk dashboard

Learning Objectives

• ✓ Explain what Grafana is and where it sits in a data architecture
• ✓ Import the Lecture 10 transaction CSV into MySQL and connect Grafana
• ✓ Understand the Grafana mental model: data sources → queries → panels → dashboards
• ✓ Build a complete three-panel dashboard from the transactions table
• ✓ Match business questions to suitable visualisations
• ✓ Distinguish between Grafana's use cases vs Atlas Charts (Assignment 3)

Session Timeline

Time	Segment	Activity
0–10 min	Recap	From Kafka storage to business decisions
10–20 min	What Is Grafana?	Architecture, users, and business value
20–30 min	Grafana Mental Model	Data sources, queries, panels, dashboards
30–45 min	Setup & Connection	Run MySQL and Grafana with Docker
45–55 min	Existing Dataset	Import the Lecture 10 transactions CSV
50–80 min	Dashboard Lab	Build a Stat, Bar Chart, and Table panel
80–88 min	Interpretation	Read the dashboard and recommend an action
88–90 min	Wrap-Up	Save the dashboard and preview Lecture 15

Section 1: What Is Grafana?

The Problem It Solves

In Modules 2 and 3, you built the full backend: Kafka producers, consumers, MongoDB storage, alert routing, and windowing. All that work produces data — but the data lives in a database only engineers can query. Grafana closes this gap. It turns database queries into visual panels that refresh automatically, putting your streaming data in front of the people who need to act on it.

What It Is	What It Isn't
A dashboard and visualisation platform	A database
A query builder (UI on top of your data)	A streaming system
An alerting layer with notification routing	A replacement for MongoDB/Kafka
Open source + free for self-hosted use	An analytics/BI tool (that's Metabase, Tableau)

Who Uses Grafana

2014 — Created by Torkel Odegaard as a fork of Kibana
2021 — Grafana Labs valued at $3 billion
2024 — ~20 million users, 200,000+ organisations
      Default monitoring dashboard at:
      Netflix, PayPal, Booking.com, Zepto, Swiggy, Paytm, HDFC Securities

Section 2: The Grafana Mental Model

Four Building Blocks

1. Data Source — A connection to a database. This lab uses MySQL because Grafana supports it without an Enterprise plugin.
2. Query — The question you're asking of the data source. This lab uses SQL.
3. Panel — One visual unit: one chart, one number, one table. Each panel has exactly one query and one visualisation type.
4. Dashboard — A collection of panels on a grid. Has a time range selector and auto-refresh interval.

Time as a First-Class Citizen

Unlike static BI tools, Grafana treats time as the primary dimension. Every dashboard has a time range (last 5 min, last 1 hour, custom). This makes it ideal for streaming data — you can zoom into the exact window when an alert fired.

Section 3: Setup & Connection

Run MySQL and Grafana with Docker

# Create one network so Grafana can reach MySQL by container name
docker network create sda-analytics

docker run -d \
  --name sda-mysql \
  --network sda-analytics \
  -e MYSQL_ROOT_PASSWORD=admin \
  -p 3306:3306 \
  mysql:8.4 \
  --local-infile=1

docker run -d \
  --name grafana \
  --network sda-analytics \
  -p 3000:3000 \
  -e GF_SECURITY_ADMIN_PASSWORD=admin \
  grafana/grafana:latest

# Open http://localhost:3000 and log in with admin / admin

Add the Built-In MySQL Data Source

Section 4: Import the Existing Transactions

Reuse the transactions.csv generated in Lecture 10. This is the same dataset used in the Kafka fraud exercise; MySQL is only an additional analytics destination for Grafana.

Fields Used Today

Field	Example	Business Meaning
transaction_id	TXN-00847	Unique transaction reference
amount	4500.00	Transaction value imported as a decimal
city	Mumbai	Location where the transaction occurred
merchant	Amazon	Merchant receiving the payment
timestamp	2025-08-01 10:00:00	Business event time imported as DATETIME
is_fraud	true	Classroom fraud label

Create the Table and Import the CSV

Run these commands from the folder containing transactions.csv:

docker cp transactions.csv sda-mysql:/var/lib/mysql-files/transactions.csv
docker exec -it sda-mysql mysql -uroot -padmin

Then run this SQL in the MySQL prompt:

CREATE DATABASE IF NOT EXISTS sda_course;
USE sda_course;

DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions (
  transaction_id VARCHAR(20) PRIMARY KEY,
  user_id VARCHAR(20),
  user_name VARCHAR(100),
  card_last4 VARCHAR(4),
  amount DECIMAL(12,2),
  merchant VARCHAR(100),
  city VARCHAR(50),
  country VARCHAR(50),
  lat DECIMAL(9,4),
  lon DECIMAL(9,4),
  `timestamp` DATETIME,
  is_fraud VARCHAR(5),
  ingested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

LOAD DATA INFILE '/var/lib/mysql-files/transactions.csv'
INTO TABLE transactions
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(transaction_id,user_id,user_name,card_last4,amount,merchant,
 city,country,lat,lon,`timestamp`,is_fraud);

CREATE USER IF NOT EXISTS 'grafana'@'%' IDENTIFIED BY 'grafana';
GRANT SELECT ON sda_course.* TO 'grafana'@'%';
FLUSH PRIVILEGES;

SELECT COUNT(*) AS total_transactions,
       SUM(is_fraud = 'true') AS flagged_transactions
FROM transactions;

Section 5: Hands-On Lab — Transaction Risk Dashboard

Scenario: You manage fraud operations for a digital bank. Your team needs one screen that answers three questions: What percentage of transactions are flagged? Which cities carry the most transaction value? Which individual transactions require review?

Step 1 — Create and Name the Dashboard

1. Go to Dashboards → New → New Dashboard.
2. Select Add visualisation, then choose the MySQL data source.
3. Click Save dashboard and name it Transaction Risk Overview — Lecture 14.
4. Because this is a fixed historical dataset, set a custom dashboard time range of 1 August–31 August 2025.

Step 2 — Panel A: Flagged Transaction Rate

This panel answers: “What percentage of transactions are labelled as fraud?”

SELECT
  100.0 * SUM(is_fraud = 'true') / COUNT(*) AS value
FROM transactions;

1. Choose the Stat visualisation.
2. Set the title to Flagged Transaction Rate.
3. Set the unit to Percent (0–100) and decimals to 1.
4. Add thresholds: green below 3%, amber from 3%, red from 5%.
5. Click Apply. The generated dataset should display 3.8% in amber.

Step 3 — Panel B: Transaction Value by City

This panel answers: “Where is the bank's transaction value concentrated?”

SELECT city, SUM(amount) AS value
FROM transactions
GROUP BY city
ORDER BY value DESC;

1. Add a visualisation and choose Bar Chart.
2. Set the title to Transaction Value by City.
3. Use city as the category and value as the numeric field.
4. Set the value unit to Indian Rupee and orientation to horizontal.
5. Click Apply.

Step 4 — Panel C: Flagged Transactions for Review

This panel answers: “Which transactions should an analyst inspect first?”

SELECT
  transaction_id,
  `timestamp`,
  user_name,
  merchant,
  city,
  amount
FROM transactions
WHERE is_fraud = 'true'
ORDER BY amount DESC
LIMIT 20;

1. Add another visualisation and choose Table.
2. Set the title to Flagged Transactions — Review Queue.
3. Organize columns as Transaction ID, Timestamp, Customer, Merchant, City, Amount.
4. Set Amount to Indian Rupee and sort it descending.
5. Click Apply.

Step 5 — Arrange and Refresh

1. Place the Fraud Rate Stat panel at the top left.
2. Place the city Bar Chart beside it and give it more width.
3. Place the Review Queue Table across the full width below.
4. Set dashboard refresh to 30 seconds and save.

Step 6 — Read the Dashboard Like a Manager

1. Observe: Read the fraud-rate KPI before looking at individual rows.
2. Prioritise: Use the review queue to identify the highest-value flagged transactions.
3. Contextualise: Compare city exposure before treating a high count as unusual.
4. Decide: Recommend investigation or monitoring; the label is a signal, not proof of fraud.

Section 6: Panel Types Reference

Panel Type	Best For	Example Use
Time Series	Trends over time	Transaction count or value per hour
Stat	Single important number	Fraud rate or total transaction value
Bar Gauge	Progress / comparison	Flagged transactions by merchant
Bar Chart	Categorical comparison	Transaction value by city
Table	Raw data / detail view	Flagged transactions requiring review
Gauge	Single value with thresholds	Risk score: green/amber/red

Key Takeaways