TechEd: ‘Creating Self-Service Analytic BI Applications with Microsoft SharePoint 2010’ with Peter Myers

At TechEd today, Peter Myers presented a demo-centric session on Creating Self-Service Analytic BI Applications with Microsoft SharePoint 2010.  Peter began by explaining that BI (Business Intelligence) "is delivered as a suite of components and service applications that deliver business information to drive decisions," and explained that the BI audience consists of: IT pros; power users, analysts, and developers (and Peter said it was this group that was the target audience for today's session); and Information Workers.  Peter then explained that the BI Center is a "one-stop shop for reporting and analytics solutions," going on to say that, "for me, this is a starting point," and that he extends from the BI Center.

Peter explained that, in the interests of time, he had pre-defined a number of data connections for his demos, and began by creating a sales quota page as a new Web Part page.  Showing that the source list had columns for year, country, and quota, Peter edited the Web Part page, choosing to add a Chart Web Part.  Next, Peter clicked to connect the chart to his data source (the aforementioned list), selected sales quotas, and filtered by year, then previewing the results within the chart.  Showing that configuration options include a tooltip, Peter added that feature before also adding a Choice Filter Web Part, defining filters, and filtering by a single year's data.

Peter then discussed the Status List Web Part, but mentioned that he de-emphasizes it in favor of PerformancePoint Services' superior capabilities in this area.

Moving on to the Excel Services Web Part, Peter began his demo by inserting a Pivot Table from the Ribbon, clicking Select data source to choose the desired file (Reseller Sales in this case).  With a look at Pivot Table Field List, Peter selected his fields of interest, mentioning that you can slice by country of reseller and filter by quarter.  Peter then published the workbook to a SharePoint library (File -> Save & Send -> Save to SharePoint) and, in SharePoint, as an end user would, navigated to the site's Excel Reports library.  Peter explained that among the available options are expand/collapse and sort/filter.  Based on their permissions, users may also be able to download, and /or reload/recalculate.  The Excel Web Access Web Part is what makes it possible to expose the data in SharePoint, and you do this by referencing the workbook by its URL in the configuration.  Peter also added a Filter Web Part and SQL Server Analysis Filter Web Part, and configured them both, all of which combined into a robust dashboard page.

In his PerformancePoint Services demo, Peter explained that the associated dev environment is Dashboard Designer.  Using the Scorecard Wizard, he selected a Scorecard Template (Analysis Services), then chose KPIs to import, and the KPI and Scorecard were added to the project.  Peter formatted numbers and defined different levels and thresholds to define the KPI.  In the Scorecard, he then modified Trend metrics so that only the icon indicator (up/down/no change) would be shown, and selected members to complete the definition of the Scorecard.  Right-clicking the list, Peter demonstrated the Analytic Grid, placing sample data, auto-selecting all salespeople, what they sold, and related profit-margin info.  To filter by fiscal hierarchy, Peter selected a Filter Template (Member Selection) with a Tree display since it was a multi-level hierarchy.  Adding an Excel Services report was the final component.  Next, while still in Dashboard Designer, Peter created the layout for a Dashboard page and went on to "wire up the connections as far as what goes where" via the Connection option, of which connections Peter made several.  Adding a second page, for an Excel Services report, the result was a two-page dashboard.  With the deployment process, each Dashboard page resulted in the automatic creation of a corresponding Web Part page in SharePoint.

Moving on to Visio Services, Peter showed a SharePoint list and how its data is added to the Visio drawing, then demonstrated how  to use the Automatically Link command to link rows to shapes within the drawing.  Peter also showed how to create a new Data Graphic before moving on to publishing.  Within the Save to SharePoint process, Peter stressed that "It must be saved as a Web Drawing."  Once published, Peter demonstrated the instant-update capability of changed information within a SharePoint list appearing with the changes reflected within the Visio drawing.

Next up was Reporting Services integration, using SQL Server Report Builder 3.0.  Using the Report Builder Wizard, Peter added a Bing map and an analytical dataset for map-related data.  With his created-in-advance data source hooked up, Peter filtered for U.S. states only, also filtering by time as a report parameter.  He then chose a color theme and visualization, mapped polygon properties, and clicked Run on the Home Ribbon tab to preview the report.  Once integrated with SharePoint, you can easily share the Reporting Services info with others.

For his final demo, Peter addressed PowerPivot integration, which allows end users to create self-service BI reports.  In Reporting Services, Peter specified parameter values by selecting the RSS icon to configure consuming of a report, which Peter then demonstrated the connection of as a data feed via PowerPivot.  After manipulating some of the data presentation, it was off to the SharePoint list itself, and the Export as Data Feed button in the Ribbon.  Peter explained that you can create relationships even though the included data comes from different sources.  Due to time constraints, Peter was unable to complete this final demo, but he did talk us through the final results.


Read our complete coverage of Microsoft TechEd North America 2011:

All SharePoint Versions

The web parts are functional components that extend your SharePoint environment whether it’s hosted, on-premises, or part of Microsoft® Office 365.

SharePoint 2013, 2016, 2019, Online (Office 365)

On-Premises Only

These web parts extend SharePoint beyond its out-of-the-box capabilities by tailoring it to your requirements with Bamboo Solution’s growing portfolio of SharePoint Web Parts.

SharePoint 2013, 2016, 2019


Product Suites

Experience greater power and savings by bundling our SharePoint apps and web parts.

Essentials Suite

Essentials Plus Suite

Bamboo Premier Suite

Project Management Suite

Knowledge Management Suite

External User Manager


For more information on our product suites, contact us.

Featured Services

SharePoint Health Check

A SharePoint Health Check will identify the causes of issues and risks associated with your specific environment, and is custom tailored to provide you with the best recommendations to optimize your SharePoint environment.

SQL Health Check

Document recommendations relating to performance, stability, availability, or a specific focus you request of your SQL Server database instances.

My SharePointXperts

The truth is that each SharePoint skill may not be a full time job for many organizations, and it is nearly impossible for one person to do everything you need – so augment your team with SharePointXperts; providing the skill sets you need when you need them!