Transcription
Wonderware Historian ConceptsGuideInvensys Systems, Inc.Revision FLast Revision: December 3, 2009
Copyright 2002-2005, 2009 Invensys Systems, Inc. All Rights Reserved.All rights reserved. No part of this documentation shall be reproduced, stored in aretrieval system, or transmitted by any means, electronic, mechanical,photocopying, recording, or otherwise, without the prior written permission ofInvensys Systems, Inc. No copyright or patent liability is assumed with respect tothe use of the information contained herein. Although every precaution has beentaken in the preparation of this documentation, the publisher and the authorassume no responsibility for errors or omissions. Neither is any liability assumedfor damages resulting from the use of the information contained herein.The information in this documentation is subject to change without notice and doesnot represent a commitment on the part of Invensys Systems, Inc. The softwaredescribed in this documentation is furnished under a license or nondisclosureagreement. This software may be used or copied only in accordance with the termsof these agreements.Invensys Systems, Inc.26561 Rancho Parkway SouthLake Forest, CA 92630 U.S.A.(949) 727-3200http://www.wonderware.comFor comments or suggestions about the product documentation, send an e-mailmessage to productdocs@wonderware.com.TrademarksAll terms mentioned in this documentation that are known to be trademarks orservice marks have been appropriately capitalized. Invensys Systems, Inc. cannotattest to the accuracy of this information. Use of a term in this documentationshould not be regarded as affecting the validity of any trademark or service mark.Alarm Logger, ActiveFactory, ArchestrA, Avantis, DBDump, DBLoad, DT Analyst,Factelligence, FactoryFocus, FactoryOffice, FactorySuite, FactorySuite A2, InBatch,InControl, IndustrialRAD, IndustrialSQL Server, InTouch, MaintenanceSuite,MuniSuite, QI Analyst, SCADAlarm, SCADASuite, SuiteLink, SuiteVoyager,WindowMaker, WindowViewer, Wonderware, Wonderware Factelligence, andWonderware Logger are trademarks of Invensys plc, its subsidiaries and affiliates.All other brands may be trademarks of their respective owners.
3ContentsWelcome. 11Wonderware Historian Documentation Set .11Documentation Conventions.12Technical Support .13Chapter 1 Introduction . 15The Wonderware Historian Solution.15Process Data .16About Relational Databases .16Limitations of Relational Databases .17Wonderware Historian as a Real-Time RelationalDatabase .17Integration with Microsoft SQL Server .18Support for SQL Clients.19Wonderware Historian Subsystems .19Chapter 2 System-Level Functionality . 21About Tags .21Types of Tags .21Sources of Tag Values .22Naming Conventions for Tags .23Security .24Windows Operating System Security.24SQL Server Security.25Wonderware Historian Concepts Guide
4ContentsManagement Console Security .31Default Access Rights for Different OperatingSystems .32Time Handling.32System Parameters .33System Messages.38Wonderware Historian Services .40The System Driver and System Tags.42Error Count Tags .42Date Tags.42Time Tags .43Storage Space Tags .43I/O Statistics Tags .43System Monitoring Tags .44Miscellaneous (Other) Tags .45Event Subsystem Tags.46Replication Subsystem Tags .47Performance Monitoring Tags .48Supported Protocols .50Modification Tracking .51Modification Tracking for Configuration Changes.52Modification Tracking for Historical Data Changes .53Data Quality .54Viewing Quality Values and Details .55Acquisition and Storage of Quality Information .59Client-Side Quality.61Chapter 3 Configuration Subsystem .63Configuration Subsystem Components.64About the Runtime and Holding Databases .65The Runtime Database .65The Holding Database.66About the Configuration Service .67Dynamic Configuration.67Effects of Configuration Changes on the System .68Cases in Which Configuration Changes are notCommitted.70Wonderware Historian Concepts Guide
Contents5Chapter 4 Data Acquisition Subsystem . 71Data Acquisition Components .72Data Acquisition from I/O Servers .73I/O Server Addressing .74About IDASs .75I/O Server Redundancy .86Redirecting I/O Servers to InTouch HMI Software .87Time Synchronization for Data Acquisition.87Data Acquisition by Means of INSERT and UPDATEStatements.90Data Acquisition from MDAS .90Importing Data from a CSV File .91Chapter 5 Data Storage Subsystem. 93Storage Subsystem Components .94Storage Data Categories .94About the Real-Time Data Window.97Data Modification and Versioning.98Storage Modes .99"Forced" Storage .99Delta Storage .100Time and Value Deadbands for Delta Storage .100"Swinging Door" Deadband for Delta Storage .101Cyclic Storage .111Data Conversions and Reserved Values for Storage .112History Blocks .113History Block Notation.113History Block Creation.114History Block Storage Locations .115Automatic Deletion of History Blocks .117About the Active Image.119Automatic Resizing of the Active Image .120How the Active Image Storage Option Affects DataRetrieval .121Dynamic Configuration Effects on Storage.122Memory Management for Data Storage.122About Snapshot Files .124How Snapshot Files are Updated .126Wonderware Historian Concepts Guide
6ContentsChapter 6 Data Retrieval Subsystem . 127Data Retrieval Components.128Data Retrieval Features .129History Blocks: A SQL Server Remote Data Source . 129Retrieval Service .130About the Wonderware Historian OLE DB Provider. 130Extension (Remote) Tables for History Data.132Query Syntax for the Wonderware HistorianOLE DB Provider . 133Wonderware Historian OLE DB ProviderUnsupported Syntax and Limitations . 137Linking the Wonderware Historian OLE DBProvider to the Microsoft SQL Server . 146Wonderware Historian Time Domain Extensions. 147Wonderware Historian I/O Server .149Chapter 7 Data Retrieval Options . 151Understanding Retrieval Modes.151Cyclic Retrieval .152Delta Retrieval .156Full Retrieval.163Interpolated Retrieval.165“Best Fit” Retrieval .171Average Retrieval.176Minimum Retrieval .182Maximum Retrieval .188Integral Retrieval .194Slope Retrieval .197Counter Retrieval .200ValueState Retrieval .205RoundTrip Retrieval .212Understanding Retrieval Options .217Which Options Apply to Which Retrieval Modes?. 217Using Retrieval Options in a Transact-SQLStatement . 218Cycle Count (X Values over Equal Time Intervals)(wwCycleCount) . 219Resolution (Values Spaced Every X ms)(wwResolution).222About “Phantom” Cycles .224Time Deadband (wwTimeDeadband) .227Value Deadband (wwValueDeadband). 231Wonderware Historian Concepts Guide
Contents7History Version (wwVersion) .235Interpolation Type (wwInterpolationType).237Timestamp Rule (wwTimestampRule).240Time Zone (wwTimeZone) .242Quality Rule (wwQualityRule) .244State Calculation (wwStateCalc).252Analog Value Filtering (wwFilter) .254Selecting Values for Analog Summary Tags(wwValueSelector) .261Edge Detection for Events (wwEdgeDetection) .264Chapter 8 Query Examples . 275Querying the History Table .275Querying the Live Table .276Querying the WideHistory Table.277Querying Wide Tables in Delta Retrieval Mode .278Querying the AnalogSummaryHistory View .279Querying the StateSummaryHistory View .280Using an Unconventional Tagname in a WideTable Query .281Using an INNER REMOTE JOIN.281Setting Both a Time and Value Deadband forRetrieval .282Using wwResolution, wwCycleCount, andwwRetrievalMode in the Same Query .285Determining Cycle Boundaries.286Mixing Tag Types in the Same Query.286Using a Criteria Condition on a Column ofVariant Data.287Using DateTime Functions .288Using the GROUP BY Clause.290Using the COUNT() Function.290Using an Arithmetic Function .291Using an Aggregate Function .292Making and Querying Annotations .294Using Comparison Operators with Delta Retrieval .294Using Comparison Operators with Cyclic Retrievaland Cycle Count .299Using Comparison Operators with Cyclic Retrievaland Resolution.302SELECT INTO from a History Table.306Wonderware Historian Concepts Guide
8ContentsMoving Data from a SQL Server Table to an ExtensionTable . 307Using Server-Side Cursors .308Using Stored Procedures in OLE DB Queries .310Querying Data to a Millisecond Resolution using SQLServer 2005. 310Getting Data from the OPCQualityMap Table.312Using Variables with the Wide Table . 312Retrieving Data Across a Data "Hole" .313Returned Values for Non-Valid Start Times . 315Retrieving Data from History Blocks and theActive Image. 315Querying Aggregate Data in Different Ways .316Chapter 9 Replication Subsystem . 319About Tiered Historians .319How Tags are Used During Replication.321Simple Replication .323Summary Replication.324Analog Summary Replication .325State Summary Replication.326Replication Schedules .327Replication Schedules and Daylight Savings Time.328Replication Groups.330How Replication is Handled for Different Types ofData .331Streaming Replication .332Queued Replication .332Tag Configuration Synchronization between TieredHistorians . 333Replication Components .334Replication Run-time Operations. 335Replication Latency.336Replication Delay for “Old” Data. 336Continuous Operation .336Overflow Protection.337Security for Data Replication .337Using Summary Replication instead of Event-BasedSummaries. 338Wonderware Historian Concepts Guide
Contents9Chapter 10 Event Subsystem . 339Event Subsystem Components .340Uses for the Event Subsystem.341Event Subsystem Features and Benefits .342Event Subsystem Performance Factors .343Event Tags .344Event Detectors .345SQL-Based Detectors .345Schedule Detectors .348External Detectors .349Event Actions.349Generic SQL Actions .349Snapshot Actions .350E-mail Actions .350Deadband Actions.351Summary Actions .351Event Action Priorities.353Event Subsystem Resource Management .353Detector Thread Pooling .354Action Thread Pooling.355Event Subsystem Database Connections .356Handling of Event Overloads and Failed Queries.356Event Subsystem Variables .358Index . 361Wonderware Historian Concepts Guide
10ContentsWonderware Historian Concepts Guide
11WelcomeThis guide provides information about the generalarchitecture of the Wonderware Historian and describes thedifferent subsystems and components that make up theproduct. This guide can be used as a reference guide for allconceptual information about the Wonderware Historiancomponents.Wonderware Historian Documentation SetThe Wonderware Historian documentation set includes thefollowing guides: Wonderware Historian Installation Guide(InSQLInstall.pdf). This guide provides information oninstalling the Wonderware Historian, includinghardware and software requirements and migrationinstructions. Wonderware Historian Concepts Guide(InSQLConcepts.pdf). This guide provides an overview ofthe entire Wonderware Historian system and describeseach of the subsystems in detail. Wonderware Historian Administration Guide(InSQLAdmin.pdf). This guide describes how toadminister and maintain an installed WonderwareHistorian, such as configuring data acquisition andstorage, managing security, and monitoring the system. Wonderware Historian Database Reference(InSQLDatabase.pdf). This guide providesdocumentation for all of the Wonderware Historiandatabase entities, such as tables, views, and storedprocedures.Wonderware Historian Concepts Guide
12Welcome Wonderware Historian Glossary (InSQLGlossary.pdf).This guide provides definitions for terms used throughoutthe documentation set.In addition, the Wonderware ArchestrA License ManagerGuide (License.pdf) describes the ArchestrA LicenseManager and how to use it to install, maintain, and deletelicenses and license servers on local and remote computers.A PDF file for each of these guides is available on theWonderware Historian installation CD. You can easily printinformation from the PDF files. The Wonderware Historiandocumentation is also provided as an online help file, whichcan be accessed from the System Management Consolemanagement tool.Documentation ConventionsThis documentation uses the following conventions:ConventionUsed forInitial CapitalsPaths and file names.BoldMenus, commands, dialog box names,and dialog box options.MonospaceCode samples and display text.Wonderware Historian Concepts Guide
Technical Support13Technical SupportWonderware Technical Support offers a variety of supportoptions to answer any questions on Wonderware productsand their implementation.Before you contact Technical Support, refer to the relevantsection(s) in this documentation for a possible solution to theproblem. If you need to contact technical support for help,have the following information ready: The type and version of the operating system you areusing. Details of how to recreate the problem. The exact wording of the error messages you saw. Any relevant output listing from the Log Viewer or anyother diagnostic applications. Details of what you did to try to solve the problem(s) andyour results. If known, the Wonderware Technical Support casenumber assigned to your problem, if this is an ongoingproblem.Wonderware Historian Concepts Guide
14WelcomeWonderware Historian Concepts Guide
15Chapter 1IntroductionThe Wonderware Historian, formally known asIndustrialSQL Server , bridges the gap between a real-timehigh-volume plant monitoring environment and an open,flexible business information environment. The historian: Acquires plant data from high-speed Wonderware I/OServers, DAServers, InTouch HMI software, WonderwareApplication Server, and other devices. Compresses and stores data. Responds to SQL requests for plant data.The historian also contains event, summary, configuration,security, backup, and system monitoring information.The historian is tightly coupled to Microsoft SQL Server.The Wonderware Historian SolutionThe Wonderware Historian is a real-time relational databasethat stores plant data. The historian acquires and storesprocess data at full resolution or at a specified resolution andprovides real-time and historical plant data together withconfiguration, event, summary, and associated productiondata to client applications on the desktop. The historiancombines the power and flexibility of Microsoft SQL Serverwith the high speed acquisition and efficient datacompression characteristics of a real-time system.Wonderware Historian Concepts Guide
16Chapter 1 IntroductionProcess DataProcess data is any relevent information to successfully run aprocess. The following information is considered to be processdata: Real-time data - What is the current value of this tag? Historical data - What was the value of this tag everysecond last Monday? Summary data - What is the average of each of these fivetags? Event data - When did that boiler trip? Configuration data - How many I/O Servers am I usingand what are their types?To improve performance and quality while reducing cost,process data must be available for analysis. Process data istypically analyzed to determine: Process analysis, diagnostics, and optimization. Predictive and preventive equipment maintenance. Product and process quality (SPC/SQC). Health and safety; environmental impact (EPA/FDA). Production reporting. Failure analysis.About Relational DatabasesA relational database management system (RDBMS) storesdata in multiple tables that are related or linked together.Storing and accessing information in multiple tables makesdata storage and maintenance more efficient than if all of theinformation was stored in a single large table. For example,Microsoft SQL Server is a relational database.SQL, is the language to communicate with relationaldatabases. SQL is an industry "super-standard," supportedby hundreds of software vendors. SQL provides an opennessunmatched in the plant environment. Relational databasesare mature and are the accepted IT workhorses in databaseapplications today. Power and flexibility are far superior inSQL than in the proprietary interfaces that have come out ofthe plant environment.Wonderware Historian Concepts Guide
The Wonderware Historian Solution17Limitations of Relational DatabasesA typical relational database is not a viable solution to storeplant data because of the following limitations: Cannot handle the volume of data produced by plants. Cannot handle the rapid storage rate of plant data. SQL does not effectively handle time-series data.Industrial plants have thousands of tags, all changing atdifferent rates. Several months of plant history result inhundreds of gigabytes of data in a normal relationaldatabase.For example, a plant with 10,000 variables changing on theaverage of every two seconds generates 5,000 values persecond. 5,000 rows of data must therefore be inserted into thedatabase each second to store a complete history, which isunsustainable by typical relational databases like Oracle orSQL Server on standard computer hardware.Wonderware Historian as a Real-Time RelationalDatabaseAs a real-time relational database, Wonderware Historian isan extension to Microsoft SQL Server, providing more thanan order of magnitude increase in acquisition speeds, acorresponding reduction in storage volume, and elegantextensions to structured query language (SQL) to query timeseries data. High-speed data captureThe comprehensive range of Wonderware I/O Servers andDAServers are used to connect to over 500 control anddata acquisition devices.Designed for optimal acquisition and storage of analog,discrete, and string data, the Wonderware Historianoutperforms all normal relational databases on similarhardware by a wide margin, making the storage ofhigh-speed data in a relational database possible. Thehistorian acquires and stores process data many timesfaster than a RDBMS.Wonderware I/O Servers and DAServers support theSuiteLink protocol. SuiteLink allows for time andquality stamping at the I/O Server and further improvesthe rate of data acquisition.Wonderware Historian Concepts Guide
18Chapter 1 Introduction Reduced storage spaceThe Wonderware Historian stores data in a fraction ofthe space required by a normal relational database. Theactual disk space required to store plant data depends onthe size and nature of the plant and the length of theplant history required. Time domain extensions to SQLThe SQL language does not support time series data. Inparticular, there is no way to control the resolution ofreturned data in SQL. An example of resolution would bean evenly spaced sampling of data over a period of time.Microsoft SQL Server supports its own extensions to theSQL language, called Transact-SQL. The WonderwareHistorian further extends Transact-SQL, allowing controlof resolution and providing the basis for time-relatedfunctions such as rate of change and process calculationson the server.Integration with Microsoft SQL ServerA large amount of plant-related data has the samecharacteristics as normal business data. For example,configuration data is relatively static and does not change ata real-time rate. Over the life of a plant, tags are added anddeleted, descriptions are changed, and engineering rangesare altered. A Microsoft SQL Server database, called theRuntime database, stores this type of information.The Runtime database is the SQL Server online database forthe entire Wonderware Historian. The Runtime database isshipped with a set of standard database entities, such astables, views, and stored procedures to store configurationdata for a typical factory. You can use the ConfigurationEditor within the System Management Console to easily addconfiguration data to the Runtime database that reflects yourfactory environment.Microsoft SQL Server Object Linking and Embedding forDatabases (OLE DB) is used to access the real-time plantdata that the historian stores outside of the SQL Serverdatabase. You can query the Microsoft SQL Server for bothconfiguration information in the Runtime database andhistorical data on disk, and the integration appearsseamless.Because the historian is tightly coupled to and effectivelyextends a Microsoft SQL Server, it can leverage all of thefeatures that Microsoft SQL Server has to offer, such asdatabase security, replication, and backups.Wonderware Historian Concepts Guide
Wonderware Historian Subsystems19Support for SQL ClientsThe client/server architecture of Wonderware Historiansupports client applications on the desktop, while ensuringthe integrity and security of data on the server. Thisclient/server architecture provides common access to plantand process data: real-time and historical data, associatedconfiguration, event, and business data. The computingpower of both the client and the server is exploited byoptimizing processor intensive operations on the server andminimizing data to be transmitted on the network to improvesystem performance.The gateway for accessing any type of information in thehistorian is the Microsoft SQL Server. Thus, any clientapplication that can connect to Microsoft SQL Server canalso connect to the historian.Two categories of client applications can be used to accessand retrieve information from the historian: Clients developed specifically to access data fromhistorian. Wonderware provides a number of client toolsto address specific data represen
Guide (License.pdf) describes the ArchestrA License Manager and how to use it to install, maintain, and delete licenses and license servers on local and remote computers. A PDF file for each of these guides is available on the Wonderware Historian installation CD. You can easily print information from
