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	Where	Why
1	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.
2	Friendly Name for Tables	Design Ribbon > Table Properties or 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.
3	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.
4	Relationships	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.

Fields

	To Do	Where	Why
5	Hide Fields	Data View > Right-click column > Hide from Client Tools or 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.
6	Friendly Name for Fields	Data View > Right-click column > Rename or 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.
7	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.
8	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.
9	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.
10	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.
11	Calculated Measures	Data View > Calculation Area or 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	Where	Why
12	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.
13	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.
14	Images	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.
15	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.

Other

	To Do	Where	Why
16	Hierarchies	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).
17	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.
18	Perspectives	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.
19	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.
20	Documentation	Excel worksheet or 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!