How long? A few months actually scattered here and there between projects, and when I ran into an error or snag (and there were a lot of them) I usually shelved it for something else. This post will hopefully make it easy for you (but Long) to build a SharePoint 2013 Server and configure Power View on it. There are a lot of posts out there for SharePoint 2013 configuration for BI, but I needed several of them put together to get this accomplished. So that is why I outlined it below for you in one nice blog post so you don't have to.
I downloaded a trial of SharePoint 2013 because I like to stay bleeding edge and work with all the latest technologies. SharePoint is now an integral the part of the Microsoft Business Intelligence Stack and the technology I know the least about so why not use the latest and greatest right? I know how to use SharePoint and place documents out on a SharePoint site etc., but to configure it on a server as a farm install for Business Intelligence is a whole different ball game. This post is for those who are novices with installing and configuring SharePoint but want to have Power View installed centrally on a server. I also used Windows Server 2012 RC with the GUI as the operating system via VMWare VM Workstation 8.0 for my SharePoint 2013 server.
Prerequisite steps that I performed were:
- Created a Virtual Machine (VM) that was a Domain Controller. It's simpler than you think.
- Created a VM for the Sharepoint 2013 server.
- Joined the SharePoint 2013 VM to the domain.
- Understand how to deploy a tabular model to an Analysis Server 2012 machine because you will need to have an Analysis Services Tabular database for Power View to use as a source or have a PowerPivot workbook. My blog post here can be used to help with SSAS Tabular.
I should note that you don't have to use VMWare or Windows Server 2012. Any machine virtual or physical will do and Windows Server 2008 R2 is fine as long as you have a domain controller setup and a server for SharePoint 2013.
- Download SharePoint 2013.
- Download SQL Server 2012 SP1 CTP 3 (CTP4 is out now, but CTP3 is what I used and know works) ENU\SQLServer2012SP1-CTP3-ENU.iso
- Install SQL Server 2012 SP1 CTP3 DB Engine, Analysis Services (Pick Tabular Mode), Management Tools, plus SQL Server Data Tools as described from Installing the BI Features of Sharepoint 2013 Preview. (I did not install anything from SQL Server 2008 that is mentioned in this article).
- Download the Adventure Works DW data file and AdventureWorks Tabular Model SQL Server 2012 here.
- Open the Tabular model with SQL Server Data Tools you installed in step 3, and Publish it to the Analysis Services server.
- Installed Sharepoint 2013 Prerequisites as described from Installing the BI Features of Sharepoint 2013 Preview. After the restart I had to initialize the Prerequisites again. Once Finished you need to restart the machine again.
- Install Sharepoint 2013 as described from Installing the BI Features of Sharepoint 2013 Preview. I followed the screen shots exactly.
- Install SQL Server 2012 SP1 CTP3 as described from Installing the BI Features of Sharepoint 2013 Preview. I followed the screen shots exactly for adding the Reporting Services Services to the default instance in step 3 above.
- Install SQL Server 2012 SP1 CTP3 as described from Installing the BI Features of Sharepoint 2013 Preview. I followed the screen shots exactly for adding SQL Server PowerPivot for Sharepoint.
- Install PowerPivot for Sharepoint 2013 Configuration as described from Installing the BI Features of Sharepoint 2013 Preview. I followed the screen shots exactly.
- Add service applications and the many configurations via the Sharepoint 2013 interface as described from Installing the BI Features of Sharepoint 2013 Preview. I followed the screen shots exactly. Make sure under "Provision Subscriptions and Alerts" you download the script and execute it on the SQL Server.
- After all the steps have been completed in Installing the BI Features of Sharepoint 2013 Preview, go to http://[Servername]:2013 in Internet Explorer. This is Central Administration. If you followed to this point it has all been pretty straightforward and you're all setup with everything installed for Power View (and Performance Point) to work. For SharePoint folks it may seem elementary to do the next few steps but like I stated early in this post, I am a SharePoint newbie so here are the final steps in detail I performed.
- Under Application Management, click on Create site collections, give your site a name and be sure to select the PowerPivot Site template. I used [Domain]\Administrator accounts for Primary and Secondary Site Collection Administrators. I use [Domain]\Administrator accounts everywhere for the purposes of setting up a lab\demo\play environment. This is NOT a best practice and there are plenty of articles out there describing what groups, accounts to create for SharePoint setup. I recommend you follow them for production etc. environments.
- Navigate to the site you just created. Mine is http://[Servername]/sites/Powerpivot. Click on Documents in the left navigation>then library in the top menu>library settings>Advanced Settings>click "Yes" to allow management of content types>Scoll down and hit "Ok".
- You should still be in the library settings location>click "Add from existing site content types"
- Add BI Semantic Model Connection. I added Report Builder Model and Report but you don't have to>hit OK.
- You should be back at http://[Servername]/sites/Powerpivot now.
- In the left NAV click Documents> top NAV click Files>click "New Document"> BI Semantic Model Connection. YOUR ALMOST THERE STICK WITH IT! :-)
- Create a file name for the connection, specify the [Servername], and the Database>Click OK.
- Under Documents you should see your new connection. Click the three dots>you should see "EDIT SHARE FOLLOW" click the next three dots>click Create Power View Report!
- If the next screen prompts you to install Silverlight go ahead and install it.
- If it asks you to restart your browser like mine did feel free to curse at it. It may make you feel better and worked for me, then give in and restart it.
- Navigate to http://[Servername]/sites/Powerpivot>Click the three dots>you should see "EDIT SHARE FOLLOW" click the next three dots>click Create Power View Report. You are done and hope this helped out because when I journeyed down this road it wasn't easy! If you want help with creating Power View reports check this out. Dragging and clicking items from the field list on the right will get you familiar with it quickly. I created the report below in under 5 minutes.