By: Siddharth Mehta | Comments | Related: > SharePoint Configuration
Problem
Microsoft SQL Server Business Intelligence (BI) Professionals are generally well versed with either of these technologies: SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). But with Sharepoint Server continuously growing as a collaboration platform with it's increasing set of Business Intelligence features, MS BI Technology Stack considerably relies on Sharepoint for hosting and/or consuming SQL Server based BI deliverables.
I find many SQL Server BI Professionals confused with the question of what are the Sharepoint based BI features one needs to have in vision to architect an end-to-end BI solution. In this article, we would look at an overview of Sharepoint BI features from the eyes of SQL Server BI Professionals.
Solution
Sharepoint Business Intelligence can be divided into following services from a major classification point of view, considering business intelligence features:
Excel with Powerpivot Addin: From a SQL Server eye, this can been seen like a scaled down version of SSAS. It uses MS Excel as a container, uses an engine called Vertipaq which pivots huge amounts of data, that is generally out of the reach of the Excel engine. Behind the scenes it uses ADOMD.Net to pivot the data. One striking feature to look out for is the DAX (Data Analysis Expressions) library, which can be seen as a library of MDX (Multi Dimensional Expressions) functions as functionally it does the same thing what MDX does in SSAS.
Powerpivot workbooks can be used as data sources to other excel workbooks, and there can be a case where your SSRS reports might need to fetch data from these workbooks. Powerpivot Add-in for sharepoint is the tool that business users would use to work with powerpivot in Sharepoint using Excel as the operating interface.
Visio Services: Using Microsoft Visio one can develop Strategy Maps, which is technically a data driven diagrams. These diagrams are developed using Microsoft Visio and generally hosted in specialized web parts in Sharepoint. These diagrams can fetch data from different relational and analytical data sources that are hosted in SQL Server. These services are vital in the display of Strategy maps on your dashboard, which is accessed using a browser. So this is one important service to keep in view.
Performancepoint Services (PPS): Performancepoint Server 2007 has been discontinued as a product and the same has been welded into Sharepoint as Performancepoint Services. It's one of the most important client or consumer of KPI (Key Performance Indicators) that we develop in SSAS. KPI's can be defined using the Performancepoint Dashboard Designer and it can fetch data from different relational and analytical data sources that are hosted in SQL Server.
The main use of Performancepoint services is to build dashboards that would be used as a MIS (Manangement Information System) by the business leaders of an organization. Any end-to-end BI solution that is built upon a datawarehouse, generally requires a dashboard as it's presentation layer. As it's a consumer of KPI's developed in SSAS, many a times one would require to follow a bottom down approach where KPI's are developed keeping the PPS design in view. This is one of the most important services to look out in Sharepoint Business Intelligence.
Excel Services: This can be seen much like EXcel, but with the advantage of being hosted on a colloboration platform like Sharepoint. Often this is used to develop Excel based reports that consume relational or analytical data from SQL Server and other supported data sources. The analytical reports developed using Excel Services can also be a part of a dashboard developed using Performancepoint services. So from a SQL BI point of view, if these Excel services based reports are consuming data from SSAS, one might want to define the calculated measures and administer the dimension members accordingly. Also when reports developed using Excel services are hosted in a dashboard, it can work in a context sensitive manner as per the selection on scorecard. So this is a very sensitive reporting area if it's used as a part of a dashboard, and if it's used independently by business users, it can be realtively quite simple.
Reporting Services: A SSRS professional would have the idea that SSRS can be deployed in native mode as well as Sharepoint Integrated mode. Sharepoint can host a vast variety of content including SSRS Reports. When SSRS reports are deployed on sharepoint, they can be made available in the document libraries and the same can be hosted using different web parts. SSRS reports are best used at an organization level using Sharepoint as the hosting and collaboration platform. When SSRS reports are hosted in sharepoint, other shared services like Performancepoint services can use it as a part of the dashboard.
Access Services: It has more to do with application development side, but many a times one needs to facilitate data access to IT and/or Business users for purpose like Quality testing, User Acceptance Testing, Data Validation, and others. So though it's not a chunk that is directly related to BI features, but it's one such service that BI features can use as a facilitator. Behind the scenes, access services uses Reporting services. One of the prerequisite for installing access services is the installation of SSRS 2008 R2 Add-in for SharePoint.
I hope this overall high level summary answers the question of which are the important services in Sharepoint from the viewpoint of a SQL Server BI professionals.
Next Steps
- Download and study the Sharepoint Business Intelligence Model diagram from here
- Start getting hands-on experience with the above listed services.
- Check out MSSQLTips.com for great information about Microsoft SQL Server.
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
