Analyzing Data with Power BI and Power Pivot for Excel, First Edition

Analyzing Data with Power BI and Power Pivot for Excel, First Edition

by Alberto Ferrari, Marco Russo
Released April 2017
Publisher(s): Microsoft Press
ISBN: 9781509302833

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Renowned DAX experts Alberto Ferrari and Marco Russo teach you how to design data models for maximum efficiency and effectiveness.

How can you use Excel and Power BI to gain real insights into your information? As you examine your data, how do you write a formula that provides the numbers you need? The answers to both of these questions lie with the data model. This book introduces the basic techniques for shaping data models in Excel and Power BI. It’s meant for readers who are new to data modeling as well as for experienced data modelers looking for tips from the experts. If you want to use Power BI or Excel to analyze data, the many real-world examples in this book will help you look at your reports in a different way—like experienced data modelers do. As you’ll soon see, with the right data model, the correct answer is always a simple one!

By reading this book, you will:

• Gain an understanding of the basics of data modeling, including tables, relationships, and keys

• Familiarize yourself with star schemas, snowflakes, and common modeling techniques

• Learn the importance of granularity

• Discover how to use multiple fact tables, like sales and purchases, in a complex data model

• Manage calendar-related calculations by using date tables

• Track historical attributes, like previous addresses of customers or manager assignments

• Use snapshots to compute quantity on hand

• Work with multiple currencies in the most efficient way

• Analyze events that have durations, including overlapping durations

• Learn what data model you need to answer your specific business questions

About This Book

• For Excel and Power BI users who want to exploit the full power of their favorite tools

• For BI professionals seeking new ideas for modeling data

Table of contents

  1. Title Page
  2. Copyright Page
  3. Contents at a glance
  4. Contents
  5. Introduction
    1. Who this book is for
    2. Assumptions about you
    3. Organization of this book
    4. Conventions
    5. About the companion content
    6. Acknowledgments
    7. Errata and book support
    8. We want to hear from you
    9. Stay in touch
  6. Chapter 1. Introduction to data modeling
    1. Working with a single table
    2. Introducing the data model
    3. Introducing star schemas
    4. Understanding the importance of naming objects
    5. Conclusions
  7. Chapter 2. Using header/detail tables
    1. Introducing header/detail
    2. Aggregating values from the header
    3. Flattening header/detail
    4. Conclusions
  8. Chapter 3. Using multiple fact tables
    1. Using denormalized fact tables
    2. Filtering across dimensions
    3. Understanding model ambiguity
    4. Using orders and invoices
      1. Calculating the total invoiced for the customer
      2. Calculating the number of invoices that include the given order of the given customer
      3. Calculating the amount of the order, if invoiced
    5. Conclusions
  9. Chapter 4. Working with date and time
    1. Creating a date dimension
    2. Understanding automatic time dimensions
      1. Automatic time grouping in Excel
      2. Automatic time grouping in Power BI Desktop
    3. Using multiple date dimensions
    4. Handling date and time
    5. Time-intelligence calculations
    6. Handling fiscal calendars
    7. Computing with working days
      1. Working days in a single country or region
      2. Working with multiple countries or regions
    8. Handling special periods of the year
      1. Using non-overlapping periods
      2. Periods relative to today
      3. Using overlapping periods
    9. Working with weekly calendars
    10. Conclusions
  10. Chapter 5. Tracking historical attributes
    1. Introducing slowly changing dimensions
    2. Using slowly changing dimensions
    3. Loading slowly changing dimensions
      1. Fixing granularity in the dimension
      2. Fixing granularity in the fact table
    4. Rapidly changing dimensions
    5. Choosing the right modeling technique
    6. Conclusions
  11. Chapter 6. Using snapshots
    1. Using data that you cannot aggregate over time
    2. Aggregating snapshots
    3. Understanding derived snapshots
    4. Understanding the transition matrix
    5. Conclusions
  12. Chapter 7. Analyzing date and time intervals
    1. Introduction to temporal data
    2. Aggregating with simple intervals
    3. Intervals crossing dates
    4. Modeling working shifts and time shifting
    5. Analyzing active events
    6. Mixing different durations
    7. Conclusions
  13. Chapter 8. Many-to-many relationships
    1. Introducing many-to-many relationships
      1. Understanding the bidirectional pattern
      2. Understanding non-additivity
    2. Cascading many-to-many
    3. Temporal many-to-many
      1. Reallocating factors and percentages
      2. Materializing many-to-many
    4. Using the fact tables as a bridge
      1. Performance considerations
    5. Conclusions
  14. Chapter 9. Working with different granularity
    1. Introduction to granularity
    2. Relationships at different granularity
      1. Analyzing budget data
      2. Using DAX code to move filters
      3. Filtering through relationships
      4. Hiding values at the wrong granularity
      5. Allocating values at a higher granularity
    3. Conclusions
  15. Chapter 10. Segmentation data models
    1. Computing multiple-column relationships
    2. Computing static segmentation
    3. Using dynamic segmentation
    4. Understanding the power of calculated columns: ABC analysis
    5. Conclusions
  16. Chapter 11. Working with multiple currencies
    1. Understanding different scenarios
    2. Multiple source currencies, single reporting currency
    3. Single source currency, multiple reporting currencies
    4. Multiple source currencies, multiple reporting currencies
    5. Conclusions
  17. Appendix A. Data modeling 101
    1. Tables
    2. Data types
    3. Relationships
    4. Filtering and cross-filtering
    5. Different types of models
      1. Star schema
      2. Snowflake schema
      3. Models with bridge tables
    6. Measures and additivity
      1. Additive measures
      2. Non-additive measures
      3. Semi-additive measures
  18. Index
  19. Code Snippets

Product information

  • Title: Analyzing Data with Power BI and Power Pivot for Excel, First Edition
  • Author(s): Alberto Ferrari, Marco Russo
  • Release date: April 2017
  • Publisher(s): Microsoft Press
  • ISBN: 9781509302833