Managing the appearance of tables and fields

A PowerPivot workbook contains two products that allow the user to analyze data. Firstly, there is the xVelocity in-memory analytics engine (the tabular model) which is a columnar database embedded in the workbook. Secondly, there is a client tool that allows the model to be queried, it also displays the results to the user in the form of a pivot table or pivot chart. In Excel 2010, the client tool was restricted to pivot table functionality (for example, a pivot table or pivot chart). In Excel 2013, the tools set has been extended to include Power View. The important distinction here is that the client tool is used to present the model to the user. This recipe shows how to control the way the model is presented to the user.

Getting ready

This recipe uses the model that has already been created in the prior recipe Creating the model. If this model has not been created, follow the recipe to ensure that the model has been loaded with data.

How to do it…

Start from an existing model within PowerPivot.

1. Ensure that you are in the PowerPivot window (not Excel), then click on the PivotTable button in the Home Tab.
   
2. PowerPivot will switch back to the Excel window and a dialog will prompt for the location of the new pivot table. Select New Worksheet and click on OK.
   
3. Excel will now show PowerPivot Field List and a pivot table work area. These are identified by the arrows in the following screenshot. Note that PowerPivot Field List shows tables from the model as nodes, with the fields from the model as children.
   
4. Return to the PowerPivot application window and double-click on the Table1 tab. The name Table1 will be in a blue background and rename it to Products. Repeat this exercise for the other tables (Subcategory, Category, Dates, and Sales). The table names in your PowerPivot window will now look like the following screenshot:
   
5. Return to Excel and notice that PowerPivot Field List has detected a change in the model and prompts the user to refresh. Click on the Refresh button and note that the changes in the names of the tables are now reflected in the PowerPivot Field List panel.

   Before	After

6. Return to the PowerPivot application window and select the Products table. Double-click on the product_id field and enter Product ID as the new name. The field will have a blue background when its name can be changed.
   
7. Return to the PowerPivot window and update the remaining columns to the following names:

   Table	Column	New name
   Products	product_id	Product ID
   Products	product_name	Product Name
   Products	colour	Colour
   Products	size_range	Size Range
   Products	size	Size
   Subcategory	subcategory_name	Subcategory
   Category	category_name	Category
   Dates	date	Day
   Dates	year	Year
   Dates	month_name	Month
   Dates	half_name	Half
   Sales	order_number	SO Number
   Customers	customer_id	Customer ID
   Customers	customer_name	Customer Name
   Customers	country_code	Country Code
   Customers	state_code	State Code

8. Return to Excel and refresh PowerPivot Field List. The column names will now display as those that were entered.

   Tip

   You can also rename fields by right-clicking on the field and selecting Rename Column from the pop-up in PowerPivot. Alternatively, you can double-click on the field name (so that it changes the color of the field) and rename it.

9. Return to the PowerPivot window and select the Sales table. Right-click anywhere on the order_number_line field and select Hide from Client Tools in the pop-up window. Select all the fields product_id, order_date, and customer_id by clicking-and-dragging the mouse across the three fields and hide these fields too.

   Tip

   PowerPivot mimics Excel in the way that you can select multiple fields by dragging your mouse across several columns (with the left button continually pressed). You can also select the first column, hold the Shift key, and select the final column.

   Unlike Excel, multiple columns cannot be selected by using the Ctrl key and selecting multiple fields.

10. Return to the PowerPivot window, refresh PowerPivot Field List, and expand the Sales table. Note that these fields no longer appear in the field list.
11. Add the Day field to the pivot by expanding the Dates table and selecting the checkbox next to the Day field. The column will be automatically added to the rows area of the pivot and will be displayed, as shown in the following screenshot:
    

    Tip

    You can achieve the same result by dragging the Day field and dropping it in the Row Labels area of the pivot.

12. Return to the PowerPivot window and select the Day column. From the format list, select More Dates Formats…, and then select the dd-MMM-yy format from the list of available formats. The value presented will show a formatted sample of the data. Choose the item that shows 14-Mar-01.
    
13. Return to Excel and refresh the pivot table. Note that the PowerPivot Field List panel may not indicate the change to the model. However, when the pivot table is refreshed, the data displays the new format.
    
14. In the Sales table, format the columns unit_price, unit_cost, tax, and total_price as a whole numeric number by selecting the columns and choosing Currency from the Format drop-down list.

How it works…

The semantic model defines the metadata structure of the model and includes information such as table names, column names, and data presentation formats. The model designer interacts with the semantic model through its presentation layer in a real-time manner (note that the model did not have to be deployed to a server), so that the changes made in the model are immediately available to the user.

The modeling environment behaves in a What You See Is What You Get (WYSIWYG) manner which means that any changes made to the design environment are reflected in the model that is presented to the user.

There's more…

There are two methods that the model designer can use to examine the structure of the model. So far, we have only examined the data view. The diagram view shows all tables and columns (including hierarchies) that are used within the model and presents them on a design surface. This is shown in the next recipe.