Querying stuff in narrow format for Wonderware Historian is easier, but the formatting sucks, as you need to select your tag or description and effectively filter out the tag you are searching for, from a long list.
In PowerBI, this just plain sucks, as you essentially need to duplicate columns the filter them for each tag you are searching for.
Wide format is much nicer, it gives you DateTime as your rowid's and a tag per column.
The only problem is that you pull the wide format you need to use OpenQuery, which essentially means you need to provide a string to parse via ODBC. Which is all good and all, until you need to do a dynamic query.
Why would you need this. Let say you want to query all the data from a certain date, until now, or the results from the last 48 hours, so you can provide a daily report. You know, all the most common tasks that recent data is important for.
Fortunately, there is a function in the wonderware SQL database to acheive this, that can be quoted. The important bit is getdate(). It's case sensitive (ie the old GetDate() doesn't work)
Here's my query - that works. I've anonymized the tags. Mine won't be any use to you, unless you work on my site, in which case, come and find me.
SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 21), [Site.TAG1_PV], [Site.TAG2_PV], [Site.TAG3_PV], [Site.TAG4_PV], [Site.TAG5_PV], [Site.TAG6_PV], wwResolution FROM WideHistory WHERE wwRetrievalMode = 'Cyclic' AND wwResolution = 60000 AND wwQualityRule = 'Extended' AND wwVersion = 'Latest' AND DateTime >= '20200910 12:00:00.000' AND DateTime <= getdate() ")
For completeness, here's the narrow version of the same query.
SET NOCOUNT ON DECLARE @StartDate DateTime DECLARE @EndDate DateTime SET @StartDate = '20200910 00:00:00.000' SET @EndDate = CONVERT(date,GetDate()+1) SET NOCOUNT OFF SELECT temp.TagName ,Description ,DateTime ,Value ,vValue ,Unit = ISNULL(Cast(EngineeringUnit.Unit as nVarChar(20)),'N/A') ,StartDateTime From ( SELECT * FROM History WHERE History.TagName IN ('Site.TAG1_PV', 'Site.TAG2_PV', 'Site.TAG3_PV', 'Site.TAG4_PV', 'Site.TAG5_PV', 'Site.TAG6_PV') AND wwRetrievalMode = 'Cyclic' AND wwResolution = 60000 AND wwQualityRule = 'Extended' AND wwVersion = 'Latest' AND DateTime >= @StartDate AND DateTime <= @EndDate) temp LEFT JOIN Tag ON Tag.TagName =temp.TagName LEFT JOIN AnalogTag ON AnalogTag.TagName =temp.TagName LEFT JOIN EngineeringUnit ON AnalogTag.EUKey = EngineeringUnit.EUKey WHERE temp.StartDateTime >= @StartDate
Oh - Added bonus. The Wide query runs significantly faster than the narrow version. Each row effectively runs as a query. In my case there are 1/6 as many rows to query. Therefore 1/6 the time.