Accessing Cygnet oil/gas data from Access or SQL Server
A client recently purchased some oil/gas wells that use Cygnet as their production data collection and reporting system. However, they have other wells that use other production data which we pull into SQL Server for use by their Business Intelligence software.
I've been looking at the Cygnet data for the last several nights and believe that the HistoricalValues table contains the data that I need to pull into SQL Server. Historically, I've used Access to pull the data from different data sources, massage it, and populate the SQL tables. However the data structure of this "HistoricalValues" table makes the number of records massive (I still don't know how massive because every time I try to count the records with the table linked to Access, the query locks up). I speculate that the table contains well over 50 million records (500 wells, 50 Record Types, 365 days, 5+ years).
The structure of this table looks something like:
WellID - String (50)
ReadingDate - Date
RecordType - String (50) - there appear to be about 75 record types
Value - String (50) - even numeric values are stored as strings
many more Yes/No fields, but these are the critical fields.
Rather than creating multiple tables for specific types of data and storing that data in multiple columns, this table is structured with a primary key that looks like:
WellID + ReadingDate + RecordType
I have attempted to access this data in a variety of methods from Access and have set the timeouts of the queries to zero, but every query other than SELECT TOP 100 records has taken over a half hour to execute in Access.
Does anyone have any experience working with Cygnet data? I there a way that I can link the Cygnet data to SQL Server (linked server or something?) that will allow me to use the processing power of SQL Server to extract the necessary data from this table?
I've been looking at the Cygnet data for the last several nights and believe that the HistoricalValues table contains the data that I need to pull into SQL Server. Historically, I've used Access to pull the data from different data sources, massage it, and populate the SQL tables. However the data structure of this "HistoricalValues" table makes the number of records massive (I still don't know how massive because every time I try to count the records with the table linked to Access, the query locks up). I speculate that the table contains well over 50 million records (500 wells, 50 Record Types, 365 days, 5+ years).
The structure of this table looks something like:
WellID - String (50)
ReadingDate - Date
RecordType - String (50) - there appear to be about 75 record types
Value - String (50) - even numeric values are stored as strings
many more Yes/No fields, but these are the critical fields.
Rather than creating multiple tables for specific types of data and storing that data in multiple columns, this table is structured with a primary key that looks like:
WellID + ReadingDate + RecordType
I have attempted to access this data in a variety of methods from Access and have set the timeouts of the queries to zero, but every query other than SELECT TOP 100 records has taken over a half hour to execute in Access.
Does anyone have any experience working with Cygnet data? I there a way that I can link the Cygnet data to SQL Server (linked server or something?) that will allow me to use the processing power of SQL Server to extract the necessary data from this table?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help David. Didn't solve the problem but did point me towards some options.
ASKER
You obviously have some experience in the oil/gas industry. Your advice is solid, but what I'm really looking for is someone with experience with the Cygnet database. These guys are supposedly one of the big dogs in oil/gas industry, but this is a terrible data structure for a "big dog".