Paul kicked off the first session of the STP Africa Tour with a talk about the Business Intelligence (BI) features in SharePoint. Contending that SharePoint doesn't really provide traditional BI, however, Paul explained that, more accurately, he would be addressing SharePoint's data visualization features, along with the available tools and services. Paul's take is that while SharePoint doesn't provide BI per se, it does provide the data visualization tier for traditional BI. Learning what tool is best for a given type of visualization in SharePoint, and being aware of its strengths and weaknesses, is what's really necessary (and not difficult, as Paul said).
SharePoint's visualization components include, but are not limited to: Reporting Services; PowerPivot; PowerView; and PerformancePoint Services; and Excel Services. Also, SQL Server Integration Services (SSIS) and third-party Microsoft Partners provide additional data integration and visualization services.
Paul went on explain that, at the visualization layer, the Insights Site Template is often the starting point. You don't have to start here, however, as you may choose to start with a blank SharePoint site with publishing and reporting turned on, but with the Insights Site Template, everything has already been preconfigured for you.
Out-of-the-box (OOTB) Web Parts can also be used "to put together what some might call a dashboard in SharePoint," Paul said. Functionality is limited with the OOTB Web Parts, but quick visualization is certainly possible using them, and Paul said that this approach can work particularly well for small companies. Web Parts can be configured to point at Excel spreadsheets, SQL databases, and more. As well, Paul pointed out that Web Parts are also connectable to the OOTB Filter Web Part.
Moving on to PerformancePoint Services, Paul said that, if it's turned on, you'll be provided with additional application layers. Dashboards/scoreboards can be quickly created in PerformancePoint Services and, once published, can be viewed in the browser. As with Web Parts, however, robust functionality is often sacrificed for speed and ease of use. One such limitation is the inability to filter data. In summary, Paul recommended that so long as you "do as much as you can at the database level, it'll be blazing fast."
PowerPoint Power View, which is new in SQL Server 2012, was Paul's next topic. "It's a tool to create presentation-ready views of your data," he said, explaining that it's a browser-based Silverlight app. Like PerformancePoint, it's very easy to use. "It's also very data-aware," but again, functionality is limited, and it's mostly intended for use by analysts.
PowerPivot for SharePoint was up next, with Paul explaining that, historically, it's been "cumbersome to configure from an administrative perspective...but it's a lot easier to use in SQL Server 2012." In short, Paul said that it "gives Excel an extra app layer of horsepower." All you need to do is take the PowerPivot sheets and publish them to SharePoint so they can be rendered in the browser. "It's really an extension of Excel," said Paul, and that's a nice thing to have, especially given Excel's continuing popularity and ubiquity. Also, "with 2012, installation is much easier...it's refreshingly easy to configure."
Paul then moved on to SQL Server Integration Services (formerly DTS). Paul explained that the process here involves creating packages, pulling in PeopleSoft data, then transforming the data within SQL Server (a process known as ETL). This is "most often the best option for integrating your LOB data with SharePoint." Making it especially useful, Paul explained, is that this system allows you to create your own reporting database without impacting any other system. Paul cautioned, however, that there isn't currently a native SP adapter to get data directly from SharePoint, though there are free tools available to do this.
Addressing Reporting Services 2012, of which Paul said, "at the end of the day, it's probably my favorite reporting service [because] if I can't do it anywhere else, I can always do it in Reporting Services, in Visual Studio." Reporting Services allows you to create reports using Visual Studio and Report Designer which are managed at the farm and site collection level, and includes Web Parts and SQL Server Data Tools (SSDT).
Paul noted that there is a feature comparison chart available from MSDN which details what tools and Services available for BI/data visualization, and is arranged by SharePoint version.
In deference to his remaining time, Paul touched briefly on the remaining data visualization options, beginning with configuration with SQL Manager. With this, you can connect to a site collection, manage roles, manage jobs, and manage schedules.
Next up was SSRS Report Designer, which Paul explained is a lightweight, browser-capable tool that allows for the creation and modification of reports.
Of Excel Rendering Extension in 2012, Paul said that with SQL Server 2012, open XML can be saved as 2007 and/or 2010 versions (noting that, previously, it could only be saved as 2003).
Wrapping up with existing Excel apps, Paul plainly stated that Excel is "the keystone of data visualization" in SharePoint.
Sharing the Point Africa Tour is made possible through the generous sponsorship of Colligo.
All sessions on the STP Africa Tour:
Sep 09 2012, 12:00 PM
Filed under: Business Intelligence, Paul Swider, PowerPivot, Sharing the Point, data visualization, STPAfrica, SharePoint & SQL 2012, BI, PerformancePoint Services, SQL Manager, Reporting Services 2012, #STPAfrica, SQL Server Integration Services, PowerPoint Power View, Excel Rendering Extension
John Anderson joined Bamboo Solutions as Manager of Content & Syndication in May of 2008 after a 12-year career at AOL. New to SharePoint at the time of his hiring, John was tasked with creating a new blog for the just-launched Bamboo Nation community in which he would document his daily SharePoint learning process. Thus was born the end user-centric SharePoint Blank, for which John authored 200 posts within a year, and which he continues to write today (albeit much more sporadically). Currently serving as Managing Editor, John sets the tone for Bamboo Nation as its lead blogger, and oversees content across Bamboo properties.