Introduction to business intelligence in public statistics: ESTP-course in BI with Power BI

Introduction

This course/webinar is intended for newcomers to Power BI, who want to learn more about how to use a business intelligence tool like Power BI to visualize data. The course is also geared towards the type of data ususally seen in the production of official statistics.

1. Installation

The only prerequisite for attending the course is that you have Power BI Desktop installed on a computer that is at your disposal during the 3-day course. We will only use the Desktop-software, which is free, so there is no need to install anything more. Please download the latest version.

1.2 Recommended settings

We recommend to choose the following settings in Power BI desktop:

Set english as the language.
Security > Allow Data Extensions

2. Resources

2.1 What is Power BI?

This video gives you a nice introduction to what Power BI is:

2.2 Importing data

Importing data is fundamental step in data-tool. Power BI makes this very easy and can connect a large amount of data sources:

Importing csv-files

Example with a SQL server database (with parameters)

2.3 Building a data model

When you start use Power BI you should try to understand the recommended way to organize you data into a model. Its a fundamental part of the Power BI workflow, and avoiding could case bad performance and/or errors in your results. The recommended way to build up a data model in Power BI is called star schema (see official documentation):

Whats the deal with star schemas in Power BI?

A common problem for newcomers to Power BI is understand how to handle dates! Basically, the answer amounts to including a separat date-dimension-table. This is such a fundamental part of any Power BI workflow that I would recommend setting up a template project that prompts for the start- and -stopdates of your datetable. Then you never have to think about it again!! While your at it, you can also disable the automatic time intelligence in the settings.

How to create a datetable?

Since I mentioned template-projects and parameters, Im also gonna share a video that shows how to set ut up:

Parameters and template-projects

Colors, fonts and other design-elements are inevitably a big part visualizing data, and are usually subject to stricts rules from your organization. We recommend that apply the desired design-principles using json-files in Power BI. Its really easy and very fast. Check out this video that Carl made:

Themes with json Official documentation: Use report themes in Power BI Desktop

2.5 Import data from SSBs APIs - Import data from KLASS

ssb.no/en/klass/

Browse to the variant or correspondance you are after
Note id at the end of the URL
Change the code to reflect the ID, or maybe klassType to “variants” / “versions” etc.
Check the documentation of the API: data.ssb.no/api/klass"

Code to paste under: “Get data > Blank query > Advanced Editor”

2.6 Import data from Eurostat API using a custom connector

We just found out about this trick a few weeks ago, through Eurostats Github-page. Please take advantage of all the great data thats in these databases. Again, Carl shows you how:

Download the connector .mez-file: press download on this page"
Make a folder here: ‘C:\Users\[username]\Documents\Microsoft Power BI Desktop\Custom Connectors’
Add the .mez-file in the folder
Change setting in Power BI > Security > Allow Data Extensions, restart Power BI

2.7 Import data from STATBANK

ssb.no/en/statbank

Find the ID of the table you want to get info from
Expand the “API-query box” at the bottom of the page after following the whole web-interface.
You might have to replace single double-quotes, with double double-quotes.

Code to paste under: Get data > Blank query > Advanced Editor

Working example of Python-script for getting a statbank table.

2.8 Visualize with Python and Seaborn

Python-code for density&violin visualizations

Publish to web

Embed

ESTP-course in BI with Power BI