Microsoft SharePoint 2013:Using SQL Server Analysis Services (SSAS) cube as a Datasource

From PlexHosted Knowledgebase
Jump to: navigation, search
Dedicated SharePoint Hosting Services at PlexHosted

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

  1. Open the Excel workbook you would like to connect to the database and go to the PowerPivot tab. In the PowerPivot tab click Manage.
    How to connect to SSAS cube from PowerPivot-1.png
  2. When the PowerPivot for Excel window opened, click on From Database and then From Analysis Services or PowerPivot in the Get External Data section.
    How to connect to SSAS cube from PowerPivot-2.png
  3. In the Table Import Wizard Specify the Analysis Services instance to which you would like to connect, click Test Connection.
    How to connect to SSAS cube from PowerPivot-3.png
  4. Click OK in the connection status window.
    How to connect to SSAS cube from PowerPivot-4.png
  5. Choose the database from the drop-down menu and click Next.
    How to connect to SSAS cube from PowerPivot-5.png
  6. On the Specify a MDX Query page click Design.
    How to connect to SSAS cube from PowerPivot-6.png
  7. Choose the required measures, columns, filters and click OK.
    How to connect to SSAS cube from PowerPivot-7.png
  8. On the Specify a MDX Query page click Finish.
    How to connect to SSAS cube from PowerPivot-8.png
  9. 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.
    How to connect to SSAS cube from PowerPivot-9.png
  10. You can see the data imported to the model and can start creating your PowerPivot reports by clicking on the PowerPivot table button.
    How to connect to SSAS cube from PowerPivot-10.png

Creating a connection to SSAS cube from Excel

  1. 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.
    How to connect to SSAS cube from PowerPivot-11.png
  2. Specify the Analysis Services instance, authentication type and credentials if required. Then click Next. .
    How to connect to SSAS cube from PowerPivot-12.png
  3. Select the database to which you would like to connect and a cube. Click Next.
    How to connect to SSAS cube from PowerPivot-13.png
  4. Click Finish .
    How to connect to SSAS cube from PowerPivot-14.png
  5. Choose how you would like to import data and click OK.
    How to connect to SSAS cube from PowerPivot-15.png
  6. Now you can start creating your PowerPivot reports.
    How to connect to SSAS cube from PowerPivot-16.png

You can find more about SharePoint Business Intelligence feature on the following page – Business Intelligence