Transcription
Modernizing PI SQL For The FutureBodo BachmannChristopher Sawyer#PIWorld 2019 OSIsoft, LLC1
Your Speakers Today Bodo Bachmann Team Leader, SoftwareDevelopment bodo@osisoft.com Christopher Sawyer Technology Enablement csawyer@osisoft.com#PIWorld 2019 OSIsoft, LLC2
Why SQL for the PI System Simplest place to cross-pollinate actionable line-ofbusiness (LOB) data with real-time data Enhancing Real Time Data with LOB data Enhancing LOB Data with RT Maximizes the resources you already have tocomplete data projects: SQL Database Administrators Web Developers LOB App Developers#PIWorld 2019 OSIsoft, LLC3
Why SQL for the PI System Some data will never live in SQL (streams) Some data will never live in the PI System(financial costing) We’ve optimized how we merge relationalstructures and their relationships to assets withstreaming data in the PI System#PIWorld 2019 OSIsoft, LLC4
Next Generation Development Goals Performance and scalability Review data model Reduce query complexity What are top use cases (use customer feedback) Address known issues (e.g. timestep data type, PI time literals) Infrastructure to support multiple standards and thin clients Works across WAN and different time zones Review security and authentication#PIWorld 2019 OSIsoft, LLC
PI SQL Client ArchitectureMiddlewareJava Client ApplicationSQLQueriesPI SQL Client JDBCJDBCClientJDBCv4.0,v4.1, v4.2httpsPI SQL Client ODBCODBCClientODBC v3APIPI OLEDBPI Data ArchivePIOLEDBEnterpriseMiddlewareClient ApplicationSQLQueriesPI SQLData AccessServerC Libnet.tcporhttpsPI Integratorfor BusinessAnalyticsPI AF ServerPI SQL DAS(IntegratorFramework)MS SQL ServerMiddlewareClient ApplicationSQLQueriesOLE DBConsumerPI SQL Client OLEDBOLE DBAPIC Libnet.tcporhttpsAF ServerPI SQL DAS(RTQPEngine)#PIWorld 2019 OSIsoft, LLC6
Architecture#PIWorld 2019 OSIsoft, LLC
RTQP Engine – A Learned Technology Deployment Working with different internal data sources Issue of Data References Memory Management / Intermediate Results Optimization Streaming Security#PIWorld 2019 OSIsoft, LLC
RTQP Engine: Data Model Entity-Relationship Model Connection AF Database Master Catalog Elements and Event FramesAttributes and DataCategories and TemplatesTemplate based Table-Valued Functions(New!) Denormalization Navigation Columns Multiple Value columns#PIWorld 2019 OSIsoft, LLC
Simplified QueriesDenormalizationSELECT eh.Path eh.Name Element, et.Name TemplateFROM NuGreen.Asset.Element eLEFT OUTER JOIN NuGreen.Asset.ElementTemplate etON et.ID e.ElementTemplateIDINNER JOIN NuGreen.Asset.ElementHierarchy ehON eh.ElementID e.IDWHERE et.Name IN ('Boiler', 'Heater')SELECT e.PrimaryPath e.Name, e.TemplateFROM Master.Element.Element eWHERE e.Template IN ('Boiler', 'Heater')#PIWorld 2019 OSIsoft, LLC
Simplified QueriesExtentionSELECT eh.Path eh.Name Element, et.Name Template, ea.Name Attribute, s.Time, s.ValueFROM NuGreen.Asset.Element eLEFT OUTER JOIN NuGreen.Asset.ElementTemplate etON et.ID e.ElementTemplateIDINNER JOIN NuGreen.Asset.ElementHierarchy ehON e.ID eh.ElementIDINNER JOIN NuGreen.Asset.ElementAttribute eaON eh.ElementID ea.ElementIDINNER JOIN NuGreen.Data.Snapshot sON ea.ID s.ElementAttributeIDWHERE et.Name IN ('Boiler', 'Heater')AND eh.Path N'\NuGreen\Houston\Cracking Process\Equipment\'ORDER BY 1SELECT e.PrimaryPath e.Name Element, e.Template, ea.Name Attribute,ea.ValueTimeStamp, ea.ValueFROM Master.Element.Element eINNER JOIN Master.Element.Attribute eaON e.ID ea.ElementIDWHERE e.Template IN ('Boiler', 'Heater')AND e.PrimaryPath N'\NuGreen\Houston\Cracking Process\Equipment\'ORDER BY 1#PIWorld 2019 OSIsoft, LLC
RTQP Engine: Custom Objects Views Custom Table-Valued Functions Function Tablesfor Table-Valued Functions Custom Functions#PIWorld 2019 OSIsoft, LLC
SQL Clients#PIWorld 2019 OSIsoft, LLC13
PI SQL Client 2018 R2 No AF SDK dependency ODBC added JDBC added#PIWorld 2019 OSIsoft, LLC
Connection and Connection Options AF Database names can be browsedwhen PI Web API CoreServices are configured Time Zone support Query Date#PIWorld 2019 OSIsoft, LLC
PI SQL Client: OLE DB Provider SQL Server integration (SSRS, SSIS, etc.) BizTalk SAP Crystal Reports ADO.NET, ASP.NET#PIWorld 2019 OSIsoft, LLC
PI SQL Client: ODBC Driver Oracle integration BI Tools, e.g. Tibco, Spotfire, Power BI Crystal Reports ADO.NET, ASP.NET#PIWorld 2019 OSIsoft, LLC
PI SQL Client: JDBC Driver Pure Java Https only#PIWorld 2019 OSIsoft, LLC
PI SQL Client: PI SQL Commander Browse Data Model and Functions Compendium Code/keyboard pre-completion (it might also be knownas IntelliSense )#PIWorld 2019 OSIsoft, LLC
Sample Queries and Performance#PIWorld 2019 OSIsoft, LLC
Future Locale property: e.g. '01-Dec-2019' vs N'01-Dez-2019’ Data Archive tables (PI tags vs AF attributes) INSERT/UPDATE/DELETE event frames INSERT/UPDATE/DELETE tags/attribute values#PIWorld 2019 OSIsoft, LLC
DEMOMicrosoft SQL Server – PI SQL Client Remote Connection#PIWorld 2019 OSIsoft, LLC22
#PIWorld 2019 OSIsoft, LLC
Questions?Please rememberPlease wait forthe microphoneState yourname & companyDOWNLOADTHE MOBILEAPP#PIWorld 2019 OSIsoft, LLC24
#PIWorld 2019 OSIsoft, LLC25
SQL PI SQL Client JDBC Queries v4.0,v4.1, v4.2 https 6 PI SQL Client Architecture Client Application OLE DB Consumer (RTQP PI SQL Client OLEDB SQL Queries PI Data Archive PI AF Server OLE DB API net.tcp or https C Lib Middleware PI Integrator for Business Analytics PI SQL DAS (Integrator Framework) Middleware PI SQL Data Access Server Enterpris
