Self-Service Business Intelligence with SharePoint 2010

Of the six major capabilities that Microsoft attributes to SharePoint 2010, it is the Insights capability that provides SharePoint's powerfulbusiness intelligence (BI) functionality.

Power users can harness the Insights tools and techniques to create powerful dashboards, without investing in complex and expensive software. With this capability, Microsoft addresses the challenge of self-service BI.

This approach involves setting up an environment in which information workers can create, access, share, and collaborate on specific sets of business reports, queries, and analytics, with minimal IT intervention. The self-service BI experience combines three components of the Microsoft BI stack:

With SharePoint 2010, powers users can do more than create and share dashboards. They can also collaborate on BI documents (typically dashboards), thanks to new SharePoint 2010 communities functions (e.g., tagging) and coauthoring. Moreover, with the new Microsoft FAST Search Server 2010, SharePoint users can find documents based on BI criteria.

SharePoint 2010 plays a vital role by providing status indicators; Chart Web Parts; and PerformancePoint, Visio, and Excel Services. (Note that most SharePoint 2010 BI components require the SharePoint 2010 Enterprise CAL.) SQL Server PowerPivot for SharePoint and SharePoint integration with SSRS also are important.

Status indicators

Status indicators (also known as Key Performance Indicators -- KPIs) show how actual performance for a particular measure compares to a goal. A good status indicator lets you know, without requiring calculations, if results are on or off target. In SharePoint 2010, status indicators can be implemented by creating a SharePoint status list, as Figure 1 shows.

Figure 1: Status list 

Each element of the status list defines one KPI, which can be based on data from a variety of sources: Excel workbook, SSAS, SharePoint list data, or manually entered information. For instance, Figure 2 shows how the Belgium sales KPI is based on the Excel worksheet that Figure 3 shows. This worksheet provides sales data for different countries and is stored in a SharePoint document library. As Figure 2 shows, each worksheet row (e.g., Belgium) is used as a KPI (e.g., Belgium sales). The KPI measurement value is based on the Sales column and the goal is based on the Goal column.

Figure 2: Defining a KPI based on an Excel spreadsheet 

Figure 3: Excel spreadsheet that defines KPIs 

Any modification to the original data in the spreadsheet is reflected in the status list. Each KPI can be displayed individually in a SharePoint page as part of a dashboard, if needed.

Chart Web Parts

Chart Web Parts are out-of-the-box SharePoint 2010 Web Parts that help you to visualize your data by using charts, as Figure 4 shows. Chart Web Parts can pull data from another Web Part, from SharePoint lists, from non-SharePoint data (via Business Connectivity Services), or from Excel Services. The Web Parts provide a huge variety of chart types, as Figure 5 shows.

Figure 4: Using a Chart Web Part 

Figure 5: Chart Web Parts 

These Web Parts are easily customizable, thanks to a ton of properties. Like other SharePoint components, a Chart Web Part can be used as a dashboard element.

PerformancePoint Services

SharePoint 2010 PerformancePoint Services is a powerful tool for creating dashboards to monitor and analyze your business, as illustrated in Figure 6. In the Microsoft Office SharePoint Server 2007 (MOSS), PerformancePoint was a separate tool. Now, it's fully integrated in SharePoint 2010 Server. Indeed, like many SharePoint 2010 services, PerformancePoint is a service application and therefore has a scalable and robust architecture that can be load-balanced and is easy to manage. PerformancePoint also makes use of SharePoint artifacts (i.e., SharePoint list and content types) for its internal workings.

Figure 6: A dashboard created with PerformancePoint 

Creating a PerformancePoint dashboard can be easy when you use the rich client Dashboard Designer tool, a ClickOnce application that can be installed safely from a SharePoint web site. Every object that you create in Dashboard Designer is stored in SharePoint lists. It is a common practice (but not required) to Creating a SharePoint web site based on the SharePoint Business Intelligence Center site template, which automatically creates the necessarily PerformancePoint lists, is a common (though not required) practice.

The first type of PerformancePoint object -- called a Data Connections object, as illustrated in Figure 7 -- is the connection to the data source from which we want to grab data. This kind of object is stored in a document library. Data can be fetched from a multidimensional data source (e.g., SSAS), or from a tabular data source (e.g., Excel workbook, SQL Server table, SharePoint list). Using a multidimensional data source is the only way to get some kind of drillthrough functionality but requires more specialized skills, since a SSAS Cube must be created and managed.

Figure 7: PerformancePoint Data Connection objects 

The second type of object -- PerformancePoint Content objects -- comes in six varieties, as Figure 8 shows:

Figure 8: PerformancePoint Content objects 

Keep in mind that a scorecard is a set of KPIs and that a dashboard can be made of reports, scorecards, filters, and SharePoint pages.

Suppose we want to create the scorecard that Figure 9 shows. This scorecard indicates whether certain vendors achieved their sales goals. To create this scorecard, we typically must first define a KPI object, as Figure 10 shows.

Figure 9: Creating a scorecard

Figure 10: Defining a KPI object 

When the KPI object has been defined, we then need to create a Scorecard object that's based on this KPI, as Figure 11 shows. To publish the scorecard, we must create a Dashboard object, as Figure 12 shows. We can then deploy the dashboard to SharePoint, and the salescard shows up as illustrated in Figure 9.

Figure 11: Defining a Scorecard object

Figure 12: Creating a Dashboard object 

We can add other objects to the dashboard, such as a Filter object that provides a list of fiscal years on which to filter our scorecard. Figure 13 shows this type of object (see the red arrows).

Figure 13: Creating a Filter object 

Another popular object is the Report object, as Figure 14 shows. By using this object, you can place a report (SalesReport) in the dashboard and connect it to the filter, as Figure 15 shows. When deployed to SharePoint, the dashboard looks like the one shown in Figure 16. Note that right-clicking the report in the dashboard provides numerous options, as Figure 17 shows.

Figure 14: Creating a Report object

Figure 15: Adding the report object to the dashboard 

For instance, by choosing Report Type, Bart Chart, you get the chart that Figure 18 shows.

Figure 18: Scorecard on the left, report object on the right (Bart Chart mode) 

This kind of chart allows a deeper analysis. For instance, if you right-click one of the vendor bars, you can display the Decomposition Tree (see Figure 19), which allows you to analyze your data through several axes (i.e., dimensions) of the Cube. The Decomposition Tree is available only when the data source is based on a SSAS Cube.

Figure 19: Decomposition tree 

SSRS 2008

SSRS 2008 is not a SharePoint technology, but it plays quite well with SharePoint. Developers can create reports in Microsoft Visual Studio, or power users can create reports in Report Builder 3.0; these reports can be published to a SharePoint Reports Library, as Figure 20 shows.

Figure 20: SSRS objects published to a SharePoint document library

Figure 21: SSRS report displayed in SharePoint 2010 

In the Report Library, users can click a report and display the report in a separate window (as Figure 21 shows), print or export the report to a different format (e.g., PDF, Excel, Microsoft Word), or even subscribe to reports. Reports can also be displayed within a SharePoint Web Part.

Visio Services

Visio Services is SharePoint service application that let users publish and share Visio diagrams in a pure web format. Visio Services can play a BI role by providing some meaning to raw data. Indeed, shapes in a Visio diagram can be bound to data from different data sources. For instance, the Visio diagram in Figure 22 illustrates a set of flight slots in an airport. This diagram can be bound to ODBC, a SharePoint list, Microsoft Access, Excel, or SQL Server data.

Figure 22: Visio 2010 diagram 

When the shapes are bound to data, we get the diagram that Figure 23 shows, which can be published to and shared in SharePoint. When the diagram is shared in SharePoint, users can still interact with the different shapes and visualize the associated data, as Figure 24 shows.

Figure 23: Visio 2010 diagram with shapes bound to data

Figure 24: Visio 2010 diagram published to SharePoint 

Excel Services

Excel Services is another SharePoint service application that plays a crucial rule in the SharePoint BI story. You can use Excel 2010 and Excel Services, along with the Excel Web Access Web Part, to create browser-based dashboards. PerformancePoint Services can also use Excel Services workbooks as a data source.

It is important to consider that Excel 2010 (a rich client) is a powerful BI tool that can be leveraged in SharePoint 2010 to achieve the "one version of the truth" principle. Figure 25 illustrates an example of a pure Excel 2010 spreadsheet (based on a pivot table) that can be used as a dashboard and shared in SharePoint.

Slicers are a new Excel 2010 filtering technology that can be linked to pivot tables and leveraged in SharePoint 2010 when a workbook is published. The Ship Province slicers in Figure 25 are an example.

Figure 25: Excel workbook published to SharePoint and used as a dashboard 

Figure 26 illustrates other interesting BI tools, such as sparklines, which show trend values (as shown in the Cost Trend column), and KPIs (as shown in the Stock Level column). You can create KPIs in Excel 2010 by adding formatting rules.

Figure 26: Sparklines and KPIs in Excel 2010 

PowerPivot

PowerPivot is a SQL Server technology that allows huge data manipulation in Excel 2010 and SharePoint 2010. Starting in Excel 2007, the size of the grid expanded from 65,000 rows to more than one million rows. However, in BI we often need to manipulate huge amount of data from different data sources, and the Excel cannot traditionally handle that type of demand. That's what PowerPivot is all about. PowerPivot is available as an add-in for Excel 2010 or SharePoint 2010.

The Excel add-in allows the design and creation of an in-memory database, in which in-memory tables created from many data sources can be linked together in a relational way. PowerPivot, using its in-memory engine and efficient compression algorithms, can process even huge data sets extremely quickly: You can process millions of rows with about the same performance as a few hundred rows would demand.

Using PowerPivot, you can leverage your Excel skills to create more powerful pivot tables. When your Excel workbook (using a PowerPivot source) is ready, you can deploy it to SharePoint 2010, just as you would a typical workbook.

When installed on the server, PowerPivot for SharePoint 2010 can be managed as a service application. Workbooks can be deployed to any SharePoint document library. However, a new kind of library -- called a PowerPivot Gallery -- is designed to highlight your workbook and let people use it for slicing and dicing in the browser, without downloading or opening the workbook in Excel. Figure 27 shows a PowerPivot Gallery.

Figure 27: Workbook published to a PowerPivot Gallery 

A new Excel like language, called Data Analysis Expressions (DAX -- similar to Multidimensional Expressions -- MDX), allows the manipulation of PowerPivot data. DAX formulas differ from Excel formulas in that DAX functions work with tables and columns, not ranges.

PowerPivot has also been improved by supporting

New SharePoint BI features with SQL Server 2012

The integration of SSRS 2012 with SharePoint 2010 has been improved. SSRS is now provided as a SharePoint service application, resulting in better performance, better scalability (through load balancing), and support for claim-based authentication.

A new alerting capability, Self-Service Alerting, allows users to set up alert rules, to be alerted when report data changes that match those rules occur. This alerting service can be leveraged only in SharePoint 2010 Enterprise.

A new tool, Power View (formerly code-named Crescent), allows users to easily create and design complex and interactive reports. Power View is a Microsoft Silverlight application that runs in the browser and works against a new unified model call the BI Semantic Model (BISM). However, in its first release, Power View won't be able to read a multidimensional data source -- something that PerformancePoint does very well.

SharePoint is a powerful BI platform

Clearly, SharePoint 2010 is a key platform that follows the self-service BI philosophy that Microsoft promotes. End users can leverage their creativity and the tools that they like to perform web development without writing a single line of code -- or relying on IT! With self-service BI, users no longer need to wait days, weeks, or months for a report, only to discover that a key functionality is missing or no longer relevant. Giving business people the tools to get prompt answers when required can have a big impact on the quality of decision-making within an organization.

Nevertheless, we should not underestimate the effort that's required to have a data discipline, à la extraction, transformation, and loading. ETL is the set of processes by which data is extracted from numerous databases, applications, and systems; transformed as appropriate; and loaded into target systems, including (but not limited to) data warehouses, data marts, and analytical applications. In many situations, BI teams will still need to provide Cubes if we want to perform multidimensional analysis, as is the case with the PerformancePoint Decomposition Tree.