You are not registered yet. Please click here to register!


 
 
plc storereviewsdownloads
This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc.
 
Try our online PLC Simulator- FREE.  Click here now to try it.

---------->>>>>Get FREE PLC Programming Tips

New Here? Please read this important info!!!


Go Back   PLCS.net - Interactive Q & A > PLCS.net - Interactive Q & A > LIVE PLC Questions And Answers

PLC training tools sale

Reply
 
Thread Tools Display Modes
Old April 26th, 2017, 02:30 AM   #1
martinshaw
Member
United Kingdom

martinshaw is offline
 
Join Date: Jan 2013
Location: Rotherham
Posts: 13
Rockwell Historian SE 4.00.01 and MS SQL Server Express

Hi
Really quick question.
Rockwell Historian SE 4.00.01 has MS SQL Server Express behind it. In a previous life, using Wonderware InSQL Historian, I remember being able to access the SQL database behind the application directly (AnalogHistory, DiscreteHistory, etc tables) using different database administration tools, Toad being one of them. Its this possible with Rockwell's Historian and does anybody know what the database configuration is in terms of tables and relationships?
I should point out that the Rockwell Historian is due to arrive on site in a couple of months so I want to get a bit of background information before hand.
Thanks
Martin

Last edited by martinshaw; April 26th, 2017 at 02:32 AM. Reason: Update
  Reply With Quote
Old April 26th, 2017, 06:50 AM   #2
Dravik
Member
United States

Dravik is offline
 
Join Date: Jun 2008
Location: New York
Posts: 1,330
The actual data is not stored in SQL w/ FactoryTalk Historian. It is stored in flat files.

If you want to access the historical data using OLEDB/ODBC style connectivity you'll be wanting to purchase and install the Advanced Server option.

The SQL stores the Asset Framework configuration and other things however, I can probably dump the schema of mine if you are curious.

Last edited by Dravik; April 26th, 2017 at 06:53 AM.
  Reply With Quote
Old April 26th, 2017, 07:00 AM   #3
martinshaw
Member
United Kingdom

martinshaw is offline
 
Join Date: Jan 2013
Location: Rotherham
Posts: 13
Hi Dravik
Thanks for the reply.
From memory, Wonderware InSQL was also in flat files but if you used a standard query tool (for example Toad for SQL Server), you could get to the data. but only historic, not live. I must say though, its over 10 years since I played with that technology so I could have been mistaken.
I would be interested in your offer of a schema though. I am definitely curious.
Just as thought, if you login to the SQL Server database behind your Rockwell historian, what tables do you see?
Martin.
  Reply With Quote
Old April 26th, 2017, 07:18 AM   #4
dmargineau
Lifetime Supporting Member
United States

dmargineau is offline
 
dmargineau's Avatar
 
Join Date: Dec 2011
Location: Midwest
Posts: 2,385
Any of the MS SQLs databases associated with FactoryTalk products (including FTHSE) are locked-up and available exclusively to the Rockwell application install which they are related to; one cannot 'look-up' the SQL files since there are no available 'SQL Manager" type tools.

In fact, the SQL installed by FTHSE does not contain any 'historical' data since any implemented FTHSE databases are PI (Time-Series) containers as opposed to SQL-relational type ones.

FTVantage-Point and/or FTView SE (the Historian 'front-end") use the FTHSE SQL for Historian SE database connectivity.

Last edited by dmargineau; April 26th, 2017 at 07:28 AM.
  Reply With Quote
Old February 8th, 2018, 03:48 AM   #5
martinshaw
Member
United Kingdom

martinshaw is offline
 
Join Date: Jan 2013
Location: Rotherham
Posts: 13
I know this thread has been dormant for a while but I have a few more questions and observations.

We got FT Historian SE installed in August last year (2017) and its been happily sat there collection data from various PLC's and SCADA's around site. Yesterday was the first time we have tried to use it to view the data and it proved a little disappointing.

I was wanting to simply look at historical data for 10 items over a specific time period of three hours on a specific day and create a graph of the results. I used the PI DataLink excel addin to query the historian data and found that although the wizard looked like it would accept multiple items to select, it would only select one. And then I had to create the graph manually.

I then tried the PI System Management tool to export to CSV (and then to excel and create the graph manually), and although it looked like it had selected multiple items, it grouped them all together and there was no way to separate them.

Is it really the case that in order to display the collected data in some meaningful way, I have to create trend on a scada screen through FTView or use FTVantage-Point? Maybe I'm missing something?
  Reply With Quote
Old February 8th, 2018, 07:00 AM   #6
crawler009
Member
Switzerland

crawler009 is offline
 
crawler009's Avatar
 
Join Date: Feb 2012
Location: Planet Earth
Posts: 185
Quote:
Originally Posted by dmargineau View Post
Any of the MS SQLs databases associated with FactoryTalk products (including FTHSE) are locked-up and available exclusively to the Rockwell application install which they are related to; one cannot 'look-up' the SQL files since there are no available 'SQL Manager" type tools.

In fact, the SQL installed by FTHSE does not contain any 'historical' data since any implemented FTHSE databases are PI (Time-Series) containers as opposed to SQL-relational type ones.

FTVantage-Point and/or FTView SE (the Historian 'front-end") use the FTHSE SQL for Historian SE database connectivity.
Not totally correct, imho. Or i understood you wrong, then im sorry.
I use the standard Datalog from FTVSE, and log the data into a MS SQL (express). Then i use DreamReport to get the data out of the SQL database for further use, print reports and trends.

here is the screenshot in higher resolution: https://ibb.co/er4qMH
Attached Images
File Type: jpg datalog.jpg (47.4 KB, 39 views)

Last edited by crawler009; February 8th, 2018 at 07:03 AM. Reason: added picture link
  Reply With Quote
Old April 17th, 2018, 06:35 AM   #7
martinshaw
Member
United Kingdom

martinshaw is offline
 
Join Date: Jan 2013
Location: Rotherham
Posts: 13
Update on the whole historian / excel question

Yesterday, I spoke to Rockwell and they confirmed that to query the historian as a relational database, I need to purchase the Advanced Server Options as Dravik suggested. As money is tight, I looked at a demo of XLReporter.

It does exactly what I want. I connects directly to the historian using ODBC and I can pick multiple tags across a specified time period and display the data, either tabulated or graphically. It looks like its a fraction of the cost of the Advanced Server Options too so it looks like this might be the way to go for my particular requirements.
  Reply With Quote
Old April 17th, 2018, 06:50 AM   #8
seth350
Member
United States

seth350 is offline
 
seth350's Avatar
 
Join Date: Jul 2011
Location: Over yonder
Posts: 220
Look at Metabase. It’s free and really really nice.
__________________
“Did the Lord say that machines outta take the place of livin’, and what’s the substitute for bread and beans? Do engines get rewarded for their steam?” -John Henry
  Reply With Quote
Old April 17th, 2018, 07:37 AM   #9
martinshaw
Member
United Kingdom

martinshaw is offline
 
Join Date: Jan 2013
Location: Rotherham
Posts: 13
Does Metabase work on windows OS and connect to Rockwell historian? Looking through the user guide, it looks like it will only connect to a regular database and not a historian?
  Reply With Quote
Old April 17th, 2018, 08:11 AM   #10
seth350
Member
United States

seth350 is offline
 
seth350's Avatar
 
Join Date: Jul 2011
Location: Over yonder
Posts: 220
Yes it runs on Windows and provides a web server with login information for multiple users.
Idk about historians...
__________________
“Did the Lord say that machines outta take the place of livin’, and what’s the substitute for bread and beans? Do engines get rewarded for their steam?” -John Henry
  Reply With Quote
Old April 17th, 2018, 01:20 PM   #11
janner_10
Supporting Member
United Kingdom

janner_10 is offline
 
Join Date: Dec 2014
Location: Tewkesbury
Posts: 622
Quote:
Originally Posted by crawler009 View Post
Not totally correct, imho. Or i understood you wrong, then im sorry.
I use the standard Datalog from FTVSE, and log the data into a MS SQL (express). Then i use DreamReport to get the data out of the SQL database for further use, print reports and trends.

here is the screenshot in higher resolution: https://ibb.co/er4qMH
This is what we use, it is by far the easiest and less messy way. The less you introduce factorytalk into things, the better and more robust the data collection is IMO.
  Reply With Quote
Reply
Jump to Live PLC Question and Answer Forum

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Topics
Thread Thread Starter Forum Replies Last Post
Ftv: Ms sql server 2008 r2 Seyed Mohamed LIVE PLC Questions And Answers 5 December 28th, 2015 06:10 AM
Factory Talk View SE to MS SQL database creation fails!! mahone LIVE PLC Questions And Answers 1 May 31st, 2015 01:18 AM
MS SQL Server or Other robw53 LIVE PLC Questions And Answers 10 May 17th, 2012 02:33 PM
OT mrplc offline? BobB LIVE PLC Questions And Answers 28 December 31st, 2008 06:02 AM
Is there an "MS SQL Server Express for dummies" somewhere ? JesperMP LIVE PLC Questions And Answers 6 February 27th, 2008 05:54 AM


All times are GMT -5. The time now is 08:52 PM.


.