Getting data from a PI/OLEDB Provider is a no brainer for most of us, but assigning row numbers to each row according to some criteria is quite a challenge and it is something customers request frequently. I have learned the hard way that there is no "easy" way to do this and that in order to achieve this you need to make some sacrifices: some of them come in the shape of internal functions, external code that manages the visualisation layer or (in this case) having a linked server inside SQL Server 2005/2008. This may not be the best way to do this (performance-wise) but its simplicity is enough to give it a try.
The way this works is that the SQL Server will get the data from the PI Server through the PI OLEDB Provider and will then add the Row Numbers before returning this dataset to the final client. This final client can be anything that can connect to the SQL Data provider. Note that we need a client able to issue the row_number() function so the use of "SQL Server Native Client 10.0" is strongly recommended.
Adding a Linked Server in SQL Server 2008
First we need to get our PI Server listed into the SQL Server, and we need to start up the Microsoft SQL Server Management Studio (or the MSSMS, too many M's and S's if you ask me). Once in that program and after we connected to our desired SQL Server we...
- Expand 'Server Objects'.
- Right Click on 'Linked Servers'.
- Select 'New linked Server'. A dialog will pop up.
- Fill 'Linked Server' with a name of your choice. "LOCALHOST" was used in this example.
- Select in 'Provider:' dropdown "PI OLE DB Provider".
- Fill 'Product Name:' with a value of your choice. "piserver" was used in this example.
- Fill 'Data source:' with the name or ip of your pi server as it appears on the PI SDK Connections. "localhost" would work in this example.
- Click 'OK'.
Building a query
After we have added our linked server we will need to create a new query and write the query we want to execute.
- Click on the 'New Query' button. A new, empty, query will appear.
- Write the query you want to run in the new window:
WITH OrderedValues AS
SELECT TOP 10000
ROW_NUMBER()OVER (ORDER BY [time] DESC) AS RowNumber
WHERE tag like'cdt158'
WHERE RowNumber BETWEEN 1 AND 10
- Now click on 'Execute' and you should get a list of results that will not be exactly those displayed but will look pretty much alike.
Please note that you can change the tag name, select more than one tag, select a different number of rows (I used from rows 8829 up to 8838 in this example), you could even use parameters in this query. I specifically used these values to show the same range of data in all three windows so you could compare them.
Verifying the values
So far we got a linked server and some data, now we'll verify that we have the same values in both places, in the PI OLEDB Tool and in the SQL Linked Server, we will be using the infamous CDT158 random tag for this test, and the query we'll use is SELECT [tag],[time],[value],[status] FROM [picomp] WHERE tag like 'cdt158'
The query can be seen in the lower part of the screenshot from the program.
Using the values
Now we will be consuming the values connecting to the SQL Server. Note that we could do this step with any tool that supports acquiring data from SQL (such as Excel, SharePoint, ProcessBook or any other zillion applications). However, for ease of use and since the dialogs for the configuration are almost the same in all of them we'll be connecting with Excel.
- Create a blank workbook in Excel
- Select 'Data', From other sources, From the 'Data Connection Wizard'
- Select 'Other/Advanced' and click 'Next'
- Under 'Provider', Select the 'SQL Server Native Client 10.0' (I have SQL 2008, that's why it says 10, it may say 9 if you have SQL 2005)
- Under 'Connection', enter your SQL Server name (LOCALHOST\SQLEXPRESS in this example)
- Select the appropiate log on type ('Use Windows NT Integrated security' in this example)
- Select any database (PIAF_Database in this example), note that the linked server's databases do not show up, so any database will do for now.
- 'Test' your connection and if everything succeeds...
- Click 'OK'
- In the next dialog check 'Connect to specific' table
- Select any table (AFTableView in this example)
- Click 'Finish'
- It may complain about a file that already exists (even more so if you have done this step before), be sure to overwrite it.
- In the 'Import Dialog' I left everything as is, your preferences may change, it is ok to change this.
- Click 'Properties'
- In 'Connection name' any name will do (PIoledb on this example)
- Under 'Definition', for the 'Command Type' select 'SQL' from the dropdown list.
- In 'Command Text' paste the query you build before (ref.: Building query, Step 2 in this example)
- Click 'OK'
- Excel will complain about the connection having changed from the one in the configuration file and warn you about removing the link to the external file (wait, does that mean that we won't need the .odc file anymore? that's correct! can you believe it?) be sure to click 'OK'
- Back on the Import data dialog, click 'OK'
- And voila! we've got data numbered on our spreadsheet, thanks to the PI OLEDB Provider and SQL Server.
- You’ll notice that even getting this amount of data can be done in less than 6 seconds on a low-end computer running the PI Server and the SQL Server (which is not recommended)
- There are some other interesting numbering functions available in SQL 2005/2008 like DENSE_RANK(), RANK() and NTILE()
- You could try to do this with the SQL Server Native Client instead, but it will fail miserably when changing the query as that connector won't understand the new functions.
- We added too much complexity for numbering these rows, that could be easily done with a programmatic interface after we have collected the values.
- The index in this example will change from query to query, which is less than optimal. But then again, if it didn't change why would we need dynamic numbering anyway?
- This can be used to do some pagination-like feature on some places, like RtWebparts, or a datagrid.