Checklist for Knowing Your PowerPivot Model is Complete

Overview: This post includes a suggested list of items to be addressed by the data modeler before publishing a PowerPivot model for other users to consume.

If you are creating a PowerPivot model for others to use for reporting, then I firmly believe you have a responsibility. (Think Spiderman here just a wee bit.) This responsibility is to make sure the data model is tidy, easy to use, and useful for its intended purpose. Thus the inspiration for the following checklist of things to address before considering a PowerPivot model complete.

Tables and Relationships

To Do




Import Minimum # of Fields

Design Ribbon > Table Properties

Importing only the fields that are needed for the data model not only simplifies the model, but it reduces the size which is important considering it’s all in memory. Sometimes you don’t always know all the specifics when the data is imported initially, so you may have to revisit this while finalizing the model to make sure you’re satisfied with what is and isn’t being imported.


Friendly Name for Tables

Design Ribbon > Table Properties
Right-Click Table (Worksheet) Name

A table name like “Students” is nicer to look at within a field list than something like “TblStdt.” It goes without saying that this name should represent the fields contained within it. If you know there’s a granularity issue (like it’s a snapshot set of numeric fields rather than at the detail level), that’s a great piece of information to make part of the table name.


Date Table

Design Ribbon > Mark As Date Table

By marking a date table, additional date-oriented filters and calculations are available. Most models will have a date table.



Diagram View

Depending on the source of data, relationships may or may not be created when the model is initially set up. One of the most important things the data modeler needs to do is verify that all required relationships have been created and are accurate.



To Do




Hide Fields

Data View > Right-click column >
Hide from Client Tools
Diagram View > Right-click field >
Hide from Client Tools

Hiding fields, such as ID fields needed to join tables but not useful for reporting, helps simplify the data model because there’s less fields shown in the field list. The consumers of your data model will appreciate the lack of what they’d view as clutter.
One caveat: If a field is not needed for something – a relationship, basis for a calculation, something – then don’t import it at all (see #1 above).
Second caveat: Hiding of fields isn’t a security mechanism; rather, it’s meant for ease of use and simplification. If you have conditions when you want to hide fields, consider using a Perspective instead.


Friendly Name for Fields

Data View > Right-click column > Rename
Diagram View > Right-click field > Rename

A field such as “Student Name” is nicer to look at for reporting than something like “Stdt_nm” which may be how it’s stored in the source database. Source systems often have abbreviated naming conventions which is not necessarily the ideal name for display on reports. Since field names are used by default as column titles, friendly names are well worth a bit of time investment. You can also use them for self-documenting the model.


Formatting of Fields

Home Ribbon > Data Type
Home Ribbon > Format
(for stored & calculated columns)

Data View > Calculation Pane
(for calculated measures)

It’s no fun to add a field onto a report that needs to be reformatted every single time. Defining units as whole numbers, or amounts as currency, is a helpful timesaver on the reporting end.


Sorting of Fields

Home Ribbon > Sort
Home Ribbon > Sort By Column

Creating a default sort order is a common need for certain types of fields, such as dates. If needed, you have the flexibility to sort one column by another column if you need to - for example, you could sort the Month Name field by the Month Number field.


Aggregation Behavior

Advanced Ribbon > Summarize By

The aggregation default is sum, but this doesn’t suit all fields. Some fields are more appropriate to be set to choices such as count, min, or max. For example, high temperature per day would never be added together for a meaningful number; rather, its average is likely a better choice for aggregation. Setting this properly allows subtotals and totals to be presented properly.


Calculated Columns

Data View > Add Column

Creation of calculated columns (aka derived fields) is useful to enrich the data model when the stored fields aren’t sufficient. A very simple example of this is names – perhaps the underlying data source keeps First Name and Last Name in separate fields; you may wish to derive a Full Name field for reporting purposes which concatenates them together. Neither Power View nor Excel permits calculated columns on-the-fly, so having these set up in the data model helps usability significantly.


Calculated Measures

Data View > Calculation Area
Excel window > PowerPivot Ribbon > New Measure

Creation of calculated measures (aka explicit measures) is extremely useful to augment reporting and analysis. Calculated measures rely upon “context” so they may be calculated on aggregated data sliced a certain way versus a calculated column (from #10 above) that is calculated for every row regardless of context.
A reporting tool like Power View doesn’t permit calculations on-the-fly, so having a good set of calculations available helps the usability factor tremendously. Try not go overboard with every possible calculation anyone could need, but start out with the most logical & useful calculations.


Power View Optimization

Within the PowerPivot model, there are several things which can significantly enhance the reporting within Power View. These settings don’t have an effect within Excel.

To Do




Default Field Set

Advanced Ribbon > Default Field Set

Selection of the default fields, as well as the order they will be displayed initially, defines which fields will be automatically added to a Power View report when a table is added to a view.


Table Behavior

Advanced Ribbon > Table Behavior

Grouping behavior within Power View is affected by the unique values specified. Also, default labels and images used in a Power View (such as in a Card) may also be included.



Advanced Ribbon > Table Behavior
Advanced Ribbon > Image URL

For images to be displayed in Power View (whether they use a URL pointing to a SharePoint document library or are embedded within the PowerPivot model), several settings need to be specified.


Field Descriptions

Data View > Right-Click column > Description

A description can help users understand the contents of a field. When a description has been defined, it is shown in the Power View field list as a tooltip when the mouse hovers on the field name.



To Do





Diagram View > Right-Click Field > Create Hierarchy

Diagram View > Right-Click Field > Add to Hierarchy

Date fields (such as Year>Quarter>Month) and geography fields (such as Country>State>City) are great candidates for hierarchies. After a field has been added to a hierarchy, then it’s up to you whether the individual fields are still visible and available for reporting (sometimes users find having the individual fields & the hierarchy fields listed as confusing, so you may want to opt for showing one or the other for simplicity).


Key Performance Indicators

Data View > Calculation Area >
Right-click calculated measure >
Create KPI

Creation of KPIs can be a great enhancement to reporting because they facilitate visual indicators for how a value compares to its target (often seen as red/yellow/green). KPIs is often a really popular enhancement to a data model.



Advanced Ribbon > Perspectives

If multiple subject areas are contained within a PowerPivot model, creation of a Perspective may enhance the usability of the model because each Perspective contains a subset of the entire model. This solves the problem of wanting to hide Fields A, B and D when doing a certain type of reporting, yet show them in another situation.


File Name

File > Save As

When saving the file, you may wish to include the word “Model” in the name. For example, “Sales Forecast Model” lets users know that within this xlsx file is a PowerPivot model. This helps when viewing the PowerPivot Gallery SharePoint, which contains both models and reports.



Excel worksheet
SharePoint metadata fields

Depending on the complexity level of the data model, and how well versed users are with the data, you might consider using an Excel worksheet as a place for documentation about the underlying model.


Testing and Validation

  • Excel Data Visualization. Even if users will be developing their own independent Excel & Power View reports from your data model, they may find an Excel worksheet of data visualization to be very helpful to get started reporting on the underlying data model.
  • Excel Testing. Test the data model using Excel as the reporting tool. Because the data model and Excel data visualization are linked together via the Excel file, it’s very easy to iteratively go back and forth between windows to finalize the data model.
  • Power View Testing. Test the data model using Power View as the reporting tool (if you have SharePoint 2010/2013 and Power View available in your environment, or a business edition of Excel 2013).

The above list might look like a lot, but once you’re in a rhythm it’s really not bad. Many of the items above are cases where a little work upfront pays off over and over on the reporting end. And, you’ll be a hero among your coworkers if you make a little extra effort in your data models.

See anything I missed? Leave me a comment & I’ll be sure to add it!