BI tools tour

Why it matters

Python is ideal for analysis and one-off charts, but most organisations share regular reporting through business intelligence (BI) toolsSoftware such as Power BI, Tableau and Looker Studio that organisations use to share regular reporting and dashboards.: Power BI, Tableau, Looker Studio, Looker, Qlik, Metabase, Apache Superset and others. Job postings for data analysts mention at least one of these more often than not. The good news is that they share the same core ideas, so learning one makes the next much easier.

What a BI tool does

Almost every BI tool has the same five parts:

1. Data connection. Connect to a spreadsheet, CSV, database or warehouse (Google Sheets, Excel, PostgreSQL, BigQuery, Snowflake...). Some tools import a copy; others query the source live.
2. Data model. Define tables and how they relate (for example, orders joined to customers on customer_id), set data types, and mark which fields are dimensions and which are measures.
3. Calculated fieldsMetric formulas defined once in the BI tool so every chart uses the same definition. and measures. Formulas such as Revenue = SUM(quantity * unit_price) or Conversion rate = buyers / visitors. Defining a metric once, centrally, is what keeps every chart consistent.
4. Visuals. Drag fields onto a chart: a dimensionA field you group or filter by, such as category, date or region. (region, month) on an axis, a measureA number you aggregate, such as sum of revenue or count of orders. (revenue) as the value.
5. Interactivity and sharing. Filters, date pickers, drill-down, scheduled refresh, and sharing with permissions.

Dimensions vs measures is the key vocabulary. A dimension is something you group or filter by (category, date, region). A measure is a number you aggregate (sum of revenue, count of orders, average delivery time). Most confusing BI results come from aggregating the wrong way, such as averaging an average.

A quick tour of free options

Pricing and features change, so check each vendor's site before relying on a feature. Free tiers are enough to learn and to build portfolio pieces. Never upload confidential or personal data from work or school to a public or personal account.

Worked example: a sales dashboard in Looker Studio

1. Put a small dataset in Google Sheets: columns date, region, category, units, revenue (a few hundred rows is plenty; you can generate them with pandas or use a public sample dataset).
2. In Looker Studio, create a blank report and add the sheet as a data source. Check each field's type: date should be a Date, revenue a Number (currency).
3. Add a scorecardA Looker Studio tile showing one headline number, optionally with a comparison to a previous period. for total revenue and set "comparison date range" to the previous period. This gives you the number plus a percentage change.
4. Add a time series chart with date (granularity: month) and revenue.
5. Add a bar chart with category as the dimension, revenue as the metric, sorted descending.
6. Add a date range control and a drop-down control for region. Every chart on the page now responds to them.
7. Add a text box with the data source and a one-line definition of revenue.
8. Use Share (view-only) to send it to a friend for feedback, or keep it private.

Power BI Desktop follows the same path: Get data, check types in Power Query, build visuals on the report canvas, add slicers (filters), and write measures in DAX, such as Total Revenue = SUM(Sales[revenue]).

Common mistakes

• Aggregating twiceAveraging values that are already averages, which gives a wrong overall figure; compute ratios from sums instead.. Averaging a column that already contains averages gives a wrong overall average. Compute ratios from sums: SUM(buyers) / SUM(visitors).
• Wrong field types. A date stored as text sorts alphabetically and cannot be grouped by month.
• Metrics defined in each chart. Define them once as a calculated field or measure.
• Publishing private data publicly, especially on Tableau Public.
• Over-filtering. Ten filters make a dashboard hard to use and easy to misread.

Quick recap

• BI tools connect data, model it, define measures, draw visuals and share them with filters.
• Dimensions are grouped by; measures are aggregated.
• Learn one free tool well; the concepts transfer.
• Define metrics once, check field types, and keep private data private.

Try it

The Do tab walks you through building a small dashboard in Looker Studio or Power BI Desktop, using only free tools and sample data.