An Overview of Microsoft PerformancePoint 2013 in SharePoint 2013

Posted by EPC Group on May.07, 2015 3:36 pm

Microsoft PerformancePoint 2013 and 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.

PerformancePoint 2013 Consulting Services Sharepoint Office 365 Power BI

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 SharePointOffice 365Infrastructure 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.”

Developing a Scalabile Information Architecture for SharePoint Office 365 Consulting 5

Topics: Business Intelligence (BI) Business Intelligence Center PerformancePoint 2013 Power BI PowerPivot PowerView SharePoint 2013

A Business Intelligence (BI) Deep-dive into SharePoint 2013

Posted by EPC Group on May.08, 2015 10:56 am

The Business Intelligence Capabilities of SharePoint 2013

Business Intelligence (BI) capabilities in SharePoint 2013 are vast and in many cases are achieved with a combination of SharePoint and Microsoft Office or SharePoint and SQL Server’s capabilities. I have worked with a variety of organizations in presenting SharePoint 2013‘s BI capabilities and have found that it is best done by showing examples rather than speaking in abstract so I have included a larger number of screen shots in this section with brief summaries as I believe this is the best way to get a complete grasp of SharePoint 2013 and Business Intelligence (BI).

The following image details SharePoint’s different tiers of Business Intelligence which include SharePoint, Office and in many cases Excel, as well as SQL Server:

Business Intelligence Capabilities of SharePoint 2013 EPC Group 1

Reviewing the Business Intelligence Center in SharePoint 2013

SharePoint 2013 provides a Business Intelligence Center site that enables your organization to centrally store and manage data connections, reports, scorecards, dashboards, Apps and Web Part pages as shown below:

Business Intelligence Capabilities of SharePoint 2013 EPC Group 2

 

Microsoft Excel 2013 & SharePoint 2013

Excel has more capabilities and more relevance in BI and SharePoint than any other Microsoft Office program. The following details the wide range of powerful reports, scorecards, and dashboards that can be accomplished with a combination of Excel and SharePoint 2013:

Business Intelligence Capabilities of SharePoint 2013 EPC Group 3

Excel Services & SharePoint 2013

Excel Services allows for manual emailing or sharing of spreadsheets that causes manual errors to stop and the ability to share workbooks with others.

The following is an overview of Excel Services 2013 and SharePoint 2013:

  • Excel Services 2013 is a SharePoint service application that enables the loading, calculation, and browser-based rendering of Excel workbooks (Enterprise Edition only)
  • Designed to be a scalable, robust, enterprise-class service that provides feature and calculation fidelity with Excel
  • Delivers rich analysis capabilities that are supported in a zero-footprint web client with a familiar interface
  • Live connections to data sources are queried to deliver up-to-date data throughout analysis

The following is a graphical example of the moving pieces of SharePoint 2013 and how they come together at a very high level:

Business Intelligence Capabilities of SharePoint 2013 EPC Group 4

PowerPivot & SharePoint 2013

PowerPivot is an add-in for Excel User that provides the ability to create powerful data models that can include calculated fields, reports and scorecards.

The following is a summary of PowerPivot and SharePoint 2013:

  • PowerPivot for SharePoint extends SharePoint 2013 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks published to SharePoint
  • The PowerPivot Gallery, a special type of document library, is provided to browse published PowerPivot workbooks and to configure automatic data refresh
  • The PowerPivot Service deploys the embedded data model to an Analysis Service instance within the SharePoint farm, and Excel Services is used to query the deployed data model
  • Data connections can be defined to query the PowerPivot data model by using the document URL

PerformancePoint Services & SharePoint 2013

PerformancePoint is one of the most used BI components within SharePoint 2013 and it primarily used to create and share centrally managed dashboards. In many cases PerformancePoint reports are design in a manner to be updated at any time and modified to be viewed in a variety of manners such as reports or even KPIs and scorecards.

The following image shows a PerformancePoint report as well as the web part \ features available to filter the data to provide a specific report or to drill-down to find specific and more granular data and underlying metrics:

Business Intelligence Capabilities of SharePoint 2013 EPC Group 5

PerformancePoint 2013 and the related site and capabilities comes with Dashboard Designer natively within the site’s ribbon. This integration has been a welcomed new feature to many users I have spoken with as they feel its more user friendly and easier to user. The following is a screen shot of a SharePoint 2013 site with the Dashboard Designer shown in the ribbon:

Business Intelligence Capabilities of SharePoint 2013 EPC Group 6

Visio Services & SharePoint 2013

Visio Services in 2013 comes with a list of new capabilities in this latest release and is a SharePoint service application that enables the loading and browser-based rendering of Visio diagrams. The following are the major list of features and capabilities of Visio Services in SharePoint 2013:

  • Designed to be a scalable, robust, enterprise-class service that provides feature and rendering fidelity with Visio
  • Delivers rich browsing and navigation capabilities that are supported in a zero-footprint Web client
  • Live connections to data sources are queried to deliver up-to-date data
  • Renders diagrams in the browser
  • High quality PNG(s) for full fidelity
  • No dependence on Silverlight
  • Native support for Visio file format
  • Diagram consumers do not need a Visio client
  • Available across devices using desktop browsers and mobile browsers
  • Users interact with content
  • Pan, Zoom, Hyperlinks, View Shape Data
  • Accessible only via SharePoint
  • On-premises & in cloud using Office 365

The following graphic was created by Visio Services to be stored within a SharePoint page that users can view and is updated on a weekly basis:

Business Intelligence Capabilities of SharePoint 2013 EPC Group 7

Self-Service Business Intelligence (BI) Architectural Overview

Microsoft has introduce a huge push toward “self-service” BI (SSBI) and providing the user with tools that allow them to run reports and perform BI actions as needed at any time without the need for IT’s intervention.

The goal of SSBI is to empower analysts so that they can design, customize and maintain their own BI solutions but it is important to note that SSBI is not meant to be a complete solution or a replacement for corporate BI initiatives and standardized reports offered by the organization.

The following is a diagram of the entire “technology stack” around SSBI:

Business Intelligence Capabilities of SharePoint 2013 EPC Group 8

Excel PowerView

Power View sheets can be added to the workbook to enable an interactive data exploration, visualization, and presentation experience. They allow for a highly visual design experience as well as for rich meta-driven interaction. These are also “presentation-ready” at all times.

Power View reports can be based on an embedded PowerPivot data model, or an external tabular data model that can be optimized to fully exploit the capabilities of the Power View experience.

Business Intelligence “From the Consulting Trenches”

EPC Group will continue this business intelligence (BI) series of blog posts in the weeks to come to touch on the real-world “from the consulting trenches” approach that EPC Group has successfully implemented for hundreds of organizations throughout North America.

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.”

Developing a Scalabile Information Architecture for SharePoint Office 365 Consulting 5

Topics: Business Intelligence (BI) Business Intelligence Center PerformancePoint 2013 PerformancePoint Services Power BI PowerPivot PowerView SharePoint 2013