Reports with Power BI¶

Exercise power-bi-reports-annex.pdf · PDF

You apply for your first job. The interview goes well, and then comes the sentence: "By the way, here we use Power BI." You have spent five months in Superset. Do you start from zero?

No. You know what a dashboard is for, what a KPI is, what a filter does and which chart answers which question. That knowledge is yours, not Superset's. This lesson checks that it survives a change of tool: you will rebuild three charts of your Steam market dashboard in Power BI and prove, with SQL, that they show the same numbers.

You met Power BI's data side in Getting data with Power BI and Power Query; today is the part people actually look at.

Same game, other console¶

Imagine your favourite game comes out on another console. The rules are the same. The map is the same. Your strategy still works. Only the buttons have other names. You do not need a new manual: you need the button chart.

Power BI is Microsoft's tool for turning data into interactive reports. It has two halves: Power BI Desktop, a free Windows application where you build, and the Power BI service, a website where you publish and share.

Here is the button chart. Keep it open for the whole lesson:

A tour of the screen¶

Open Power BI Desktop and look at four places. No more.

flowchart TB
    RIB["<b>Ribbon</b><br>tabs with tools: Home, Insert, Modeling"]
    subgraph MAIN ["Under the ribbon"]
        direction LR
        CAN["<b>Report canvas</b><br>the white page where<br>the visuals go"]
        VIS["<b>Visualizations pane</b><br>choose the type of visual,<br>fill its field wells"]
        DAT["<b>Data pane</b><br>your tables and<br>their fields"]
        DAT -- "drag a field" --> VIS
        VIS -- "draws the visual" --> CAN
    end
    RIB --> MAIN

Now look for the SQL editor. There is none in sight. This is the biggest difference of the day: in Superset you could always open SQL Lab and write the query yourself. In Power BI you drag fields, and the query is written for you.

Visuals and field wells¶

A visual is one chart, table or number on the report canvas. A field well is a labelled box in the Visualizations pane where you drop a field to tell the visual what to draw.

A field is simply a column of your data, such as price_band or release_year. Each type of visual has its own wells, and they map directly onto what you already do in Superset:

When you drop a field into a well that expects a number, Power BI must decide how to summarise it: add it up, count it, average it. It guesses. And it often guesses Sum.

Pages and slicers¶

A Superset dashboard is one screen, sometimes split into tabs. A Power BI report is made of pages: one or more canvases, like slides, each with its own visuals. The page tabs sit along the bottom of the canvas. One page for the overview, another for the detail.

A slicer is a filter that lives on the canvas as a visual. The reader clicks a value, and the other visuals on the page keep only the rows that match.

It is the same idea as the native filter you built in Filters and cross-filters: one control, many charts react, and behind every one of them a WHERE has been added. Two details are different:

Try it: predict, build, compare¶

The method matters more than the clicks: first you predict in SQL, then you build, then you compare. A visual you cannot check is a visual you cannot trust.

Power BI will read a file, so you need games_clean.csv: your cleaned Steam table from Unit 2, exported to a file. If you do not have it yet, make it yourself in two minutes.

Step 1 — Predict. In SQL Lab, with the database Dades classe, run these three queries one at a time (SQL Lab only shows the result of the last statement) and write the results down:

SELECT COUNT(*) AS games FROM games_clean;

SELECT release_year, COUNT(*) AS games FROM games_clean
GROUP BY release_year ORDER BY games DESC LIMIT 1;

SELECT price_band, COUNT(*) AS games FROM games_clean
GROUP BY price_band ORDER BY games DESC;

Then run the same three again with WHERE owners_tier = 'Hit' before the GROUP BY. That is what the slicer will do in Step 4. A hit is a game with at least a million owners. You should end up with these six results:

Step 2 — Load the file and build the card.

Step 3 — Two charts. Click an empty part of the canvas first, or the new visual will replace the card instead of being added next to it.

Step 4 — The slicer.

Compare with your "hits only" predictions. If all six numbers match, you have not just built a report: you have verified one.

Publishing to the service¶

In Superset, your dashboard lived on the server from the first click. Here, everything you have built so far lives in one file on your PC: File → Save creates a .pbix file. Nobody else can see it.

To change that you publish:

What travelled to the cloud: the report, the data, or both? Both. The report goes up together with its semantic model, and the model carries a copy of the data inside. This is called importing: the 26,685 rows are no longer only in your CSV file, they are also inside the published report.

flowchart LR
    subgraph SUP ["<b>Superset</b>"]
        direction LR
        DB1[("PostgreSQL<br>the data stay here")] -- "query on every view" --> D1["Dashboard<br>on the server"]
    end
    subgraph PBI ["<b>Power BI</b>"]
        direction LR
        F["<b>.pbix file</b> on your PC<br>report + a copy of the data"] -- "Publish" --> S["<b>Power BI service</b><br>My workspace"]
    end

Two tools, two answers to the same question: how does my chart reach other people? In Superset you gave a role access to a dataset. In Power BI you publish to a workspace and then share. The question is the same on every console.

The word that changes meaning¶

One button on the chart has the same name on both consoles and does something different.

What you built in Superset — a screen of charts you can filter — matches a Power BI report. In Power BI, a dashboard is another thing: a single page of tiles that you pin from reports, to keep the highlights in one place.

So if a colleague says "send me the Power BI dashboard", ask one more question. They may well mean the report.

Key takeaways¶

• Power BI has two halves: Power BI Desktop (free, Windows only) to build, and the Power BI service (a website) to publish and share.
• The ideas are the ones you know; the names change. Dashboard → report, chart → visual, Big Number → card, native filter → slicer, dataset → semantic model.
• You build by dragging a field into a field well. There is no SQL editor: Power BI writes the query, so check Sum against Count.
• A slicer is a filter that sits on the canvas and filters the other visuals on its page.
• Predict in SQL, build, compare. games_clean has 26,685 games; a card that says 27K is rounding, not lying.
• Publish sends the report and a copy of the data to My workspace, where only you see it. Sharing is a separate step that needs a paid licence.
• A Power BI dashboard is a single page of pinned tiles in the service. Your Superset dashboard matches a Power BI report.

Quick check¶

You now speak two consoles. So which one should you use, and, more important, what do you actually say with the charts once they are built? Next: Superset vs Power BI, and data storytelling.