Chapter 30. Architecture to Support SharePoint BI

In this chapter, we discuss what is needed in order for IT professionals (“ITPros”) to properly support the SharePoint BI framework. We make some assumptions about the ITPro’s level of knowledge regarding SharePoint. We will not walk through the out-of-the-box implementation of SharePoint in this chapter, but instead will focus on the implementation that has been described in this book.

The following software is needed in order to implement what we cover in this book:

Warning

All of these licenses can be obtained for development under the umbrella of an MSDN Premium license.

You may wonder why we are not doing a walkthrough of implementing SharePoint out of the box. Instead, we chose to focus on a business intelligence solution and in doing so, assume that SharePoint is already a part of your current strategy. These next sections are targeted at the SharePoint ITPros who will support the environment that will contain the BI stack. If you are looking for a general implementation guide on setting up SharePoint, please see many of the other wonderful O’Reilly/Microsoft Press publications that cover that topic.

While Microsoft has put massive amounts of effort into the BI solutions that we cover, one of the areas that can be very confusing is figuring out what is required to actually make it all work. The implementation can be brittle at times, but given the correct support and infrastructure understanding, supporting the BI functionality of SharePoint is no different from any other area of the product. We will endeavor to guide you on the road to success over the next few chapters.

SharePoint Architecture with SQL 2012 BI

In this section, we will cover the architecture components required to support SharePoint with SQL 2012 BI features including:

The first thing to understand in the SharePoint BI story is that the rest of SharePoint doesn’t matter. That isn’t to say it isn’t important. It is critical to the SharePoint infrastructure. What doesn’t matter is the underlying architecture of the rest of SharePoint, specifically. When we begin to look at SharePoint BI architecture, the first thing that is understood is that SQL Server 2012 is required. This scares ITPros because the first thing that pops into our heads is “Now I have to go perform a complete upgrade to my SharePoint database environment to support this new functionality.” This is a false assumption.

Happily, we can leave our SharePoint database infrastructure completely alone when implementing SharePoint BI. The SQL Server 2012 implementation is specifically NOT the database component. We will be implementing SQL Server 2012 Analysis Services and Reporting Services on the application tier and web tier, never touching the database tier. Only the required Report Server databases will even come in contact with the database infrastructure. When we implement SharePoint Integrated Mode reports, they will live in the Content Database, but will exist there as data only. All of the work will be done at the application tier.

The one piece of configuration that is important to the BI story inside of the SharePoint stack is the authentication method. In SharePoint 2010, the supported authentication method for the BI components is Classic Mode Authentication (Windows Auth), while in SharePoint 2013, the supported authentication method is Claims Based Authentication. PowerPivot automated data refresh will not function properly if the incorrect authentication model is backing the SharePoint farm.

As a part of the new Reporting Services Integrated Mode Service Application, three databases are created automatically on the SQL Server that is backing the SharePoint implementation.

These three databases are:

When deploying SQL 2012 Reporting Services as a Farm Admin via Central Administration, the proper permissions are automatically granted to these databases.

In order to properly configure the SharePoint Farm for SQL 2012 Reporting Services, the Reporting Services add-in for SharePoint Products must be installed on all servers running the Web Role that house SharePoint web applications that will run Power View. This includes the server that houses Central Administration. If this add-in is not installed, Power View will not light up and other Reporting Services capabilities will be incomplete.

SQL 2012 Reporting Services also leverages the SETUSER() T-SQL functionality that allows users to pass their credentials to external systems without encountering the dreaded double hop issues. Kerberos is not required for this, however if you are dealing with multiple hops already, it is not a bad idea to have Kerberos implemented in your SharePoint environment, though it is outside the scope of this book. More on this topic is available here.

Excel Services is the engine that will drive much of the SharePoint BI functionality that we describe in this book.

The three core components to Excel Services are:

Additional functionality is provided to Excel Services via:

One key thing to keep in mind with Excel Services is that in order to support the SharePoint BI functionality, Excel Services should only be running on servers that have the SQL Server 2012 components installed on them. Errors will occur if you attempt to run Excel Services on a server that does not contain the installation bits and this will cause a troubleshooting nightmare.

PowerPivot and Power View rely on the installation and configuration of SQL Server PowerPivot for SharePoint and Reporting Services for SharePoint from the SQL Server 2012 install media. Additionally, PowerPivot requires Excel Services, Secure Store Service, Claims to Windows Token Service, and a SharePoint web application. Power View requires that PowerPivot be operational and that the Reporting Services add-in for SharePoint Products is installed on all web servers housing SharePoint web applications that will run Power View.

SharePoint 2013 Changes

While the implementation for this book is done using SharePoint 2010, it is important to know where Microsoft is taking the product in the next release. There are major architectural changes coming and it is important to know what they are and how the solution in this book can handle those changes. The new SharePoint app model has been discussed in previous sections, so we will focus on the infrastructure changes here.

One of the wonderful capabilities of SharePoint for enterprises has been the ability to share services across farm boundaries. Sadly, until now the BI components such as Excel Services, PerformancePoint Services, PowerPivot for SharePoint, and Power View must be deployed in each farm. Part of the reason for this architectural strategy is that the BI functionality can quickly run out of control if not monitored closely, and having multiple farms utilizing a single instance running in parallel with other SharePoint functionality can easily cripple the host farm.

In SharePoint 2013, PowerPivot becomes an out-of-the-box functionality and a local implementation of Analysis Services is no longer required for interacting with a PowerPivot workbook. Analysis Services can now be run outside of the SharePoint farm and be pointed to by multiple farms. This lowers the total cost of ownership (TCO) to run PowerPivot because a local implementation, and thereby SQL BI or Enterprise license, of SQL Analysis Services on each App Server is not needed. Additionally, multiple Analysis Services servers can be registered in Excel Services to allow for failover.

As a result of this, SQL Analysis Services are no longer a direct part of the SharePoint farm and can once again become the domain of SQL Server professionals rather than SharePoint ITPros.

There are a few caveats to running SharePoint BI in this manner:

The newest and most welcomed changes to the BI stack for SharePoint 2013 are in the SSAS arena. Microsoft has implemented a new function known as EffectiveUserName to allow the BI Semantic Model to be enhanced with the ability to pass credentials seamlessly. From the end user’s browser, to the service application, to the tabular or OLAP cube on the back-end, the end user’s credential can be passed without loss of integrity through the implementation of EffectiveUserName.

This new feature requires that the context that is running the service application have read access to the cube or cubes. Excel Services and PerformancePoint Services both have native settings that assist in the configuration of this new feature. More details about how to set this up can be found here.

Part of the overall strategy for SharePoint 2013 BI is to make the BI functionality easier to deploy for SharePoint IT Pros. Figure 30-1 is a representation of what Microsoft is referring to as the “BI Light-Up Story.”

As you can see from , Excel Services comes as an out-of-the-box feature that gives you full data exploration interactivity in the browser and the ability to access workbooks as a data source from inside the farm. Next, the core BI feature set requires that a SQL Analysis Services server be installed in the environment and registered with Excel Services, which will allow you to migrate a PowerPivot model from workbook to an Analysis Services tabular mode cube. To enable Power View, the Reporting Services add-in is required on the web servers in the SharePoint farm. Lastly, the PowerPivot for SharePoint 2013 add-in is required to access a workbook as a data source from outside the farm, perform Scheduled Data Refresh, implement a PowerPivot Gallery, access the Management Dashboard, and use the BISM link file content type.

Microsoft’s BI Light-Up Story

Figure 30-1. Microsoft’s BI Light-Up Story

One additional thing to keep in mind with SharePoint 2013 is that PowerPivot models for 2013 must be authored in Excel 2013. This new functionality is not backwards compatible, which means that your current models will have to be upgraded in order to be used in 2013. Once upgraded, they will no longer work in any of the previous versions of PowerPivot.

Summary

In this chapter, we discussed what is needed in order for ITPros to properly support the SharePoint BI framework. We covered the software needed to implement what we cover in this book: SharePoint Enterprise Edition, SharePoint databases backed on SQL 2008 R2 or higher, SQL Server 2012 BI or Enterprise edition, and Visual Studio LightSwitch. We detailed the parts of SharePoint that will be required to make the solutions in this book work, specifically SSRS, Excel Services, PowerPivot and Power View. We covered the Microsoft BI Light-Up Story and explained how the features fit together to build the complete infrastructure for Microsoft’s BI solution and also reviewed some of the changes that are a part of SharePoint 2013.