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

Linking fields between tables

There may be a requirement to create fields in a table that contain data from a separate table. In Excel, this would usually be achieved with a VLOOKUP function.

The sales model that has been developed in this chapter contains three tables which define Products, Subcategory, and Category. When the user browses the model in a pivot table, each of these tables appear as tables in the PowerPivot Field List pane. However, in this model, the category and subcategory directly relate to the product and it is our intent to show these fields in the Products table.

Getting ready

This recipe assumes that the sales model created in the Adding fields to tables recipe is available and that the appropriate relationships exist among the Product, Subcategory, and Category tables.

How to do it…

Start by opening the PowerPivot window and then perform the following steps:

  1. Switch to the data view and create two new columns in the Products table titled Category and Subcategory. In the Category column enter the following formula:

  2. In the Subcategory column enter the following formula:

      , Subcategory[product_id],Products[Product ID]


    Formulas can be multiline (just like in Excel). To move to the next line when typing simply press Alt + Enter.

Hide the Subcategory and Category tables in the model by right-clicking on the tables tab and selecting Hide from Client Tools from the pop-up menu. Note that the hidden tables are still visible in the data view and diagram view, although they are now more transparent.

How it works…

These two formulas achieve the same result but in different ways.

The related function returns the specified column, based on the relationship within the data model. This can span more than one table (for example, a related table to the Category table could be referenced from the Products table), however, a relationship must be defined between all the linking tables that are spanned by the formula. Furthermore, because the formula relies on these relationships (that is, those defined within the model), the formula will not result in an error since the model enforces the integrity defined by model relationships.

The LOOKUPVALUE function is quite different from the related function because it does not utilize or rely on a relationship within the model. That is, LOOKUPVALUE would still return the same results had the relationship not be defined between the Products and Subcategory tables. Furthermore, the LOOKUPVALUE function can use multiple columns as its reference (to lookup) which may be beneficial when a desired value in another table cannot be related to the source data through a single field. Note that relationships can only be defined on single columns. However, unlike the RELATED function, the LOOKUPVALUE function may return an error when more than one match can be found in the lookup table.

Both formulas return results by creating a row context filter for each row in the source table.

It is considered best to utilize the relationship wherever possible. Therefore, the use of the RELATED function is preferred over the LOOKUPVALUE function. Furthermore, the RELATED function makes the model simpler for others to understand. However, the LOOKUPVALUE function does have some benefits. It allows the value to be determined, based on multiple search conditions. The syntax for LOOKUPVALUE is defined as:

LOOKUPVALUE( <result_columnName>
  , <search_columnName>, <search_value>
  [, <search_columnName>, <search_value>]

Here, a result_columnName column is returned from a target table where search conditions are satisfied. These conditions are defined by a search_columnName parameter and a search_value parameter. This means that we specify the column (in the lookup table) and the value that should be searched for—this is the field in the current table.