Choosing the right business intelligence tool for an organization can be tricky. There are several dashboard and reporting solutions available in SharePoint, and picking the right tool(s) to satisfy an organization’s business intelligence goals isn’t always as cut and dry as some would hope. A good starting point is to identify what the options are, and understand what they offer at a high level. With that in mind, let’s take a look at the four most popular business intelligence reporting solutions in SharePoint, and what they have to offer.
Reports developed with Microsoft SQL Server Reporting Services (SSRS) are highly customizable, and that flexibility makes them a great fit for a variety of scenarios. While most commonly used for “Operational” or “Static” reporting, it also has a wide array of visuals, including charts, gauges, indicators, and maps that also allow it to function very effectively as a dashboard tool.
Integrating Reporting Services with your SharePoint environment allows reports and dashboards to be deployed directly to SharePoint document libraries. Once deployed, reports can be viewed via the Report Viewer Web Parts, or by navigating to them directly. If you need tight control over your report formatting and layout, there’s a good chance Reporting Services will be a good fit for you.
• Great for reporting and dashboards
• High level of control over format/layout
• Can connect to a wide range of data sources
• Report development will most likely require a technical resource
• Highly scalable
Many of us have some familiarity working with data using Excel. The great thing about Excel Services is that now you can use a tool you’re already familiar with (Excel) to create reports that you can then publish in SharePoint. Excel Services takes care of all the rendering of the Excel document inside the browser, so that the person viewing your published Excel report doesn’t even need to have Excel installed on his or her computer.
If you already have reports in Excel, this is an excellent opportunity to drive traffic to SharePoint to view them instead of using a file share or e-mailing them out. Security can also be applied to each workbook, allowing you to only share the specific sections of your workbook you want to be visible. If your organization is just getting started with business intelligence, this is a good place to begin your journey so you can get up and running fast.
• Familiar UI for report development
• Once reports are published, users don’t need to have Excel installed on their computer to view the report
• Outside data connections are supported
• Ability to control which section of your Excel workbook you want to share
On the surface, PowerPivot looks the same as Excel Services. Under the hood, however, there’s a lot more going on. PowerPivot allows you to design a data model. Once that data model is in place, it allows for rapid report development. A PowerPivot data model can potentially touch multiple different systems, and gives the creator of the model the ability to define how the data in those different systems relate to each other. Another big advantage of PowerPivot is the amount of data it can handle. Users of a PowerPivot workbook could potentially be working with millions of rows of data in the background and never even be aware of it from a performance standpoint.
Most important of all is that you don’t have to be a highly technical person to build a PowerPivot data model. PowerPivot is actually intended for power users who are familiar with the company data and want the ability to combine data across their organization into useful reports.
• Familiar UI for report development
• Can connect and relate multiple different systems and types of data sources at once
• Once the workbook is published, users don’t need to have Excel or PowerPivot installed on their computer to view the report in SharePoint
• Can handle millions of rows
• Can be leveraged as a data source in both PerformancePoint and PowerView
PerformancePoint is all about putting together dashboards. Within PerformancePoint, you can create KPIs, scorecards, filters and several different types of reports and charts. Those components can then be used to construct dashboards that are relevant to the end user.
PerformancePoint can also incorporate reports you’ve created in other tools like Excel Services and Reporting Services into its dashboards so that you can leverage those technologies.
PerformancePoint also has some very powerful analytic tools, such as the analytic chart and decomposition tree. These tools allow users to navigate through data in a very dynamic way by enabling them to drill up, down or even across different areas of their data. If you want to empower end users to explore the data that’s driving their decision-making, these are incredibly powerful tools.
• Used for creating high-level dashboards
• Can combine business intelligence collateral from Excel Services or Reporting Services into dashboards
• Extremely interactive visualizations and data navigation
• Requires an analysis services cube, tabular model, or PowerPivot workbook in order to use analytic charts/grids and the decomposition tree
Deciding which business intelligence tool is the best fit for an organization can vary greatly depending on an organization’s in-house technical skillset, the current business intelligence maturity level of the organization, and the end users’ desired functionality, just to list a few. That said, understanding the tools even if only at a high level is a great place to start thinking about business intelligence and where you might want to take it in the future.
Tavis Lovell is a consultant with SharePoint911, a Rackspace company.