Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Accessing Cygnet oil/gas data from Access or SQL Server

Posted on 2013-10-30
4
Medium Priority
?
817 Views
Last Modified: 2013-11-09
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?
0
Comment
Question by:Dale Fye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 2000 total points
ID: 39613500
Hi,

While that type of table has its appeal, as you've discovered, performance isn't great.

The problem is that say you want all the OilPressure Type readings - there is now way to create indexes that sensibly know about this. Why better to add a table for each type of reading.

The other thing, is that this supposes that all kinds of readings can be fully encapsulated by a ... and hear starts the discussion: Will a float do, or do you need a double? How do you range check this for sensibleness? ie temperature ranges from 0 to a couple of thousand kelvin as a max. A temperature reading of a million in any scale has almost got to be wrong!, Whereas pressure in MegaPascals is quite reasonable.

HTH
  David

PS Store numerical data as numeric data, and not as strings! That will use the processing power of SQL to extract the data. Otherwise you are shackling SQL by having to convert from strings to numbers to do things intelligently like find sum and average.
0
 
LVL 48

Author Comment

by:Dale Fye
ID: 39613896
David,

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".
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 39614945
Hi,

I don't have any experience with oil/gas, but recognise the pattern you propose, known as attribute/key/value.

Here is one thought for performance once you get the data into SQL - you could try some indexed views. That is, if you want all the temperature readings then create a view and then create indexes on that view.

http://technet.microsoft.com/en-us/library/dd171921(v=sql.100).aspx

The other thought is that the newer sqls have filtered indexes, which may be able to do the same thing for you.

HTH
  David
0
 
LVL 48

Author Closing Comment

by:Dale Fye
ID: 39636110
Thanks for your help David.  Didn't solve the problem but did point me towards some options.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question