Book Image

Microsoft Tabular Modeling Cookbook

By : te Braak
Book Image

Microsoft Tabular Modeling Cookbook

By: 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 (13 chapters)
12
Index

Introduction

This chapter is designed as an introduction to tabular modeling by using PowerPivot. It shows the process by which a user imports data into PowerPivot for Excel, creates relationships between the datasets, and then reports on it.

The data used in this chapter is based on the orders of the fictitious bicycle company (named Adventure Works). Our data includes six datasets and they are:

  • Product list: This shows some generic information about the products being sold (for example, the name, color, and size of the product). Each product is identified by a product_id value.
  • Product subcategories list: This shows a subcategory that a product belongs to. The list shows the product_id value and the associated subcategory (by ID and Name).
  • Product categories list: This shows the product category that a product belongs to. The list shows the product_id value and the associated category (by ID and Name).
  • Orders list: This shows what orders have been placed by customers. The list includes an entry for each product that has been ordered. This data simulates a detailed extract from an operational source system.
  • Customer list: This gives us information about the customer (for example, their names, countries, and states) by customer number.
  • Dates list: This simply lists consecutive days defining information such as the month name, year, and half-year period of the date.

The tabular modeling lifecycle revolves around three primary steps. These are:

  • Getting the data into the model
  • Defining the relationships among tables
  • Defining calculations based on business logic

This chapter examines these steps and allows the reader to become familiar with the tabular (PowerPivot) design environment.