Now that you know how SharePoint fits into the bigger picture, let’s look at the features and tools that are available to us when developing BI solutions.
Excel Services were discussed , where its uses within the BI domain were mentioned. One of the things about Excel is that it’s commonly used to hold a lot of vital management information within an organization. In an ideal world, every useful piece of data would be found in a data warehouse and would be accessed via OLAP or some other reporting mechanism. In the real world, that just isn’t the case. Despite the best efforts of BI professionals the world over, business users simply find Excel to be easy to use and an essential tool for performing their day-to-day jobs. In the real world, some vital data will always live in Excel.
Rather than chasing some data warehousing utopia, a more effective approach is to allow users to continue to use Excel. By taking advantage of the features offered by Excel Services, data managed and stored in Excel workbooks can play an important part in an organization’s BI strategy. All the tools available within SharePoint can make use of Excel data as readily as OLAP data or relational data from SQL Server. As well as making effective use of data stored within Excel workbooks, you’ve seen how the Excel application forms a key part of the Microsoft BI solution. The analytical and display capabilities coupled with a general familiarity with the product represent an unbeatable combination that should always form a core part of any BI strategy. By allowing workbooks to be hosted in Excel services and used via a web browser, SharePoint extends the reach of the powerful features of Excel beyond the desktop.
Business Intelligence Web Parts
SharePoint provides a few basic BI web parts out of the box. As a platform that’s often used for creating intranet sites and other organizational portals, the web parts allow users to include important performance metrics within portal pages easily. Along with web parts that are used to display information, SharePoint also defines a number of content types specifically for storing BI data. Based on the Common Indication Columns content type, these additional content types all describe business metrics that can be displayed using the built-in web parts. The indicator types available out of the box include the following:
NOTE:At the time of writing, on SharePoint 2010 Beta 2, it’s not possible to create a custom list with the facilities to add new indicators. Although the appropriate content types can be added to the list, they’re not displayed in the New Item menu and therefore can’t be created. To resolve this problem, enable the SharePoint Server Enterprise Site Features option within Site Settings, and then create an indicator list using the Status List template, as shown next:
Several built-in web parts are available out of the box and are discussed in the following sections.
Indicator Details Web Part
The Indicator Details web part can be used to display details of a single indicator value. As you’ve seen, all indicator values are derived from the Common Indicator Columns content type, and each of these types can be appropriately rendered using the Indicator Details web part.
The following screenshots show the configuration settings and rendered output of an indicator derived from an Excel workbook:
Status List Web Part
The Status List web part works in a similar fashion to the Indicator Details web part, except the Status List web part shows all indicators from a specific SharePoint list. Indicators derive from a common content type, and by creating a list based on this content type, we can store a number of different indicators in a single location. The Status List web part renders each indicator appropriately based on its underlying source. The Status List web part is useful for displaying a range of important metrics on an intranet page or other portal site, as shown here:
Chart Web Part
The Chart web part is a useful new addition in SharePoint 2010. Data can be retrieved from Lists, Business Connectivity Services, or Excel Services. Alternatively, the chart control can be connected to another web part, which can act as a data source for the control.
The web parts that are available out of the box are great for adding basic BI functionality to portal sites or other SharePoint applications. However, when it comes to building a dedicated BPM portal, the real jewel in the SharePoint 2010 crown is PerformancePoint Services, with features that allow developers and BI professionals to create highly interactive BPM portals. Ultimately, PerformancePoint solutions are delivered as a series of SharePoint web part pages and can therefore be completely integrated into a larger portal solution in the same way as any other SharePoint content. Although the pages consist of a collection of PerformancePoint-specific web parts, a rich client editing experience is provided via the Dashboard Designer, which is accessed as a ClickOnce-deployed application from any PerformancePoint-enabled site.
Using PerformancePoint Within a Site
To use PerformancePoint within a site, you must create a few specific lists and libraries. In much the same way as the Status Indicator web parts, PerformancePoint content is based on a few custom content types. Data based on these content types is then stored in specific lists and libraries, which are referenced in turn by the custom web parts. To add PerformancePoint functionality to an existing site, take the following steps:
NOTE:The PerformancePoint data connection library uses a different template to the standard data connection library that is generally used by Office applications. Be sure to select the correct type when creating the library. The difference between the two is that the standard data connection library can contain only Office Data Connection files as used by Excel or Universal Data Connection files as used by InfoPath, whereas the PerformancePoint library can also include the PerformancePoint Data Source content type. Bearing this in mind, you can convert an existing data connection library to support PerformancePoint simply by adding the PerformancePoint Data Source content type.
To start using the PerformancePoint Dashboard Designer, navigate to the PerformancePoint content list and then add a new item. The Dashboard Designer will start automatically, as shown here:
The Workspace Browser performs a similar function to the Solution Explorer in Visual Studio: it allows you to see all the items that are available for use within the project. One important difference here, however, is that two views are available for each of the items listed in the Workspace Browser: the SharePoint view, which lists all the items in the associated SharePoint list, and the Workspace view, which lists all the items in use in the current workspace. An item from SharePoint can be added to the current workspace by double-clicking it. One thing to bear in mind about the Dashboard Designer is that workspace files can be saved to the file system. You can open these files using the Open command in the Dashboard Designer or by double-clicking the filename to view the workspace, which will be automatically bound to the appropriate SharePoint lists. However, it’s also possible to use the file to export a workspace to another site. Rather than opening the file directly, you can click the Import Items button in the Home menu of the Dashboard Designer application to allow artifacts from the workspace file to be imported and automatically added to a new SharePoint site.
Performance Point Data Connections
In much the same way as Office Data Connections (ODCs) are created and managed via the Excel client application and Universal Data Connection (UDCX) connections are managed via InfoPath, PerformancePoint connections are created and managed using the Dashboard Designer application A number of different types of data sources can be used, including Analysis Services, Excel Services, SharePoint lists, and SQL Server tables. To provide the highest degree of interactivity, an Analysis Services–based data source is the preferred option. However, as you’ll see later, PowerPivot offers a new way for knowledge workers to create in-memory Analysis Services cubes easily using an add-in for Excel client. When PowerPivot integration is configured, SharePoint makes use of Analysis Services in SQL Server 2008 R2 to host these user-generated cubes; as a result, they are usable by PerformancePoint in the same way as cubes generated using the more traditional data warehousing method.
To demonstrate how to use the Dashboard Designer, consider the AdventureWorks sample database available from MSFTDBProdSamples. In addition to the sample databases, we’ll also need to deploy the OLAP sample project that can be found at C: Program FilesMicrosoft SQL Server 100 Tools Samples Adventure Works 2008 Analysis Services Project standard after the Adventure Works sample project has been Installed. For full details of how to set up the sample databases, please see the instructions that are available from the CodePlex site.
To create a connection to an OLAP data source, take the following steps:
The three options in the Data Source Settings section warrant some explanation:
Although we’ve used only one data connection for this demonstration, you can use any number of data connections within a PerformancePoint workspace. For example, if our application captured data using a survey in SharePoint 2010, we could easily include the results of the survey in our dashboard by creating a connection to the appropriate SharePoint list. As you saw when creating our Analysis Services connection, available options include Excel Services, SharePoint lists, and SQL Server tables.
PerformancePoint Content: With a connection set up and ready to go, we can move on and take a look at creating our first dashboard using PerformancePoint. Before jumping into this, however, you’ll find it worthwhile to develop an understanding of how everything hangs together behind the scenes. Select the PerformancePoint Content folder that we created earlier, and then click the Create tab in the ribbon. This time, you’ll see a much wider range of options, as shown next:
Dashboards are essentially a collection of web pages. As mentioned earlier, PerformancePoint dashboards are made up of web parts, which are discussed in the following sections.
PerformancePoint Filter When the Filter item is added to a dashboard from the Dashboard Items section of the ribbon, behind the scenes the PerformancePoint Filter web part is added to the underlying page. As you’ll see, the Filter control can be used to filter other elements on the page.
PerformancePoint Report The PerformancePoint Report web part does most of the heavy lifting within a dashboard. Whenever a report is added to a dashboard, behind thescenes a PerformancePoint Report web part is added to the underlying page. This means that each of the options available from the Reports section of the ribbon are implemented using a PerformancePoint Report web part.
PerformancePoint Scorecard Scorecards are implemented using a specific web part. Scorecards work in a similar fashion to the Status List web part discussed earlier. The major difference is that PerformancePoint scorecards are managed by the PerformancePoint Service Application, as opposed to being rendered from a SharePoint list.
PerformancePoint Stack Selector The PerformancePoint stack selector web part is used to provide a navigation mechanism between the dashboard pages. When creating a dashboard, the Stack Selector is added automatically.
Create a Dashboard
Now that you know how PerformancePoint weaves its magic, let’s create a simple dashboard:
Once the dashboard has been deployed, it will automatically be opened in a new browser window and will look similar to this:
Before we look at some of the advanced functionality of the PerformancePoint Report web part, let’s take a look at another important aspect of most BI solutions: time intelligence. Practically every dataset has some aspect of time involved, and more often than not, data is analyzed over specific time periods. To show how this works in PerformancePoint, we’ll add a filter to our report that will allow us to select a specific time period and will automatically update our reports accordingly.
PerformancePoint has its own time aggregation levels that need to be mapped to the corresponding levels in our dimension hierarchy. In our case, this is pretty much a one-to-one mapping.
We can now make use of our time intelligence functionality to create a filter for our sample dashboard.
NOTE We’re subtracting 15 from the current quarter because the AdventureWorks data set contains data from 2001 through to 2006, so subtracting 15 quarters will ensure that we’ve got something to see in our report. Depending on the version of the sample data that you’re using, you may need to adjust the formulae accordingly.
Our dashboard now contains a drop-down selector that we can use to select the date range for our reports. Notice that when we select Year, a chart is drawn for each quarter in the year rather than a single chart covering the entire period. This happens because we defined year as a series of quarters rather than a single time period, and our chart is bound to use a time period as one of its axis.
Although we specified which dimensions and measures were to be used on our reports together with the layout and other options, all of this stuff can be changed dynamically by the user so that he or she can further analyze the data being represented. In reality, when we create a dashboard, we’re simply defining the starting point for further analysis by the user. When a user right-clicks any PerformancePoint Report web part, a context menu appears and presents an array of options, such as drill up/drill down and measure selection. Covering the entire range of options available is outside the scope of this chapter. However, of these options, one of the new additions in SharePoint 2010 is the Decomposition Tree. The Decomposition Tree is a Silverlight control that allows users to drill down visually into data using any of the dimensions and measures that are available within the cube. Clicking each data item presents a list of dimensions that can be used to expand the dataset as well as relevant properties and rollup information on the current selection. The best way to understand the Decomposition Tree is to mess around with it. To show the control, right-click any data element—for example, right-click a pie chart slice or a number on the grid report, and then select Decomposition Tree from the context menu. The control will be shown as follows:
We’ve looked at PerformancePoint and how it can be used to build powerful business process management solutions using the SharePoint platform. Such solutions are excellent for providing day-to-day management information or other business information, for whichthe requirements can be easily defined and implemented. However, one of the common stumbling blocks in large-scale BI projects is that too often the project becomes a victim of its own success. A project may start off with a range of commonly used line-of-business reports, but over time, more and more requests for additional reports or changes to existing reports can overwhelm available development resources. When this happens, users who are unable or unwilling to wait until a suitable resource is available often resort to cobbling together their own solutions using Excel, Access, or some other tool.
Each homegrown solution is a step farther away from the BI mantra of one version of the truth, and over time, a lot of the good work done on the project is lost. Thankfully, it doesn’t have to be this way. The solution to this problem is to empower users to create their own ad hoc reports using a series of common data sources. Historically, solutions to this problem have focused on the front end, presentation layer of report generation.
Tools such as Reporting Services include a report builder with which users can create reports from published data sources. Although this approach does go a long way toward reducing the burden on development resources, the presentation layer is probably the least time-consuming aspect of report generation. Where the hard work comes in is at the data warehousing and OLAP layers, and tools such as Report Builder don’t provide any assistance here.With SQL Server 2008 R2, Microsoft includes a new product known as PowerPivot.
PivotTables are a well-known and widely used feature of Excel, especially when it comes to analyzing business data. However, PivotTables have their limitations, and one of the most significant with respect to this discussion is in the selection of data sources. PivotTables can either make use of data within a workbook or they can be connected to a predefined data source. You’ve seen that a fair bit of work is involved in predefining data sources, especially when attempting to meet specific reporting requirements. PowerPivot addresses these issues by allowing users to create their own data sources from a mash-up of existing sources.
Additionally, PowerPivot lets users work with much larger datasets that would normally be possible using PivotTables. In effect, PowerPivot is a user-driven OLAP tool. It allows users to create in-memory OLAP cubes and uses those cubes within Excel in the same way as external data sources.
Now, the implications of that are pretty significant, but when coupled with the fact that the resulting Excel workbooks can then be hosted using Excel services and accessed as OLAP data sources in their own right, you can see that PowerPivot truly opens the door to collaborative BI solutions.
PowerPivot Excel Add-In
Users can create PowerPivot data sources using an add-in for Excel 2010 that can be downloaded .Let’s work through an example to see how theadd-in works. As earlier, we’ll make use of the AdventureWorks sample databases.
PowerPivot can import data from a variety of sources, including traditional sources such as SQL Server and other database systems, as well as other sources such as Reporting Services reports, ATOM feeds, and other PowerPivot workbooks. For the purposes of this demonstration, we’ll use the AdventureWorks database running on SQL Server.
Data Analysis Expressions (DAX)
In the Manage Relationships dialog, you can see that the ProductInventory table is related to the Product table using ProductId. This relationship was picked up from the underlying database. We’ll make use of this relationship to illustrate the use of the new DAX language. DAX uses a syntax that’s similar to Excel formulae. The main difference is that DAX functions generally operate on multiple rows of data. In our example, our function summarizes quantity values from a related table.
We can now create a PivotTable in the usual manner by adding columns to the appropriate sections. Notice that our calculated InventoryCost column appears in the list and can be used in the same way as other columns. We can drag the InventoryCost column into the Values section of the Gemini Task Panel to create a new summary value named Sum of InventoryCost, as shown here:
PowerPivot for SharePoint
In addition to the Excel add-in that allows users to create and use PowerPivot enabled workbooks, another feature of SQL Server 2008 R2 provides SharePoint integration for PowerPivot. This allows PowerPivot-enabled workbooks to be hosted by Excel services in the same way as regular workbooks. Instead of the in-memory version of Analysis Services that’s used when accessing a Power Pivot -enabled workbook via the Excel client, when SharePoint integration is configured, PowerPivot cubes are hosted on-demand by Analysis Services.
Making use of the simple PowerPivot workbook that we created earlier, we can publish the workbook to SharePoint. Before we do this, we need to create a PowerPivot Gallery document library to contain our workbook.
Now that we’ve published our PowerPivot data source, we can make use of it as a data source anywhere that can utilize Analysis Services data. Let’s see this in action by creating a PerformancePoint dashboard based on our PowerPivot workbook.
NOTE : I’ve used localhost as the server name for illustrative purposes. Enter the actual name of your server to prevent security errors.
The real power of PowerPivot on SharePoint is that a workbook published to Excel Services can be treated in the same way as a regular OLAP cube. Instead of development resources being tied up designing and developing every cube using Business Intelligence Studio, you can now build simple cubes using Excel and PowerPivot. Since the data is easily accessible, it can be integrated into a wider BI solution with relatively little effort.
As you’ve seen, for example, PowerPivot data can be used to generate the same dynamic PerformancePoint reports as OLAP data hosted using Analysis Services.
Reporting Services are generally used in conjunction with SQL Server and Analysis Services to generate all manner of reports. A standard installation of Reporting Services will provide a web-based portal where users can browse through a collection of published reports. As an alternative to a stand-alone portal, Reporting Services can also be installed in SharePoint integration mode. As the name suggests, this mode provides a much higher degree of integration with SharePoint. All report data, rather than being stored in a dedicated Reporting Services database and presented via a stand-alone portal, is now stored directly within SharePoint lists and libraries.
Creating a Reporting Services Report
Let’s take a look at creating a report using Reporting Services and publishing it to a SharePoint document library. Before Reporting Services can be used with SharePoint, you need to install and configure the Reporting Services add-in for SQL Server 2008 R2. The add-in can be downloaded. aspx?FamilyID=16bb10f9-3acc-4551-bacc bdd266da1d45&displaylang=en. After you’ve installed the add-in, do the following:
After a report has been published to SharePoint, it can be modified by users who have the appropriate permissions by using the Report Builder tool. Like the PerformancePoint Dashboard Designer, the Report Builder is a click-once application that can be accessed directly from within SharePoint. To open a report using the Report Builder, select Edit in Report Builder from the context menu, as shown here:
Reporting Services Web Part
When a user clicks a report in a SharePoint library, the report is automatically rendered on the page. It should come as no surprise to learn that this is done using a Reporting Services–specific web part. However, having such a web part available presents the opportunity to integrate Reporting Services content with other content on pages within a SharePoint application. For us developers, being able to amalgamate Reporting Services content with application-generated content greatly increases the flexibility of our applications. When it comes to displaying or printing application data, offloading the task to Reporting Services provides a whole host of functionality that would be very time-consuming to build from scratch.
Share Point 2010 Related Interview Questions
|Web Services Interview Questions||XML Interview Questions|
|Share Point 2010 Interview Questions||ASP.NET Interview Questions|
|Share Point Administration Interview Questions||BizTalk Admin Interview Questions|
|Microsoft Office SharePoint Server (MOSS) Interview Questions||Biztalk Server Interview Questions|
|Asp Dot Net Mvc 4 Interview Questions||Biztalk Esb Toolkit Interview Questions|
|InfoPath Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.