Introduction to business intelligence in public statistics (Power BI)

class: middle right
background-image: url(pictures/PowerBI_logo.png)
background-position: left

<div>
<style type="text/css">.xaringan-extra-logo {
width: 110px;
height: 128px;
z-index: 0;
background-image: url(pictures/ssb-logo-rgb300pxl.png);
background-size: contain;
background-repeat: no-repeat;
position: absolute;
top:1em;right:1em;
}
</style>
<script>(function () {
 let tries = 0
 function addLogo () {
 if (typeof slideshow === 'undefined') {
 tries += 1
 if (tries < 10) {
 setTimeout(addLogo, 100)
 }
 } else {
 document.querySelectorAll('.remark-slide-content:not(.title-slide):not(.inverse):not(.hide_logo)')
 .forEach(function (slide) {
 const logo = document.createElement('div')
 logo.classList = 'xaringan-extra-logo'
 logo.href = null
 slide.appendChild(logo)
 })
 }
 }
 document.addEventListener('DOMContentLoaded', addLogo)
})()</script>
</div>

.title[Introduction to business intelligence in public statistics (Power BI)]

.title[.posi2[Webinar hosted by European Statistical Training System (ESTP) and Statistics Norway (SSB)]]
 
.title[.posi[20.-22. april 2021]]

???

- Welcome
 
 
- First ESTP-course
 
 
- a large group
 
 
- challenges with a webinar
 
 
- why the interest

---

## About us

.pull-left[

<div class="figure">
<img src="pictures/obr2.png" alt="Øyvind Bruer-Skarsbø" width="30%" />
Øyvind Bruer-Skarsbø
</div>

<div class="figure">
<img src="pictures/Carl_bg.png" alt="Carl Franklin Corneil" width="30%" />
Carl Franklin Corneil
</div>

]

.pull-center[
.bio[
*Senior Advisor* 
*Division of Social Statistics* 
*Labour Market and Wage Statistics* 
*Statistics Norway*]

.bio2[
*Advisor*  
*Division of Social Statistics*  
*Labour Market and Wage Statistics*  
*Statistics Norway*]
]
---
class: inverse center middle right

## Aim of this course

.quote[
The aim of this course is to give the participants an understanding of how a business intelligence tool like Power BI can be usefull in the production of official statistics, and show how to build basic dashboards using Power BI Desktop.]

---

## About this course

- All resources available at the [course website](https://skars82.github.io/estp_course_pbi/)

--
- We assume that you have installed .animated.bounce.dib[[Power BI Desktop!](https://powerbi.microsoft.com/en-us/downloads/)]

--
- We are a big group (35 participants)

--
- Lets divide into 7 groups with 5 each for discussions

---
## What is Business intelligence (BI)?

- Tools to ~~easily~~ extract and communicate insights from data

--
- Response to the growing amount of data in all organizations

--
- Make an organisation less dependent on its IT-departement

--
- An answer to the "webbrowser-takeover"

--
- A javascript-free life &#x1F60E;

---
class: top left
background-image: url(pictures/gartner_2020.png)
background-size: 80% 80%
background-position: bottom center

## Why Power BI?

---
class: inverse center middle

<div class="container">
<blockquote class="brooks">
I web, therefore I am <strike>spiderman</strike>
 
<footer>Yihui Xie, RStudio</footer>
</blockquote>
</div>

---

## Fundamentals of Power BI

- Microsoft product

--
- Very well integrated with the rest of Microsoft 365

--
- "*cradle to grave*" solution (from source-data to embeddable link)

--
- Tool for (mostly) descriptive statistics

--
- You .yellow1[can] use it without code

--
- Desktop-version is free, and the rest costs

--
- Can handle most data sources...even big data!

--
- can do webscraping, text-analyzes, machine-learning

---
class: inverse left 
background-image: url(pictures/pipeline.png)
background-position: center middle

## Basic PBI pipeline

---
class: inverse center middle

.animated.heartBeat.dib2[Breaktime!!]

---

## Group discussions

.pull-left[

### How?

- 5 in each group
 
 
- 15 minutes
 
 
- select 1 group leader
 
 
]

--
.pull-right[
### What?

- How are you gonna use PBI ? 
 
 
- Power BI service implemented? 
 
 
- Why did your boss send you here? 
 
 
]

---
## Main parts of Power BI

.pull-left[

### 1. Report window

- Visualizing
 
 
- Data model
 
 
- Publishing
]

.pull-right[

### 2. Power Query

- Extract/import data
 
 
- Transform data
 
 
- Load data

]

--
 
 
 
.animated.heartBeat.dib2.yellow[Lets open Power BI!]

---

## Task 1: Importing (more) data

- **Import** and **append** the the rest of the csv-files. Remember to do the same transformations as we did with with the first dataset!

*Hint: There are many ways to do this, but remember the trick of duplicating queries in Power Query.
 
 
- After youre done importing data, check that data types and other things have not changed. 
 
 
- Load the data into the report-window. 
 
 
- Create a line chart with date on the x-axis and the number of people on the y-axis.

---

## Building a data model

--
.pull-left[

### Star schema

- fundamental part the PBI-workflow
 
 
- 1 table that has all the variables to be aggregated
 
 
- all other information separated out to other tables
 
 
- requires some experience to set up if the data is large and you want to track changes within units over time (slowly changing dimensions)
 
 
- Dont worry!&#x1f60c; Only simple examples in this course..

]
 
--
.pull.right[
<img src="pictures/star_schema.png" width="50%" />

]

--
.animated.pulse.dib.yellow[Lets open Power BI!]

???

- Create date table
 
 
- Create region_dim

---
## Task 2: Adding datasets to the model

- Import the file *sn07.dim.csv* (Standard Industrial Classification 2007)
 
 
- Clean the data so that we can connect it to to our main table
 
 
- Load data from Power Query to report page
 
 
- Connect it to the main dataset
 
 
- Create a matrix-visual that allows the user easily drill up and down from 1-digit to 5-digit NACE2007. The drilling should happen by the NACE-name and not the NACE-code!

### Extra-task:

- Manually create a dimension-table for gender and connect it to the main dataset. Create a slicer that lets the user select which sex is diplayed in the matrix.

---
## DAX

--
- DAX is the "programming language" of the report window. [See documentation](https://docs.microsoft.com/en-us/dax/dax-overview)

--
- DAX is used to create measures, calculated columns and calculated tables.

- Measures a by far the most important and the focus in this course.

--
- Organize your Measures in a separate table

.pull-right[
<img src="pictures/measures_table.PNG" width="40%" />
]

---

## Basic DAX

.pull-left[

### Syntax

`\(MeasureName=\)`                   
  
`\(Function('TableName'[Column])\)`

.yellow2[for example]

`\(People=COUNT('factable'[pid])\)`

.yellow2[counts the number of rows in the column **pid** in the table **factable**.]  
]

.pull-right[

### Important functions

- SUM
- COUNT
- DISTINCTCOUNT
- CALCULATE
- FILTER
- SELECTEDVALUE
]

---

## DAX Operators

![](pictures/operators.PNG)

---
class: inverse center middle

.animated.heartBeat.dib2[Lets write  
some DAX!!]

---

## Task 3: Write more DAX

- Create a visual that in some way shows the number of workers in each region.

--
- Insert a slicer that lets the "user" filter by year.

--
- Create a measures that shows the count of working people last year relative to the chosen year. Copy-paste the visual from [1] and replace the measure with the new measure.

---

## Project for day 2

- Pick a data source that is relevant to you and your work (API, database, csv, web, etc.)
 
 
- Think about what you want to display 
 
 
- Build a data model with the data
 
 
- Use dax to calculate measures
 
 
- Use company colors and logos to beatify your report
 
 
- Do a project that lets finish in a day (dont be over-ambitious...)
 
 
- Send us pdf-copy at the end of the day
 
 
- On day 3 you show it to the group and discuss

.yellow.dib[GOOD LUCK &#128513;]

---

## Discussing the tasks

- How was to work with Power BI? 
 
 
- What was the most difficult? 
 
 
- Did you model your data?