May 24
Creating an Excel PowerPivot (SharePoint) Enable Project

PREREQUISITES
To create a PowerPivot Enable Excel 2010 worksheet you will first need to download and install the Microsoft Excel 2010 PowerPivot add-in from here - http://www.microsoft.com/en-us/bi/powerpivot.aspx

CREATE A POWERPIVOT ENABLED EXCEL WORKSHEET

  • Open Microsoft Excel 2010
    • From within the Excel Ribbon, Click PowerPivot
    • Click the PowerPivot Window, a new Window will be displayed which is an additional component service of Excel
      • Click the From Database located in the Ribbon
        • Select the dropdown and choose your data type
          • You can select SQL Server, Analysis Services, and From Access
            • NOTE* When using the University BI it is always best to use either the Analysis Services or a Units SQL Server.
          • Input the connection details
            • For this example will use the Enterprise Analysis Services (ebis.uits.iu.edu\orgunit)
              • Select a Database name: in the dropdown menu that you have access to and click Test Connection
              • Click Next
              • Click Design
              • Select the Cube you wish to open

  • A new window will open, select a cube from the available options
    • Click OK

DESIGNING THE INFORMATION

  • Select a Dimension from the Metadata window (tab)
    • Note* When designing, consider not creating filters first since this may slow your client desktop performance each time a pre-filtered data element is updated
  • Drag and Drop the Dimension(s) to be used into the Query Design window
  • Add Measure (What's being counted "hours, count, FTE, etc.") by expanding the Measures grouping at the top of the Metadata Measure Group: Window
    • Drag and Drop the measures to the same Window
      • You will now see the list of data generated
        • Click OK
        • Click Finish
          • Note that this will result in a window displaying the resulting MDX
        • Click Close

DESIGNING THE POWERPIVOT VIEW

  • Your now presented with the PowerPivot for Excel Book
    • Rename all Header fields using friendly names
  • From within the Ribbon Click PivotTable and select Pivot Chart
    • A new window (Create PivotChart) will open, select New Worksheet
    • Click OK
      • You should now see a window similar to this

  • Select the desired fields from the PowerPivot Field List (Right Menu) to add to the report
    • You will see that these sections are added to the Axis Fields (Categories) pane
      • Move any of these Axis Fields to the ∑ Values pane
        • You will see that from within the Chart 1 Area that data is now displayed

  • Locate the Excel Window (PowerPivot for Excel)
    • Select the header name that contains the Measures (What's being counted)
      • When you select this you will have the option in the Ribbon to select Data Type:
        • Change this value to Whole Number
          • Note* The PowerPivot Field List will close; to reopen the list click on the chart. The same behavior will result if you click a cell.

  • Navigate back to the (Book) that has the chart previously created
    • From within the ∑ Values pane, right click the value
      • Select Summarize By
        • and select Sum

DESIGNING THE CHART

  • Drag any of the Query objects from the PowerPivot Field List to the Slicers Vertical or Horizontal pane
    • Doing so will create a Slicer
      • You can remove or edit these options by Right Clicking the data field
  • A slicer is now available next to the chart which permits the ability to filter data choices by clicking an option from within the Slicer
    • You can additionally add other Query objects to either of the Horizontal or Vertical Slicers panes to allow additional Slicing functionality

SAVING AND PUBLISHING TO A SHAREPOINT POWERPIVOT GALLERY

  • Save the Excel file to a secure fileserver
    • Note* Data in the Excel document could contain sensitive information and all such information should be saved to a secure file server
  • Click the File Tab
    • Select Save and Send > Save to SharePoint

  • If prompted for credentials input these
    • Click Save

VIEWING THE EXCEL POWERPIVOT IN SHAREPOINT

  • Navigate to your PowerPivot Gallery (Previous URL used to save the document) to view the end product.
    • To edit you can open in Edit mode to make changes
  • To manage the data refresh schedule select the option when using the Gallery View
    • This option permits the scheduling of data on a predefined time schedule or using other options

Comments

There are no comments for this post.

 ‭(Hidden)‬ Blog Tools