SharePoint Tip of the Month

April 2011: Which Microsoft BI Tool is Right For Us?

In trying to define a business intelligence strategy for their organizations, IT leaders often find themselves navigating through a sea of tools. We’re often asked the question, “Which tool is right for us?” SQL Server Reporting Services. SQL Server Analysis Services. PerformancePoint. Excel. PowerPivot. Others? As one recent client learned when we worked with them on an Enterprise Reporting solution, the answer is typically that more than one of the tools are right, as each one serves a different purpose.

Our recent client presented a few fundamental challenges to us:

• How can we execute reports faster? Some of our existing reports take upwards of an hour to execute, and our users are frustrated.
• How can we provide a greater degree of self-service for our user community? We have a small IT department, and the business keeps asking for more and newer report formats.
• How can we best disseminate report data to the organization?
• What’s the right tool to meet these objectives?

For this client, a service-oriented company, the solution to their reporting needs was found in a multitude of the tools within the Microsoft BI stack.

• In order to execute the reports faster, SQL Server Analysis Services was incorporated. Existing transactional databases contained the underlying data, with a lot of complex joins bridging multiple tables. The architecture of the SSAS data cubes reduced the time it took reports to be generated from as much as an hour to less than 30 seconds for even the most complex reports.
• SQL Server Reporting Services played a role in report delivery. SQL Reporting Services provided a web interface with pre-defined reporting layouts. The interface provided users with a pre-determined set of filters – such as Office, Customer, Start Date and End Date – from which they could refine their report data. It also provided them with a set of standard layouts for the report output. This was a great tool for the users who wanted to execute the same general report month after month.
• Other users needed more flexibility in their reporting. If they observed a significant change in data with a particular Customer, then they might have the need to analyze in greater depth. These types of users have very analytical mindsets and possess very good understandings of the underlying data involved. For such users, Microsoft Excel proved to be the perfect tool. Excel pivot tables, by connecting to the main SSAS data cubes, provided the ability to start with an initial format, but quickly add-in or remove other fields in order to further refine the data. The flexibility with the data format helped fulfill the self-service needs when the demands proved to be too great for the IT department to keep pace.
• The long-term vision for this customer is to also incorporate PerformancePoint into their reporting delivery model. The customer has a strong vested interest in a SharePoint portal, including dedicated sites and pages for their offices, clients and projects. Office sites will incorporate dashboards displaying high-level office revenue, while project sites will include dashboards to allow project managers to track project billings against the project budget. It will also help provide a web interface to allow analysts to interact with the reports and refine the data in real time.

The Microsoft product set provides a number of tools to help fulfill the needs of end users. Backend systems like SQL Server, SQL Analysis Services, and SQL Integration Services provide the repositories for large scale data storage. SQL Server Reporting Services provides an approach to delivering on structured report formats. And tools such as Excel, PerformancePoint and PowerPivot bring the report design closer to the end user. Because of the different roles that these tools play, it’s easy to see how each one could conceivably have a place in a company’s overall business intelligence strategy.

This month’s tip contributed By Ben Nadler, Abel Solutions Senior SharePoint Consultant.