Ignition Version: 7.9.20
Sepasoft OEE Downtime Version: 3.79.3
We have setup the Sepasoft Production & OEE modules for one of our production lines and want to pull the OEE related data into Power BI, our corporate reporting tool, using SQL queries against the SQL Server instance we are running and storing the Sepasoft database in.
Here are some examples of the data we need:
- OEE every 5 minutes and aggregated
- Availability every 5 minutes and aggregated
- Performance every 5 minutes and aggregated
- Quality every 5 minutes and aggregated
- Good Parts Made every 5 minutes and aggregated
- Bad Parts Made every 5 minutes and aggregated
- Total Parts Made every 5 minutes and aggregated
- Cell Downtime Reason
- Line Downtime Reason (the clever calculated reason sepasoft's engine generates by analyzing the states of the cells in the line)
- Equipment Mode over time
- Equipment State over time
I am familiar with relational databases and have been writing queries for years, but the Sepasoft database schema is proving exceptionally challenging to understand and i cannot find anything in their docs or online forums to use as examples.
If we cannot find a way to do this, we will have to scrap using sepasoft. Using Power BI is a hard requirement from stakeholders.
what we have done in the past is utilize ignition as sort of an ETL.
Due to the complexity of the Sepasoft mes database you have to go through and link MESEquipment,MESObjectLink,MESEquipmentStateClass,MESEquipmentStatee,MESEquipmentStateValue, and as well as the count tables and such.
Instead of trying to make some overly complex CTEs and things to get data properly, you can call some of the system.mes functions on a gateway script and then write that data to another table on some interval.
Downtime is a pretty easy one if you look at their docs. system.mes.getEquipmentStateHistory (sepasoft.com)
Another example, if you know you wanted to track OEE over time as well as maybe count metrics, etc. You could run a gateway script to read from the mes tag provider to capture the live analysis tags and store them through named queries.
I have had many conversations with Sepasoft about their database and the answer is always "We do not recommend querying the database directly, as it is complex"
2 Likes
: ) Thanks for replying
Thanks for the suggestion! I will look more into that.
I am going to meet with them today on this issue and if that is really what i have to do i guess i can make it work, since it will still probably be faster/cheaper than building my own OEE solution, i hope.
That is really a shame that they don't at least provide queries/views/tvfs/sprocs/functions/etc. automatically generated when they make the DB or in their docs so you can copy-paste to handle what i assume is a very common problem. Having the database on prem was one of the main selling points to me because it should allow for much faster/simpler integration with other systems as opposed to cloud-based solutions. This seems a bit kludgy for what otherwise seemed so far to be an excellent corporate-grade OEE solution.
They do have functions, but they are in the Ignition gateway as ScriptingFunctions. Really and ETL is going to be a better way anyways because the Sepasoft database is kind of awful in terms of quick querying. They don't store downtime for rollups in the database, which is great when you may need to update a downtime and want that to go up the production model, but when tons of time has passed it is less than useful. Most all large Sepasoft solutions should either be married with an ETL Tool of some sort, or you are forced into multi gateway licensing for task routers and enterprise setup.
Using an ETL you simply get all the data you need in any format you want, and you control exactly what columns get ported over.
If you or your team needs any ETL architecture assistance, feel free to PM me as that is outside of the scope of this question.