Power BI Data Connector manual
It is possible to visualise data from ClockWise within Power BI. This could be done via the Data Connector. In this manual it is explained how to configure the data connector to get the right data from ClockWise.
Creating an API client
To be able to work with Power BI, you need an API client. This could be done within ClockWise in Configuration → Settings → OAuth/API clients. Click on 'Add OAuth client' and give a name to the client (e.g. Power BI). Note: We need the Client ID and Client secret which are generated on this page, so note these or keep the page of the API Client open while creating the connection.
'Installing' the Data Connector in Power BI
The data connector could be recognized by its .mez extension. The file has to be placed in the folder 'Documents\Power BI Desktop\Custom Connectors' in the current user. If the folder does not exist, create it. Moreover, to actually load the extensions in aforementioned folder, we need to change a setting in Power BI: In 'File → Options and settings → Options → Security → Data extension' select '(Not recommended) Allow any extension to load without validation or warning'. Now restart Power BI.
Retrieving data with the data connector
There are a few settings in Power BI which are configured per file. A couple of them are options which could release stress on the server. For larger ClockWise accounts, it is recommended to adjust these settings before creating the data connector.
In Power BI, go to 'File → Options and settings → Options → Data Load (Under Current File)'
Turn the following options off:
- Background Data → Allow data preview to download in the background.
- Parallel loading of tables → Enable parallel loading of tables
The latter of the two is the most important to turn off: Otherwise four (possibly big) tables are downloaded from the server at the same time, which could give stress to the server.
Creating the connection
To retrieve the information from ClockWise via the Data Connector, you need to click in Power BI on 'Get data'. Now a menu is shown with all data connectors in Power BI. Click on 'Others'. You will find two data connectors from ClockWise, those are 'ClockWise' and 'ClockWise Hours Data'. The latter of those will only retrieve the hours table, but will feature more columns in this table, like the name path of the project, the employee name, and so on. The former of those has split the data over four tables, where each have more specialised columns (For instance, the employee name is missing in the hours table, but could be found in the employees table together with employee-specific columns). After choosing one of those two, click on 'Connect'.
Now click on the background. Power BI warns that the connector is in beta, klik on OK (and check the box to skip the box any other time you want to create a connection).
Now a popup is shown with either four or eight fields. For the 'ClockWise' connector, those fields are respectively:
- ClockWise account (company name). Enter the name of your ClockWise account (for instance, when you normally log in at yourcompany.clockwise.info, you need to enter yourcompany in this box).
- Client ID. Enter here the Client ID you created in the API Client.
- Client Secret. Enter here the Client secret you created in the API Client.
- Retrieve rates. This field is optional, and could only contain the values 'true', 'false', or be left empty. When 'true', the rate information of the different tables will be retrieved as well.
- Extra parameters for the hour table. This field is optional, therefore may be left empty. If you want to give additional restrictions to the data, enter those here in the same way you would give them to the API (e.g. start=YYYY-MM-DD&end=YYYY-MM-DD if you would only want hours between two given dates). The valid options are documented in the API Documentation, located under yourcompany.clockwise.info/api/v2/doc → Reports →Hours in flat format.
- Extra parameters for the resources table. This field works exactly the same as aforementioned field, but now gives parameters to the resources report.
- Extra parameters for the projects table.
- Extra parameters for the employees table.
The 'ClockWise Hours Data' connector only has fields 1, 2, 3 and 5 of this list. To retrieve the rates with this connector, add the parameter 'fields=salesfields,purchasefields'.
The next window that will be opened the first time you make a connection with this ClockWise account, Client ID and Client Secret is the login screen. It will say that you are not signed in, and featue a 'Sign in' button. By clicking on this button, a popup is opened with the ClockWise Login page. Log in with your ClockWise credentials. Now click on Connect.
If you logged in before with the same ClockWise account, Client ID and secret, this screen is skipped. If you would like to login as a different user, you could go to 'File → Options and Settings → Data connector settings'. There you could edit the credentials of any data connector you loaded, logging in as a different user.
Choosing the tables
Now yet another window opens. If you chose the 'ClockWise' connector, this window featurs four tables: Employees, Projects, Resources and Hours. By selecting any table, Power BI could give a preview of a few rows and all columns of a table, however this is not recommended for large accounts, as it needs to retrieve the data twice. This could give a lot of stress to both Power BI and the server. It can be turned on or off in the setting just above those four tables.
Now choose the tables you want to retrieve, and click further. Power BI will calculate, initialise the tables and retrieve the data. If Power BI is ready, the data could be used to create visualisations.
On the other hand, if you chose the 'ClockWise Hours data' connector, the opened window has a preview of the data which will be loaded.
Power BI could refresh the data, so it is not necessary to recreate the connector if something changes within ClockWise. However, if the parameters need to be adjusted, like the start and end dates, you need to recreate the connector to enter the new parameters.