An Overview of Microsoft PerformancePoint 2013 in SharePoint 2013

PerformancePoint is one of the most used Business Intelligence components within SharePoint 2013, and it is primarily used to create and share centrally managed dashboards. In many cases, PerformancePoint reports are designed in a manner to be updated at any time and modified to be viewed in various ways, such as reports or even KPIs and score cards.

PerformancePoint reports as well as the web part and related features available will enable you to filter data to provide a specific report or to drill down to find specific and more granular data and underlying metrics.

PerformancePoint 2013, and the related site and capabilities, comes with Dashboard Designer natively within the site’s ribbon. This integration has been a welcome new feature to many users I have spoken with because they feel it’s more user-friendly and easier to user.

PerformancePoint 2013 and SharePoint 2013 introduce some new user interface (UI) enhancements as well as new server-side enhancements. The user interface enhancements in PerformancePoint 2013 include the following:

• Theme support, which enables you to change the look and feel of core PerformancePoint functionality.
• iPad interface, which means, among other things, that iPad-using executives can access your PerformancePoint dashboards using this highly interactive form factor, including the ability to use touch intuitively and effectively.

The server-side enhancements in PerformancePoint 2013 include these:

• PerformancePoint now provides the capabilities to migrate dashboards that have been successfully created and tested in a development environment over to the production environment. PerformancePoint finally fits into first-class software development life cycle (SDLC) models with this functionality as in prior versions, dashboard creators had to re-create and publish their dashboards again from development to production once they were created, tested and approved.
• You are now able to bundle a dashboard just like any other SharePoint artifact into a single file and hand that off to an administrator or a relevant team member for deployment.
• PerformancePoint has improved integration with SQL Server Analysis Services (SSS) by supporting its concept of an “effective user.” If your organization is leveraging SSAS effective-user functionality, SharePoint 2013 supports it natively with a simple configuration step.

Business Connectivity Services can now connect diagrams directly to external data, opening a wealth of opportunities for user-driven data displays. Because SharePoint can provide and control access to key data, administrators can easily share data sources through the SharePoint interface.

Functionality in the Dashboard Designer tool, as shown in the image below, has been improved, and it enables users to define data sources and regions on a page that hold such artifacts as filters, grids, charts, and graphs. With the push of a button, it creates web part pages and connected web parts that represent the dashboard.

Database Connections and External Data Sources

Although SharePoint is a very agile piece of software that is capable of holding just about any type of data you can throw at it, most organizations are going to store data in various disparate systems. This means that you will, at some point, have to connect SharePoint data with information stored in an external data source.

As an example, you may have inventory information stored in an Oracle database and sales information stored in a SQL Server database. Your salespeople will surely want to know if you have product available to sell, but with the information stored in two different systems, they would need to look at two different applications to find out whether you have product in stock. With SharePoint’s capability to display data from external data sources, you can display information from both systems on a single page and make the salespeople’s jobs much easier.

Of course, you are not limited to Oracle and SQL Server when creating external data sources. We will look at how to use each of the external data source connections available to you in SharePoint 2013, including these:

• Database
• SOAP Service
• REST Service
• XML file
• Linked data sources

Depending on the type of database that is being utilized, there are a number of different variables and methods for connecting to an external database.

Database Types and Connections

Database Type                                                                               Connection Type

Microsoft SQL Server (2000–2012)                    Microsoft .NET Framework Data Provider for SQL Server

OLE-DB Compatible                                               Microsoft .NET Framework Data Provider for OLE DB

Oracle                                                                         Microsoft .NET Framework Data Provider for Oracle

EPC Group Tip: You will notice that ODBC-compatible data sources are not listed here. This is because data views do not support connections to ODBC data sources and should not be used, even though you may see them as an available connection type.

Connect by Using a Custom Connection String

A connection string is used to provide any information that a data provider might need to connect to a data source, such as the username, password, or database name. To connect by using a custom connection string, perform the following steps:

1. Open SharePoint Designer 2013 and select Data Sources in the navigation pane.
2. On the Data Sources tab of the ribbon, in the New group, click the Database Connection button.
3. On the Source tab of the Data Source Properties dialog box, click the Configure Database Connection button.
4. In the Configure Database Connection dialog box, check the Use Custom Connection String check box and click the Edit button.
5. In the Provider Name drop-down box, select Microsoft .NET Data Framework Provider for Microsoft SQL Server.
6. Enter your connection string in the Connection String box. It should look something like this: Data Source=ServerName ;Initial Catalog= Database ;User Id= username ;Password= password .
7. Click the OK button to complete the connection string.
8. You are presented with a dialog box warning you that the username and password you entered will be stored as plain text in the data connection. Click OK to continue.
9. In the Database list, select AdventureWorks2012 (or whichever version of AdventureWorks you may be using). At this point, you have the following options:
10. Click the Select a Table or View radio button and then select the table or view you want to query from the list; then click Finish.

This option creates a default query that selects all records in the table or view. After you click Finish, you can modify the default query by clicking Fields, Filter, and Sort in the Data Source Properties dialog box.

1. Click the Specify Custom Select, Update, Insert, and Delete Commands Using SQL or Stored Procedures radio button, and then click Finish.

Note: This option is available only if your administrator has turned on the Enable Update Query Support option in SharePoint Central Administration.

You can create custom SQL commands by using this option. When you click Finish, the Edit Custom SQL Commands dialog box opens.

1. For our purposes, click the Select a Table or View radio button; then select Address from the list of available tables and click Finish.
2. In the Data Source Properties dialog box, click the General tab and type TestDBConnCustom as the name for the data source; then click OK.
3. The new database connection now appears in the Data Sources list.

EPC Group’s Nationally Recognized Practice Areas

EPC Group leading SharePoint, Office 365, Infrastructure Design and Business Intelligence Practice areas continue to lead the way in providing our clients with the most up-to-date and relevant information that is tailored to their individual business and functional needs.

Additional “From the Consulting Trenches” strategies and methodologies are covered in EPC Group’s new book, “SharePoint 2013 Field Guide: Advice from the Consulting Trenches” covering not only SharePoint 2013, Office 365 and SharePoint Online but Information Management, ECM\RM and overall compliance strategies in this ever changing world of “Hybrid IT.”