SQL saved views like this make me sad:

 
SELECT 
  e.Name as AssetName
, e.ID as AssetID
, et.ID as TemplateID
, tir.*
FROM NuGreen.Asset.ElementTemplate et
INNER JOIN NuGreen.Asset.Element e
ON et.ID = e.ElementTemplateID
INNER JOIN NuGreen.Asset.ElementCategory ec
ON ec.ElementID = e.ID
INNER JOIN NuGreen.Asset.Category c
ON ec.CategoryID = c.ID
CROSS APPLY NuGreen.DataT.TransposeInterpolateRange_Compressor
     (
        e.ID
      , 't-1w'
      , '*'
      , '1h' 
     ) tir
WHERE
c.Name = 'Equipment Assets'
AND (et.Name = 'Compressor' OR et.InheritancePath LIKE '%Compressor%')
OPTION(FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

 

It’s a PI OLEDB Enterprise query to run a (data query) on a (subset of assets) for a (time range). That cries out for being broken into components.

 

 

Breaking views into pieces

To continue this example, we can start by finding a list of assets. Incidentally, a query like this would be the ideal centerpiece (fact table) for a snowflake schema in a BI cube.

 

 

 
CREATE VIEW [NuGreen].[Asset].[CompressorsOfInterest]
AS
SELECT 
  e.Name as AssetName
, e.ID as AssetID
, et.ID as TemplateID
FROM NuGreen.Asset.ElementTemplate et
INNER JOIN NuGreen.Asset.Element e
ON et.ID = e.ElementTemplateID
INNER JOIN NuGreen.Asset.ElementCategory ec
ON ec.ElementID = e.ID
INNER JOIN NuGreen.Asset.Category c
ON ec.CategoryID = c.ID
WHERE
c.Name = 'Equipment Assets'
AND (et.Name = 'Compressor' OR et.InheritancePath LIKE '%Compressor%')

 

 

If we write a second view which performs the data call for these assets, we’re on the right track – we’ve broken the query into (subset of assets) and (data query for time range). But we could do better and separate (data query) from its (time range).

 

 

 

How do we break it apart? We’ve been calling a function with fixed parameters…

 

 

 
CROSS APPLY NuGreen.DataT.TransposeInterpolateRange_Compressor
     (
        e.ID
      , 't-1w'
      , '*'
      , '1h' 
     ) tir

 

 

The magic becomes possible when we switch over to the transpose function’s equivalent Function Table.

 

 

6470.sqloe_5F00_tvf_5F00_params.png
2134.sqloe_5F00_ft_5F00_columns.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Either this is a little-known trick, or it’s so obvious that nobody’s written about it here on vCampus, but PI OLEDB Enterprise saved views are completely stackable. WHERE restrictions in one view will cascade directly into nested views. Because the function table exposes StartTime, EndTime, and TimeStep as columns, those are ideal targets for this trick.

 

 

 

So we can make a data query view which, by itself, won’t execute because it is incomplete, lacking time range restrictions:

 

 

 
CREATE VIEW [NuGreen].[DataT].[CompressorDataInterpolateRange_Flex]
AS
SELECT compressors.AssetName, tir.*
FROM NuGreen.Asset.CompressorsOfInterest compressors
INNER JOIN NuGreen.DataT.ft_TransposeInterpolateRange_Compressor tir
ON compressors.AssetID = tir.ElementID
--no StartTime restriction
--no EndTime restriction
--no TimeStep restriction
OPTION(FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

 

And call it from a query (or saved view) with the time restrictions specified in the WHERE clause:

 

 

 
SELECT *
FROM [NuGreen].[DataT].[CompressorDataInterpolateRange_Flex]
WHERE
StartTime = 't-1w'
AND EndTime = '*'
AND TimeStep = '1h'

 

 

My testing indicates no quantifiable performance hit in taking this layered approach, and we end up with the advantages of flexible, reusable queries. Users now have this query in their toolbox.

 

 

Views with parameters from SQL Server

Recently, I was working with a customer who was trying to pull PI assets and data into a 3rd-party OLAP package, but the data access options were limited. There was only limited JDBC connectivity to a handful of specific targets – including Microsoft SQL Server, but not PI JDBC. So we chose SQL Server as our gateway to PI OLEDB Enterprise (as a Linked Server). If you’re unfamiliar with SQL Linked Servers, this MSDN article is a fair introduction. The product manual for PI OLEDB Enterprise holds specific configuration instructions.

 

 

 

 

 

In an ideal world, we could take a query drafted in PI SQL Commander (such as that very first one in this post), adapt it very slightly, and execute it from SQL Server to our PI OLEDB Enterprise Linked Server. The Execution Plan will be 100% remote:

 

 

 

5305.ssms_5F00_queryErrorGoodPlan.png

 

 

 

But the Linked Server approach has an Achilles’ Heel: the SQL Server query optimizer cannot be disabled. A correctly-written PI OLEDB Enterprise query may fail to execute from SQL Server after the SQL Server decides to “help”:

 

 

 

5228.ssls_5F00_queryErrorPlan.png

 

0827.ssms_5F00_queryError.png

 

 

 

Classically, there are two workarounds for this situation: VIEWs saved in the target provider (i.e. PI OLEDB Enterprise), or the OPENQUERY command. But views don’t traditionally accept parameters (such as time ranges). OPENQUERY is SQL function to pass a query string directly to a remote provider, but it too is limited to static queries (so no stringbuilding in SQL unless the workaround is employed).

 

 

 
--IN SQL Management Studio
SELECT * FROM OPENQUERY(AF_Loth,'
SELECT compressors.AssetName, tir.*
FROM NuGreen.Asset.CompressorsOfInterest compressors
INNER JOIN NuGreen.DataT.ft_TransposeInterpolateRange_Compressor tir
ON compressors.AssetID = tir.ElementID
AND tir.StartTime = ''t-1mo''
AND tir.EndTime = ''*''
AND tir.TimeStep = ''1h''
')

 

 

But naturally, the customer’s use case called for the ability to pass custom time ranges into their data queries. So what did we do? We made some incomplete/non-time-restricted views in PI OLEDB Enterprise and called them from SQL Server with WHERE restrictions on time. The restrictions get passed straight through into PI OLEDB Enterprise. Extending the Compressors example from earlier:

 

 

 
--IN SQL Management Studio
SELECT *
FROM AF_Loth.[NuGreen].[DataT].[CompressorDataInterpolateRange_Flex]
WHERE
StartTime = '01-Mar-2012 00:00:00'
AND EndTime = '01-Apr-2012 14:25:31'
AND TimeStep = CAST('01:00:00' AS TIME(0))

 

 

4188.ssms_5F00_compressorDataFlex.png

 

 

 

Voilà, no execution errors, and we keep the ability to specify time range and resolution from a query in SQL Server. And of course, this trick works for parameters other than just time ranges - hierarchy paths, template names... whatever floats your boat.