When authoring a tabular model, there are certain properties that you can set on individual columns and tables to enhance the end-user reporting experience in Power View. In addition, you can create additional model data to support data visualization and other features specific to the reporting client. For the sample Adventure Works Internet Sales Model, here are some of the changes you will make:

• Add new data – Adding new data in a calculated column by using a DAX formula creates date information in a format that is easier to display in charts.
• Hide tables and columns that are not useful to the end user - The Hidden property controls whether tables and table columns are displayed in the reporting client. Items with that are hidden are still part of the model and remain available for queries and calculations.
• Enable one-click tables – By default, no action occurs if an end-user clicks a table in the field list. To change this behavior so that a click on the table adds the table to the report, you will set Default Field Set on each column that you want to include in the table. This property is set on the table columns that end users will most likely want to use.
• Set grouping where needed - The Keep Unique Rows property determines if the values in the column should be grouped by values in a different field, such as an identifier field. For columns that contain duplicate values such as Customer Name (for example, multiple customers named John Smith), it is important to group (keep unique rows) on the Row Identifier field in order to provide your end users with the correct results.
• Set data types and data formats - By default, Power View applies rules based on column data type to determine whether the field can be used as a measure. Because each data visualization in Power View also has rules about where measures and non-measures can be placed, it is important to set the data type in the model, or override the default, to achieve the behavior you want for your end-user.
• Set the Sort by Column property – The Sort By Column property specifies if the values in the column should be sorted by values in a different field. For example, on the Month Calendar column that contains the month name, sort by the column Month Number.

Because there is already a Product Category calculated column and Product Subcategory calculated column in the Product table, it is not necessary to have the Product Category and Product Subcategory tables visible to client applications.

Sometimes it may be necessary to create new data in your model by using DAX formulas. In this task, you will add two new calculated columns to the Date table. These new columns will provide date fields in a format convenient for use in charts.

The Default Field Set is a predefined list of columns and measures for a table that are automatically added to the Power View report canvas when the table is clicked on in the report field list. Essentially, you can specify the default columns, measures, and field ordering users will want to see when this table is visualized in Power View reports. For the Internet Sales model, you will define a default field set and order for the Customer, Geography, and Product tables. Included are only those most common columns that users will want to see when analyzing Adventure Works Internet Sales data by using Power View reports.

Default Field Set dialog box

For detailed information about Default Field Set, see Configure Default Field Set for Power View Reports (SSAS Tabular) in SQL Server Books Online.

By using Table Behavior properties, you can change the default behavior for different visualization types and grouping behavior for tables used in Power View reports. This allows better default placement of identifying information such as names, images, or titles in tile, card, and chart layouts.

Table Behavior dialog box

For detailed information about Table Behavior properties, see Configure Table Behavior Properties for Power View Reports (SSAS Tabular) in SQL Server Books Online.

There are a number of basic column properties and specific reporting properties on columns you can set to improve the model reporting experience. For example, it may not be necessary for users to see every column in every table. Just as you hid the Product Category and Product Subcategory tables earlier, by using a column’s Hidden property, you can hide particular columns from a table that is otherwise shown. Other properties, such as Data Format and Sort by Column, can also affect how column data can appear in reports. You will set some of those on particular columns now. Other columns require no action, and are not shown below.

You will only set a few different column properties here, but there are many others. For more detailed information about column reporting properties, see Column Properties (SSAS Tabular) in SQL Server Books Online.

Because you have changed the model, you must re-deploy it. You will essentially repeat the tasks performed in [[Lesson 14: Deploy]].

You can now use Power View to visualize data from the model. Ensure the Analysis Services and Reporting Services accounts on the SharePoint site have read permissions to the Analysis Services instance where you deployed your model.

To create a Reporting Services report data source that points to your model, see Table Model Connection Type (SSRS).