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

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

by | May 16, 2011 | SharePoint 2010

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:

Get a Demo
Contact Us
Newsletter Signup