14 Replies Latest reply on Jun 11, 2014 3:11 PM by mhamel

    Relational data in PI Database

    veljovic

      Hi,

       

      I am having the following problem:

       

      to generate some reports in my application, I need time-based data which comes from PI Database and some relational data which comes from a relational DB(for example Oracle).

       

      Now, as the amount of data is huge, the relational database is periodically cleaned, say data older than 30 days is deleted. If someone wants a report which needs data older than

       

      30 days, I have a problem because it is not available anymore(relational part).

       

      Therefore, I am wondering if it is possible to store the relevant relational data also in PI database, and if so, how to do it? Is this a good idea at all?

       

       

       

      Thanks in advance,

       

      Slobodan

        • Re: Relational data in PI Database

          Hello Slobodan,

           

          Time series data belongs into the PI Data Archive (PI Server). Therefore your idea to transfer the data from your relational database makes absolute sense. PI Interface for Relational Database (RDBMS via ODBC) is designed to interface with relational databases. Please use this link to download the interface documentation.

            • Re: Relational data in PI Database
              veljovic

              Hi Gregor,

               

               

               

              thank you for your answer. Maybe my question wasn't clear, I'll try to explain it better.

               

              I am not interested to make use of PI interface for relational databases, because my assumption is that the data which I need

               

              is not present in the relational database at the time I am trying to access it.  I am looking for some kind of concept to represent

               

              relational data in the PI System, i.e. if I want to have a kind of relational DB table, how to model it in the PI system. For example,

               

              I am interested to model the table which describes chemical composition of certain alloy,

               

              which contains the following fields(alloyId, percentage of Al, percentage of Fe, percentage of Mn, ...).  Then I need to add records

               

              to this table programmatically, using AF SDK and later on to access data stored in this table, also using AFSDK.

               

              My question is how to do it?

               

              Regards,

               

              Slobodan

               

               

               

              P.S. I am sorry if I posted my question in the wrong part of the forum.

               

               

                • Re: Relational data in PI Database
                  Rick Davin

                  Is it possible?  Sure, but the cleanliness of the implementation may be in question.  Is there a time associated with the relational data?  If not per second, then per day or per month?  If the data is frequent, as in many readings per day, then I would suggest using Gregor's advice to interface the relational data into PIPoints and PIValues in the PI historian.  If the data isn't that frequent, perhaps weekly or monthly, then perhaps AF Event Frames would be a better fit.

                   

                  So yes, in general, it is possible.  But the best solution requires more detailed information about the relational data.

                    • Re: Relational data in PI Database
                      veljovic

                      Hi,

                       

                      Thank you very much for your answer. Is it correct to assume that using PI AF Table for my purpose is not recommended?

                       

                      (AF reference mentions some limit of 10 000 records per table.)

                       

                      My application handles automation of a rolling mill and I have a large amount of relational data, which is the main reason

                       

                      to delete the data periodically in the first place. But I do not need all of it to generate reports afterwards,

                       

                      perhaps only a small subset of it.

                       

                      Out of the two possible solutions you have sugested, I like the one with AF Event Frames better, because then I do not need to

                       

                      worry about questions like which data from two different PI Points belong to the same record, and with Event Frames I get it

                       

                      automatically: one Event Frame represents one record from the conceptually associated relational table. Do I get this right?

                       

                      But maybe I am not seeing the whole picture and there are other advantages of the first approach, other than being able to handle

                       

                      larger amount of data.

                       

                       

                       

                      Regards,

                       

                      Slobodan 

                        • Re: Relational data in PI Database
                          Rhys Kirk

                          Slobodan, you could solve this by instead of deleting the data periodically, first extract the relevant data (the subset you refer to) to longer term storage, delete the non-required data, and then access the historical data as you do for the < 30 days old data. Moving the data to non-linked AF Tables gets stored in AF's SQL Server data store anyway. To echo Rick's comments, if this is time-series data then it may fit to be stored directly in the PI Data Archive.

                           

                          The chemical composition of the alloy example you gave, is that reference data relevant to a product batch?

                            • Re: Relational data in PI Database
                              veljovic

                              Well, basicaly I have two types of data: measurements of certain quantity during material rolling(e.g. temperature of the material) and

                               

                              this is the time-series data which I store in the Pi archive; the second type of data is not time-series data, it is calculated for each material(setup of some actuators in the system)

                               

                               or is given  as input data(like checmical composition of material). This second type is the one I am having problems with, how to save it in the PI system/ make it available for the

                               

                              longer period of time(longer than mentioned 30 days).

                               

                              In any case, thank you for the comments and sharing your ideas.

                               

                               

                               

                              Regards,

                               

                              Slobodan

                                • Re: Relational data in PI Database

                                  Hello Slobodan,

                                   

                                  is the material setup like a recipe e.g. actuators are configured depending on the product? If so, you may want to create Elements for each product and store actuator values with attributes. If you prefer storing that information in a relational database "close" to the PI System so it remains available, you may want to create a separate Database within SQL Server. It might also be an approach to keep some data in the original relational database e.g. copying it into another table before cleaning up the original table. AF Table lookup can be used to make the data available in the PI System independent from where it is stored. For this purpose it's recommended keeping the AF lookup tables small to avoid performance issues. Event frames is another option to refer data that is related to time series production data. Combinations of solutions are as well possible.

                                   

                                  Assume you would have a kind of batch process. Dependent on the product, you need to run your machines with a specific setup. Certain limits depend as well on the product. You could use AF Database to store the product specific information and when running a certain batch just apply settings based on the product name and use Event Frames to bring information for a certain batch into context.

                                   

                                  There are many options. For a decision a good understanding of your production process is required. It's also worth to think about what kind of reports you like to create. One possible solution could be, running reports before data becomes deleted every 30 days, store the reports in the file system and a the path to each single report as an Attribute inside an Event Frame. The downside would be that without the data reports have been built with, you will not be able to verify them. One thing we don't know is, if this would be acceptable.

                                    • Re: Relational data in PI Database
                                      skwan

                                      Slobodan:

                                       

                                      From your description, the settings for each material rolling, I would suggest you seriously look into storing them as event frame attributes.  For example, let's imagine you are performing your material rolling from 10:00 - 14:00 and during this time you also have the actuator settings and chemical compositions.  This would be an event frame with start time = 10:00, end time = 14:00, event frame attributes that are PI Point data references to your time series data like temperature, and lastly event frame attributes to store the actuator settings and chemical compositions.

                                        • Re: Relational data in PI Database
                                          veljovic

                                          Thank you all for your suggestions.

                                           

                                          Regarding Event Frames, which I already use to contextualize measured values, is it recommended to have time series data as attributes of Event Frames

                                           

                                          or rather as attributes of separate AF Elements? Is there some recommendation regarding the maximal number of such attributes of an Event Frame?

                                           

                                          Due to large number of different measured values, and here I am talking about possibly hundreds of values in some cases, does it influence system performance

                                           

                                          if I put all these values inside an Event Frame(as attributes with PI Point as data reference)?

                                           

                                           

                                           

                                          Regards,

                                           

                                          Slobodan

                                            • Re: Relational data in PI Database
                                              skwan

                                              Slobodan:

                                               

                                              Under the hood, event frames are really very similar to an element.  You can have many hundreds of attributes to an event frame and you should be fine.  Beginning with AF 2014 (v2.6), we added a new feature call "event frame value capture".  Therefore, you now have two options with event frame attributes.  Option 1, you leave the attributes as data references and they will be evaluated at run time - for example when you examine a PI Point data reference, AF will retrieve the values from the server(s).  Option 2, you toggle the value capture option on the event frame and the data references are resolved once and then stored in the AF database.  You can re-capture at a later time.  There are advantages to both and you have to decide which is best for you.

                                                • Re: Relational data in PI Database
                                                  veljovic

                                                  So, I will try to sum up the possibilities, according to my understanding of the discussion so far:

                                                  1. store the data using Asset Framework with AF Elements representing tables, AF Attributes(Data Reference is PI Point) representing table columns; then the problem to determine
                                                    which values from different columns belong to the same record(table row) needs to be solved
                                                  2. store the data using Event Frames: Event Frame represents a record(tabel row) and Attributes represent table columns; no need for PI Points as data references of Attributes
                                                  3. store the data in AF Tables; the restriction of up to 10000 rows applies according to AF Help(Imported Tables and Linked Tables)
                                                  4. create separate database on SQL Sever to store the data

                                                  Now, ruling out options 1 and 3, I would like to somehow compare the second and the fourth approach: how does storage space for Event Frame compares to a space needed

                                                   

                                                  for one table row; how long it takes to find the needed Event Frame compared to time to find it from the database?

                                                   

                                                  Is it possible to say something about these queations in advance, before testing both options on some practical example? Is there another important aspect to consider?

                                                   

                                                   

                                                   

                                                  Regards,

                                                   

                                                  Slobodan

                                                   

                                                   

                                                    • Re: Relational data in PI Database
                                                      mhamel

                                                      @Slobodan: I would like to get the big picture. What is the goal of your report? Where do you produce it? How many fields of data are required from the relational side? Are there more than of time-series ones?

                                                        • Re: Relational data in PI Database
                                                          veljovic

                                                          @Mathieu: One typical use case is to produce coil of thin material out of the coil of thick material. The customer wants a report about the produced coil.

                                                           

                                                          This report contains relational data, like chemical composition of the material, coil weight, start and end time of production, etc., and time-series data, like

                                                           

                                                          thickness, temperature, etc. The report is produced when the customer wants to see it, typically soon after the coil is produced, or at the later time to, e.g.,

                                                           

                                                          compare it with some other coil. In this case there are up to ten data fields from relational data and 10 to 20 time-series related  data fields. The amount of data for

                                                           

                                                          other report types may vary.

                                                           

                                                           

                                                           

                                                          Regards,

                                                           

                                                          Slobodan

                                                            • Re: Relational data in PI Database
                                                              mhamel

                                                              @Slobodan: Thank you for your explanation. I would concur with Steve Kwan suggestion about the use of Event Frames to report the coil production filled with time-series and relational data.

                                                               

                                                              This way you can think of automated report generator (a.k.a. event frame generator) containing the information you need. Thereafter, it would be easy to "show" your report with PI DataLink.