SharePoint Server 2010 Business Intelligence Platform Share Point 2010

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
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:

  • Excel-based status indicator This indicator is used for retrieving values from an Excel workbook hosted using Excel services. The indicator can refer either to a cell address such as Sheet1!$A$1 or a named range such as MyIndicator.
  • Fixed Value–based status indicator The Fixed Value indicator has been designed to be manually updated. However, it’s particularly useful to us as developers, because its value can be manipulated programmatically as the following code sample shows:
  • SharePoint list–based status indicator While a fixed value indicator makes use of a single value stored within SharePoint, the SharePoint list–based indicator makes use of all items in a SharePoint list. The value of the indicator can be either the number of the items in the list or the percentage of the items where a particular condition is met or even a calculation based on the values of particular fields in the list. This indicator is especially useful for showing metrics for data that is created and managed within SharePoint.
  • SQL Server Analysis Service–based status indicator As you’ve seen, most BI data is processed using OLAP. The SQL Server Analysis Services indicator allows you to display an indicator easily from an OLAP cube.

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:

SharePoint Server 2010 Business Intelligence Platform

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:

Indicator Details Web Part

Indicator Details Web Part

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:

Status List Web Part

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.

PerformancePoint Services

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:

  1. Enable the PerformancePoint Services Site Collection Features at the Site Collection level. From Site Settings, choose Go To Top Level Site Settings | Site Collection Features.
  2. Enable the PerformancePoint Services Site Features feature in Site Settings | Manage Site Features.
  3. Add a new list based on the PerformancePoint Content List type, as shown:

Using PerformancePoint Within a Site


  1. Add a new data connections library based on the DataConnections Library for PerformancePoint type.
  2. 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.

  3. Add a new library based on the Dashboards library type.

Dashboard Designer

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:

Dashboard Designer

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:

  1. In Dashboard Designer, select the Data Connections folder that we created earlier when we enabled PerformancePoint functionality for our site.
  2. Select the Create tab on the ribbon, and then click the Data Sources button in the Dashboard Items section.
  3. From the Select a Data Source Template dialog, click Analysis Services, as shown, and then click OK:
  4. Performance Point Data Connections

  5. In the Properties tab, set the name of the new connection to AdventureWorksOLAP. Then in the Editor tab’s Connection Settings section, enter the name of the Analysis Services instance that hosts the AdventureWorks OLAP sample cube. Select the Adventure Works DW 2008 SE database and the Adventure Works Cube.

connection strings

The three options in the Data Source Settings section warrant some explanation:

  • Unattended Service Account This generic account is used by thePerformancePoint Service application to access data sources where no specific credentials are provided. In practice, the unattended service account works in a similar fashion to the unattended account discussed in Chapter on Excel Services. The Unattended Service Account is configured via Central Administration and can be found in the PerformancePoint Service Application Settings section of the PerformancePoint Service Application management page. This can be accessed by choosing Central Administration | Manage Service Applications | PerformancePoint Service Application | PerformancePoint Service Application Settings | Secure Store And Unattended Service Account. To use the Unattended Service Account, the Secure Store Service must also be properly configured.
  • Unattended Service Account and Add Authenticated User Name In Connection String For some data sources, the username is useful for providing a personalizedview of the data. By selecting this option, the data source is still accessed usingthe unattended service account credentials, but the username of the requestinguser is also passed to the data source via the CustomData connection stringproperty. Custom Data is an Analysis Services–specific connection string propertyand can contain any string value. The value of the property can then be pickedup within the OLAP project by using the CustomData() MultidimensionalExpressions (MDX) language function.
  • Per-user Identity By selecting this option, the identity of the requesting user is used to access the data source. This option offers a more granular approach to security but does so at the expense of having to manage requesting user access directly on the data source.
  1. Now that we have some understanding of the options available, we’ll use the default value of Unattended Service Account. As mentioned earlier, in order for this to work the account must be appropriately configured in Central Administration.
  2. Click Test Data Source to confirm connectivity. Once connectivity has been confirmed, click the Save icon to persist the changes.

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:

Performance Point Content

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:

  1. From the Dashboard Items section, click Dashboard to add a new dashboard to the workspace.
  2. From the Select a Dashboard Page Template dialog, select the 2 Columns template.
  3. Type the name of the dashboard as MySampleDashboard.
  4. In the Series box, click the Product Categories drop-down. When the Select
  5. Members dialog appears, uncheck the Default Member (All products) checkbox, and then expand the All Products branch and select Accessories, Bikes, Clothing, and Components.
  6. You can see in the editor that three sections are shown. The top section allows you to add additional pages to the dashboard, and the bottom section represents the dashboard content and contains two columns, as shown next, because we selected the 2 Columns template. We can add content to the Dashboard Content section by dropping the appropriate item from the Details section on the right side of the page. Of course, before we do this, we’ll need to create some content to add!
  7. Create a Dashboard

  8. From the Reports section of the ribbon, click Analytic Chart. In the Select a Data Source step of the wizard, select the AdventureWorksOLAP data source and then click Finish.
  9. Type the name of the new report as MyChartReport. You’ll see the Analytic Chart designer interface. To create a chart, you can drag measures, dimensions, or named sets from the Details section on the right side of the page onto the design interface.
  10. Drag the Product dimension into the Series section and the Date dimension into the Bottom Axis section.
  11. Rather than displaying our data using the default bar chart, change the layout to Pie Chart by right-clicking anywhere on the chart and selecting Report Type | Pie Chart.
  12. Save the finished report, and then switch back to MySampleDashboard.
  13. Drag the finished report onto the dashboard. Under the Reports heading in the Details pane, drag MyChartReport onto the left column of the dashboard.The next thing we need to add is some content for the right column. This time, we’ll use an Analytic Grid report to allow users to examine the details that make up the chart.
  1. As before, click the Analytic Grid icon in the Reports section of the ribbon. Select the Adventure Works OLAP data source, and then name the report MyGridReport.
  2. This time we want to show a bit more detail. Drag the Product dimension into the Rows section, the Geography dimension into the Columns section, and the Date dimension into the Background section. Save the completed report, and then add it to the right column of the dashboard.
  3. Now that our sample dashboard is populated, we can publish it to SharePoint to see the finished result. Right-click MySampleDashboard, and then select Deploy to SharePoint.

Once the dashboard has been deployed, it will automatically be opened in a new browser window and will look similar to this:

Create  a Dashboard

Time Intelligence

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.

  1. Switch back to Dashboard Designer. Time intelligence must first be configured on the data connection before it can be used. Double-click the AdventureWorksOLAP connection, and then switch to the Time pane.
  2. Our sample cube has various dimensions that we can use for time intelligence. For the purposes of this demonstration, select Date.Date.Calendar from the Time Dimension drop-down.
  3. With the dimension defined, we can set a reference member. All we’re doing here is selecting a specific value from our chosen time dimension and then specifying to what actual value it corresponds. Click the Browse button, and then select a single day from the Date.Date.Calendar hierarchy.
  4. In the Hierarchy Level drop-down, select Day and then enter the corresponding date in the Reference Date text box, as shown next:
  5. reference data mapping

    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.

  6. In the Time Member Associations section, select the appropriate Time Aggregation values, as shown:
  7. time member associations

  8. Save the changes to the AdventureWorksOLAP data connection.

We can now make use of our time intelligence functionality to create a filter for our sample dashboard.

  1. Switch to MySampleDashboard, and then click Filter from the Dashboard Items section of the Create ribbon.
  2. In the Select a Filter Template dialog, select Time Intelligence, as shown:
  3. select a filter template

  4. Click Add Data Source, and then select AdventureWorksOLAP.
  5. Add formulae as shown in the following illustration. This step warrants a bit of further explanation. Time, of course, is always changing. By tomorrow, today will have become yesterday. To accommodate this constant change, values shown in the time selector are calculated using simple formulas that take the current date into account. You’ll see this in action by creating a selector that shows how to select from one of the previous four quarters.
  6. create a filter

    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.

  7. Click Next, and then select List as the Display Method. Click Finish to create the new filter. Type the name MyTimeFilter.
  8. Switch back to the MySampleDashboard item and drag the new filter from the Details pane onto the left column.
  9. We can hook the filter up to our reports by dragging the AdventureWorksOLAP field onto the Drop field to create a connections section of the appropriate report. In the Connection dialog, accept the default values of Connect To: Date Calendarand Source Value: AdventureWorksOLAP.
  10. Do this for both reports, and then deploy the updated dashboard to SharePoint.

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.

Decomposition Tree

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:

Decomposition Tree

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

  1. PowerPivot data is created using Excel 2010, as mentioned. Open the Excel application and then, from the PowerPivot tab, select PowerPivot Window from the ribbon, as shown here:
  2. Power Pivot Excel Add-In

    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.

  3. From the Home tab, select From Database in the Get External Data section of the ribbon, and then select From SQL Server.
  4. Configure the connection to connect to the AdventureWorks sample database, and then select the Product and ProductInventory tables, as shown:
  5. table import wizard

  6. Data from the selected tables will be imported into PowerPivot and will be displayed as data grids within individual tabs. As part of the import process, PowerPivot automatically creates relationships between the two tables. We can check that these relationships are correct by clicking the Table tab and then clicking the Manage Relationships button from the ribbon.
  7. TIP Creating relationships within PowerPivot is an important feature. You can import data from a variety of sources and create relationships between tables from different sources.

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.

  1. In the Product table, select the Column tab, and then click the Add Column button.
  2. In the formula bar, enter the following DAX expression:
    =SUMX(RELATEDTABLE('ProductInventory'),
    'ProductInventory'[Quantity])*'Product'[StandardCost]
  3. Right-click the CalculatedColumn1 header, and then select Rename Column. Change the column name to InventoryCost.
  4. To make use of this data in Excel, switch back to the Home tab and then select PivotTable | Single PivotTable from the ribbon.

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:

Data Analysis Expressions (DAX)

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.

  1. You publish PowerPivot workbooks in exactly the same way you publish other Excel content to Excel Services. Select the File tab to enter the backstage area, and then select Share.
  2. Click Publish To Excel Services, and then navigate to the PowerPivot gallery that we created earlier. If everything is set up properly, the workbook will be uploaded to the library. Navigating to the PowerPivot gallery will show details of the workbook together with previews of each page:

PowerPivot for SharePoint

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.

  1. In Dashboard Designer, create a new data source by selecting Data Source from the Create tab.
  2. In the Select a Data Source Template dialog, select Analysis Services.
  3. In the Connection Settings section, set the Server to the URL for our sample PowerPivot workbook, and then choose the default Database and Cube values from the respective drop-downs.
  4. New data source 2

    NOTE : I’ve used localhost as the server name for illustrative purposes. Enter the actual name of your server to prevent security errors.

  5. Using this new data connection, we can now create dashboards and reports in the same way we did earlier when addressing the AdventureWorksOLAP data source.

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

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:

  1. Start the SQL Server Business Intelligence Studio application, and then create a new project using the Report Server Project Wizard template, as shown next:
  2. Creating a Reporting Services Report

  3. We’ll make use of the AdventureWorks sample database for this report. In the Report Wizard dialog, click Next to move to the Select the Data Source step. Create a new data source and name it AdventureWorks. Set the connection string to point to the AdventureWorks sample database.
  4. Click the Make This A Shared Data Source checkbox. By making the data source shared, it will be published as a separate data source item in SharePoint; this will allow it to be reused by other reports. The alternative to a shared data source is an embedded data source, where the details are embedded within the report.
  5. Either click the Query Builder button to create the following query or manually enter it into the Query string text box:
  6. Create a Tabular report grouped by Title with FirstName, LastName, and Phone in the Detail section, as shown here:
  7. Design the table

  8. Click Finish and then name the report Telephone Directory.
  9. Before we can publish the new report to Share Point, we need to let the project know where to store the various components, such as the data connection and the report definition. To set these options, choose Project | MySampleReport Properties.
  10. In the Deployment section, specify appropriate values for each target folder and the Target Server URL, as shown next. Notice that the folders must be fully qualified URLs and the Target Server URL must be the URL to the SharePoint site where the reports will be deployed.
  11. my sample report property pages

  12. To deploy the report to SharePoint, choose Build | Deploy MySampleReport. If everything is properly configured, the report and its associated data connection will be uploaded to the configured SharePoint document library.
  13. To view the report, simply click the item in the document library. Creating a report and publishing it to SharePoint is no more difficult than creating a Word document or any other content. Since the report is stored within SharePoint, it is automatically subject to the same security and information management policies as other content.

Report Builder

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:

telephone directory

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.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Share Point 2010 Topics