ConnectWise Manage and Power BI Desktop Live Connection & Integration

[jessicaL_51]

Hello everyone,

I am working on a Power BI desktop version, trying to build a live connection from ConnectWise Manage on-premises (a ticketing system) to Power BI, to extract ticket data and tables under ConnectWise Manage.

At the moment I have an API key (public & private key) generated from ConnectWise. And I have followed the procedure of the posts below (also posts on Reddit): 

• https://opalbiz.com/microsoft/view-connectwise-data-in-power-bi-via-rest-api/ 
• https://comertechnology.com/cw-manage-getting-started-with-powerbi-the-update/ 
• https://dbeta.com/2020/11/19/ConnectingCWMtoPowerBI

However, it doesn't work by connecting web sources using API, showing 500:Internal Server Error. Another issue is that the URL I chose cannot be retrieved under data source settings - global permissions. 

Screenshots details: 

1. Data Source Settings

Data Source Settings

2. Get data from web source: the content after 'Basic' has been encoded to Base64 format

Get data from web source: the content after'Basic' has been encoded to base64 format

 3. Unable to connect - shows 500 error

Unable to Connect - Shows 500 Error

I have also tried RPA (power automate desktop) and doing advanced queries in PBI. I would appreciate knowing the best practice for connecting CW Manage and PBI desktop. (Or any chance of using Excel to automatically extract live data from ConnectWise Manage?) 

Thanks for your reading and please feel free to leave your thoughts below! 🙂

Kind Regards,

[Corlia]

Hi Jessica,

I'm curious, did you manage to solve this issue? I have the same requirement and encounter the same issues here

[howellchrisj]

@jessicaL_51 , I normally work on connecting to Connectwise Manage (cloud version), but recently worked on connecting to Connectwise Manage (on-premise).  Is there a reason you are using the API vs. connecting directly to the SQL Server database?  

If it is a security governance issue, I still may be able to assist with the API.  In your first post, the link to the Opal website, I wrote that many years ago, and while things have changed,, just yesterday, I revisited it as I need to extract data that wasn't in the Connectwise Manage URL Reporting tool.  

For your API call, you have the Authorization and about two years ago, ConnectWise required a cliendID.  I am not sure if the clientID is only needed for the cloud version, but if you do need it, it would be created in the ConnectWise Developer portal.

Here is a sample of my lastest API call for you to reference.

Source = Json.Document(Web.Contents( https://api-na.myconnectwise.net/v4_6_release/apis/3.0/

    ,  [ RelativePath = "company/companies?pagesize=1000&page=1",  Headers=[Authorization="Basic asfd34lkjalksjdfakj434souyavbaurbg39848sfoia0r83u4bg%=",clientID="duddn6686-7i45-2233-823r-849545454sdfa", ContentType="JSon"]]))

(please note the Authorizations Key and ClientID have been altered, but are similar values)

Another thing to mention, I don't believe you could ever make the API approach to ConnectWise a realtime data pull.  You could do this via DirectQuery into the SQL database, or from a Push into Power BI.

[jessicaL_51]

Hi Lucien,

Hope you are doing well.

Thank you for sharing the info. however, I am looking for solutions for the ConnectWise Manage platform instead of Cloud.

I would appreciate it if you have knowledge of ConnectWise Manage (data warehouse is on-premises) or ConnectWise & Power BI automation. And thanks again for your answer. 

Kind Regards,

Jessica

[v-luwang-msft]

Hi @jessicaL_51 ,

Pls refer the below ,and check if it is helpful :

https://cw.wise-sync.com/support/solutions/articles/36000041214--resolved-known-issue-connectwise-cl... 

 Best Regards

Lucien