prescriptive architecture guide: integrating pi system ... · pdf fileprescriptive...
TRANSCRIPT
Prescriptive Architecture Guide:
Integrating PI System 2010 with
Microsoft PowerPivot
OSIsoft vCampus White Paper
How to Contact Us
Email: [email protected]
Web: http://vCampus.osisoft.com > Contact Us
OSIsoft, LLC
777 Davis St., Suite 250
San Leandro, CA 94577 USA
Houston, TX
Johnson City, TN
Mayfield Heights, OH
Phoenix, AZ
Savannah, GA
Seattle, WA
Yardley, PA
Worldwide Offices
OSIsoft Australia Pty Ltd.
Perth, Australia
Auckland, New Zealand
OSIsoft Europe GmbH
Frankfurt am Main, Germany
OSI Software Asia Pte Ltd.
Singapore
OSIsoft Canada ULC
Montreal, Quebec
Calgary, Alberta
OSIsoft, LLC. Shanghai
Shanghai, People’s Republic of China
OSIsoft Japan KK
Tokyo, Japan
OSIsoft Mexico S. De R.L. de C.V.
Mexico City, Mexico
Sales Outlets and Distributors
Brazil
Middle East/North Africa
Republic of South Africa
Russia/Central Asia
South America/Caribbean
Southeast Asia
South Korea
Taiwan
WWW.OSISOFT.COM
OSIsoft, LLC is the owner of the following trademarks and registered trademarks: PI System, PI ProcessBook, Sequencia, Sigmafine, gRecipe, sRecipe, and RLINK. All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Any trademark that appears in this book that is not owned by OSIsoft, LLC is the property of its owner and use herein in no way indicates an endorsement, recommendation, or warranty of such party’s products or any affiliation with such party of any kind.
RESTRICTED RIGHTS LEGEND Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at DFARS 252.227-7013
Unpublished – rights reserved under the copyright laws of the United States.
© 1998-2010 OSIsoft, LLC
ii
TABLE OF CONTENTS
Table of Contents ............................................................................................................................... ii
Overview ........................................................................................................................................... 1
About this Document ............................................................................................................................. 1
About this White Paper .......................................................................................................................... 1
What You Need to Start ......................................................................................................................... 2
PowerPivot Best Practises .................................................................................................................. 3
Creating a PowerPivot Report ............................................................................................................ 4
Connecting PowerPivot to PI AF ............................................................................................................. 4
Creating a Query in PowerPivot ............................................................................................................. 6
Explanation of the PowerPivot Query .................................................................................................... 6
Data Options ........................................................................................................................................... 8
Using Slicers ............................................................................................................................................ 8
Deploying PowerPivot to SharePoint ................................................................................................ 10
Refreshing PowerPivot data in SharePoint and Excel .......................................................................... 10
Conclusion ....................................................................................................................................... 12
Revisions ......................................................................................................................................... 13
1
OVERVIEW
ABOUT THIS DOCUMENT
This document is exclusive to the OSIsoft Virtual Campus (vCampus) and is available on its
online Library, located at http://vCampus.osisoft.com/Library/library.aspx. As such, it is
provided 'as is' and is not supported by OSIsoft's regular Technical Support.
Any question or comment related to this document should be posted in the appropriate
vCampus discussion forum (http://vCampus.osisoft.com/forums) or sent to the vCampus
Team at [email protected].
ABOUT THIS WHITE PAPER
The purpose of this paper is to guide users in creating a PowerPivot model for reports based
on data from the PI System.
Users will find that PowerPivot is an excellent tool for creating and drafting reports from
scratch and providing the freedom to display and format the data in a way that meets their
reporting needs. PowerPivot lets users design queries against different data sources, create
relationships between these data sources, add calculations, and publish their workbooks to
SharePoint. PowerPivot is best suited when working with limited data sets (less than 2GB)
and when trying to conduct what-if analysis to find correlations between data from the PI
System and data from other sources. For more information on whether you should be using
PowerPivot for your reporting needs, refer to the “Introduction to BI” document.
Below is a list of some typical business scenarios of when to use PowerPivot:
When drafting reports
When trying to discover new correlations across data from multiple systems
Investigating opportunities from the operational data stored in their PI Systems
When working with smaller data sets
When you want the flexibility to modify and format the look and feel of the
report
The target audience of this paper is technical clients familiar with Microsoft PowerPivot and OSIsoft’s PI System who wish to extend the platform’s analytical Business Intelligence (BI) capabilities. This guide will focus on the integration points between the PI System, including both the Asset Framework (AF) and PI Server, and the Microsoft BI tools. This is not intended to be a comprehensive guide for creating a PowerPivot model but will include links for more information on PowerPivot development. For a brief overview of the main tables in AF which are useful for BI analysis please refer to the PAG Introduction document. This document contains an overview of the schema and a
2
brief description of each of the main tables. For a deeper and more comprehensive understanding of the PI System, please refer to the PI System user manuals. Recommendations for PI System best practices are also included in the introduction. Through this paper, there are recurring references to PI SQL Commander. PI SQL Commander is a query building and execution tool that is provided with the PI OLEDB Enterprise Provider. It is recommended to use PI SQL Commander to develop and test queries against the PI System in order to save time and ensure issues that arise are not related to the query itself. The approach of this paper is very functional, with topics following the order needed for creating a PowerPivot model. The paper is broken out in five different sections: PowerPivot Best Practises, Implications and Recommendations for PI Best Practices, Creating a PowerPivot Report, Deploying PowerPivot to SharePoint, and Conclusion.
WHAT YOU NEED TO START
The following software requirements are needed:
Microsoft Excel 2010 and Office Shared Features installed on the workstation
PowerPivot for Excel add-in. This is a free add-in that can be downloaded here:
PowerPivot for Microsoft Excel 2010.
Microsoft SharePoint Server 2010 (for deploying PowerPivot reports to
SharePoint)
PI OLEDB Enterprise. This is the OLE DB Provider for accessing PI Asset
Framework (AF)
3
POWERPIVOT BEST PRACTISES
There are a few things to keep in mind when working with PowerPivot in order to make the
best use of the tool.
When developing reports using PowerPivot, follow these general guidelines:
Test and develop your queries using PI SQL Commander. This is a useful tool for
designing your queries, allowing you to view the tables presented by the PI OLEDB
Enterprise provider and troubleshoot the queries before adding them in PowerPivot.
Write clear and concise queries – preferably with comments in them – so that other
users who want to see how or what data is being pulled can easily do so.
Give meaningful names to the columns in your queries. These will be the names that
users will see in the Pivot tables and charts.
Keep in mind the layout of your reports and charts – especially when deploying to
SharePoint. Preferably users should not have to scroll on their browsers to view
information in the reports – especially scrolling horizontally. Also be aware that users
may want to print these reports, so make sure page layouts are fit for printing.
Avoid creating Pivot tables next to each other. Since Pivot tables can expand and
collapse to accommodate the data being browsed, it is possible to run into issues
when expanding one table and overlapping into an adjacent one. In general, try to
keep one report per sheet.
Make use of the PowerPivot Gallery in SharePoint to store your PowerPivot reports.
For information on PowerPivot Gallery click here.
Give your Excel worksheets descriptive names.
Turn off unused visual elements such as Gridlines, Formula Bar, and Headings. These
can be turned off in the Show area of the View ribbon.
Hide Excel worksheets that are empty or that are not relevant to the report.
Limit the use of shapes and objects. Some of these cannot be displayed when
viewing the report through SharePoint.
When the file is opened in SharePoint, the focus is always placed on the last active
cell in the workbook; it is therefore recommended that you select cell A1 prior to
saving and publishing the workbook.
Try to keep the size of the workbooks under 2GB since Excel will begin reaching
memory limitations at this size.
4
CREATING A POWERPIVOT REPORT
CONNECTING POWERPIVOT TO PI AF
The first step in developing a report against the PI System is to create a data source. In
Excel 2010, click on the PowerPivot ribbon and select “PowerPivot Window”. This will launch
the PowerPivot interface.
In the new window click on “From Other Sources” in the “Get External Data” section. This
will launch the Table Import Wizard.
Choose “Others (OLEDB/ODBC)” as the data source and click “Next.” In the Specify a
Connection String section, click on “Build…” to begin creating the connection string. This will
open the Data Link Properties window.
In the “Provider” tab of the Data Link Properties Window, select “PI OLEDB Enterprise
2010.”
In the “Connection” tab, specify the name of the target AF Server in the data source field and
choose to use Windows NT Integrated Security.
5
Important Note: In PowerPivot for Excel, the connection wizard does not properly save the
Integrated Security settings in the connection string. You need to add this part manually by
selecting the “All” tab and entering “SSPI” in the “Integrated Security” field:
After returning to the “Connection” tab, select an initial catalog and click “Test Connection”
to make validate the connection. Click “OK” when finished.
Your connection string should look similar to the one below:
1
2
3
4
5
6
CREATING A QUERY IN POWERPIVOT
Once you have created a connection to the source, the next step in the Table Import Wizard
is to import the data. The way to get data from PIAF is by using SQL queries. Although the
Table Import Wizard gives you the option to select data from a list of tables, this option
gives little functionality and flexibility. To create a query, first specify that you want to write
a query in the Table Import Wizard and then type the query in the following screen.
SQL queries enables the use of the interpolate function to pull data on the time granularity
desired. It also allows more control to select fields for grouping and slicing. The key is
understanding the structure of the tables provided by the PI OLEDB Enterprise provider and
the key fields useful for reporting. The section below explains a comprehensive query which
includes most tables needed for BI reporting. For more information on PI’s SQL Command
Language see the PI OLEDB Enterprise 2010 User Guide.
EXPLANATION OF THE POWERPIVOT QUERY
Below is a sample query that gets data related to ‘Energy KPI – Use per Ton of Feed’ metrics
for the prior 10 days:
-- In this query, we are using the ElementHierarchy as the primary table and joining to a series of tables -- to get details related to the metrics being pulled back SELECT eh.ElementID ,lvl1.name as Location
7
,lvl2.name as Process ,et.Name as EquipmentType ,eh.name as EquipmentName ,ea.Name as MeasurementType ,i.ValueDbl as Measurement --Here we are formatting the Time field from PI to display the Date only ,FORMAT(i.Time, 'MM/dd/yyyy') as Date --Here we are formatting the Time field from PI to display the Time only ,FORMAT(i.Time, 'HH:mm:ss') as Time FROM -- The ElementHierarchy table contains a Parent-child structure with information related to Location, Process, and Equipment name. We join to this table several times to get the values for each of these levels [NuGreen].[Asset].[ElementHierarchy] eh --The ElementAttribute table contains information related to attributes of the pieces of equipment. --Since we are only interested in the 'Energy KPI - Use per Ton of Feed' data, joining to this table will returns the Measurement Type for the given piece of equipment INNER JOIN [NuGreen].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID --The ElementAttributeCategory table is an intermediate table that is used to create a many-to-many --relationship between the ElementAttribute table and the Category table. No data useful for reporting is --contained here INNER JOIN NuGreen.Asset.ElementAttributeCategory eac ON eac.ElementAttributeID = ea.ID --The Category table contains information related to the type of metrics returned from the function tables --We use this table here to filter the results to show only data related to 'Energy KPI - Use per Ton of --Feed'. Other options include 'Real-Time Data' and 'PI Tag Names' INNER JOIN NuGreen.Asset.Category c ON c.ID = eac.CategoryID --The ElementTemplateAttribute table is an intermediate table that is used to create a many-to-many --relationship between the ElementTemplate table and the ElementAttribute table. No data useful for reporting is contained here INNER JOIN NuGreen.Asset.ElementTemplateAttribute eta ON eta.ID = ea.ElementTemplateAttributeID --The ElementTemplate table contains information about the different types of equipment available. --These include values such as 'Boiler', 'Pump' and 'Heater' INNER JOIN NuGreen.Asset.ElementTemplate et ON et.ID = eta.ElementTemplateID --Here we are joining to the ElementHierarchy table again to get the ParentElementID for the 3rd level. --This information is used in the next join to get the Process names INNER JOIN nugreen.asset.elementhierarchy lvl3 ON eh.ParentElementID = lvl3.ElementID --Here we are joining to the ElementHierarchy table to get the name of the process for each record INNER JOIN nugreen.asset.elementhierarchy lvl2 ON lvl3.ParentElementID = lvl2.ElementID --Here we are joining to the ElementHierarchy table to get the Location for each record INNER JOIN nugreen.asset.elementhierarchy lvl1 ON lvl2.ParentElementID = lvl1.ElementID --This query is pulling data from the InterpolateRange function. CROSS APPLY NuGreen.Data.InterpolateRange (ea.ID, N't-10d', N't-1d', N'1h') i WHERE c.Name = 'Energy KPI - Use per Ton of Feed' OPTION(ignore errors, force order)
8
For additional details on each of the tables listed in the query above, as well as other tables
presented by the PI OLEDB Enterprise provider, refer to the PI OLEDB Enterprise 2010 User
Guide.
DATA OPTIONS
PI OLEDB Enterprise provides several different ways of viewing the data from the PI System,
each at a different level of granularity. These include querying against tables – such as
“Snapshot” and “Archive”, as well as calling functions – such as “DataInterpolateRange” and
“DataInterpolateDiscrete”.
DataInterpolateRange is the recommended function to use when creating PowerPivot
reports directly against the PI System. Understanding interpolated data and ranges may
impact how the data is interpreted. Please refer to the “PI OLEDB Enterprise 2010 User
Guide” for more information.
It is important to keep in mind that long running queries may time out when trying to view
data over a long period of time at very low levels of granularity (i.e. hourly data for the
previous 90 days). Users should take into consideration the level of granularity and time
range that they need for their reports when passing parameters to the
DataInterpolateRange function.
USING SLICERS
Once a query has been created and a dataset is available to work with, Pivot tables and
charts can be created. One of the features in PowerPivot is the ability to use “Slicers”. Slicers
are one-click filtering controls that narrow the data shown in a PivotTable or PivotChart and
allow users to interactively filter and analyze data. To view more information about creating
and formatting slicers see articles on Microsoft’s TechNet.
Slicers are added to reports to allow users to filter on specific attributes (also known as
‘dimensions’) from an AF database. For example, a Slicer can filter equipment by
Manufacturer, or another Slicer can be created to filter data on location – such as
“Houston” or “Little Rock”. When creating a Slicer, end-user functionality should be
considered. Slicers can be used to discover interesting findings in the data - such as which
Manufacturers perform better than others, and also allow users to focus on a specific area
of interest in the report. Slicers that contain a large number of categories should be avoided
(i.e. Equipment Name), as this makes it difficult to use and will slow down the performance
of the reports. Slicers that filter the data in ways that are not meaningful for analysis should
also be avoided (such as Table IDs or Descriptions).
Good candidates for Slicers include (but are not limited to): Process, Measurement
Category, Equipment Type, Location and Date.
Below is a report with Slicers on Equipment Type, Measurement Type, Date and Time.
9
10
DEPLOYING POWERPIVOT TO SHAREPOINT
Once created, a report can be shared by deploying it to a SharePoint document library. In
order to deploy the report to SharePoint, make sure that the target site is set up properly.
Verify that PowerPivot for SharePoint has been installed and that users have the proper
SharePoint permissions for publishing or viewing workbooks. For general information on
how to configure PowerPivot for SharePoint see Configuration (PowerPivot for SharePoint).
There are two options for deploying reports to SharePoint:
1. Through the PowerPivot window, click on the icon in the top-left corner, select
Publish and choose the URL for where the report will be deployed.
2. Through Excel, go to File Save & Send, select “Save to SharePoint” and browse to
a location on your site.
REFRESHING POWERPIVOT DATA IN SHAREPOINT AND
EXCEL
One of the advantages of deploying your PowerPivot report to SharePoint is the ability to
create automated schedules to refresh the data in the workbook. These data refreshes are
setup through SharePoint – using unattended accounts – and can be configured to run on a
daily, weekly or monthly basis. For more information on setting up unattended accounts in
SharePoint, see How to: Configure the PowerPivot Unattended Data Refresh Account.
The image below shows the options available when creating a data refresh schedule in
SharePoint:
11
For additional details on saving and deploying to SharePoint, see Save to SharePoint.
To refresh the PowerPivot data through Excel, launch the PowerPivot window and select
Refresh in the Get External Data section of the Home ribbon.
Once this is done, close out of the PowerPivot window and refresh the data in the Excel workbook. Keep in mind that sharing PowerPivot reports via email requires users to have access to the data source in order to refresh the data in the workbook. Users without access to the data source will still be able to view and browse the data as it was saved in the workbook.
12
CONCLUSION
PowerPivot is a useful tool that enables users to leverage data from the PI System by quickly
and easily creating useful reports and performing analyses. With PowerPivot users can
establish a direct connection to the PI System, query the Asset Framework (AF), and begin
developing PivotTables and PivotCharts. Additionally, they can create connections to
external data sources and create relationships between datasets to display correlations, or
to show interesting new findings. The ability to use Slicers in the reports lets users
interactively filter and analyze data. Using Slicers provide users with guidance on filtering
the data and lets them narrow down the results to focus on areas of interest. Finally,
PowerPivot reports can be deployed to SharePoint where they can be configured to be
automatically refreshed and SharePoint’s security can be applied to determine which users
have access to edit or modify the workbooks.
13
REVISIONS
17-Mar-2010 Final draft provided by David Doll, OSIsoft Microsoft Alliance Program Manager
18-Mar-2010 Put into OSIsoft vCampus White Paper Template by Han Yong