I admit it freely that I am a complete novice when it comes to PowerPivot. I'm tasked with getting PowerPivot running on SharePoint 2013 and I'm not having much luck getting it done. I'm significantly confused over the process and am hopeful that I can get some direction here on how to proceed to get this to work.
So, here's what I've got: a 2 server test environment SharePoint setup (running on Windows Server 2012 R2), 1 server running SP 2013 (WFE and APP services), 1 server running SQL Server 2012 SP1. They are patched, at least they were as of last Wednesday. These are new servers (VMware hosted), new clean installations of both SP and SQL. SharePoint is up and working, I can create web apps, site collections, sites, libraries, lists, etc with no problem.
On SQL I've got 1 instance of the database engine running (SQL01), 3 instances of Analysis Services running (on the default instance of SQL - SQL01, a TABULAR instance - SQL01\TABULAR, a POWERPIVOT instance - SQL01\POWERPIVOT), 1 instance of Reporting Services (SQL01), 1 instance of Integration Services (SQL01).
On SP I've mounted the SQL ISO and installed SQL Server PowerPivot for SharePoint, SSRS in SharePoint Integration Mode, and spPowerPivot.msi. There are SQL Server PowerPivot System Service and SQL Server Reporting Services Service on the server and both are started. Both PowerPivot Service Application and SQL Server Reporting Services Service applications and proxies are running on the farm as well.
Which brings me to simpleton question#1: is there supposed to be a SQL Server Analysis Service service app on SharePoint and where did I miss installing it? (I've seen SSAS service apps on SP 2010 and PowerPivot, I've also seen references to SSAS in some of the blogs I've browsed.)
I've configured Excel Services App data model settings and added entries pointing to Analysis Services on both the default instance and the PowerPivot instance (SQL01, SQL01\POWERPIVOT).
Simpleton question #2: should I just have the PowerPivot instance defined here; and does it matter whether I use the SQL alias setup for the SharePoint installation when entering the server name, or should it point to the server itself and not use the alias?
The PowerPivot Management Dashboard loads without error, but "The cube has not been processed!" so there's no usage data, etc. I'm assuming this is because I've still not got PP installed and configured correctly.
So, any suggestions? The SharePoint install is pretty generic, I even used the config wizard to set everything up instead of doing it manually and avoiding the GUIDs in the db names. I've referenced several different blogs and technotes during all this:
http://msdn.microsoft.com/en-us/library/hh231725(v=sql.110) - this one totally confused me...
Question 1: is there supposed to be a SQL Server Analysis Service service app on SharePoint and where did I miss installing it?
SQL Server Analysis Services in SharePoint integrated mode is an Analysis Services server instance that supports in-memory storage of compressed data that is calculated in real time, in response to queries for PowerPivot data. PowerPivot for SharePoint provides server hosting of PowerPivot data in a SharePoint farm. PowerPivot data is an analytical data model that you build using one of the following:
- The PowerPivot for Excel 2010 add-in
- Excel 2013
So, I would suggest you take a look at the following picture regarding PowerPivot for SharePoint 2013 two server deployment:
Excel Service Application. The service application is created as part of the SharePoint installation.
PowerPivot Service Application. Default name is Default PowerPivot Service Application.
RUN the spPowerPivot.msi to install data providers, the PowerPivot configuration tool, PowerPivot Gallery, and schedule data refresh.
An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.
The SharePoint content, configuration, and service application databases.
For detail information, please see: http://msdn.microsoft.com/en-us/library/hh231674.aspx#bkmk_powerpivot_sharepoint2013_2server
Question 2: should I just have the PowerPivot instance defined here; and does it matter whether I use the SQL alias setup for the SharePoint installation when entering the server name, or should it point to the server itself and not use the alias?
Analysis Services processes PowerPivot queries and data refresh jobs in the farm. This account is always specified during SQL Server Setup when you install PowerPivot for SharePoint. I'm not very sure which you are talking about for this issue, but I would suggest you take a look at the following document:
Configure PowerPivot Service Accounts: http://technet.microsoft.com/en-us/library/ee210642(v=sql.110).aspx
TechNet Community Support
Thanks again for the info. I've gone through the information at http://msdn.microsoft.com/en-us/library/hh231674.aspx#bkmk_powerpivot_sharepoint2013_2server regarding the 2 server deployment. That is how I've set up my environment, configured the Excel Services App, PowerPivot Service App, and installed the spPowerPivot.msi on server 1 (App server). I've installed Analysis Services Server in SharePoint mode on my SQL server and configured the Excel Data Model Settings to point to that instance on the SQL server.
On my first question, perhaps a little more detail: On a SharePoint 2010 server to which I have access, that also has PowerPivot installed on it, there is a "SQL Server Analysis Services" service running on the app server (Central Admin, Manage services on server) - also "SQL Server PowerPivot System Service". On my new 2013 farm, there isn't a "SQL Server Analysis Services" service, just "SQL Server PowerPivot System Service" and "SQL Server Reporting Services Service". So, is there supposed to be a "SQL Server Analysis Services" service on my 2013 farm, like there is on the 2010 farm? (yes, it may be a stupid question, but at this point in this mess I just need to know) If there is supposed to be this service, how and where in the whole install process for PowerPivot is it installed?
My second question was more about the entries in the Excel Services App Data Model Settings. I've got entries in the data model settings pointing a the POWERPIVOT Analysis Services instance on my SQL server, one entry using the SQL alias I've setup on the SharePoint server using cliconfg, another entry pointing to the NetBIOS name of the server. So, I've got:
Do I need both entries? Will the SQL alias entry suffice? Should I change or add an entry using the FQDN of the SQL server and the instance? (Would having the NetBIOS entry here cause a Named Pipes error in the ULS if Named Pipes is disabled on the SQL Server?)
Sorry for what may be really basic and obvious questions. I've been hacking at this now for weeks and getting stymied and confused. The users testing this site say that PowerPivot is mostly working, but I don't know if it is complete or just waiting to fail on me when I say it's ready. The management dashboard isn't refreshing, the cube isn't processing, and the annoying little graphic snapshots in the PP library aren't being created.
Thanks for any and all help and suggestions, I'm just very frustrated.