7 Replies Latest reply on Feb 29, 2012 2:07 AM by Gopal

    Visualising PI & AF data through MS Reporting Services


      Our customer wants a web-based visualisation application for their PI data. They have a large number (>15,000) of assets so an AF  hierarchy is required. Each asset has of the order of 10 raw data feeds and there are a number of KPIs that can be calculated from this raw data. Geographical information is also relevant in some cases (eg heat maps) and this is available in the AF data.


      The customer's strategic tool of choice for data visualisation is Microsoft SQL Server 2008 Reporting services and they would like us to build our application around that.


      Does anyone have any experience with showing AF data through MS Reporting Services? Can it be used from within an ASP web page  using C# to collect data from the AF SDK? Or must it talk directly to the data source through ODBC/OLEDB using SQL?


      The customer wants to be able to ask questions such as "at what time of day was the daily temperature highest during the last x days/weeks/months? Show as a bar chart" or "show me a heat map of the average utilisation over the last month of all my 200 primary substations". Questions which I think can't be answered easily in AF alone and would need either a .NET middle layer or maybe some custom data references.


      --- Alistair.

        • Re: Visualising PI & AF data through MS Reporting Services



          SSRS works well with AF via PI OLE DB Enterprise; so there is no need to write your own interface to AF. PI OLE DB Enterprise effectively calls AF via the AF SDK and does resolve all data references including custom ones. Custom DR's are useful for roll up.


          The second portion of your post (last paragraph) hints towards OLAP. Again you can use AF and SSRS in conjunction with SSAS (SQL Server Analysis Services).


          There are some nice white papers available on vCampus that address both.

            • Re: Visualising PI & AF data through MS Reporting Services

              This is a great project Alistair, lucky you! I would like to second Michael's suggestion about PI OLEDB Enterprise in SQL Server Reporting Services (SSRS), for the reporting part.


              For the BI part, you could definitely use SQL Server Analysis Services (SSAS) or even Microsoft Excel Services and PowerPivot - in conjunction with PI OLEDB Enterprise, once again.


              In both these scenarios, you can even host these on a corporate SharePoint environment.


              I look forward to hearing more about this project and how it went!

                • Re: Visualising PI & AF data through MS Reporting Services

                  I will try to keep this thread updated then. I have downloaded a number of white papers and will have a play with the technologies.


                  One potential problem is that they have PI Server 3.4.380, not PI Server 2010 and I am not sure that we can use PI OLEDB Enterprise with 3.4.380 for licensing reasons. However, there is a discussion ongoing with the customer about buying PI Server 2010 so this may well add impetus to that. Is there another way around if they don't upgrade to 2010?


                  --- Alistair.



                    • Re: Visualising PI & AF data through MS Reporting Services

                      Alistair Frith

                      Is there another way around if they don't upgrade to 2010
                      No... a PI Server 2010 is required to obtain the Runtime licenses for the PI Data Access family of products - that is, the customer will need the appropriate PI System Access (PSA) licenses (whether the Named User or the Server version) to deploy/run the solution. I would suggest taking this discussion offline, with their Account Manager.


                      The good news is, you can start working on this in the meantime   As you know, your vCampus membership provides you with the Development licences for the PI Data Access family of products, as well as a PI Server 2010. So from the development perspective, you're all set!

                        • Re: Visualising PI & AF data through MS Reporting Services

                          Wow, it almost sounds like you talked to a product manager lately!

                            • Re: Visualising PI & AF data through MS Reporting Services

                                Normal   0           false   false   false     EN-GB   X-NONE   X-NONE                                  MicrosoftInternetExplorer4                                                                                                                                                                                                                                                                                                                       


                              Apologies for this rather long 'stream of consciousness' post!


                              The initial 'quick wins' application is now finished, allowing the customer to browse the hierarchy and view the existing data in various ways. In order to get it out quickly, it was done sub-optimally as an element-relative Processbook display with a fair bit of VBA inside it.


                              We have been having some more thoughts on the possible technologies for the next phase of this project, which needs to be web based and more flexible,  comprehensive and responsive. One aspect that has come to light is the amount of data validation and cleansing that is needed. Last year, I attended an OSISoft VEE workshop and I am thinking that this kind of application is similar to what VEE is aimed at.


                              Below, I summarise the current system and the customer’s requirements for the project. Then I suggest how VEE might be used and ask a number of questions around this. Finally I summarise the other solution options as we see them, along with their drawbacks. I would greatly appreciate the communities comments on all of this.




                              Current architecture and project requirements


                              ·         They currently have a PI server, receiving around 5 data streams from each of 200 primary electricity distribution substations and a further 5 streams from each of 15000 secondary substations connected to those primaries. This data is received through a 3rd-party, non-UniInt interface. It is fairly real-time in some cases and is 1/2 –hourly in others.


                              ·         There is further data which could be collected, including:


                              o   ‘Event’ indications and associated data. When an ‘event’ is indicated at a substation, they would like the associated data collected and made available, not necessarily within the PI data archive.


                              o   Harmonic information for around 15 harmonics at a substation. This data is fairly real-time. They would like these signals combined into a single summary of ‘Total Harmonic Distortion’ (THD) and stored in a PI tag.


                              ·         They also have lots of infrastructure information about the substations and the feeder cables connecting them held in several relational databases.


                              They want to be able to visualise this data using GIS, Graphs, Trends and Schematics using a flexible Web-based interface. They want to be able to aggregate large amounts of this data. E.g.

                              • A graph of the average voltage, load and temperature during each 1/2 –hour of the day, averaged over the last month.
                              • Or a table with rows for each of the 200 secondaries under a primary substation and columns for Voltage, Load and Power Factor.
                              • Or a GIS map with symbols for each substation representing the load, temperature and voltage
                              • Or a graph showing how well the average load at a substation through the day matches a pre-defined profile
                              • Or a graph showing how the peak demand at a substation has changed over the last 5 years



                              A possible solution using VEE


                              We feel that the best place for the infrastructure information is within AF. Firstly as statically loaded and then later on collected dynamically from the data sources through custom data references and used to automatically reconfigure the AF asset hierarchy.


                              We think that the OSISoft VEE architecture could be well placed for collecting the data. Some of it is unreliable and in order to avoid performance hits at the user interface, we would like to do validation and data cleansing on the incoming streams. The Web back end then just has to concern itself with summarising the requested data and not with validating and cleansing it. To extract, summarise and visualise the data, we would use a web-based system using something like MS SQL Server Reporting services or Tatsoft.


                              Our concerns with regards to the VEE aspects of this are:

                              • Whether we can interface with the control system through the VEE architecture at all. The VEE architecture is targeted at Smart grids, collecting data from Head End systems through AMI interfaces. This 3rd-party interface is not an AMI interface and neither is it even a standard UniInt interface.
                              • VEE is mainly targeted at customer meters whereas this system collects data from one and two points further back in the distribution chain, I.e. the Secondary and Primary substations. As such, although there are fewer data streams, the data rate is likely to be much higher. Is VEE suited to this kind of data profile?
                              • When I attended the OSISoft VEE workshop in Frankfurt last year, it was focused on batch processing ½-hour values on a daily basis. Real-time processing through Stream Insight was mentioned as a capability not yet released. Is it properly available now?
                              • What licenses would we need and what are the cost implications?
                              • The customer has several years-worth of data already on the PI system. They want to be able to view this seamlessly with new data. Presumably, the associated tags would need to be renamed to comply with the Value tag nomenclature, I.e. Meter_Name.Input_Name.Measurement_Name.Value. Such a change may interfere with other systems.
                              • The customer is concerned about the tag count and associated cost. With State, Event and Status for each meter (3*15000 + 3*200 = 45600), plus Validated Value and Validated State tags for each value (2tags*200primaries*10values + 2tags*15000secondaries*10values = 304k), we will need around double the tag count previously envisaged. Do these calculations sound sensible?
                              • Can the architecture handle other calculations beyond VEE? I am thinking of the Event-triggered data collection and the THD described above. For Event data, we are receiving a trigger value, collecting a batch of data, summarising it and storing the summary, possibly in an AF table. For THD, we are receiving 15 signals for each substation and outputting to PI a single value calculated from these 15.



                              And without VEE


                              Our other options, as we see them are:

                              • Do the VEE calculations in ACE. On the plus side, this will not impact the current interfaces and they already have licenses. Drawbacks are the lack of integration between ACE and AF, Scalability of ACE to 15,000 contexts, the need for roughly double the tag count (as above) and a general feeling that the performance will not be acceptable.
                              • Do the VEE calculations in the Web server so PI just stores the raw data and it is validated and substituted as well as being summarised at query time. We are very concerned about the user interface performance in this scenario, however it does not require extra tags in PI or any change to the data collection interface.



                              Many thanks for any thoughts or suggestions on this.




                              --- Alistair.