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.
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.
Start from an existing model within PowerPivot.
Ensure that you are in the PowerPivot window (not Excel), then click on the PivotTable button in the Home Tab.
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.
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.
Return to the PowerPivot application window and double-click on the
Table1tab. The nameTable1will be in a blue background and rename it toProducts. Repeat this exercise for the other tables (Subcategory,Category,Dates, andSales). The table names in your PowerPivot window will now look like the following screenshot:
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
Return to the PowerPivot application window and select the
Productstable. Double-click on theproduct_idfield and enterProduct IDas the new name. The field will have a blue background when its name can be changed.
Return to the PowerPivot window and update the remaining columns to the following names:
Table
Column
New name
Productsproduct_idProduct IDProductsproduct_nameProduct NameProductscolourColourProductssize_rangeSize RangeProductssizeSizeSubcategorysubcategory_nameSubcategoryCategorycategory_nameCategoryDatesdateDayDatesyearYearDatesmonth_nameMonthDateshalf_nameHalfSalesorder_numberSO NumberCustomerscustomer_idCustomer IDCustomerscustomer_nameCustomer NameCustomerscountry_codeCountry CodeCustomersstate_codeState CodeReturn to Excel and refresh PowerPivot Field List. The column names will now display as those that were entered.
Return to the PowerPivot window and select the
Salestable. Right-click anywhere on theorder_number_linefield and select Hide from Client Tools in the pop-up window. Select all the fieldsproduct_id,order_date, andcustomer_idby 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.
Return to the PowerPivot window, refresh PowerPivot Field List, and expand the
Salestable. Note that these fields no longer appear in the field list.Add the
Dayfield to the pivot by expanding theDatestable and selecting the checkbox next to theDayfield. The column will be automatically added to the rows area of the pivot and will be displayed, as shown in the following screenshot:
Return to the PowerPivot window and select the
Daycolumn. 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.
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.
In the
Salestable, format the columnsunit_price,unit_cost,tax, andtotal_priceas a whole numeric number by selecting the columns and choosing Currency from the Format drop-down list.
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 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.