-
Notifications
You must be signed in to change notification settings - Fork 0
/
index.Rmd
185 lines (117 loc) · 9.39 KB
/
index.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
---
title: "ESTP-course in BI with Power BI"
description: |
Information about the ESTP-webinar on Power BI held on the 20.-22. april 2021.
site: distill::distill_website
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = FALSE)
```
```{r, echo=FALSE}
library(metathis)
library(magrittr)
meta() %>%
meta_description(
"ESTP-course on Power BI in april of 2021."
) %>%
meta_name("github-repo" = "skars82/estp_course_pbi") %>%
meta_viewport() %>%
meta_social(
title = "Introduction to business intelligence in public statistics (Power BI)",
url = "https://skars82.github.io/estp_course_pbi/",
image = "https://github.com/skars82/slides/blob/master/docs/estp_course_powerbi/pictures/twitter_slide.png)",
image_alt = "Title slide of first day",
og_type = "Course",
og_author = c("Øyvind Bruer-Skarsbø", "Carl Franklin Corneil"),
twitter_card_type = "summary",
twitter_creator = "@YSkarsb"
)
```
## 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](https://powerbi.microsoft.com/en-us/downloads/) 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:
<iframe width="560" height="315" src="https://www.youtube.com/embed/Vqz2d7pTOV8" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
### 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**
<iframe width="560" height="315" src="https://www.youtube.com/embed/wuLnv3QJCHg" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
**Example with a SQL server database (with parameters)**
<iframe width="560" height="315" src="https://www.youtube.com/embed/1UkDaiRGShM" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
### 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](https://docs.microsoft.com/en-us/power-bi/guidance/star-schema)):
**Whats the deal with star schemas in Power BI?**
<iframe width="560" height="315" src="https://www.youtube.com/embed/vZndrBBPiQc" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
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?**
<iframe width="560" height="315" src="https://www.youtube.com/embed/Pzn2B4laK9A" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
Since I mentioned template-projects and parameters, Im also gonna share a video that shows how to set ut up:
**Parameters and template-projects**
<iframe width="560" height="315" src="https://www.youtube.com/embed/JlUfz18cx_w" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
### 2.4 Themes and sharing JSON-files
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**
<iframe src="https://player.vimeo.com/video/537137641?color=90cc93&portrait=0" width="704" height="396" frameborder="0" allow="autoplay; fullscreen; picture-in-picture" allowfullscreen></iframe>
<a href="https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-report-themes">Official documentation: Use report themes in Power BI Desktop</a>
### 2.5 Import data from SSBs APIs - Import data from KLASS
<iframe src="https://player.vimeo.com/video/537588889?color=90cc93&portrait=0" width="704" height="396" frameborder="0" allow="autoplay; fullscreen; picture-in-picture" allowfullscreen></iframe>
<a href="https://www.ssb.no/en/klass/">ssb.no/en/klass/</a>
- 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: <a href="https://data.ssb.no/api/klass/v1/api-guide.html">data.ssb.no/api/klass"</a>
<details>
<summary>Code to paste under: "Get data > Blank query > Advanced Editor"</summary>
<script src="https://gist.github.com/aecorn/a3f42cc730f12d6b8070ebf6e7d1da1e.js"></script>
</details>
### 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](https://github.com/eurostat/PowerBI-connector). Please take advantage of all the great data thats in these databases. Again, Carl shows you how:
<iframe src="https://player.vimeo.com/video/536746730?color=90cc93&portrait=0" width="704" height="396" frameborder="0" allow="autoplay; fullscreen; picture-in-picture" allowfullscreen></iframe>
- Download the connector .mez-file: <a href="https://github.com/eurostat/PowerBI-connector/blob/master/Eurostat%20API.mez" target="_blank" >press download on this page"</a>
- 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 NACE-codelist from Eurostat
-->
### 2.7 Import data from STATBANK
<iframe src="https://player.vimeo.com/video/536752553?color=90cc93&portrait=0" width="704" height="396" frameborder="0" allow="autoplay; fullscreen; picture-in-picture" allowfullscreen></iframe>
<iframe src="https://player.vimeo.com/video/536754342?color=90cc93&portrait=0" width="704" height="396" frameborder="0" allow="autoplay; fullscreen; picture-in-picture" allowfullscreen></iframe>
<a href="https://www.ssb.no/en/statbank">ssb.no/en/statbank</a>
- 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.
<details>
<summary>Code to paste under: Get data > Blank query > Advanced Editor</summary>
<script src="https://gist.github.com/aecorn/0adf2cf352a889a769bb5f63c6a59a0d.js"></script>
</details>
<iframe src="https://player.vimeo.com/video/536743890?color=90cc93&portrait=0" width="704" height="396" frameborder="0" allow="autoplay; fullscreen; picture-in-picture" allowfullscreen></iframe>
<details>
<summary>Working example of Python-script for getting a statbank table.</summary>
<script src="https://gist.github.com/aecorn/e833246f39255df94e3876c467b3046b.js"></script>
</details>
### 2.8 Visualize with Python and Seaborn
<iframe src="https://player.vimeo.com/video/536747489?color=90cc93&portrait=0" width="704" height="396" frameborder="0" allow="autoplay; fullscreen; picture-in-picture" allowfullscreen></iframe>
<details>
<summary>Python-code for density&violin visualizations</summary>
<script src="https://gist.github.com/aecorn/8ef142cd9f5e15b6d8f606f2f3cb3f2b.js"></script>
</details>
<details>
<summary>Publish to web</summary>
<iframe width="600" height="373.5" src="https://app.powerbi.com/view?r=eyJrIjoiOGU5ODc3ODYtNjhjNS00ZGY0LWIzODUtMmUyZmFlYWJkMGU1IiwidCI6ImM3MjE3MDkyLWIyNDAtNGUxZC1iZDYxLWZhOTdiYTk3NWNiYyIsImMiOjh9" frameborder="0" allowFullScreen="true"></iframe>
</details>
<details>
<summary>Embed</summary>
<iframe width="1140" height="541.25" src="https://app.powerbi.com/reportEmbed?reportId=7b5d8c43-8c1f-4fa1-bdc5-1862344088f7&autoAuth=true&ctid=c7217092-b240-4e1d-bd61-fa97ba975cbc&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLW5vcnRoLWV1cm9wZS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzLm5ldC8ifQ%3D%3D" frameborder="0" allowFullScreen="true"></iframe>
</details>