onsdag 13 juni 2012

Business Intelligence in SharePoint

The BI offering in SharePoint is not related to a single specific technology or product. SharePoint provides a wide array of offerings that can help customers solve a variety of problems. SharePoint therefor presents us with questions that are hard to answer when it comes to BI. For instance: which of the seven ways of creating KPI’s in SharePoint should a customer use? This article will give a brief introduction to the different kinds of BI SharePoint can provide.
Bi overview

The purpose of BI is to provide the business with metrics that can be used for analysis and steering. KPI’s allow a business to measure pre-defined metrics, such a s sales for a region or the percentage of staff utilization at a given point in time. It’s a way of highlighting what is happening so that future corrective action can be undertaken by the business.

Business Intelligences role in the organization can be mapped to the steering process. Let’s exemplify this by thinking about how a business can answer these questions:

1. What has happened?

2. What is happening?

3. Why is this happening?

4. What will happen?

5. What do we want to happen?

Questions 1 and 2 are plain monitoring activities, business intelligence can support this process by providing an efficient and trustworthy visualization of data. To answer question 3 we need to perform an analysis, this is done by selecting a subset of data that can be used to explain the selected phenomenon. Now that we have established the past, present and the phenomenon driving this trend we can focus on what we want to happen in the future.

All these activities rely heavily on getting the correct data in the most efficient way possible. Normally data is stored in an unstructured way; for instance in accident-reports or ERP systems.

BI transforms this raw data into something that is meaningful for humans and allows managing of data. In the SharePoint context we can simplify this to:

BI provides the tools that enable individuals, teams and enterprises to discover and analyze information for:

- Decision making

- Analysis

- Collaboration

- Action

The case for SharePoint as a BI tool is that it enables us to create and manage information in an integrated system that includes core business productivity features such as collaboration, search and content management. What we really gain from SharePoint is a BI tool is insight into data.

Microsoft’s likes to stress that SharePoint empowers people by allowing them to access BI data through a familiar interface while working with the tools that they already know how to use, like Excel and Visio.

SharePoint in the BI context

SharePoint can be used with SQL Server reporting and BI tools to surface BI data in ways that are meaningful. A back-end system such as SQL Server Reporting Services provides the data infrastructure that allows report authors and business users trusted, scalable and secure data.

Users create BI assets in SharePoint with tools like PowerPivot, Excel, Visio, Report Builder and PerformancePoint Dashboard designer which are stored securely in SharePoint lists and libraries.

An example of how a BI solution can be leveraged is described below:

1. SCOM is used to collect state and performance counter data from managed server.

2. Collects data from the manages servers, pre-aggregates and stores in tables designed to support reporting requirements

3. Small subset is transformed and loaded into BI Framework database. Contains star schemas for the Analysis Services OLAP cubes

4. Cubes are built and processed

5. Data from cubes are used to populate PerformancePoint Server scorecards, dashboards and reports

6. Scorecards, dashboards and reports are made available to the users through SharePoint. Refreshed when new data is available in the cubes

Different users and self-service BI

The casual users of BI in SharePoint are information users, they don’t use BI daily and are mostly interested in having data presented to them that someone else have already set up and published in a central location, like a BI designer. Think of this user as the normal 9-5 employee that opens the intranet in the morning, gets a quick overview of the whole companies performance down to their own department.

To meet this scenario using SQL Server Reporting Services running as a separate solution or integrated in SharePoint is a good solution.

Information consumers tend to explore the data more than information users. But they do not have the required skills to query the database themselves.

To meet this scenario use interactive reports that have parameters that can be set by the user to filter and sort data. These reports also include a way to dig deeper into the data, either by displaying them in the same report or opening a separate report. SQL Server Reporting Services can facilitate this need. We can think if this user as perhaps a staff manager that only wants to tweak the data slightly to see how performance is related to his/her leadership role.

Power users need maximum interaction possibilities with the data. This includes building their own queries or even generating reports for other groups of users. To facilitate this Report Builder or Excel 2010 with PowerPivot should be used. PowerPivot is best suited for scenarios where multiple data-sources are used. Now this user has a higher need of seeing larger amounts of data from several different angles, often he/she is the only or one of few that needs this data to do their job, perhaps it is a controller from the finance department or a buyer that uses numbers to get the best deals? Now this user can create his/her own ad-hoc BI solutions, using for instance a large excel sheet as the datasource.

For users that needs highly summarized and processed data it’s recommended to use Reporting Services or dashboards/scorecards in SharePoint Server 2010 or PerformancePoint services.

The point is that when we think of BI we usually think of centrally configured solutions that are set up and used. But BI in SharePoint enables so much more, as long as we think about who the user is and what they are really trying to achieve. The market for these kinds of solutions is huge and growing every day. It feels nice that we are on board and starting to deliver value to our customers together with the BI team in this area.