Microsoft SharePoint 2013:Using SQL Server Analysis Services (SSAS) cube as a Datasource
How to connect to MS SQL database from PowerPivot
SQL server Analysis Services either tabular or multidimensional is the supported data source that can be used for the PowerPivot workbooks and published to SharePoint.
Creating a connection to SSAS cube from PowerPivot
- Open the Excel workbook you would like to connect to the database and go to the PowerPivot tab. In the PowerPivot tab click Manage.
- When the PowerPivot for Excel window opened, click on From Database and then From Analysis Services or PowerPivot in the Get External Data section.
- In the Table Import Wizard Specify the Analysis Services instance to which you would like to connect, click Test Connection.
- Click OK in the connection status window.
- Choose the database from the drop-down menu and click Next.
- On the Specify a MDX Query page click Design.
- Choose the required measures, columns, filters and click OK.
- On the Specify a MDX Query page click Finish.
- The Table Import Wizard will start importing the selected data to the PowerPivot model. Once the status of the wizard changes to Success click Close.
- You can see the data imported to the model and can start creating your PowerPivot reports by clicking on the PowerPivot table button.
Creating a connection to SSAS cube from Excel
- Open a workbook you would like to connect to SSAS and go to the Data tab. Then in the Get External Data section click From Other Sources and then choose Analysis Services.
- Specify the Analysis Services instance, authentication type and credentials if required. Then click Next. .
- Select the database to which you would like to connect and a cube. Click Next.
- Click Finish .
- Choose how you would like to import data and click OK.
- Now you can start creating your PowerPivot reports.
You can find more about SharePoint Business Intelligence feature on the following page – Business Intelligence