Book Image

Microsoft Tabular Modeling Cookbook

By : Paul te Braak
Book Image

Microsoft Tabular Modeling Cookbook

By: Paul te Braak

Overview of this book

Business Intelligence Semantic Models (BISM) is a technology that is designed to deliver analytical information to users through a variety of mechanisms that include model structure, definition, and design. This book demonstrates how to create BISM models so that information can be presented to users in an intuitive and easy-to-use format. Once the model is defined, we also show you how it can be managed and maintained so that the data in it remains current and secure. Microsoft Tabular Modeling Cookbook is an all-encompassing guide to developing, managing, creating, and using analytical models using the Business Intelligence Semantic Model (BISM). This title covers a range of modeling situations and common data analysis related problems to show you the techniques required to turn data into information using tabular modeling. Microsoft Tabular Modeling Cookbook examines three areas of tabular modeling: model development, model management and maintenance, and reporting. This book is a practical guide on how to develop semantic models and turn business data into information. It covers all phases of the model lifecycle from creation to administration and finally reporting. It also shows you how to create models which are designed to analyze data. All sections of BISM modeling from development to management and finally reporting are covered. The sections on development examine a wide range of techniques and tricks required to build models, including moving data into the model, structuring the model to manipulate the data, and finally the formulas required to answer common business questions; all of these are discussed in this book in detail. Finally, the book examines methods of reporting on the data within the model, including the creation of data-driven workbooks and reports for a powerful end user experience.
Table of Contents (18 chapters)
Microsoft Tabular Modeling Cookbook
About the Author
About the Reviewers

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.



  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:



    New name



    Product ID



    Product Name






    Size Range
























    SO Number



    Customer ID



    Customer Name



    Country Code



    State Code

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


    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.


    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:


    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.