BI Tools and Windchill

[K_Trekiel]

This post is about how to successfully authorize BI tools with Windchill.

A desktop version of QlickSense and MS Power BI are considered, but the technique for Power BI is applicable to Excel + PowerQuery.

With the current power of Query Builder coming to WT 11.0 upwards and growing demand for BI tools within the enterprise my company followed several attempts to connect WT Data to BI tools to easily slice and dice and communicate product information as well as design process information.

QlickSense was easy to configure, once XML report is exposed you can use REST connector to download it to the session – use data for the dashboard, pivot, etc.

Con: Your credentials travel with your BI App file.

MS Power BI works well with XML coming from QB, there is wizard assisting you, as long you authenticate Basic mode to Windchill, you choose Basic, give credentials and you are all set.

Pro: your credentials do not travel with your BI app file.

There are cases, however when your report is so complex or just out of QB limits, you hire a developer to do that using wt API, we had a similar case, resulting page is an html table. Assume you don’t want to rewrite report to version giving XML.

Current BI tools handle HTML pages with ease, however, all of the sudden MS Power BI stopped authenticate.

Simply saying credentials are invalid.

I have tried several techniques and sharing now one that worked.

Looks like you need to switch credentials to Anonymous

Then authenticate with a special header parameter:

Authorization: Basic user_password_code

Make sure code is preceded by “Basic”+”blank space”

Screens below where to set that, how I get the code, it is my Windchill and Chrome Dev tools (F12).

Now your Power BI authorize successfully.

Con: Your authentication is now within the app/Excel file PowerQuery formula.

Hope you can find use of this authentication method within Power BI or other BI tools to analyse and share Windchill Data.

 

[JeffZemsky]

@K_Trekiel Did you consider using the WRS framework as well.  While not all the domains are present today (we add more at each release) we were able to connect with Qlik very easily using the framework.  I will add some more information to the thread after the PTC/USER Forum in a few weeks when I have chance to catch up.

[K_Trekiel]

@JeffZemsky, Good point, WRS is not yet as dangerous as Query Builder is today, that is the reason, but I keep an eye on how it evolves.
May I ask you point some licensing considerations details from, especially having said what above? QSense Server and powerbi.com, how InfoEngine license support the case? In a nutshell how to comply when joining an enterprise BI tool to Windchill?

[desosav]

Many thanks for your post. It was really helpful as I was also trying to configure power bi with wnc reporting.

Did you manage to publish a power bi report to PowerBI service (powerbi.com) and run data refreshing?

Datasets on powerbi service cannot be configured with HTTP request header parameters (Authorization “Basic”+”blank space”) so I have to use one of the three  authentication methods.

Maybe setting up a data gateway could help. Any ideas?

Thanks

[K_Trekiel]

Interesting, definitely PowerBI data gateway would be my first choice, as we would prefer to not expose pdm directly to Miforcosts cloud.

However, I did not try it yet.

It worked well for me when I used this below in Qlik as just ordinary URL connection.

https://user:password@server.domain/Windchill

[desosav]

Since my last post i have tried to configure wbr reporting within powerbi desktop, configure a gateway and publish the report to powerbi.com

Everything worked like a charm until i tried to refresh the datasets online (manually & automatically)

I am getting the below error. It is weird because i am connecting anonymously.

Underlying error code:	-2147467259 Table: Table 1.
Underlying error message:	The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Referer
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.Reason:	Expression.Error

[K_Trekiel]

This kind of error we receive even within Excel when do query copy/paste. For some reason, the engine imposes once upon a time applied Basic authentication, while we would like to use Anonymous + headers to authenticate.Setting needed in Power BI while using headers to authenticate