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 the data connectors from ClockWise, named 'ClockWise v1.0'. After choosing this connector, click on 'Connect'.
Now click on the background. A popup is shown with eight fields. 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 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.
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.
When using the 'ClockWise' data connector, multiple tables get loaded. PowerBI tries to relate these tables together, based on the column names with the same name (e.g. 'hours.resource_id' in the Hours table gets coupled with the 'resources.resource_id' column in the Resources table). Unfortunately, this will not always go correctly. Besides, when you load data from a different source altogether which needs to be coupled to data from ClockWise, you will need to configure the relationship yourself. To do this, go to the relationship overview (third button on the left side). There is an overview of the different tables, with lines between them with either a 1 or an *. When hovering over the line, the columns of the tables which are couples gets highlighted. To make these relationships, there are two possibilities: By dragging a column from one table to the corresponding table in the second table, or by clicking 'Manage relationships' in the menu on top. You will see a window with all the relationships defined, where you can turn them on or off, delete them, edit htem or make new ones. In that case, you see a window with a selection for the first table, a preview of that table, a selection for the second table and a preview of the table. Below there are configurations of the relationship. Select the tables which need to be linked, and in the preview the corresponding columns. Press 'OK' to create the relation.
The relations which should have been made by PowerBi are:
- hours.resource_id ↔ resources.resource_id
- resources.employee_id ↔ employees.employee_id
- resources.project_id ↔ projects.project_id
Overview of the most used fields
The following table gives an explanation of the most used fields in Power BI. Most of them will be self-explanatory.
As a general rule of thumb, the id fields are not necessary for graphing purposes. A few of those are needed to connect the four different tables to each other, but those relations should be there already (see the 'Managing relations' section above).
|employees.contract_hours_min||Minimum contract hours of the employee|
|employees.department_name_path||Path of department names (by default separated by ' / ')|
|hourly_sales_rate||Hourly sales rate (available in all tables if 'retrieve rates' is set to true)|
|hourly_purchase_rate||Hourly purchase rate (available in all tables if 'retrieve rates' is set to true)|
|hours.hours||Amount of hours|
|hours.hourstatus||Hour status (written, submitted, approved or rejected)|
|hours.units||Units associated with the hour entry|
|projects.customer_name_path||Path of customer names (by default separated by ' / ')|
|projects.hours_budgetted||Budgetted project hours|
|projects.project_name_path||Path of project namen (by default separated by ' / ')|
|projects.start_date||Start date of project|
|resources.resource_status||Resource status (running, closed or in_preparation)|
|employee_id||Employee identifier. Is used in the relation between the resource and employee tables|
|project_id||Project identifier. Is used in the relation between the resource and project tables|
|resource_id||Resource identifier. Is used in the relation between the hours and resource tables|
There are other similar fields to the ones listed above, for instance project_code_path, yielding a path of project codes instead of project names. These are not listed to keep the table short.
Keep in mind that (in the last example) the similar named project_id_path is probably not the field to use, unless a connection with another table (with project entries) is needed.
Advanced: The power query editor
The data connector has a number of functions for the power query editor: The underlying function of the two connectors could be found there, as well as a third function from which information from one of the API endpoints could be retrieved.
To use the editor, go to 'Get Data → Others → Blank query'. The Power Query Editor will be opened. Press 'Advanced editor'.
A window will be opened with the text (on different lines): 'let Source = "" in Source'. Remove the double quotes and type 'ClockWise_Data_Connector.' with the name of the function you want to use. There are different options:
- ClockWise_Data_Connector.Contents. This function is equivalent to the 'ClockWise' data connector. It has the same eight arguments as the connector. Note: You could fill in 'null' when you wanted to omit a particular parameter. This function returns a table with the four table names in the 'Name' column, and the corresponding tables in the 'Data' column.
- ClockWise_Data_Connector.GetTable. This function has no associated data connector. It has the following parameters: instance, client_id, client_secret and url. The first three arguments are the same arguments as all the other functions/data connector. The fourth argument is the name of the api v2 endpoint. For instance, if you want to retrieve all the projects via the report endpoint, the url parameter would be "/report/flat/projects". Warning: In contrary to the other functions is this fourth argument mandatory, as the function requires a valid endpoint. This has the side effect that Power BI associates credentials with this URL. If the url parameter will be modified in the future, you will need to give your credentials again.
For the complete documentation of M, the language of Power Query, see https://docs.microsoft.com/en-us/powerquery-m/
Case: Summation of hours on sub project level versus budgetted hours on the same level.
In this case we show how you can sum hours on sub project level, together with other information of that same project.
We use the following project structure:
We want to graph the total hours of each of the projects "Documentation", "Technic" and "Website" together with the budgetted hours in Power BI.
To accomplish this, we need the following parameters in Power BI:
- hour parameters: e.g.
- resource parameters: Nothing
- project parameters:
- employee parameters: Nothing
Now, the following fields are loaded in Power BI: "project_name_1" (contains "New feature"), "project_name_2" (the one we are interested in), and project_name_3 (containing "Design", "Implementation", etc.).
Check the following column names in the column on the right: "project_name_1", "project_name_2" and "hours_budgetted". Now make a new quick measure:
- Calculation: Total for category (filters applied)
- Base value: Sum over hours.hours (Drag "hours.hours" from the "Hours" table on the right)
- Category: projects.project_name_3
Drag the quick measure from the column on the right to the table.
Now there are two rows per sub project: one containing the sum of the hours, and one with the budgetted hours. To solve this, select 'First' in the menu of "hours.hours_budgetted" in the "Values" section in the middle column on the right. With that, the rows are combined.
Note: This works with all fields of the sub project, not just with budgetted hours.
Frequently Asked Questions
Power BI gives an error after I logged in about the downloaded data unexpectedly being HTML.
It is possible that there is a whitelist of URLs allowed in your company. If an error is given, it might be possible that the URL https://oauth.powerbi.com/views/oauthredirect.html is blocked by the whitelist, so that PowerBI cannot finish logging in.
How can I configure Power BI Server to (automatically) refresh data?
You need an 'On-premise data gateway' to achieve this. Microsoft wrote documentation how to configure the gateway with a "custom connector" like ClockWise's
- To configure the gateway: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-custom-connectors
- To configure Power BI with the gateway: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-scheduled-refresh
Make sure you enter the exact same data in the gateway as in Power BI Desktop! Power BI matches the two on the ClockWise account, Client ID and Client Secret!