Power BI & Cherwell Auto Logoff

[Benjamin96]

Hello,

i need your help I am successfully using the data connector between Power BI and Cherwell. Among other things, I will be live dating for my company.

Unfortunately, I have to realize that I have to log in to PowerBI again and again after about 1-2 hours (Cherwell Login) Of course, this makes an automatic live data evaluation impossible. Has anyone of you experienced it? I can't find any proper attitude in Cherwell that prevents this from happening either. Maybe there is a trick here.

The goal would simply be a permanent data update (according to a schedule) WITHOUT having to log in again and again to establish a connection to the database.

Thanks very much

[Syndicate_Admin]

Hi @Syndicate_Admin 

I have experienced and resolved this issue for my company in the past.

For context, our instance of cherwell is hosted via a SAAS solution, so if yours is hosted internally, some of these steps may not be relevant to you.

1. Setting up a data gateway was always going to be last resort for me as well. To avoid this, I ended up hosting the Power BI reports on powerbi.com instead of the internal report server.
2. We found that the token would expire instantly if I used the same account for day to day Cherwell activities as in refreshing Cherwell data in Power BI. So I organized a separate active directory account to be created with a mailbox and granted it relevant Cherwell access.
3. We found that Microsoft Cloud App Security would interfere with using this account on powerbi.com so had to work with our security team to sort that out. 
4. Finally, we found that the token expiry had to be longer than the refresh interval of the data.

With all of this in place, I have had auto-refreshing data from Cherwell with minimal issues for about a year now.

I hope this helps.

Matthew

[Benjamin96]

That sounds interesting,

I just had a look myself and saw that the client token I find in the REST API Client was only valid for 20 minutes. I have now increased this value once. Unfortunately, the gateway would be the last card I would have to draw, since the security structure in my company would have to implement this first.

I wonder why there are so few posts on this topic, even though it should apply to everyone who uses Power BI and Cherwell to make "live" data evaluations.

Thank you!

[BA_Pete]

No problem.

You should be able to code a Client Token refresh request into your API connection query, so this is requested before passing it again with the main request body. Have a look on the forum of how to do this generically (not Cherwell-specific) and you should find plenty of ideas and code examples to help you get this working.

You could also build your own connector to handle this how you want, but that's not for the faint of heart!

Pete

[BA_Pete]

Hi @Benjamin96 ,

It sounds like you need to set up an on-prem gateway (https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem ) and add your custom connector to the gateway machine as well.

Once you've done that, the credentials timeout will be irrelevant as your gateway will provide them to the source each time it wants to refresh your dataset in the Service.

Pete