View ConnectWise Data in Power BI via REST API
Latest Revision: 10/24/2022
Click here to learn about our custom ConnectWise Power BI reporting packages.
With the recent uptick in using data to make better decisions for companies, we decided to look at linking Power BI to ConnectWise (Cloud). As a person who by nature loves to look at raw data, I didn’t want to use the existing reports in ConnectWise and didn’t want to spend the monthly cost to try and get pre-made reports. Some existing products on the market like BrightGauge might be the solution for many. However, I wanted to see how easy or difficult it would be to connect Power BI to ConnectWise via the API.
To start off, I would like to say, this was about 5 years in the making. I reviewed the ConnectWise API a few years ago, but couldn’t get it to work, and new projects arose, so I put it aside. Recently in some management meetings, we started to talk about KPIs and one of the things that always comes to mind when talking about KPIs, is, how best to display the KPIs. So, why not Power BI!!
I have been working with Power BI for about 3 years now and have implemented various solutions for our clients. The one thing I always emphasize is the vast capability of Power BI and using it to make the decisions you want, when you want. As with our company, looking at our ConnectWise data, we know not all the data is clean, but once we have the Power BI site setup we can better monitor and track the processes to ensure cleaner data. Cleaner data always means more accurate KPIs.
So, where do we start this process? For me, it all starts at the data level, however, since it is ConnectWise, it starts back just a little further.
ConnectWise API Member Setup
First thing, make sure there is an API Member setup for the Source you are connecting. In our case, we were connecting to ConnectWise Manage:
Through ConnectWise Desktop, go to System, Members, tab: API Members.
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.
Company: Opal
Public Key: 34KH3l4h34nj34TH
Private Key: Ck345k34098h44RG
Pattern (with the plus sign and the colon, no spaces):
Company+PublicKey:PrivateKey
The idea is First part is UserName, then after the : is the password.
Opal+34KH3l4h34nj34TH:Ck345k34098h44RG
Now the question here, is how to I get this into Base64, luckily, a website can do that:
https://cryptii.com/text-to-base64
Text: Opal+34KH3l4h34nj34TH:Ck345k34098h44RG
Output: T3BhbCszNEtIM2w0aDM0bmozNFRIOkNrMzQ1azM0MDk4aDQ0Ukc=
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).
A few years ago, ConnectWise updated the API connection and it is no longer just Authorization, but it requires a ClientID as well. This ClientID is obtained from the ConnectWise Developer Network.
Once logged into ConnectWise Developer, you can click on the “ClientID” option at the top. You will then receive two options, Partner Client IDs or Vendor Client IDs. For our sake, we are a Partner, so we would select the Partner Client ID.
Once you enter both the Authentication and ClientID, 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.
New Details as of 10/24/2022
1. Above we added the client ID, this will not work without the client ID
2. If you are looking to have this refresh in the Power BI Service, you would need to update your Web.Content connection string to a pattern more similar to the following:
- The key difference between this string and the earlier one is at the end of the API call, you shouldn’t add the ?<parameter>.
- Reason for the pattern is that when you try and load the original connection string above, and you click on Data Sources, you will see a warning. The approach here will eliminate the warning and allow for Power BI Service refreshes.
- The other caveat for the Service refresh with this is that it may require the privacy setting.
3. Opal has run into many of the issues that could arise. The process of looping through the pages was time-consuming to get correct, so if you are looking for some Rescue help, please let us know and we can work on getting you over the hurdle.
Want custom ConnectWise Power BI reports? Visit our ConnectWise Power BI Reporting page for more information.
