Tutorial: Optimize a Sample PowerPivot Model for Power View Reports

In this tutorial, you modify an Excel workbook in PowerPivot for Excel to make a small but functional model that you can use as a basis for sample Power View reports. Everything you do in this tutorial in PowerPivot, you can also do in tabular models that you create in SQL Server Data Tools (SSDT). In fact, you can do much more in SQL Server Data Tools (SSDT). For more information, see Tabular Modeling (SSAS Tabular).

The PowerPivot file and Power View report based on it

Prerequisites

To do this tutorial:

• Install PowerPivot for Excel.

• Have a computer that meets the System requirements for Power View.

• Download and install Power View samples, including the image files, from the Download Center. For more information, see Power View and PowerPivot HelloWorldPicnic Samples for SQL Server 2012 and Images for Power View and PowerPivot HelloWorldPicnic Samples in SQL Server 2012 Samples.

In This Article

Before you start the tutorial

Create linked tables in PowerPivot

Start Power View

Create relationships in PowerPivot

View changes in Power View

Set reporting properties in PowerPivot

Create table and cards with default values in Power View

Consolidate tables and add image URLs in PowerPivot

Try adding images in Power View

Identify image URLs in PowerPivot

Add images to a table in Power View

Default aggregates, dates, tooltips, and calculated measures and columns in PowerPivot

Create a bubble chart in Power View

Create a slicer to validate the model

Summary

Before you start the tutorial

Before you can start the tutorial:

• Save the HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx file to a SharePoint document library or PowerPivot Gallery.

  • If you save it in a SharePoint document library, you create a shared data source that points to the HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx file that you saved in the SharePoint document library.

  • If you save it to a PowerPivot Gallery, it doesn’t need the shared data source. You can open Power View directly from the gallery.

• Save the images to the associated SharePoint document library. See more information about the Images for Power View and PowerPivot HelloWorldPicnic Samples

To create a shared data source (RSDS) file to point to the PowerPivot (XLSX) file

1. In Shared Documents, create a HelloWorldPicnicSQL2012 folder, and save the HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx file there.

2. In the HelloWorldPicnicSQL2012 folder, click the Documents tab, click the arrow next to New Document, and then click Report Data Source.

   If there is no Report Data Source option on the New Document tab, then someone with adequate permissions on the SharePoint site needs to add that content type to the site. For more information, see Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).

3. In the Name box, type HelloWorldPicnicRSDS.

   Note the RSDS file extension.

4. In the Data Source Type box, click Microsoft BI Semantic Model for Power View.

5. The Connection string for an XLSX file is the full URL to the file, including the file name. For example:

   http://<myserver>/Shared%20Documents/HelloWorldPicnicSQL2012/HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx

6. For Credentials, click Windows authentication (integrated) or SharePoint user.

7. Click Test Connection.

   You see a message that the connection test was successful.

8. Click OK.

To save the images

1. Create an Images folder in the HelloWorldPicnicSQL2012 folder. For example:

   http://<myserver>/Shared Documents/HelloWorldPicnicSQL2012/Images

2. In the Images folder, save the images that you downloaded from the Download Center. For more information, see the Prerequisites section at the beginning of this tutorial.

To start PowerPivot from the PowerPivot Gallery

1. From the PowerPivot Gallery, click the image of the file HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx.

   The workbook opens in SharePoint.

2. Click Open in Excel.

3. Click the PowerPivot tab, and then click PowerPivot Window.

To start PowerPivot from Shared Documents

1. From Shared Documents, click the dropdown arrow next to HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx, and click Edit in Microsoft Excel.

2. Click the PowerPivot tab, and then click PowerPivot Window.

In Excel and PowerPivot

Create linked tables in PowerPivot

1. In the Excel window, select all the columns and rows in the Items worksheet.

2. On the Home tab, click Format as Table, and then pick a format for the table. You can make the style anything you like.

3. On the PowerPivot tab, click Create Linked Table.

   Make sure it contains the correct columns and rows and that My table has headers is checked, and then click OK.

   The table opens on a new tab in PowerPivot.

4. Right-click the tab and rename it Items.

5. Repeat this three times to create linked tables in PowerPivot for Distributors, Quantities, and Dates.

6. Click Save, and you now have a model—not much of a model, but a model nonetheless.

   Note

   You’re back in the Excel window after you save.

In Power View

Start Power View

From Shared Documents

• In the Shared Documents folder, click the arrow next to HelloWorldPicnicRSDS.rsds, the shared data source you created earlier in this tutorial, and then click Create Power View Report.

From PowerPivot

• In the PowerPivot Gallery, click the Create Power View Report icon in the upper-right corner of the HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx band.

Start a report

Power View opens to a blank view. The field list on the right contains the tables you created in PowerPivot: Dates, Distributors, Quantities, and Items.

1. Expand the Items table.

   There are the fields from the table in PowerPivot.

2. Click the Items table name itself.

   Nothing happens. We’ll come back to this later.

3. Click the Name field.

   You now have a table with one column, the Name column.

   Note, though, that now all the other tables are grayed out, unavailable. Why?

In PowerPivot: Create relationships

The other tables are grayed out because there are no relationships between the tables in the model.

To create relationships in PowerPivot

1. In the PowerPivot Window on the Home tab, click Diagram View.

   You see the tables in your model, with no relationships between them.

2. Drag the tables so you can see them all in the window. Place the Quantities table in the center, as it is central to the model.

3. Drag from the ItemID field in the Items table to the ItemID field in the Quantities table.

   PowerPivot creates a one-to-many relationship between the two tables. It doesn’t matter which way you drag. The one and many sides of the relationship are the same. For more information about one-to-many relationships, see Guide to table relationships (from Microsoft Access Help).

4. Drag these fields to create other relationships between:

   • The Date field in the Dates and Quantities tables.

   • The DistribID field in the Items and Distributors tables.

5. Save the Excel (XLSX) file, thus saving your model.

In Power View

When you make changes to the model, you need to refresh the report. There are two Refresh buttons to choose from:

• The Refresh button in the Power View Quick Access toolbar refreshes the data, if the data in the model has changed.

• The Refresh button in Internet Explorer refreshes the model, but deletes any changes you’ve made since you last saved.

1. Click the Power View Refresh button.

   Note that nothing changes.

2. Click the Internet Explorer Refresh button.

3. In this case we haven’t made any changes worth saving, so click Leave this page.

   The field list is still there but the view is blank.

4. Expand the Items table, and drag the Name field to the view.

   Note that the other tables are not grayed out. Note, too, that there is only one “apple” value.

5. Expand the Quantities table and drag the Qty Served field to the Name table.

   Now there are many “apple” values. The Qty Served values are not being aggregated.

6. Try scrolling down the list.

   Note that the scroll bar gets smaller as Power View retrieves more values. Power View retrieves values only as needed, to speed performance.

7. In the layout (lower) section of the field list, click the dropdown arrow next to the Qty Served field and then click Sum.

In PowerPivot: Set reporting properties

Time to set reporting properties to make reports based on this model work better.

1. In the PowerPivot Window on the File menu, click Switch to Advanced mode.

2. Go to the Items table.

3. On the Advanced tab, click Table Behavior.

4. For Row Identifier, click ItemID.

   Note that now the rest of the items in the dialog box are available. Until you have selected a row identifier, they are all grayed out.

5. For Keep Unique Rows, click Name.

   Thus, even if there are two identical values in this column in two different rows, Power View doesn't aggregate them if they have different ItemID values. For example, “apple”: There are two different “apple” values in the Name field. Power View no longer aggregates them, as it did earlier.

6. For Default Label, click Name.

7. Click OK.

8. Click Default Field Set.

9. Add Name, Category, and Color, and then click OK.

   These fields are the defaults that are added to a view in Power View if you click the table name rather than an individual field.

10. Save the Excel (XLSX) file to save your model.

In Power View: Create a table and cards with default values

1. Click the Internet Explorer Refresh button.

   Because you haven’t saved the report yet, you lose the few changes you have made.

2. Click Leave this page.

3. Expand the Items table.

   Note the icon next to the ItemID and Name fields indicating that they are unique fields and the default label for this table.

4. Drag the Name field to the view.

   Note that there are now two apple values.

5. Click the Undo button.

6. Click the Items table itself.

   This creates a table with the fields you identified as the default fields for this table: Name, Category, and Color.

7. With this table selected, click the dropdown arrow in the Visualizations Gallery and click Card.

   Note that the values from the Name field are displayed prominently, because Name is the default label field for the Items table.

8. Click someplace on the blank view so the cards are not selected.

9. In the fields (upper) section of the field list, expand the Distributors table.

   Note that it contains only the Distributor and DistID fields.

10. Save the Power View report someplace on the SharePoint site.

In PowerPivot: Consolidate tables and add images

We can consolidate the single field from the Distributors table into the Items table to clean up the model.

To create a calculated column

1. In the PowerPivot window in the Items table, right-click the Add Column column and click Insert Column.

2. Select the new column, right-click, click Rename, and rename it Distributors.

3. In the Excel formula box, type

   =RELATED(Distributors[Distributor Name])
   

   Note that the formula autocompletes as you type. You can use autocompletion to make sure you have the right syntax.

   The Distributor name is now in the Items table, so report creators no longer need the Distributors table or the DistID field in the Items table.

4. Right-click the DistID column, and then click Hide from Client Tools.

   This hides the column in Power View, but you can still see it in PowerPivot.

5. Right-click the Distributors table tab and click Hide from Customer Tools.

Add images to the model

Before you can add images to the model, download them from the Microsoft Download Center and save them to the SharePoint site. For more information, see the Before you start section.

1. Add a calculated column to the Items table and call it Image Name.

2. Paste in this formula:

   =IFERROR(IF(FIND("apple",[Name])>0, SWITCH(Left([Color],1),"b","applejuice","g",[Name]& "g","r",[Name],[Name]),[Name]),[Name])
   

   This column makes the image names correspond to the item names in the Name field.

3. Add another calculated column and name it Photo. Paste in this formula, and replace <servername> with your server name:

   ="http://<servername>/Shared Documents/HelloWorldPicnicSQL2012/Images/rs_crescent_picnic_image_" & [Image Name] & ".png"
   

   Each item has an associated photo. This formula concatenates the name in the Image Name column with the URL where the images are located, plus the .png file extension.

4. Add another calculated column and name it Drawing. Paste in this formula, and replace <servername> with your server name:

   ="http://<servername>/Shared Documents/HelloWorldPicnicSQL2012/Images/rs_crescent_picnic_drawing_" & [Image Name] & ".png"
   

   Each item has an associated drawing, too.

5. Add another calculated column and name it Category Drawing. Paste in this formula, and replace <servername> with your server name:

   ="http://<servername>/Shared Documents/HelloWorldPicnicSQL2012/Images/rs_crescent_picnic_drawing_" & [Category] & ".png"
   

   Each category has an associated drawing.

6. Add one more calculated column and name it Category Photo. Paste in this formula, and replace <servername> with your server name:

   ="http://<servername>/Shared Documents/HelloWorldPicnicSQL2012/Images/rs_crescent_picnic_image_" & [Category] & ".png"
   

   Each category has an associated photo, too.

7. Save the Excel (XLSX) file to save your model.

   Note that you are back in the Excel window, and the calculated columns don’t appear. They are only in PowerPivot.

Let’s see how it looks in Power View.

In Power View: Try to add images to a table

1. Click the Internet Explorer Refresh button.

   Because you have saved the report, you don’t lose the changes you have made.

   Note that there are only three tables now—the Distributors table is gone.

2. Expand the Items table.

   Note the new fields, including the Distributor field.

3. Drag the Photo column to the table on the view.

   Uh oh! That’s no photo. That’s just the URL. Back to the model.

In PowerPivot: Identify image URLs

1. Click the Items table, then the Advanced tab.

2. Check the Image URL box for all four URL fields.

3. Click Table Behavior.

4. Set the Default Image to Drawing, and then click OK.

5. Click Default Field Set, and add Drawing to the Default fields set.

6. Click OK.

7. Save the Excel (XLSX) file to save your model.

In Power View: Add images, dates, and numbers to a table

To add images

1. Click the Internet Explorer Refresh button.

   You lose the most recent changes you made.

2. Add the Drawing field to the table.

   There are the images!

Now let’s look at dates and numbers.

To add dates and numbers

1. Click the blank space in the view to start a new visualization.

2. Expand the Dates table and drag the Month Name field to the view.

3. From the Items table, add the Category field to the same table.

   Note that none of the charts in the Visualizations Gallery are available. A chart needs at least one aggregated value.

4. In the Quantities table, click the dropdown arrow next to the Qty Served field, and click Add to Table as Sum.

   Now that the table has a field with a sum, the charts are available.

   The Qty Served field contains whole numbers. Power View does not aggregate them by default because whole numbers might be in a field that shouldn’t be aggregated, such as a key field. You can change that default behavior in the model.

5. Click Line Chart.

   You should see:

   • Qty Served in Values.

   • Month Name in Axis.

   • Category in Series.

   If not, drag the fields to the right boxes.

   But something is wrong: The months start with April, August, and December, and end with September. They’re alphabetical, not chronological.

   Back to the model.

In PowerPivot: Default aggregates, dates, tooltips, and calculated measures and columns

Calculated columns calculate a result and store it for every row in a table. Calculated measures are calculated on the fly, depending on the context—where they are being calculated in a PivotTable or in a Power View report. For more about calculated columns and measures, see Build Formulas for Calculations.

To set month order

1. In the PowerPivot window, go to the Dates table.

2. On the Design tab, click Mark as Date Table.

3. Select the Date field and click OK.

   Note

   The values in the Date field must be unique.

4. Select the Month Name column, and on the Home tab, click Sort by Column and then Sort Month Name by the Month Number column.

5. Select the Month Number column, right-click, and click Hide from Client Tools.

6. Repeat steps 4 and 5 for the Day Name and Day Number fields.

To set default aggregates

1. In the PowerPivot window, go to the Quantities table.

2. Select the Qty Served, Qty Consumed, and Attendees columns.

3. On the Advanced tab, click Summarize by, then click Sum.

To set a description

• Right-click the Qty Served column, click Description, and type:

  How much we served.

  This text will appear as a tooltip over the Qty Served field in the fields (upper) section of the field list. You can add a tooltip to every table and field.

To create a calculated column

1. In the Quantities table, insert a column and rename it Leftovers. In the Excel formula box, type:

   =[Qty Served]-[Qty Consumed]
   

   PowerPivot autocompletes the column names as you type.

2. Select the Leftovers column and on the Advanced tab, click Summarize by, then click Sum.

To create a calculated measure

1. Still in the Quantities table, click in any cell in the lower part of the table, below the dividing line.

   This area is called the measure grid. You can hide or show it with a button on the Home tab. You can put measures in any cell in any column.

2. Paste the quantity served year-to-date formula in the Excel formula box:

   Qty Served YTD:=TOTALYTD(SUM([Qty Served]),Dates[Date])
   

3. Save the Excel (XLSX) file to save your model.

In Power View: Create a bubble chart

1. Click the Internet Explorer Refresh button.

   What has changed?

   • The line chart refreshes to put the month names in order.

   • The Quantities table in the field list has new numeric fields such as Qty Served, with little calculator symbol next to them. The Qty Served field now appears two times—one is aggregated as a measure, and the other isn’t aggregated. You could hide the unaggregated column in the model.

   • Also in the Quantities table, the new Qty Served YTD measure has the little calculator symbol next to it.

2. In the fields section of the field list, hover over the Qty Served field.

   The tooltip appears.

3. Create a table with the Name, Qty Served, Qty Served YTD, and Leftovers fields.

4. In the Visualizations gallery, click Scatter chart.

   Make sure the Qty Served YTD field is in the X Value box.

5. Drag the Month Name field from the fields section of the field list to the Play Axis box in the layout (lower) section of the field list.

6. Click the play arrow.

   Note that the bubbles gradually move from left to right, as the Qty Served YTD values increase.

In Excel: Validate the model

You can also validate that your model has published correctly by creating and testing a slicer on your data in Excel. You save your XLSX file to the SharePoint site, which you have already done. You create a PivotTable and add slicers to it in Excel. In the XLSX file on the SharePoint site, you click a slicer, and it sends a query that tests the data connection. If the slicer is successful, that proves that the embedded PowerPivot model has been successfully deployed and that the credentials are configured correctly.

1. In the PowerPivot window, on the PowerPivot tab, click PivotChart, then New Worksheet.

   Note that the new worksheet displays the PowerPivot Field List on the right.

2. From the Dates table, drag Month Name to Slicers Horizontal.

3. From the Items table, drag Color to Slicers Vertical, and Type to Slicers Horizontal.

4. From these tables:

   1. Items table: drag Serve to Legend Fields.

   2. Items table: drag Category to Axis Fields.

   3. Quantities table: drag Leftovers to Values (note that it is automatically aggregated as a Sum).

5. Right-click the chart and click Change Chart Type.

6. Click Stacked Column.

7. Click the different values in the slicers to filter the chart.

The slicers work, so the PowerPivot model embedded in the Excel workbook is successfully deployed and the credentials are configured correctly.

Summary

You’ve seen how you started with raw data, and then:

• Created relationships.

• Set reporting properties, identifying:

  • Unique values.

  • Default labels.

  • Default images.

  • Default fields in a table.

• Added calculated columns and a measure.

• Consolidated tables.

• Set number format.

• Bound date name fields to number fields for order.

• Brought in images.

And you’ve seen how those changes changed the way the data appeared in the report.

Next Steps

Now that you’ve completed this tutorial, you can use the model you have created to try Tutorial: Create a Sample Report in Power View in Power View Samples for SQL Server 2012 and SQL Server 2012 Samples Readme.

See Also

Concepts

Power View (SSRS)