Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
Ideally, you can find the Microsoft BI stack in every SharePoint farm across the enterprise so that users can seamlessly share their BI solutions with each other. In the real world, however, BI is only one of many SharePoint scenarios, albeit a mission-critical one. It is not uncommon for IT organizations to deploy multiple farms for different purposes, such as a general utility farm for team and My sites, another for search, and yet others for content deployment, and again others for BI. See the online topic “Services Architecture Planning (SharePoint Server 2010)” for typical farm topologies (http://technet.microsoft.com/en-us/library/cc560988.aspx).
While it is possible to share some SharePoint services across farms, BI services unfortunately do not fall in this category. In other words, for a SharePoint 2010 farm to offer BI services, such as Excel Services, PerformancePoint Services, PowerPivot for SharePoint, and Power View, these services must be locally deployed in the farm. This is a challenge because deploying all BI services in every farm can overburden front-line operators and farm administrators with complexity. On the other hand, not enabling these services can amount to a productivity issue for information workers. Anna might have no problem publishing her PowerPivot workbooks and creating Power View reports, while Bart might be struggling just because his site is hosted in a farm that isn’t BI enabled. The BI architecture for SharePoint 2013 helps IT organizations to better Bart’s situation.
The good news is that Excel Services in SharePoint 2013 includes core PowerPivot functionality straight “out of the box.” You do not need to deploy PowerPivot for SharePoint anymore to interact with a PowerPivot workbook. Just register one or multiple Analysis Services servers in the Data Model Settings of Excel Services. So, without having to deploy any additional BI services in Bart’s farm, Bart can now share his PowerPivot workbooks and enjoy seamless data exploration interactivity in the browser. The following screenshot shows the Excel Services configuration page to register Analysis Services servers.
Furthermore, your Analysis Services servers are no longer a direct part of your farms, although they still must be running in SharePoint mode. This enables you to share a single Analysis Services server between multiple SharePoint 2013 farms. Note that SharePoint 2013 Preview requires SQL Server 2012 SP1 CTP3. Analysis Services servers running SQL Server 2012 RTM cannot support SharePoint 2013. SQL Server 2012 SP1 CTP3 was specifically released to accompany the Office 2013 and SharePoint 2013 Preview versions.
For medium and large farms, sharing a single server running SQL Server 2012 SP1 CTP3 Analysis Services is not a recommended topology, but it can help to lower maintenance overhead and total cost of ownership for small farms. In the previous PowerPivot for SharePoint 2010 architecture, each farm, regardless of size, requires a separate server, which might be cost-prohibitive for some departmental or team deployments. But now, with SharePoint 2013, you can broaden the reach of BI across your organization by sharing a single server running Analysis Services between multiple small farms, and in this way maximize your return on investment. Note the emphasis on small farms! Again, medium and large farms should have their own set of Analysis Services servers for performance, scalability, and maintenance reasons. The following figure depicts several typical Analysis Services server topologies for SharePoint 2013.
There are, however, limits to what Excel Services offers “out of the box,” as far as PowerPivot functionality is concerned. You get full data exploration interactivity in the browser and access to Workbooks as a Data Source from inside the farm, but accessing a Workbook as a Data Source from outside the farm, Scheduled Data Refresh, PowerPivot Gallery, Management Dashboard, and the BISM link file content type are not included in Excel Services. These features are part of a separate PowerPivot for SharePoint 2013 add-in, which is available in the SQL Server 2012 SP1 CTP3 Feature Pack. SharePoint administrators, who shy away from installing additional components in their farms, don’t have to deploy the PowerPivot add-in. Others, who value the additional capabilities, can download and deploy the add-in free of charge. Note that SQL Server 2012 SP1 CTP3 Setup also includes the PowerPivot for SharePoint 2013 add-in when you select the PowerPivot for SharePoint installation option. This is simply for your convenience when you are installing both SharePoint and Analysis Services on a single server. In this case, you don’t have to download a separate add-in from the Feature Pack. The following screenshot shows the PowerPivot Gallery in a SharePoint 2013 site.
Another important aspect worth mentioning is that Power View for SharePoint 2013 no longer depends on PowerPivot to use Workbooks as a Data Source inside the farm. Excel Services supports this scenario natively. The same is true for PerformancePoint Services and any other SharePoint solution running inside the farm. With SharePoint 2013, you have more control and flexibility over the BI deployment. As illustrated in the following diagram, you can light-up features individually according to your specific preferences and business requirements.
And this is the BI light-up story for SharePoint 2013! Deploy your farms and then one or multiple Analysis Services servers in the backend. Next, configure Excel Services to use the Analysis Services servers to enable the core PowerPivot functionality. Finally, deploy the PowerPivot for SharePoint 2013 add-in or the Reporting Services SharePoint add-in or both. Of course, if you asked the Analysis Services team, we strongly recommend deploying the full Microsoft BI stack to boost information worker productivity, make better decisions faster, and get the most out of your investments in information technology. Subsequent blog posts will cover the setup scenarios in more detail. Stay tuned!
Great post, exactly the info I needed!
Now BI is separate server ..but can sharepoint compete with Congnos or it has edge over....
Good article Kay - thank you!