Getting data with Power BI¶

Exercise power-bi-get-data-annex.pdf · PDF

You have spent this unit inside one tool. You connected Superset to a database, asked it questions in SQL, and saved the answers as datasets and tables. Then you arrive at your work placement, and nobody there has heard of Superset. "We use Power BI", they say, and they give you a Windows laptop.

Do you start from zero? No. The sources are the same six. A database still asks for host, port, database, user and password. A CSV still has a separator. What changes is where the data end up once you have fetched them, and that one difference explains almost everything else about the tool.

On the last page you kept your work in two ways: a recipe (a saved query) or a tupperware (stored rows). Keep that kitchen in mind. Today's question is: does this tool keep the recipe or the tupperware?

A tool with no server in the middle¶

Power BI is Microsoft's family of tools for turning data into charts and reports. Power BI Desktop is the program you install on a computer to build those reports.

Microsoft released Power BI to the general public on 24 July 2015, the same year Superset was born at Airbnb. It is a commercial product: a company owns it and sells services around it. Power BI Desktop itself is a free download, with one catch: it only runs on Windows. There is no version for Linux or macOS.

The big difference is in the plumbing. Compare the two pictures:

flowchart TB
    subgraph SUP ["<b>Superset</b>: a server in the middle"]
        direction LR
        B1["Your browser"] --> S["Superset<br>on a server"] --> D1[("PostgreSQL")]
    end
    subgraph PBI ["<b>Power BI Desktop</b>: everything on your PC"]
        direction LR
        P["Power BI Desktop<br>on your Windows PC"] --> D2[("PostgreSQL")]
        P --> F["<b>.pbix file</b><br>(the fridge)"]
    end

In Superset, the connection is set up once, on the server, and everybody queries through it. In Power BI Desktop there is no server in the middle: the program runs on your PC, connects from your PC, and saves the result in a file on your PC.

A .pbix file is a Power BI report saved on disk: its charts, the description of where its data come from and, very often, a copy of the data.

Remember that file. In today's kitchen, it is the fridge.

A wall of connectors¶

In Power BI Desktop, every source starts at the same button: Home → Get data. Choose More... and the Get Data window opens with a long list of sources, grouped by category: File, Database, Online Services and others.

A connector is the piece of a tool that knows how to talk to one kind of source.

It is the same idea as the driver from Connecting Superset to a database, with a form in front of it. Scan the list and you will recognise old friends from Data sources:

Same six kinds of source as always. Only the buttons are new.

Three ways in¶

You will bring the Pokémon into Power BI three times: from a file, from the database as a copy, and from the database live.

From a file¶

Look at step 2 again. Those three boxes are the same three checks you learned to do in a text editor: encoding, separator, and what kind of value each column holds. Power BI guesses all three for you. A guess can be wrong.

From the database, as a copy¶

Now the same connection you know from Superset, in a new form. You need the table pokemon_by_generation that you built in Datasets and destination.

Where did the five settings go?

Five settings and a translator, exactly as before. If the Server box gives you trouble, remember what a host is: a machine's name or address. No http:// in front, and not Superset's port 8088. That door belongs to Superset's web page, not to PostgreSQL.

From the database, live¶

One more time, with one box changed.

Why is the Table view gone? Because there is no table to view. In DirectQuery, Power BI holds no rows. Every chart you draw, every box you tick, sends a fresh query to the database.

Import or DirectQuery: tupperware or recipe¶

That one box, Data Connectivity mode, is the most important choice on this page.

Import copies the data into the report. DirectQuery leaves the data in the database and asks again every time a chart needs them.

In the kitchen:

• Import is the tupperware. You cooked once and stored the result. The fridge is the .pbix file.
• DirectQuery is the recipe. Nothing is stored. Every chart orders from the kitchen again.

And Superset? Which of the two does it resemble? DirectQuery. Superset never keeps your rows: every chart sends a query to the database. That was the first idea of Connecting Superset to a database, and now it has a name in another tool.

An API in one window¶

Remember what it took to get 20 Pokémon from the PokéAPI into Superset: a Python script, a CSV file and an upload. Superset only talks to databases, so the API had to be extracted and loaded first.

Power BI has a connector for that.

No script. Is the API problem solved, then? Be honest about what you got:

What Power BI adds is that the flattening step (turning nested JSON into rows and columns) is done with clicks instead of code. Those clicks have a name, Power Query, and a page of their own in the next unit: Power Query.

Superset and Power BI, side by side¶

Everything on this page in one table.

Neither column is "the good one". A team that shares one database and wants everybody to see the same numbers is well served by a server in the middle. One person exploring a file on a laptop is well served by a program that just opens it. Knowing which situation you are in is the skill.

Try it: same data, another tool¶

You need a Windows PC with Power BI Desktop, the file pokemon_df.csv from the Resources page, and the connection details of your class database.

Do the three routes of this page, then fill in this card:

No Windows PC at home? Do the card from your class notes, and answer the last question from the comparison table instead.

Key takeaways¶

• Power BI Desktop is Microsoft's report-building program. It is free to download, runs only on Windows, and has no server in the middle: the result is a .pbix file on your PC.
• A connector is Power BI's version of a driver with a form in front. The sources are the same six as always, and a database still asks for host, port, database, user and password.
• Import copies the data into the .pbix (the tupperware); DirectQuery leaves them in the database and asks again each time (the recipe).
• Microsoft's advice is Import by default. DirectQuery is for data too big or too fast-changing to copy, and it only works with some connectors.
• Superset behaves like DirectQuery: it never keeps your rows. A CSV uploaded in Superset becomes a table in the database; a CSV loaded in Power BI becomes a copy in the report.
• Two tools can read the same file differently: NA became NULL in Superset and stayed as text in Power BI. Always check what the tool did.

Quick check¶

That closes Unit 1: you can find a source, get in, ask for exactly what you need, store the result, and do it in two different tools. But what you fetched is not clean yet. You have already seen NA, NULL and a column that could not make up its mind. Next unit, you fix that: Unit 2 · Cleaning and transforming data.