SCADA Historical to SQL

[relianttech]

I am looking to store historical data to a SQL Database and was wondering if anyone could offer assistance.

To start I would be looking to store HisDailyMax values to SQL.

Is this type of thing possible with GeoSCADA?  I know that with reporting software such as Crystal Reports, I am able to query HisDailyMax, ID, etc and create reports.  This would be similar but I would be storing this data to SQL.

Thank you.

[BevanWeiss]

Yes, you can do this.

CrystalReports is just using the standard ODBC connector to retrieve data.

You have access to all the tables through this interface.  Including the Historic Views, etc.

Things that you would want to keep in mind are:

1. Historic Tables almost always need an ID filter (otherwise they will complain about an unconstrained query)
2. Historic Tables almost always need a time filter (otherwise they will complain about an unconstrained query)
3. When using ODBC clients like SQL Server, you often need to add the schema prefix when accessing the table, and it can generally be better (sometimes required) to execute the query as an OpenQuery(...) to the linkedserver for Geo SCADA Expert.  Especially when referencing historic tables, otherwise if SQL Server thinks it will be able to do the filtering itself it won't supply the ID/Time filter, and then Geo SCADA will refuse to execute the query (unconstrained query...)
4. It's possible to alter historic data in Geo SCADA Expert.  This means if you only store the value at time T once (when you first see that RecordID) then your data may not match what Geo SCADA Expert reports for it.  Often in Geo SCADA Expert databases this is disallowed (modifying historic data), so not a problem... but if you anticipate having modified data in your system, then you'd want to consider how you'd handle it in your SQL transfers.
5. ODBC isn't the most efficient data transfer system.  So if you intend on transferring lots of historic data (like all the historic records) then you may want to consider a different interface, like OPC-HDA, or native .NET API transfers.  This would be especially true if you will be ingesting the data into a nice TimeSeries DB (like TimeScale or such), or where you might even be wanting to do some sharding / application level replication.
6. SQL server / mysql / postgresql on its own isn't a very nice system to deal with historic data.  It doesn't do strong compression or such on time series data.  There other add-ins (like TimeScale for Postgres) that handle this much better.  And there are dedicated Time Series Databases available.
7. If you will be obtaining large amounts of data, then looking into using RecordID and having some paging mechanism may be worthwhile.  This is how ViewX gets it's historic data (page-by-page).  I haven't looked into this myself, but I try to avoid large SQL queries in general (and go with a different interface if the data requirement is really that high).

[relianttech]

Thank you for the assistance.

You mentioned changing the schema prefix.  Any advise on how to see results in a query?  (see attached photo)

When I select top 1000 rows I am using the following:

     SELECT TOP (1000) [name]
     ,[principal_id]
     ,[diagram_id]
     ,[version]
     ,[definition]
     FROM [SCADADEV].[dbo].[sysdiagrams]
     where PRINCIPAL_ID = 7214

7214 is a GeoSCADA Tag ID with historical enabled but I do not see any results.

[BevanWeiss]

Very little of that query looks like GeoSCADA / ClearSCADA.

Certainly the table name 'sysdiagrams' is not a known GeoSCADA/ClearSCADA table.

If querying from SQL Server, you would generally setup a linked server.

I thought there were more entries in this forum for it.. but it seems they were in the old forum, and possibly haven't been migrated across.  There is this, which provides an example for the CDBObject table.

https://community.se.com/t5/EcoStruxure-Geo-SCADA-Expert/Imported-ClearSCADA-and-MS-SQL-Server-Linke...

As with most of my answers, I would refer you to the DB schema to find the information on what internal DB schema to use in the linked server queries (NOTE: the port number shown isn't the normal for GeoSCADA.. this is a custom port number I've configured.. you should use your GeoSCADA HTTP/HTTPS port number instead).

If you want historic records, then you probably want to look in the CDBHistoric table

So if you have a linked server called 'SCADA' then a valid query would be:

SELECT TOP(10) ID, FullName

FROM SCADA..Kernel.CDBObject

For a valid historic query, well you'd need to find a way to constrain by datetime, and by ID (or equivalent)

[relianttech]

Thanks for the detailed information, this is helpful.

I believe I got a little ahead of myself as I thought I had the connection established between SQL and GeoSCADA DB, however I have not gotten that far.  First, I need to establish that connection and I am failing while using the SQL Server Import/Export Wizard.  Perhaps you can assist with setting up the initial connection.

I have the .NET ODBC driver option as well as the GeoSCADA Option however neither of them work.  I think I am stuck on supplying the proper Data Source and Location when configuring the Schneider Electric SCADA Provider.  I've attached a screen shot.

Before I can query the SQL database I must create the connection and got mixed up.  I haven't gotten that far yet.

Thanks.

[JChamberlain1]

There is an ancient KB article here.

YMMV, but I think I did use it as the basis of querying CS from SQL Server a few years ago.

[BevanWeiss]

It's been a while since I've configured an SQL Server Linked Server.  But the display you have there doesn't look familiar.

And that it mentions 'SQL Server' is concerning, because Microsoft only like to use 'SQL Server' to refer to their proprietary MS SQL Server product.  For other non-MS items, they just use the term 'Server'.

So... you would add a Linked Server

https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio...

And use 'Other Data Source'

For the Data Source you would add your System name (as defined on the SQL Server GeoSCADA Expert Client Connection settings.. because you need to have installed the Data Access Components for Geo SCADA Expert).

I think it goes into the Data Source field... maybe it's the Location instead.  It's one of those places (and ODBC vs OLEDB wanted it a bit different from memory).

Plus setup authentication.

An easy way to check the connectivity is to create a text file called something like "Test.udl" and then try to open it with Windows default.. it should open the Data Link Properties.  Go to the Provider tab, set for ClearSCADA, then Next, then try the Data Source and a valid username/password (NOTE: do NOT use the superuser account here).

Test Connection will tell you if it worked.  Once you've got those settings sorted, then you can just copy it into your SQL Server Linked Server config.

[relianttech]

I've had a little more time to get back to this project.

I've created the test.udl file to verify and test my connection settings.  I am getting hung up on the DataSource and Location.  I believe the DataSource would be the name of the ODBC Connection that I have created to GeoScada, correct?  What would the Location look like?

[relianttech]

When I try to connect to the Data Source using the test method is shows "Test connection failed because of an error in initializing provider.  The specified system does not exist"

[relianttech]

I know you mentioned possibly using something besides SQL database.  I chose SQL because the newest version offers a dashboard environment for end users.  What I'm thinking is something along the lines of utilizing GEO SCADA as my polling engine software and using SQL Reporting Services to display data for multiple clients.  This would allow a web interface for them to login, view data, history, etc.  GeoSCADA's remote interface IMO is very behind and needs to keep up with other providers like Inductive Automation and Ignition.  My thought process is, use the GEO SCADA software as a polling engine, store the data in a database and find another method to display the data for my clients.  With that said, would you have any recommendations or other avenues I could explore?  I appreciate the information and help.

[BevanWeiss]

If it says 'The specified system does not exist' then this suggests that you have either mistyped the system name in your connection settings, or you haven't created the system configuration in your Client Configure Connections application (required for the Geo SCADA Expert / ClearSCADA client interfaces to know what systems are available for it to connect to).

So provide screenshots of these configurations (with sensitive data masked).

And provide screenshots of the MS SQL Server linked server configuration (with sensitive data masked).

Obviously if you're going to mask the important items, then you should detail how you've validated that they are correct... so if you don't show us the system names, then you need to tell us that you have configured the correct fields to matching values.

[relianttech]

Here is a screenshot of Power BI trying to connect using ODBC

Also worth noting is I am attempting this on a demo geoscada database.  The db service is running however on a 2 hr run-time.  

[BevanWeiss]

What user are you attempting this with?

Superuser is NOT allowed such connections.

You would want to ensure that your user is also allowed to edit SQL queries, and has read permissions for the database.

Best to try the query within ViewX first.  Make sure that it's valid, and then execute it from the other environment once you know that the query is valid.