Skip navigation
All Places > PI Developers Club > Blog > 2012 > March

Old Number 0

Posted by bperry Employee Mar 31, 2012



On behalf of myself and Martin Bryant, Howdy from Houston as we inaugurate this new blog of ours.




We’re OSIsoft Field Service engineers. In the course of our ramblings, Martin and I see a good deal of action with PI AF, the PI Data Access suite, and Analytics. We do customer coaching, internal research (fancy way of saying we get harebrained ideas from time to time), proofs-of-concept with OSIsoft partners (usually involving integration of PI with other business systems), and have been known to write material for the public Application Development course. 




Thus we find ourselves with a stack of code and lessons learned which  we “should throw on vCampus,” and this blog will be its landing pad. Thanks to the vCampus team for getting us set up here.




Brandon Perry



Recently, I have been playing around building some data cube for BI analysis with PowerPivot and PI OLEDB Enterprise, and one of the feature of PI OLEDB Enterprise that I feel that is really useful through this process is Transposed Vector Function (TVF). There were some discussions in the past that TVF is brought up, like this one. Steve basically has made a brief and concise explanation of what TVF does. If you are still not sure, here's a quick explanation.


In our usual query in the classic PI OLEDB Provider or in PI OLEDB Enterprise's tables in the Data category like:



SELECT e.Name Element, ea.Name Attribute, i.Time Time, i.ValueDbl Value
FROM [AFDB].[Asset].[ElementTemplate] et 
INNER JOIN [AFDB].[Asset].[Element] e ON et.ID = e.ElementTemplateID
INNER JOIN [AFDB].[Asset].[ElementAttribute] ea ON ea.ElementID = e.ID
INNER Join [AFDB].[Data].[ft_InterpolateRange] i ON ea.ID = i.ElementAttributeID
WHERE et.Name = N'Refinery' 
AND e.Name LIKE N'Refine%'
AND i.StartTime = N'*-1h'
AND i.EndTime = N'*'
AND i.TimeStep = N'30m' 



and a snapshot of the result set:




This get the values of the different attributes, but all are presented in a single column. In my case a more ideal case is to have the different attributes presented in different columns. This is where TVFs is helpful. Using TVF, we can create a function in PI OLEDB Enterprise that returns the result set like:




Now in PowerPivot (of cause, you can be using other tools for your BI analysis as well), I can grab the attribute values that represent the KPI that you want from the respective column directly.


What you have to take note for this

  • TVF must be created before use (using PI SQL Commander, refer to the PI OLEDB Enterprise manual)
  • TVF is created for a specific Element Template in PI Asset Framework (AF)

While I am at this, there is a new white paper, titled "Business Analytics with your PI System Data using Microsoft PowerPivot" that was recently updated in the vCampus Library, under the "OSIsoft - Microsoft Accelerator" category. It contains step-by-step walkthrough of the processing of bring PI System Data to PowerPivot using PI OLEDB Enterprise. The sample AF database is included in the supporting files, hence you can walk through the paper easily.

Alrighty! It's released! As you might have seen from the release announcement that just went out from the Tech Support website, we just (re-)released PI OLEDB Enterprise 2010 R3! You can now download it from the vCampus Download Center, under the "PI Data Access" category.


As you may remember, this release was part of the overall PI Data Access 2010 R3 wave of releases and we had to pull PI OLEDB Enterprise to fix a defect that may have affected you. We thank you for your patience as we were ensuring a higher-quality release, and we sincerely apologize for any inconvenience this may have caused.


We are looking forward to getting your feedback on this release and read the questions you may have! I did blog about these releases and the new features some time ago, but please consider registering for the upcoming webinar: All you want to know about the PI Data Access 2010 R3 releases.


Happy SQL'ing!

[If you are already familiar with Hadoop you can only read the last two paragraphs] There is a strong trend in the Big Data community about Hadoop and all different layers and implementations of it. Google started the MapReduce software framework along with the GFS as the accompanying distributed file system. Then came along the open source version expanded by Yahoo and others delivering MapReduce and HDFS as the software and file system frameworks respectively. Nowadays a lot of Big Data organizations have their own Hadoop clusters capable of handling many terabytes and petabytes of data with some good  level of fault tolerance.


The main advantage of Hadoop lies in its cheap implementation, flexibility to lack of structure in data, and fault tolerance. In fact, the justification is that we can provide sufficient redundancy so that we can always be available in face of normal hardware and software failures using Hadoop. The redundancy is of course possible because of low hardware cost. Also, we don't need to process all the data that comes in right on the spot; therefore, Hadoop can store unstructured data for future processing. The downside is that sometimes you get incoherence among data nodes. Also, for smaller amounts of data traditional database systems, or Parallel Data Warehouses (PDW) can get the job done with far fewer number of nodes. To show the trade off let's see this example: eBay handles data roughly half or a third in size of Facebook's. eBay runs on PDW while Facebook runs Hadoop. Guess how much bigger Facebook's cluster is? About 10 times! Also, you sometimes see incoherence in Facebook updates which is a direct result of multiple copies and cheaper hardware. All in all, there are merits for both paradigms to co-exist and serve different purposes. In fact Microsoft adopted Hadoop in 2011. For the same reason some organizations (including Microsoft) embrace Sqoop (SQl to hadOOP) to bridge the gap.


Now the question is what you think of the future of Hadoop and the PI System. Do you see any feasible integration or use in there? For one thing, we cannot break down the archive files at random places in chunks of 64MB sizes as Hadoop requires. What if we make archive shards of 64MB in size and replicate whole copies of each on Hadoop data nodes? What operations you think can fit well in the MapReduce paradigm? Many more advanced analytics can very well be broken down (MapReduced) into parallel operations. How about data collection in the first place?


Do you see any downside to that? Do you see any major obstacle why PI System would not fit well into Hadoop platform?

Some recent thoughts...

Posted by RJKSolutions Mar 12, 2012

Hello Community,


I have been feeling guilty recently for not blogging more frequently on vCampus, if only I could find some more hours in the day - the curse of being a vCampus All-Star .


Firstly, I want to start off talking about the OSIsoft User Conference that is coming up (go register now!).  Unfortunately I won't be able to make it this year as I have baby #2 on the way, due to arrive early May so I cannot take the risk of being an 11 hour flight away when I get that important call.  I need to stop having children that are due on or around the User Conference or vCampus Live! events.  There will be plenty of my Wipro colleagues in attendance (I encourage you to speak with them about anything PI) gathering intel and plying OSIsoft employees with alcohol with strict instructions to find out more about details and timelines for Server Side Buffering (Jay, watch out ).  Yep, I did it again, I mentioned SSB.  Seriously, the User Conference is a great annual event and I wish I could be there again this year.  I will be at vCampus Live! though with some exciting presentations.




For my next topic I am going to talk about StreamInsight.  vCampus has a dedicated forum ( for StreamInsight that seems to be relatively quiet, either people are keeping their cards close to their chests or they have not started on the road to development with PI & StreamInsight.  I am a StreamInsight fan since spending the last few months researching StreamInsight, developing with StreamInsight and looking at the OSIsoft PI for StreamInsight Adapters (that you can download right here on vCampus), and concluded that we need to get some more community involvement and discussion around development with the PI for StreamInsight Adapters.  How many of you reading this blog post have developed something using PI SDK?  Now all of you with your hands up, how many of you used EventPipes to capture data events as they happened?  If you still have your hand up then I suggest you take a look at the event driven nature of StreamInsight and keep it at the back of your mind next time you start developing an application that needs to respond to real-time events as they happen on your PI Server or Collective.  (Many, many other reasons for using StreamInsight but just wanted to fire off some thought processes in your heads with the PI SDK Eventipes example.)
In my usual obsessive manner, I will continue to post discussion points related to PI & StreamInsight, or random ideas that pop in to my head at inconvenient times related to how PI & StreamInsight can be used together.  For example, I have a nagging idea related to how Coresight can use a neat StreamInsight engine for common data requests for more efficient data retrieval.  Soon I will find some time to make sense of it and post it up on the forum. 




Lastly, I want to talk about the vCampus Community.  I hope that everyone else is feeling the nice momentum that the community has right now and is enjoying being part of that community.  Having been around since the beginning of vCampus I have seen it grow like my own son, from being relatively quiet needing input and guidance, to growing in to a self sufficient entity that now talks back!  I imagine that a lot of answers people have are now found by searching the forums, that people downloaded the AF SDK CTP with Rich Data Access  or the OLEDB Enterprise 2010 R3 quicker than they did the Windows 8 Consumer Preview, or that people get more e-mail updates (that they are genuinely interested in) from vCampus than they do from Facebook!  All in all, vCampus has been one of the best initiatives from OSIsoft for a long time and looks as though it is growing stronger each year; it is backed by a great vCampus team.  What I am looking forward to in the future is seeing an equivalent online community for TechSupport, and seeing both of those communities become a single thriving community one day.


That is all for now.



Whenever we talk to people about OSIsoft vCampus, one of the most important aspect that we highlight is having an online community of PI System developers and integrators, encourage communication and collaboration among the community on the usage of PI System technology. Because of this, it is very encouraging for us (the vCampus team) to see more active participation in discussions among the community since the beginning of 2012. More significantly, we see more users replying to posts and sharing their insights on the various discussions on the forum.


We really like to show our appreciation to members who have contributed their time and effort on OSIsoft vCampus, and vCampus All-Stars award is one of the ways that we have adopted to acknowledge users who have contributed greatly throughout the year. Of cause, we do see that there are other users who deserve acknowledgement as well, hence we are starting the "Post of the Month" initiative.


Since the start of vCampus, we have had some outstanding posts in discussions like this and this that are helpful, educational and provides insight into the products. And these are the post that we are looking for in this initiative!


For the month of February 2012, we would like to congratulate Rick Davin for his post on                                   Simplest method to get the PI tag name for an Attribute. If                                 you have not read Rick's post, check it out here! In the post, Rick has compared the performance of different methods of getting the tagnames in an AFAttribute with PIPoint data reference and shared his finding. Thanks to Rick for the initiative and effort on this!


Of course outstanding posts may not come as regularly as we want, hence we may not get one for a particular month. The vCampus Team will keep an open eye for future "Post of the Month". If you do come across any posts that you feel it should be acknowledged, feel free to drop us a note to our email or directly to any of the vCampus team members.

Hello vCampus community!


I wanted to take a few moments to describe some of the OSIsoft presentations you’ll be seeing at the User Conference in San Francisco this year. In case you haven’t heard, the annual OSIsoft User Conference will be held April 23 to 25, 2012, at the Hilton Union Square in San Francisco. You can sign up today at this link. This year’s theme is: The Power of Data, so get ready for some data-driven presentations.


Naturally, we’re all hard at work preparing the content to be presented by OSIsoft this year, but I thought this would be a good time to give you an idea of what to expect. Feel free to post your questions and comments right here on the blog. At this year’s conference you’ll have the opportunity to see what’s new in the PI System as well as watch demonstrations of how to work with our products in meaningful ways.


In the category of Client applications, you’ll see:


·         Demonstrations of the asset-relative features of PI Coresight, PI ProcessBook, PI WebParts, and PI DataLink, featuring dashboard displays in Microsoft SharePoint. There are new versions of PI Coresight, PI ProcessBook and PI DataLink, which will have their own focused presentations.


·         Demonstrations of leveraging operational data in Business Intelligence (BI) and Reporting tools


·         More information on our plans for mobile device offerings


·         A review of past customer presentations of PI ProcessBook displays (aimed at our newer users) along with demonstrations of the new features in PI ProcessBook 2012


In the category of Server applications, you’ll see:


·         Demonstrations of the new PI Server


·         Ways to build up your PI Asset Framework content with typical usage scenarios and examples


·         How customers get value out of PI Notifications


·         An overview of how PI Analytics calculations are built and used


You’ll also see presentations on:


·         Research on cloud offerings


·         Cyber Security recommendations


·         Line of Business application integration


·         An update on PI Event Frames features


·         New possibilities with PI Interfaces


To learn more about the latest and greatest the PI System has to offer, plan to stay an extra day to participate in the OSIsoft Education Day. If you bring a laptop with a wireless adapter, you can participate in the hands-on activities using our cloud-based Virtual Learning Environment.


Finally, don’t forget the always-popular Product Expo, where you’ll have an opportunity to see even more product demonstrations and speak with the people who work so hard to produce them.


The full conference agenda is available now! Check it out….


See you there!


Compare daily values

Posted by andreas Employee Mar 2, 2012

From time to time people need to compare today's trend for a PI tag with yesterdays, or even several days in the past. One way to achieve this is applying a time offset via a PI Calculation dataset in PI ProcessBook.


So if you want to compare yesterday's SINUSOID with todays, you could use a PI Calculation dataset with the formula "TagVal('SINUSOID','*-24h') and trend this along with SINUSOID. Creating such a dataset and adding it to PI ProcessBook is just a couple of lines of VBA code - so please enjoy:


First - we have a ProcessBook display with a trend, a button and two combo boxes:




To populate the comboboxes at the beginning, let's add some code:


Private Sub Display_Open()
    ' Fill in the shifts
    Dim aa As Integer
    For aa = 1 To 31
        ComboBox_Shifts.AddItem (aa)
    ' Fill in the offsets
    For aa = 1 To 24
        Combobox_Offset.AddItem (aa)
End Sub

Now we build a procedure to build the dataset. We need the servername, the tagname, the offset and how many times we want to apply the offset:


Private Sub CreateAndAddDS(ServerName As String, TagName As String, OffSet As String, OffsetNum As Integer)
    Dim MyDataset As PIExpressionDataset
    Dim MyColumn As String
    Dim MyDataSetName As String
    Dim MyDate As Date
    ' Find a reasonable name for the dataset
    MyDate = DateAdd("h", -OffsetNum, Trend1.StartTime)
    MyDataSetName = TagName & "_" & MyDate
    MyDataSetName = Replace(MyDataSetName, ".", "_")

    ' Create the dataset
    Set MyDataset = Datasets.Add(MyDataSetName, Nothing, False, 1, True, pbDatasetPIExpression)
    Set MyDataset = Datasets.GetDataset(MyDataSetName)
    ' Adjust the dataset to our needs
    MyDataset.ServerName = ServerName
    MyDataset.Expression = "tagval('" + TagName + "','*-" + OffSet + "')"
    MyDataset.RefreshInterval = 3600000
    MyDataset.ColumnName = "Value"
    MyDataset.Interval = "10m"
    Me.Datasets.SetDataset MyDataset
    MyColumn = MyDataset.Name & "." & MyDataset.ColumnName
    ' Add the dataset to the trend
    Trend1.AddTrace MyColumn

End Sub



What we need to do finally is executing this on button click. But before building the datasets we have to clean the existing ones. Than you can start building the datasets for each tag in the trend:


Private Sub CommandButton_Draw_Click()
    Dim MyFullTagname As String
    Dim MyServername As String
    Dim MyTagname As String
    Dim MyOffset As String

    Dim aa, bb As Integer
    Dim MyOffsetNum As Integer
    MousePointer = pbMPHourGlass
    ' Remove all datasets from the trend
    For aa = Trend1.TraceCount To 1 Step -1
        If (InStr(1, Trend1.GetTagName(aa), ".Value")) > 0 Then
            Trend1.RemoveTrace (aa)
        End If
    ' Remove all PIExpression datasets from the display
    Dim MyDS As Dataset
    For Each MyDS In Datasets
        If MyDS.Type = pbDatasetPIExpression Then
            Datasets.Remove (MyDS.Name)
        End If
    ' For all traces in the trend
    For aa = 1 To Trend1.TraceCount
        ' Get tagname and servername
        MyFullTagname = Trend1.GetTagName(aa)
        MyServername = Mid(MyFullTagname, 3, InStr(3, MyFullTagname, "\") - 3)
        MyTagname = Right(MyFullTagname, Len(MyFullTagname) - Len(MyServername) - 3)
        ' For the number of shifts
        For bb = 1 To (ComboBox_Shifts.Value - 1)
            ' Calculate the "real" offset by
            ' multiplying the shift with the offset
            MyOffset = (bb * Combobox_Offset.Value) & "h"
            MyOffsetNum = (bb * Combobox_Offset.Value)
            Call CreateAndAddDS(MyServername, MyTagname, MyOffset, MyOffsetNum)
        Next bb
    MousePointer = pbMPDefault

End Sub



And this is how it looks now:




The complete PB is attached - and I have to mention Dan Fishman and Michael Ramella from field service that initiated all of this.


At some point this functionality could be replaced with PI Event Frames (in PI ProcessBook and other client tools). The PM's plan to address this in a more built-in fashion but meanwhile feel free to build on this .

Thursday was Day 2 and the final day of the conference. Very interesting was the talk by Twitter on their Storm platform to handle distributed and fault-tolerant real time computation. Storm is an open source platform that is made for handling Big Data analytics including Twitter-scale word counting, for example. The whole structure is "divide and conquer" and takes advantage of Hadoop architecture.


Also very interesting was a talk by Skytree on Machine Learning and big data (my favorite!) They offer a suite of advanced Machine Learning algorithms in the Cloud. The claim was that on major existing Machine Learning algorithms they outperform others by orders of magnitude. The front end can be MATLAB, R, or command line, among others.


There was also a very interesting talk by CrowdControl who offer crowd sourcing services. The mechanics are handled by Amazon Mechanical Turk. It was interesting to see that in fact a marketplace for crowd sourcing big tasks exists out there. Microtasks are priced at cents apiece; pretty cheap!


In general the energy was very high and the buzz words were power of data, Hadoop, R, and analytics. It was my first appearance at Strata but I heard from several people that it is the biggest and most energetic one so far. To give you an idea of the boom in this market, here is the picture of one of the several job boards at the conference:



Wednesday was the official opening of the event. There was 8 parallel sessions going on all day long; so eat was drinking from a fire hose. The keynotes came from a diverse group from hard-core data analysts to computer organizations such as Microsoft, as well as a doctor who used big data to analyze different drugs and detect deceiving marketing habits.


Among other sessions, Microsoft Hadoop was a great session I attended. It was presented by Alexander Stojanovic, General Manager for Cloud Analytics and Computation at Microsoft. He emphasized how Microsoft recently adopted and embraced the open source Hadoop platform aimed at cheap, distributed storage and processing of Big Data (petabytes). Also the open source platforms makes it more vibrant and open to public. They have an end-to-end approach: data management, data enrichment, analytics with emphasis on self service. Microsoft Hadoop is now offered for Windows Server and Azure. It also comes with a Hive add-in to Microsoft Excel for direct transfer of data between Excel and Hadoop clusters. A couple partners were showcased (Hortonworks and Karmasphere) who have adopted the CTP of the product.


Another very interesting talk was offered by Netflix. They went over the algorithms and some details of how they deal with ranking their movies in a personalized fashion. They deal with 2 Billion ratings right now adding millions every day. You can imagine how difficult the problem can get.


The Exhibit Hall was very successful with tens of organizations, large and small, offering Big Data and analytical services. Several smaller firms focus on doing analysis on Big Data for you. Interestingly enough, many of them offer their unsupported solutions in an open-source fashion for free.

Filter Blog

By date: By tag: