We have a member already created, however, if you didn’t have a member created, you can click the +, and create a new member. Enter the MemberID, password and any required fields.
Once you click save, you should go to the API Keys tab. THIS IS THE MOST IMPORTANT TAB. It will provide the public and private keys that will be needed below to setup the API.
Once you click on the + to add a new API Key, you would enter a Description. On save, it will show you the Public and Private Key. These should be recorded and saved, as both will be needed for the API to work.
Since this is already established, the interface is only showing the public key. Please take note of the Note: on the screen.
Once this is setup in ConnectWise, we can head over to Power BI and setup the connection to the API.
Power BI Connection Setup
Now that we have the connection information setup, we can setup a new Web source. The best location for this is using Power BI Desktop.
Once the Web option is selected, you get the choice of Basic or Advanced. In our case, we need Advanced. Once the options are shown, there are two main sections. First would be the URL parts, which allows for breaking the URL down in case you wanted to change a small part (you don’t have to rebuild or adjust the entire URL). The second is in the HTTP request header parameters.
For the URL, the breakdown I used is as follows:
Notice the 5th section is blank in the first screenshot; this is due to the connection I am using to pull back the data. In my case below, the second screenshot you will see I am using servicetickets. To help gather the available modules, I used the ConnectWise Manage Developer guide. https://developer.ConnectWise.com/Manage/Developer_Guide
Now for the HTTP Request Header(s). I tried multiple ways to get the header information setup; looking at multiple websites and selecting multiple items including Authorization, ContentType, Accept and Cookie. These were some of the recommendations from the ConnectWise guide. In the end, I just utilized the Authorization Header only. This was a little tricky and had to be in a very specific pattern.
The Authorization Value should utilize the following pattern, using the Public and Private Keys from above, along with your companyID from ConnectWise. The first part of the Authorization value should always say “Basic “ (with a space) followed by the value from below.
Public Key: 34KH3l4h34nj34TH
Private Key: Ck345k34098h44RG
Pattern (with the plus sign and the colon, no spaces):
The idea is First part is UserName, then after the : is the password.
Now the question here, is how to I get this into Base64, luckily, a website can do that:
With the information in hand, I can populate the Authorization value field:
(please note I changed the private and public keys and company name above for privacy reasons).
If prompted for a login, Anonymous, Web API, Basic, Windows…. Select Anonymous.
Once you have entered the above information, you can click OK. The system will think for a moment and you should get the following screen:
Power BI – API Data Viewable and Clean
The data is returned in Power BI. The next issue is extracting the data and viewing it in a clean, orderly fashion (and more than 25 records, API default). If you were to click on the Record field, you will drill into the data and see information for a single record. Since we want to look at the data in whole and not just a single record, we need to make some tweaks to the query.
Let’s get started cleaning up the data.
1.Let’s edit the Query and return more than just the 25 records, and while we are at it, let’s pull the max, 1000 records in order of last updated desc.
- Currently the tab at the top would be the Transform tab, but for our case, we are going to select Home and Advanced Editor
b. On this page, we will edit the Web.Contents line and add the pageSize and orderby.
c. This would load all 1000 records ordered by the dateEntered descending
2. After completed, we want to get rid of the Record values and just see data… well, we have two more steps.
- Go back to the Transform tab and click To Table
b. This will prompt a To Table screen, just click OK
c. Once you click Okay, you will get the following values and a little icon in the corner will help.
d. If you click on the Icon in the corner, this will give you a listing of all the fields available and as you can see in my screenshot, it even warns that I may need to add more. I also recommend keeping the original column name, because when you expand additional columns later, it can help keep them be unique. And once complete, you can easily do a find and replace on the Advanced Editor screen and remove “Column. with “ (or copy and paste into notepad like I did).
e. Once you click Ok here, you get the following:
f. To remove the Record values, you can click on the Icon on each column and select the fields to see.
3. Now we have data, and we can apply and close and analyze the data further.
Now we are connecting Power BI to ConnectWise and can start to analyze the data further and further. I believe the possibilities for utilizing ConnectWise through Power BI are virtually endless. Now the fun begins, and we can build the reports and dashboards needed to analyze the company.