C135 PCS 7 (PCS7)
6C
6 Cielo Ciencia y Tecnología
·Publicada en C135 PCS 7 (PCS7)

Extracting High-Resolution Raw Historical Trend Archive Data as CSV via WinCC OLE-DB Provider

Extracting High-Resolution Raw Historical Trend Archive Data as CSV via WinCC OLE-DB Provider

In SIMATIC PCS 7 V7.0 and WinCC V7.0, exporting raw, uncompressed historical process data (such as 2 samples per second over 20 days) directly to CSV cannot be accomplished using the WinCC trend disk icon or direct SQL Server Management Studio queries. The trend disk icon downsamples data to the screen pixel resolution, while direct SQL queries fail because WinCC stores Tag Logging archives in proprietary binary compressed chunks (TagCompressed). To extract full-resolution raw archive records, query the historical database using the Siemens WinCC OLE-DB Provider via an ADO script (VBScript, PowerShell, or Excel VBA) or the WinCC Connectivity Pack. The WinCC OLE-DB Provider decompresses the archive binary blobs on the fly, outputting exact time stamps, floating-point process values, and quality flags directly to CSV.

Table of Contents

Executive Summary

Process optimization, root-cause forensic investigations, and environmental compliance audits often require extracting high-frequency historical process data (e.g., 2 Hz raw sampling over multiple weeks) for analytical evaluation in Microsoft Excel or MATLAB. In PCS 7 V7.0 / WinCC V7.0, engineers attempting to export this data face two common roadblocks: exporting from the WinCC Trend Control yields only a downsampled subset of points matching screen resolution, while querying the underlying Microsoft SQL Server 2005 tables directly returns unreadable binary hex data (TagCompressed). This guide provides the complete architecture and practical implementation script to extract uncompressed, full-fidelity raw trend data utilizing the WinCC OLE-DB Provider.

Architecture and Fundamental Mechanics

The data storage and extraction architecture of WinCC Tag Logging operates through specialized database abstraction:
1. Proprietary Binary Compression (TagCompressed):
- High-speed and slow cyclic archive tags are stored in Microsoft SQL Server inside segmented archive tables named <ProjectName>_TLG_S_<YYYYMMDD_HHMM>.
- To minimize disk storage and maximize write throughput, WinCC packages dozens of sample points into proprietary compressed binary Large Objects (BLOBs) within the TagCompressed column.
- Standard SQL SELECT queries cannot decompress or parse these BLOBs into time and value columns.
2. Trend Control Downsampling Mechanics:
- The runtime WinCC OnlineTrendControl optimizes memory by requesting only the number of data points necessary to render the current display resolution (typically 500 to 1,000 points across the time span).
- Clicking the 'Export to CSV / Disk' icon on the trend ruler exports only the actively rendered points, discarding raw sub-second samples across multi-day spans.
3. WinCC OLE-DB Provider (WinCCOLEDBProvider):
- The Siemens WinCC OLE-DB Provider acts as a database filter that translates standard SQL-like commands (TAG:R,...) into archive decompression calls.
- It automatically identifies, attaches, and reads across multiple segmented archive databases, outputting clean, uncompressed rows containing: Timestamp, TagValue (REAL), and Quality flag.

Step-by-Step Implementation Procedure

  1. Verify WinCC Connectivity Pack and OLE-DB Installation:
    a. On the WinCC OS Server or client PC, verify that the WinCC Connectivity Pack (or WinCC Open Development Kit / Connectivity Station) is installed.
    b. Confirm that the OLE-DB provider DLL WinCCOLEDBProvider.dll is registered in Windows.

  2. Determine Exact Archive Tag IDs and Time Boundaries:
    a. Open WinCC Explorer and navigate to Tag Logging.
    b. Locate the specific archive tags to export (e.g., ProcessValue_PT101, Flow_FIT202).
    c. Note the exact archive tag names and the required UTC time range (e.g., from 2020-06-01 00:00:00 to 2020-06-21 00:00:00).

  3. Construct the WinCC OLE-DB Query String:
    a. Formulate the extraction query using the WinCC Tag Logging syntax:
    TAG:R,('ProcessValue_PT101'),'2020-06-01 00:00:00.000','2020-06-21 00:00:00.000'
    b. For raw uncompressed values, omit any cyclic averaging parameters.

  4. Create Extraction Script (PowerShell / VBScript / Excel VBA):
    a. In an administrative PowerShell console, create an ADO connection script:
    powershell $conn = New-Object -ComObject ADODB.Connection $connStr = "Provider=WinCCOLEDBProvider.1;Catalog=CC_OS_Proj_20_06_15_12_00_00_R;Data Source=.\WINCC" $conn.Open($connStr) $rs = New-Object -ComObject ADODB.Recordset $sql = "TAG:R,('ProcessValue_PT101'),'2020-06-01 00:00:00.000','2020-06-21 00:00:00.000'" $rs.Open($sql, $conn) $outFile = "C:\Export\PT101_RawData.csv" Add-Content $outFile "Timestamp,Value,Quality" while (-not $rs.EOF) { $line = "{0},{1},{2}" -f $rs.Fields.Item(1).Value, $rs.Fields.Item(2).Value, $rs.Fields.Item(3).Value Add-Content $outFile $line $rs.MoveNext() } $rs.Close() $conn.Close()

  5. Execute Export and Verify Full-Resolution Output:
    a. Run the script from the command prompt.
    b. Allow the OLE-DB provider to iterate across the segmented SQL archive databases.
    c. Open the resulting CSV file to verify timestamps increment at the full 500 ms (2 Hz) resolution.

Verification

  1. Open PT101_RawData.csv in a text editor: verify timestamps increment at exactly 500 ms intervals without missing intervals.
  2. Confirm that exported values match live calibration points.
  3. Verify the file size and total row count (e.g., 20 days at 2 samples/second equals approximately 3,456,000 data rows).

Limitations and Operational Precautions

Exporting millions of raw records over wide timeframes can cause memory strain and high disk I/O on active WinCC OS servers. Execute large bulk exports during low-activity shifts or on a redundant standby/connectivity station.

Engineer FAQ

Why does the WinCC trend disk icon export only a small number of points?

Because it exports only the data points actively rendered on screen at the current display pixel resolution, downsampling raw high-frequency data.

Why can't I query WinCC process values directly in SQL Server Management Studio?

WinCC stores Tag Logging values in compressed proprietary binary BLOBs inside the 'TagCompressed' table column, which standard SQL cannot parse.

What software component decompresses WinCC Tag Logging data for external tools?

The Siemens WinCC OLE-DB Provider (included with the WinCC Connectivity Pack).

Does the WinCC OLE-DB Provider handle queries across multiple archive segments?

Yes. The OLE-DB provider automatically queries across detached, attached, and segmented archive databases seamlessly.

What time zone is used by default in WinCC OLE-DB queries?

Queries use Coordinated Universal Time (UTC) formatted timestamps.

Technical References

Related Articles

Related Tags

  • WinCC OLE-DB
  • Tag Logging
  • Historical Archive
  • CSV Export
  • Connectivity Pack
  • PCS 7 V7.0
20 respuestas