Table of Contents



Introduction

 This is all about how we can use your existing multidimensional cube to generate power view reports with SharePoint 2010/2013. This article will provide step by step procedure.

Procedure

Following are the requirements for this environment.
  1. SQL Server 2012 SP1 Cumulative Update 4 or above (http://support.microsoft.com/kb/2833645)
  2. SharePoint Server 2010 or 2013

  • First of all you need to install SharePoint server.(2010 or 2013)
  • Then Install SQL Server 2012 to same server.
    • Install instance of SQL Server database engine with reporting services-SharePoint.

SQLServer_ReportingServices

    • then you need to install PowerPivot instance for SharePoint. (Do not check on Add SQL Server Database option since you already have one)

SQLServer_PowerPivot

  • Create your site collection in SharePoint using PowerPivot site template.
  • Run the PowrPivot configuration tool in order to configure power pivot in your SharePoint installation. If you installed SharePoint 2013 then run PowerPivot configuration for SharePoint 2013 Configuration.

PowerPivot_Configuration

  • After configuring PowerPivot there will be power pivot solutions in SharePoint farm.
    • In SharePoint Central Administration go to System Settings
    • Manage farm solutions under Farm Management
    • Deploy PowerPivot solutions to your site collection.

Farm_Solutions

  • Activate power pivot integration feature in your site collection. (Site Settings –> Site Collection Features under Site Collection Administration)

PowerPivot_Feature

  • Then install SharePoint reporting service and create service proxy.
    • Run following commands in SharePoint Management Shell with administrator privileges.
    • Install-SPRSService
    • Install-SPRSServiceProxy
  • Start reporting services service
    • In SharePoint Central Administration go to System Settings
    • Then start Reporting Services in Manage Services on Server
  • Create reporting services web application in SharePoint.
    • In SharePoint Central Administration go to Application Management
    • Then go to Manage Service Applications under Service Applications
    • Create new SQL Server Reporting Services service application

ReportingServiceApplication

    • Create new application pool for this service application

CreateNewApplication

  • Create new power pivot site in your site collection

CreateNewSite

  • In that created site, you will have inbuilt power pivot gallery library. In that library, go to library settings.
  • In Advanced Settings, Enable content types.

AdvancedSettings

  • Now in library settings, you will have Add from existing site content types. Go to that link and add BI Semantic model and Report Data Source

AddContentTypes

  • After adding those content types you can create new Report Data Source in power pivot gallery.

NewReportDataSource

  • Following figure shows how to configure Report Data Source. Select Microsoft BI Semantic Model for Power View as Data Source Type. Give your analysis server as connection string.

DataSourceProperties

  • Now you have Report Data Source. That’s all you need to create your first power view report using your existing multidimensional cube. :)

PowerViewReport