AnsweredAssumed Answered

PI SQL - Linked Server - Views

Question asked by francois_ruel on Jul 17, 2019
Latest reply on Nov 25, 2019 by francois_ruel

Hi!

We are using PowerBI Report Server to do Direct Query to PI using a Linked Server.  The issue is we have to use OpenQuery statement to access the views created for the Event Frames and Asset Snapshot data.

 

For the Event Frames, PowerBI is obviously adding the WHERE statement after the OpenQuery which cause the OpenQuery to load all the Event Frames before filtering.

 

I would like to use a 4th part name query style, like:  SELECT * FROM [MyLinkedServer].[Master].[EventFrames].[MyView] WHERE StarTime> '2019-07-17'

instead of SELECT * FROM OPENQUERY([MyLinkedServer],'SELECT * FROM [Master].[EventFrames].[MyView]') WHERE StartTime>'2019-07-17'

 

From browsing the Linked Server in MS SQL Management Studio, I can see and query the PI SQL tables using the 4th part name but I cannot see or access the views.

 

Any idea?  Security settings, required configuration, feature not yet available?

Using PI SQL RTQP 2018 SP2.

 

Thanks!

Francois

Outcomes