README.md 17.1 KB
Newer Older
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
1
2
# Power BI Report Generator

Aslak Johansen's avatar
Aslak Johansen committed
3
This is a python framework which enables the developer to manipulate the layout of a Power-Bi report.
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
4

Aslak Johansen's avatar
Aslak Johansen committed
5
6
7
8
## Table of Contents

1. [Dependencies](#dependencies)
2. [Default Dataset](#default-dataset)
Aslak Johansen's avatar
Aslak Johansen committed
9
10
    1. [Description (from Original Source)](#description-from-original-source)
3. [Overview](#overview)
11
4. [Canvas](#canvas)
12
13
14
    1. [High-Level Functionality](#high-level-functionality)
    2. [Debugging](#debugging)
    3. [Layout Operations](#layout-operations)
Aslak Johansen's avatar
Aslak Johansen committed
15
5. [Visual Elements](#visual-elements)
16
17
18
19
20
21
22
    1. [Barchart](#barchart)
    2. [Linechart](#linechart)
    3. [Display](#display)
    4. [Dropdown](#dropdown)
    5. [Period](#period)
    6. [Table](#table)
    7. [Textbox](#textbox)
Aslak Johansen's avatar
Aslak Johansen committed
23
6. [Styling](#styling)
24
25
26
27
    1. [Color](#color)
    1. [Tone](#tone)
    1. [Font](#font)
    1. [Column](#column)
Aslak Johansen's avatar
Aslak Johansen committed
28
29
7. [Examples](#examples)
8. [Authors](#authors)
Aslak Johansen's avatar
Aslak Johansen committed
30
31
32

## Dependencies

Aslak Johansen's avatar
Aslak Johansen committed
33
34
It is written in Python 3.7 and relies on the following modules:
- `zipfile`
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
35

Aslak Johansen's avatar
Aslak Johansen committed
36
## Default Dataset
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
37

Aslak Johansen's avatar
Aslak Johansen committed
38
The framework defaults to using the [`REFIT Smart Home dataset`](https://repository.lboro.ac.uk/articles/REFIT_Smart_Home_dataset/2070091) dataset by Steven Firth, Tom Kane Vanda Dimitriou, Tarek Hassan, Farid Fouchal, Michael Coleman and Lynda Webb. A snapshot of this dataset from 28-07-2020 is included.
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
39

Aslak Johansen's avatar
Aslak Johansen committed
40
### Description (from Original Source)
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
41
42
43
44
45
46
47
48

This dataset is maintained by Steven Firth (s.k.firth@lboro.ac.uk), Building Energy Research Group (BERG), School of Civil and Building Engineering, Loughborough University.

The REFIT project (www.refitsmarthomes.org) carried out a study from 2013 to 2015 in which 20 UK homes were upgraded to Smart Homes through the installation of devices including Smart Meters, programmable thermostats, programmable radiator valves, motion sensors, door sensors and window sensors.

Data was collected using building surveys, sensor placements and household interviews.

The REFIT Smart Home dataset is one of the datasets made publically available by the project. This dataset includes:
Aslak Johansen's avatar
Aslak Johansen committed
49
50
51
52
- Building survey data for the 20 homes.
- Sensor measurements made before the Smart Home equipment was installed.
- Sensor measurements made after the Smart Home equipment was installed.
- Climate data recorded at a nearby weather station.
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
53

Aslak Johansen's avatar
Aslak Johansen committed
54
55
## Overview

56
57
58
59
The framework consists of three modules:

- `_type.py` includes different types that makes it easier for the developer to define a Color, Tone (color strength), Font and the allignment of text.
- `make.py` exposes functionality for creating elements such as a barchart, linechart and a card-display.
Aslak Johansen's avatar
Cleanup    
Aslak Johansen committed
60
- `model.py` maintains the status of the Layout file (essentially a canvas). This involves clearing it, setting the background, adding elements and exporting it to a new *pbix* report.
61

62
## Canvas
Aslak Johansen's avatar
Aslak Johansen committed
63

64
65
**Note:** All functions described in this section are from the `model` module.

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
To use the framework, first a Layout source must be declared. This is done by calling `model.start()`. This prompts the framework to search the *source* folder for a Layout file. The content of this file is decoded and returned.

```python
layout = model.start()
```

The layout variable is used each time changes are made to the Layout of the pbix report. These are changes such as changing the background color, storing a *debug file* and adding new elements to the Layout.

Once the layout is initialized, [elements](#visual-elements) can be created through the `make` module. After creation elements must be added to the current layout. The framework offers two ways to add element(s) to the layout:

```python
def store_many(*_object: barchart_obj or linechart_obj or display_obj or table_obj or textbox_obj or dropdown_obj or tuple): ...

def store(_object: barchart_obj or period_obj or linechart_obj or display_obj or table_obj or textbox_obj or dropdown_obj or tuple): ...
```

The `store_many` function calls `store` for each ellipsis argument given. This function is thus just a convenient wrapper.

When elements are added to the layout, it can be formatted and exported. This is done in two different steps. The `create` function stores those objects that have stored by calling the `store` functionalong with those given through its `objects` parameter. While this technically makes the `store_many` and `store` functions redundant if the elements are collected in a list and passed to create, it does provide different architecture-related functionality. If `create` is called without passing a list of elements (which is perfectly fine), `store` or `store_many` must be called prior to `create`.

```python
def create(layout: dict, objects: list=[]): ...
```

**Note:** If `create` is called with a list of elements after calls to `store` or `store_many`, all the result will contain the combined elements; potentially with duplicates.

Finally, the new layout can be exported to the `powebi` folder by calling the `export` function. Subsequently, by calling `write` the content of this folder is formatted to a `.pbix` file and written to the `gen` folder. This folder includes all generated reports.

```python
def export(layout: dict): ...
def write(filename: str='gen-report'): ...
```

`export` must be called prior to `write` which takes a filename (without file extension) as argument.

### High-Level Functionality

The framework provides a range of high-level functions to operate more easily. These combine some of the functions descriped above. Calling `export_write` means that `export` and `write` no longer has to be called separately and are thus substitued with a one-liner. The functions `debug_export_write` and `store_many_create` have a similar relationship.

```python
def export_write(layout: dict, filename: str='gen-report'): ...
def debug_export_write(layout: dict, filename: str='gen-report'): ...
def store_many_create(layout: dict, *_object: barchart_obj or linechart_obj or display_obj or table_obj or textbox_obj or dropdown_obj or tuple): ...
```

### Debugging

`debug_copy` stores a pretty JSON-formatted version of the configured Layout in the `etc` folder.

```python
def debug_copy(layout: dict):
```

### Layout Operations

The `model` module exposes functions to set the default background color, adjust the maximal width and height of the pbix report.

```python
def background_color(layout: dict, color: int, color_strength: float, section :int=0): ...
def set_max_width(layout: dict, width: int, section:int=0): ...
def set_max_height(layout: dict, height: int, section:int=0): ...
```
Aslak Johansen's avatar
Aslak Johansen committed
128
129
## Visual Elements

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
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
**Note:** All functions described in this section are from the `make` module.

An element is represented as an abstraction over a `pbixObject`. Such abstration is for example a `table_obj`. Thus two classes make up a PowerBI element, the `pbixObject` and the abstraction. An element is created through the `make` module which exposes the following functions:

- `make.table(...)` to create a Table element.
- `make.display(...)` to create a Card element.
- `make.dropdown(...)` to create a Dropdown element.
- `make.graph(...)` to create a Linechart element.
- `make.period(...)` to create a Section element.
- `make.text(...)` to create a Textbox element.
- `make.barchart(...)` to create a Barchart element.

These functions return two objects; the element-object and an identifier for PowerBI. The `pbixObject` is accessible by through `frame`.

```python
element, _id = make.table(...)
element.frame.*function()*
element.*function()*
```

**Note:** Storing an object does not require the tuple returned from the make function to be indexed. Both `store` and `store_many` accepts the full tuple as input:

```python
# Example
element = make.table(...)
layout = model.store(element)
```

The `pbixObject` (accessible in all element-objects, through the `frame` field) provides functionality to manipulate the location, size, title, font color, font size and border of the element. Thus, the appearence of all elements can be customized to the same degree:

```python
def set_pos_x(self, x: float):
def set_pos_y(self, y: float):
def set_title(self, title: str):
def set_height(self, height: int):
def set_width(self, width: int):
def remove_title(self):
def set_title_text_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_title_font_size(self, size: int):
def set_title_background_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_background_color(self, color: int, color_strength: float=Tone.LIGHT):
def toggle_border(self, toggle: bool):
```

### Barchart

The *barchart* element is created with a dataset, a y-column (the value axis), an x column (the category axis) and a label column for data descriptions. Note that these are not required, as generating a PowerBi report consisting of elements without associated data is allowed.

**Note:** Arguments such as `dataset` and `*_column` are strings. These strings indicate the name of a dataset or a column, as configured in PowerBi. The framework by default uses the REFIT Smart Home Dataset, thus the dataset in the examples provided are denoted as, `dataset="refit"`.

```python
def barchart(dataset: str='', y_column: str='', x_column: str='', label_column: str=''):
```

Once declared, the element exposes functionality to manipulate the appearance of both axes as well as the legend, in terms of font color. Furthermore, *hierarchy* can be activated (note that the deactivation operation is not supported). This formats the x-column (the category axis) to display data in days, months, quarters and years. This is also referred to as a Date-Hierarchy.

```python
def set_y_axis_font_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_x_axis_font_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_legend_font_color(self, color: int, color_strength: float=Tone.LIGHT):
def enable_hierarchy(self):
```

### Linechart

The *linechart* element is created with a dataset, a y-column (the value axis), an x-column (the category axis) and a label column for data descriptions:

```python
def linechart(dataset: str='', y_column: str='', x_column: str='', labels_column: str='', y_interpret: str='Sum'):
```

Similarly to *barchart*, the *linechart* provides functionality to manipulate the appearence of both axes.

**Note:** It is not advised to use `y_interpret` as it might result in error. It is currently *deprecated* functionality and can in most cases be ignored.

```python
def set_y_axis_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_x_axis_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_legend_label_color(self, color: int, color_strength: float=Tone.LIGHT):
```

### Display

To create a *display*, provide a dataset and a column. By default the display shows the minimal value of all values inside the column. 

```python
def card(dataset :str='', column: str=''):
```

The category label can be toggled ON and OFF. It can also be defined to display particular text.

```python
def set_display_font_size(self, size: int):
def toggle_category_label(self, toggle: bool):
def set_category_label(self, text: str):
```

### Dropdown

To create a *dropdown* element, provide a dataset and a data column. The dropdown will display all unique values from the data column and provide the user with a check-box for each.

```python
def dropdown(dataset: str='', data_column: str=''):
```

The background color and font color of the dropdown can be defined:

```python
def set_dropdown_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_dropdown_text_color(self, color: int, color_strength: float=Tone.LIGHT):
```

### Period

The *period* element is what is called a Section-element in PowerBI terminology. This is usually created with association to a column that includes timestamps to provide the user with a way of filtering data based on time.

To create a *period* element, provide a dataset and a date column.

```python
def period(dataset: str='', date_column: str=''):
```

The period-element comes with a slider and two date-input-boxes. The appearance of these can be manipulated in terms of color:

```python
def set_slider_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_date_input_font_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_date_input_background_color(self, color: int, color_strength: float=Tone.LIGHT):
```

**Note:** There is currently no way of manipulating the position of the slider handles programmatically. An issue has been created for this.

### Table

The *table* element can be created by providing a dataset and a list of columns. For each object inside the list, the framework creates a column for that object. An object inside this list is a `tuple` that contains a column type and a column name, e.g `(Column.IMAGE, "images")`.

```python
def table(dataset: str='', columns: list=''):

# Example
t, _ = make.table('mydata', [(Column.STRING, 'name'), (Column.NUMBER, 'floor')])
```

The appearance of the table can be manipulated in terms of color and font size. It is possible to define diverse colors for odd as well as equal rows. The color of the vertical and horizontal lines can also be defined. Finally, it is possible to manipulate the appearance of the header, that is, the column-titles.

```python
def set_row_font_size(self, size: int):
def set_odd_row_background_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_odd_row_font_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_equal_row_background_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_equal_row_font_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_header_font_size(self, size: int):
def set_header_font_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_header_background_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_grid_horizontal_color(self, color: int, color_strength: float=Tone.LIGHT):
def set_grid_vertical_color(self, color: int, color_strength: float=Tone.LIGHT):
```

### Textbox

A textbox does not show data, rather it displays static text that is not meant to change. It can be used for various reasons, such as providing the PowerBi report with a title.

To create a *textbox* provide a string of text.

```python
def textbox(text: str='textbox texts'):
```

The font weight can be manipulated to *italic*, **bold** and normal. The alignment can be centered, left or right. The font size can be adjusted as well.

```python
def set_font_weight(self, weight: str):
def set_font_size(self, size: int):
def set_text_alignment(self, alignment: str):
```

Aslak Johansen's avatar
Aslak Johansen committed
306
307
## Styling

308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
The framework provides a utility module called `_type` to aid the process of declaring *styling* such as color or tone. This module is used in all of the [examples](#examples) provided.

### Color

Colors are accessed through `Color.*color*`, where the colors are highlighted below.

```python
WHITE: int = 0
BLACK: int = 1
LIGHT_BLUE: int = 2
DARK_BLUE: int = 3
YELLOW: int = 4
RED: int = 5
GREEN: int = 6
CLOUD: int = 7
ORANGE: int = 8
PURPLE: int = 9
```

**Note:** These are all integers and can be used in any function that requires a color as argument. For example does `set_odd_row_font_color` in `table` require a color and can thus be executed with `set_odd_row_font_color(Color.GREEN)`.

### Tone

The tone is another setting which can be applied to most functions. The *tone* defines the color strength and might operate a bit counter intuitive. `LIGHT` refers to the *default* color where `DARK` decrements the color strength. There is, of course, also a `MEDIUM` setting which can be applied.

```python
DARK: float = -.5
MEDIUM: float = -.25
LIGHT: float = 0   

t.set_odd_row_font_color(Color.GREEN, Tone.DARK)
```

### Font

The *font* class includes not only settings for the weight, but also a setting for alignment. Alignment options are accessed through `Font.Alignment.*alignment*` where weights are accessed by `Font.*weight*`.

```python
# font
BOLD: str = 'bold'
NORMAL: str = 'normal'
ITALIC: str = 'italic'

# aligment
CENTER: str = 'center'
LEFT: str = 'left'
RIGHT: str = 'right'
```

### Column

The *column* class currently only applies to the `table`, and is accessed through `Column.*column*`.

```python
NUMBER: int = 0
IMAGE: int = 1
STRING: int = 2
```

A utility function is provided to obtain the type of column, but in reversed order. That is, from an integer. The function returns a string.

```python
def get_column_type(column: int):

_type.get_column_type(1) # results in 'IMAGE'
```

Aslak Johansen's avatar
Aslak Johansen committed
375
## Examples
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
376

Aslak Johansen's avatar
Aslak Johansen committed
377
378
379
380
Examples are provided in the `examples` folder.
- `basic_elements` is a simple example covering each element supported by the current version of the framework.
- `advanced_elements` is an example that adjusts colors and positions of select elements.
- `empty` illustrates how to generate an empty report (or to clear one).
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
381

382
383
384
385
386
The following image visualizes the output of the *advanced* example. Note, that sizes and colors or manipulated for all elements but the card:
![AdvancedScreenshot](examples/img/advanced-example.png)

The following image visualizes the output of the *basic* example, demonstrating an example of each element:
![BasicScreenshot](examples/img/basic-example.png)
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
387

Aslak Johansen's avatar
Aslak Johansen committed
388
## Authors
Emil Stubbe Kolvig-Raun's avatar
open    
Emil Stubbe Kolvig-Raun committed
389

Aslak Johansen's avatar
Aslak Johansen committed
390
391
392
- Emil Stubbe Kolvig-Raun, [eskr@mmmi.sdu.dk](mailto:eskr@mmmi.sdu.dk)
- Aslak Johansen, [asjo@mmmi.sdu.dk](mailto:asjo@mmmi.sdu.dk)
- Mikkel Baun Kjærgaard, [mbkj@mmmi.sdu.dk](mailto:mbkj@mmmi.sdu.dk)
Mikkel Baun Kjærgaard's avatar
Mikkel Baun Kjærgaard committed
393