ARCHIVED: In Microsoft Excel, how do I create a PowerPivot-enabled project?

To create a PowerPivot-enabled project, you must create a PowerPivot-enabled Excel 2010 worksheet, design the information, PowerPivot view, and chart for the project, and then finally save and publish the project to a SharePoint PowerPivot gallery, where it can be viewed.

• Creating a PowerPivot-enabled worksheet
• Designing the project information
• Designing the project view
• Designing the project chart
• Saving and publishing the project
• Viewing the project in SharePoint

Creating a PowerPivot-enabled worksheet

1. In Excel 2010, select the PowerPivot tab.
2. Click PowerPivot Window. A new "PowerPivot for Excel" window will open.
3. Click From Database, and select your data type from the drop-down menu.
   Note:

   For EBI, it is best to use either Analysis Services or a unit's SQL server.
4. Enter a server or file name. For Enterprise Analysis Services, enter ebis.uits.iu.edu/orgunit.
5. Wait for the wizard to make the connection, and then select a database from the drop-down menu. Click Next.
6. Click Design, and then click the ... icon in the upper-right of the frame. From the window that opens, select the cube you wish to open. Click OK.

Once you have created the worksheet, you can proceed to designing the project information.

Designing the project information

1. On the Metadata tab for your selected cube, select a dimension, and drag and drop it into the "Query Design" window.
   Note:

   When designing, consider not creating filters at first; filters may slow your client desktop performance each time a prefiltered data element is updated.
2. In the "Measure Group:" window on the Metadata tab, expand the Measures group to add a measure (i.e., what is being counted, such as hours, FTE, count, etc.).
3. Drag and drop the measures to the "Measure Group:" window. A list of data generated will display.
4. Click OK, and then click Finish; a window will display the resulting MDX. click Close.

You will be returned to the PowerPivot for Excel book, and can proceed to designing the PowerPivot view.

Designing the project view

1. In the PowerPivot for Excel book, rename all header fields with friendly names.
2. In the ribbon, click PivotTable and select PivotChart.
3. In the "Create PivotChart" window, select New Worksheet, and then click OK.
4. In the "PowerPivot Field List" pane on the right, select the fields to add to the report. These will be added to the "Axis Fields (Categories)" pane.
5. Drag any of these axis fields to the "[Sigma] Values" pane. That data will be displayed within the chart area.
6. In the "PowerPivot for Excel" window, select the header name that contains the measures (what is being counted).
7. In the ribbon, from the Data Type drop-down menu, select Whole Number.
   Note:

   This will close the "PowerPivot Field List" pane. To reopen the list, click the chart or click a cell in the table.
8. Navigate back to the book with the chart. In the "[Sigma] Values" pane, right-click the desired value, select Summarize By, and choose Sum.

Designing the project chart

To design a project chart, from the "PowerPivot Field List" pane, drag any of the query objects to either the "Slicers Vertical" or "Slicers Horizontal" pane. This will create a "slicer", which will be available next to the chart; clicking an option within the slicer permits filtering of data choices.

• Right-click the data field to remove or edit these options.
• Drag other query objects to either of the slicer panes to allow additional slicing functionality.

When you have finished designing the chart, you can save and publish the project to your SharePoint PowerPivot gallery.

Saving and publishing the project

1. Save the Excel file to a secure file server.
   Note:

   Data in the Excel document may contain sensitive information and all such information should be saved to a secure file server.
2. From the File tab, click Save & Send, and select Save to SharePoint.
3. Find your site among recent locations, or navigate to your departmental PowerPivot gallery (e.g., https://edss.iu.edu/sites/snd/dept_bi/edss/bi_cntr/pivot). Copy the gallery URL.
4. Click Save As. In the "Save As" window, paste the URL into the document location bar at the top. Provide your SharePoint credentials, if prompted. Click Save.

Viewing the project in SharePoint

Navigate to your departmental PowerPivot gallery (the URL used to save the document) to view the end product.

• When viewing the project, you can edit it in Excel, or in the browser.
• To modify the data refresh schedule, click the Manage Data Refresh icon (a calendar) in the upper right-hand corner of the Gallery View. These options permit data refreshing on a predefined or other schedule.