How To Add A Power Pivot Data Model To SSAS Running In SharePoint Mode

Adding a Power Pivot data model to SSAS running in SharePoint mode requires the installation of SQL Server Analysis Services server in Power Pivot mode and its configuration with SharePoint. To guide you through the process, follow these steps:

1. Install Power Pivot for SharePoint: Begin by running the SQL Server Installation Wizard and selecting “New SQL Server stand-alone installation or add features to an existing installation.” Choose the evaluation edition or enter a product key for the enterprise edition. Review and accept the license terms, install setup files, and select the server configuration options. On the Analysis Services Configuration page, choose PowerPivot Mode under Server Mode and add users with administrative permissions. Once completed, restart the computer.

2. Configure Basic Analysis Services SharePoint Integration: Launch SQL Server Management Studio and connect to the Analysis Services instance. In Object Explorer, right-click the instance name, go to Properties, and select Security. Add the machine account of Office Online Server and the domain login for the Excel Services Application as Analysis Services administrators. Connect to Office Online Server using PowerShell and add the Analysis Services server. Grant Excel Services Application server administration rights on Analysis Services.

3. Verify the SQL Server installation: Click Start, select All Programs, and navigate to the Microsoft SQL Server group. Choose SQL Server Management Studio and connect to the Analysis Services instance ([your server name]\POWERPIVOT) to confirm that the service is running.

4. Install Power Pivot for SharePoint (alternative method): If you are using SharePoint 2013, you have the option to install the Power Pivot for SharePoint add-in (spPowerpivot16.msi). This will enable additional functionality and features in your SharePoint farm. Run the installer with administrator privileges, review and accept the license agreement, select the features to install, and complete the installation.

By following these steps, you will successfully add a Power Pivot data model to SSAS running in SharePoint mode. This will empower your SharePoint farm with the remarkable capabilities of Power Pivot.

Configuring Power Pivot for SharePoint Integration

To configure Power Pivot for SharePoint integration, we need to start SQL Server Management Studio and connect to the Analysis Services instance. In Object Explorer, right-click the instance name, go to Properties, and select Security. Here, we will add the machine account of Office Online Server and the domain login for the Excel Services Application as Analysis Services administrators.

Next, we need to connect to Office Online Server using PowerShell and add the Analysis Services server. By granting the Excel Services Application server administration rights on Analysis Services, we ensure smooth integration between the two.

Once these steps are completed, it is important to verify the SQL Server installation. Simply click Start, select All Programs, and go to the Microsoft SQL Server group. From there, select SQL Server Management Studio and connect to the Analysis Services instance ([your server name]\POWERPIVOT) to confirm that the service is running properly.

If you are using SharePoint 2013, you have the option to install the Power Pivot for SharePoint add-in (spPowerpivot16.msi) for additional functionality and features in your SharePoint farm. To do so, run the installer with administrator privileges, review and accept the license agreement, select the desired features to install, and complete the installation process.

By following these steps, you will successfully configure Power Pivot for SharePoint integration, allowing you to add a Power Pivot data model to SSAS running in SharePoint mode. This, in turn, enables Power Pivot functionality within your SharePoint environment, enhancing data analysis and reporting capabilities.

Meet the Author

Abdul Rahim has been working in Information Technology for over two decades. Learn how Abdul got his start as a Tech Blogger , and why he decided to start this Software blog. If you want to send Abdul a quick message, then visit his contact page here.