-
Notifications
You must be signed in to change notification settings - Fork 2
/
tutorialScript.txt
290 lines (182 loc) · 12.3 KB
/
tutorialScript.txt
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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
Story about funnel plot
https://www.theguardian.com/commentisfree/2011/oct/28/bad-science-diy-data-analysis
This atricle from "thegaurdian" tells us the inspiring story about how using wrong visualization tools can
trigger wrong conclusions even by such a serious news publisher as BBC. The real "hero" of this story is "funnel plot".
Which can be used for comparing institutional performance and medical data analysis.
[image: fp.JPG]
The funnel plot is easy to consume and interpret. The "funnel" is formed by confidence limits and show the amount of expected variation.
The dots outside the funnel are outliers.
In this blog author demonstrates the implementation of "funnel plot" in R, and we use it as starting point
https://onlinejournalismblog.com/2011/10/31/power-tools-for-aspiring-data-journalists-funnel-plots-in-r/
We are going to use this code in order to incrementally create
1) R-script for RStudio
2) R-visual in Power BI
3) R-powered Custom Visual in Power BI (PNG-based)
4) R-powered HTML-based Custom Visual in Power BI
Of cause we could choose not to create R-visual or PNG-based custom visual and to go for HTML-based visual from the beginning, we only do it for the sake of completeness of tutorial.
Chapter 1
The minimal R-script and the accompanying data table:
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter1_R\ver_00\dataset.csv
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter1_R\ver_00\script_R_v1_00.r
The next version of R-script is essentially the same, but implements input errors handling and user parametes to control the appearance of the plot
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter1_R\ver_01\dataset.csv
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter1_R\ver_01\script_R_v1_01.r
Chapter 2
Let us load the "dataset.csv" into Power BI desktop workspace as "Cancer Mortality" table.
The code in "script_R_v1_01.r" is almost ready to be used within R-visual.
We only need to comment out the "read.csv" call.
The R-code is:
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter2_Rvisual\script_RV_v2_00.r
See the result in:
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter2_Rvisual\funnelPlot_Rvisual.pbix
Remark: The "dataset" is hard-coded name for R-visual and we took care about it already in Chapter 1.
Chapter 3
We are about to package R code in R-powered Custom Visual.
Before you can get started you'll need to install the PBIVIZ tools. This should only take a few seconds.
https://github.com/Microsoft/PowerBI-visuals/blob/master/tools/README.md#installation
Section 3.1
Now use any command line shell to create new R-powered custom visual
> pbiviz new funnelRvisual -t rvisual
> cd funnelRvisual
> npm install
> pbiviz package
It will create funnelRvisual folder with initial basic visual.
The PBIVIZ is in "dist" folder. Try to import it in PBIX and see what it does.
* Open "script.r" file for editing and copy the contents "script_RV_v2_00.r" just as is !!!
* Open "capabilities.json" in any editor and Find/Replace the "Values" string by "dataset" string. It replaces the name of "Role" in template to be like in R-code.
* Open "dependencies.json" in any editor and add one section for each R-package required in R-script (if you want to support automatic import of packages, while visual is added first time)
Now re-package the visual again:
> pbiviz package
Try to import it in PBIX again and see what it does.
The resulting PBIX and the whole Custom Visual Project may be found in
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\funnelRvisual_v01\
Section 3.2
The Custom Visual in previous section is good to go, but it is not really user-friendly.
Let us divide the input field "dataset" into 3 parts: Population, Number and Tooltips.
* Edit "capabilities.json" by replacing "dataset" role by three new roles. You will need to update 2 sections: dataRoles and dataViewMappings
These sections define names, types, tooltips and maximum columns for each input field. See more here (???)
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\funnelRvisual_v02\capabilities.json
* Edit "script.r" to support "Population", "Number" and "Tooltips" as input dataframes instead of "dataset"
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\funnelRvisual_v02\script.r
To follow the changes in R-script, search for the blocks:
#RVIZ_IN_PBI_GUIDE:BEGIN:Added to enable custom visual fields
...
#RVIZ_IN_PBI_GUIDE:END:Added to enable custom visual fields
and
#RVIZ_IN_PBI_GUIDE:BEGIN:Removed to enable custom visual fields
...
#RVIZ_IN_PBI_GUIDE:END:Removed to enable custom visual fields
Now re-package the visual again:
> pbiviz package
Try to import it in PBIX again and see what it does.
The resulting PBIX and the whole Custom Visual Project may be found in
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\funnelRvisual_v02\
Section 3.3
The Custom Visual in previous section is almost perfect, but something is still missing. What is it?
Of cause, user parameters.
We want user to control colors and sizes of visual elements as well as some internal parameters of algorithm from UI.
* We need to edit "capabilities.json" again, this time the "objects" section. This is the place to define names, tooltips and types of each parameter.
As well we decide on partition of parameters into groups.
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\funnelRvisual_v03\capabilities.json
* Now edit "src\visual.ts" file, it is a typeScript code. You may find this part a little confusing, escpecially if you are not familiar with JavaScript / TypeScript.
You will find four blocks of code added.
1) New interface to hold the property value;
2) Define a member property and default values;
3) Change the updateObjects method to get the value of the enumeration;
4) The code in enumerateObjectInstances to show the property in the property pane
To follow the changes in TypeScript, search for the blocks:
//RVIZ_IN_PBI_GUIDE:BEGIN:Added to enable user parameters
...
//RVIZ_IN_PBI_GUIDE:END:Added to enable user parameters
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\funnelRvisual_v03\src\visual.ts
* Edit "script.r" to support the parameters in UI, it is quite easy just by adding "if.exists" calls per user-parameter
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\funnelRvisual_v03\script.r
To follow the changes in R-script, search for the blocks:
#RVIZ_IN_PBI_GUIDE:BEGIN:Added to enable user parameters
...
#RVIZ_IN_PBI_GUIDE:END:Added to enable user parameters
and
#RVIZ_IN_PBI_GUIDE:BEGIN:Removed to enable user parameters
...
#RVIZ_IN_PBI_GUIDE:END:Removed to enable user parameters
Note that you may decide not to expose some of the parameters to UI, like we did.
Now re-package the visual again:
> pbiviz package
Try to import it in PBIX again and see what it does.
The resulting PBIX and the whole Custom Visual Project may be found in
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter3_RCustomVisual\funnelRvisual_v02\
Remark: In this tutorial we add all parameters of different types (boolean, numeric, string) at once.
If you find it too complicated to follow, please have a look at this example which shows how to add single parameter:
https://github.com/Microsoft/PowerBI-visuals/blob/master/RVisualTutorial/PropertiesPane.md
Chapter 4
The resulting visual is PNG-based and therefore not responsive to mouse hover, can not be zoomed in etc.,
In the last step we will show how it can be converted to HTML-based visual.
We will create an empty R-powered HTML-based Cutom Visual template and then copy scripts from PNG-based custom visual.
Use any command line shell:
> pbiviz new funnelRHTMLvisual -t rhtml
> cd funnelRHTMLvisual
> npm install
> pbiviz package
Explore capabilities.json and pay attention to "scriptOutputType": "html" line
Explore dependencies.json and pay attention to names of R-packages listed there
Explore script.r and pay attention to its structure. You may open and run it in RStudio.
You will find that it creates and saves "out.html" file. This file have to be self-content (without external dependencies) and defines graphics inside HTML widget.
In template we also provide R-utilities in "r_files" folder to help with conversion of plotly object into self-content HTML.
Note that this version of R-powered visual supports "source" command (unlike previous types of visuals) and we use it to make code more readable.
* Replace capabilities.json by capabilities.json from previous step, but obviously keep: "scriptOutputType": "html"
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter4_RCustomVisual\funnelRHTMLvisual_v01\capabilities.json
* Merge latest version of the sript.r file from Chapter 3 with script.r from the template.
The changes are very obvious, we only use plotly packages to ggplot object to plotly object. And then use htmlWidgets package to save it to html file.
We also move most of utility functions to "r_files/utils.r" and add "generateNiceTooltips" function for cosmetics of plotly object
To follow the changes in R-script, search for the blocks:
#RVIZ_IN_PBI_GUIDE:BEGIN:Added to create HTML-based
...
#RVIZ_IN_PBI_GUIDE:BEGIN:Added to create HTML-based
and
#RVIZ_IN_PBI_GUIDE:BEGIN:Removed to create HTML-based
...
#RVIZ_IN_PBI_GUIDE:BEGIN:Removed to create HTML-based
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter4_RCustomVisual\funnelRHTMLvisual_v01\script.r
* Merge latest version of the dependencies.json file from Chapter 3 with dependencies.json from the template, to include new R-package dependencies
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter4_RCustomVisual\funnelRHTMLvisual_v01\dependencies.json
* Change the script src/visual.ts in exactly the same way as you did in Chapter 3.3
To follow the changes in TypeScript, search for the blocks:
//RVIZ_IN_PBI_GUIDE:BEGIN:Added to create HTML-based
...
//RVIZ_IN_PBI_GUIDE:BEGIN:Added to create HTML-based
You will find same four blocks of code added (like in Section 3.3)
The resulting file is
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter4_RCustomVisual\funnelRHTMLvisual_v01\src\visual.ts
Now re-package the visual again:
> pbiviz package
Try to import it in PBIX again and see what it does.
The resulting PBIX and the whole Custom Visual Project may be found in
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter4_RCustomVisual\
c:\Users\boefraty\projects\PBI\R\Interactive\TutorialFunnelPlot\chapter4_RCustomVisual\funnelRHTMLvisual_v01\
Last Chapter
* We recommend developers to edit "pbiviz.json" to contain correct metadata (such as version, email, name, license type etc.)
IMPORTANT: the "guid" field is an unique identifier for custom visual, so change it if you want several visuals to co-exist.
* We recommend developers to edit "assets/icon.png" to create cool unique icon for your custom visual
And finally we recommend developers to submit their R-powered custom visuals to the store. It will make your visual famous and make you get cool t-shirt.
Useful links:
R-script showcase
https://community.powerbi.com/t5/R-Script-Showcase/bd-p/RVisuals
Office Store (gallery)
https://store.office.com/en-us/appshome.aspx?ui=en-US&rs=en-US&ad=US&clickedfilter=OfficeProductFilter%3aPowerBI&productgroup=PowerBI
Tutorial on Custom Visuals
https://github.com/Microsoft/PowerBI-visuals
Basic tutorial on R-custom visuals
https://github.com/Microsoft/PowerBI-visuals/tree/master/RVisualTutorial
Develop and submit custom visuals to the store
https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-office-store/