Power Pivot or SSAS Tabular data model optimization is important for Power View reporting usability and functionality.
In this posting, we will discuss the following topics:
X-Axis Guidelines
Data type and distribution
Utilize a number or date that increases uniformly (primarily for Power BI forecasting but we have seen Power View not normalize the data on the axis due to not being uniform). Strings are not supported for x-axis values when forecasting.
Power View will typically normalize the date values across the x-axis. Instead of displaying the date as a date, it will display a partial date (e.g. month year instead of day-month-year).
Advanced Properties
The Power Pivot add-in in Excel allows you to configure advanced properties on the Power Pivot model. These are configured on the Advanced tab of the Power Pivot model. This is where you configure:
- Perspectives (subsets of fields from the model typically defined for particular user groups to improve usability and navigation in large data sets)
- “Show Implicit Calculated Fields” option (used to show calculated fields that are automatically created when dragging and dropping a field into a Pivot Table that is not an explicitly created calculated field)
- “Summarize By” option for a column/field (i.e. sum/avg/min/max/etc.). If you have a field that you do not want to summarize, be sure to set to “Do not summarize” (i.e. Year/Zip or some other numeric column
- “Reporting Properties” that include the table behavior (details on table behavior below)
- “Data Category” (ability to define a column/field data type – i.e. geography, image, or URL)
- “Default Field Set” (set of columns/fields included by default when creating a new table on the Power View canvas and are added automatically when you click on the table. This is useful if fields need to be included together to make sense to the business user. The columns are displayed in the defined order but can be changed within each report)
Image Handling
Utilizing images in the Power View report should be done sparingly…and only if it is beneficial to the understanding or usefulness of the visualization / data. A few things to follow:
- Column Data Type
- In order to include the image in the data model itself (JPEG, GIFF or BMP format), the column data type should be binary.
- In order to save memory, you would include the URL string that points to the location of the image.
- Data Category
- Image: if the column contains the image, which again, must be the Binary column data type
- Image URL: if the column contains the URL pointing to the location of the image, which must be the String column data type
- Default Image
- See Table Behavior below
Should you utilize an image URL or store the image in memory? There are pros and cons to each:
| Pro | Con | |
| In Memory | Works w/o Internet Typically faster |
Consumes Memory (OK if Not Many Images) |
| URL | Less memory | Requires Internet Connection |
If you utilize URL(s), remember to utilize short url(s) if possible (i.e. use a URL shortening tool) as this will utilize less memory. URL(s) also require the http:// prefix but do not necessarily have to include the www prefix if DNS is configured appropriately.
Data Categories
Data Categories allow a method to add semantic knowledge about the content of a table’s column beyond purely its data type. For example, one can mark a column’s data type but marking columns as Year or Zip provides a little more description and allows Power View to treat the column appropriately.
Later, in Q&A, the data category helps Power BI choose the proper visual display for the column. For example, for geographical data categories, Q&A will suggest a map visualization. For dates, it may suggest a line chart or the play axis of a bubble chart.
Table Behavior
Power View acts like a typical Pivot Table. It will automatically group what is placed in the report field list and on the visualization you are utilizing. Typically, this will produce optimal results. However, there are times when a detail table does not need to be grouped at a higher level (where the individual records are of interest and the data does not need to be summarized at a higher granularity). In these scenarios, table behavior needs to be configured.
Row Identifier
Specifies a column that contains only unique values, allowing that column to be used as an internal grouping key. This should be set on the unique column in the table (i.e. the business key). Setting this enables other properties of table behavior such as “Keep Unique Rows”, “Default Label”, and “Default Image”. This cCn be expensive if configured for a fact table – see Optimize Tabular / PowerPivot Data Model.
Keep Unique Rows
Specifies which columns provide values that should be treated as unique even if they are duplicates (for example, employee first name and last name, for cases where two or more employees share the same name). This only works for Power View configuration and not a standard Pivot Table. You should be mindful of this as it could be misleading to the end users to show the same attribute more than once on a visualization.
Default Label
Specifies which column provides a display name to represent row data (for example, employee name in an employee record). This property specifies the field to use as a label that appears primarily in a card, or together with the default image in the navigation strip of a tile report. When used with a default image, the default label appears under the image.
Default Image
Specifies which column provides an image that represents the row data (for example, a photo ID in an employee record)…..again, primarily used in tiles and/or cards and is useful especially when there are multiple image columns in the table.
Field Behavior
Hierarchies
When including a field from a hierarchy, Power View will include all fields from the hierarchy by default. If you want to utilize a single field, ensure the hierarchy source fields are not hidden (may be listed under “More Fields”).
Sort By Field
For non linear date fields (i.e. month name / day of week), use the sort by field option to utilize a default sort order and Power View will sort these fields properly.
Conclusion
That wraps up the Power View for Power Pivot / Tabular data model best practices. You can also view the Power View for Multidimensional Best Practices.
View other Power View Best Practices!