Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jessicaL_51
Frequent Visitor

ConnectWise Manage and Power BI Desktop Live Connection & Integration

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): 

 

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 SettingsData 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 formatGet 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 ErrorUnable 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,

4 REPLIES 4
Corlia
New Member

Hi Jessica,

 

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

howellchrisj
Frequent Visitor

@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
Frequent Visitor

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
Community Support
Community Support

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors