Case Study: InSQL to PI Migration

14 December 2012

This case study describes the process, the challenges and the outcomes of a historical data migration project. This project consisted of migrating data from two Wonderware (Industrial SQL) Historians to a single OSISoft PI Historian. The PI Historian had replaced the InSQL Historians, but this resulted in valuable historical data being split among three historians.

By migrating the historical data from the two InSQL Historians to the PI Historian, all the historical data became available in the PI Historian, and the two InSQL Historians could be decommissioned.

We will refer to the 2 InSQL Historians as ‘InSQL 1’ and ‘InSQL 2’. These two historians had the following properties:

InSQL 1:

  • Time span: 4 years and 7 months.
  • No. of Tags: approx. 23 000.

InSQL 2:

  • Time span: 1 year and 11 months.
  • No. of Tags: approx. 40 000.

The InSQL Historians not only overlapped in time but also had common tags which extracted their data from the same source. The tags in the PI Historian are a superset of the union of the tags in the two InSQL Historians. However, for many tags, the name in PI was different from the corresponding tags in InSQL 1 and/or InSQL 2.

The results of the migration are summarised in the figure below. The two plots show historical data from InSQL 1 and the migrated data in PI for a single tag covering one day. These plots are derived from the migration analysis results (more on which see below).

The result of migrating historical data from InSQL 1 and InSQL 2 to PI.

Migration Analysis: Source (InSQL) Plot.

Migration Analysis: Target (PI) Plot

The migration process consisted of the following steps:

  • Synchronisation of tag configurations: This consists of linking tags from the source historians (InSQL 1 and InSQL 2) to the corresponding tags in the target historian (PI). This step is particularly important for tags whose names differ in the source and target historians.
  • Import tag configurations and their synchronisation into Tag Manager: By using Tag Manager, this otherwise manual process is greatly simplified.
  • Create the Migration Jobs: Migration Jobs are responsible for the actual migration and run as services. A Migration Job is characterised by source and target historians, a tag list (imported from Tag Manager), and start and end times. Migration jobs are subdivided into batches, which are the smallest repeatable units of a migration job. The migration was completed using 6 jobs (4 for InSQL 1 and 2 for InSQL 2) and each batch had a time range of one day.
  • Analysis and verification: After a batch completes data migration, there is an option of running an analysis step. During this step, data from the source and the target are extracted and compared. Based on the difference between the source and target data, thresholds can be set to identify potentially problematic cases. Because of this, the migration was not merely a blind process. The results of the migration were verified.

Summary of the migration process

The migration was executed on 6 virtual machines (VMs) running Windows Server 2003 R2. Three VMs were used per migration job (one for the InSQL Historian Server, one for the PI Historian Server and one for IDX 8 and Migration Job service). This meant that two Migration Jobs could be run in parallel.

The time it took to complete the entire migration process is summarised as follows:

  • Tag configuration synchronisation and importing to Tag Manager (InSQL 1 and InSQL 2): 2 weeks.
  • Executing the migration jobs (including analysis):

    • InSQL 1 (23,000 tags, 4 years and 7 months): 2 weeks.
    • InSQL 2 (40,000 tags, 1 year and 11 months): 5 days.

One of the challenges encountered was that certain tags had an incorrect data type in the InSQL Historian. This meant that the data types did not match in InSQL and PI. This required that the data for these tags not be migrated as is, but that a ‘processing’ step be included during the migration that transformed the source data into an appropriate version that was written into the target. This process was facilitated by Tag Manager, which held the source and target tag configurations, allowing these special cases to be easily identified during the migration.

<a href="" target="_self">IDX Team</a>

IDX Team

Technical Team

The IDX team is a dynamic group of professionals dedicated to delivering cutting-edge industrial ICT solutions. With a focus on innovation, they leverage their expertise to streamline processes and drive efficiencies for your plant data.

Show your love!

Recent Posts

Driving Productivity with LineView Training

In light of our partnership with LineView Solutions, our team has taken up the opportunity to equip themselves with insights into the LineView system and its impact on the industry. “LineView is a production line monitoring and recording system that automatically...

Exciting New Launch: Onsite ProfiDay

IDX is excited to announce the launch of our onsite ProfiDay offering. This is available to all industries and companies that utilise PROFIBUS and/or PROFINET within their industrial communications systems. What is ProfiDAY? An exclusive onsite program offering...

Introducing our Partnership with LineView Solutions

We are excited to announce our partnership with LineView Solutions, a leading UK-based company specialising in smart factory solutions for optimising production processes. LineView developed a cutting-edge software suite designed to automatically gather data from the...

Optimise PROFIBUS and PROFINET Network Performance

As a field technician handling PROFIBUS and PROFINET systems, it is crucial to recognise the common errors that can result in costly downtime. From improper network configuration to neglecting regular maintenance, these mistakes can greatly affect system performance...

Delighted Customer Shares Experience

We recently assisted a client in rectifying damage to the Anybus ABC4090 Communicator caused by incorrect power usage onsite. We arranged for the device to be sent to HMS in Sweden for repairs, and they restored it before promptly returning it to us. Here are some...

Price Decrease on PROFIBUS Connectors

PROFIBUS connectors play a crucial role in industrial automation and data exchange systems by facilitating smooth communication between devices and networks. We are pleased to announce a significant price decrease on all our PROFIBUS connectors. Thanks to recent...

PROFIBUS Standards: Functional bonding and shielding

Electromagnetic Interference (EMI) and earthing issues can significantly impact the performance of your network. By following these six proven recommendations, you can ensure that your network is always up and running.

PROFIBUS Standards: Terminations

What is the Volts Direct Current (VDC) required for optimal operation of a PROFIBUS DP termination circuit? Get the answers you need in our latest video on PROFIBUS Standards: Terminations. Don't miss out on this essential knowledge to boost your system's performance!

PROFIBUS Standards: Segment Cable Lengths

Finding the perfect balance between PROFIBUS cable length and network speed is crucial. While longer cable segments might seem tempting, they can lead to signal degradation and slower speeds. Conversely, too short segments might limit your layout options. Striking the...

Sign up for our newsletter

Get exclusive updates and a sneak peek into our world every two months. Stay connected, stay informed.

Related Posts

Our expertise lies in establishing, maintaining, and leveraging plant data for business benefit. Through our innovative solutions, we work alongside you to streamline processes, enhance efficiency, and reduce costly downtime.


Main: +27 (11) 548 9960
Sales: +27 (11) 548 9970


Copyright @2024 Industrial Data Xchange. All rights reserved.