-
Book Overview & Buying
-
Table Of Contents
Microsoft Tabular Modeling Cookbook
By :
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.



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:
|
Before |
After |
|---|---|
![]() |
![]() |
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.
|
Table |
Column |
New name |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
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.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.
Sales table. Note that these fields no longer appear in the field list.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:
You can achieve the same result by dragging the Day field and dropping it in the Row Labels area of the pivot.
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.

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.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.
Change the font size
Change margin width
Change background colour

