THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

The new data modeling book for Power BI and Power Pivot users

In the last few years, I and Alberto Ferrari assisted many users of Power Pivot and Power BI who wanted to create their reports using these tools and were struggling with getting the desired numbers from their data. The first approach of an Excel user is to look for a function, or a more complex DAX expression, that can implement the calculation required. In a similar way, this is also the approach of Power BI users that don’t have a database design background.

Several times, before looking for a correct DAX expression, the problem is defining the correct data model, putting data in the right tables, and creating the proper relationships. The tools to create queries in M (Power Query in Excel, now called Get Data, and the Query Editor in Power BI) are wonderful to help the users in doing the proper massage to the data. However, these tools cannot help users that don’t know how to properly define the right data model. For this reason, when we thought to a new version of the book about Power Pivot (we previously wrote one for Excel 2013/2016 and one for Excel 2010) we considered that the DAX chapters were no longer necessary (because we have The Definitive Guide to DAX for that), so we decided to dedicate an entire book to the data modeling, targeted to business users that usually do not have such a skill, or that learned that by trial and errors and without a more structured approach.

Because the concepts are identical for Power Pivot and Power BI, we wrote a single book that target both products: Analyzing Data with Power BI and Power Pivot for Excel. Several examples are created in Power BI Desktop (because it is free and available to anyone), but certain reports are created using pivot tables in Excel. The goal is to teach the concepts, rather than providing formulas to copy and paste. In fact, the reader should try to apply the same ideas to its own data, recognizing the data modeling patterns described in the chapters of the book.

We tried to minimize the use of theoretical terms, trying to introduce with very practical examples and design patterns the terminology that is commonly used in data modeling (such as fact, dimensions, normalization, denormalization, star schema, snowflake schema, and so on). Thus, is this book for you?

  • If you are an advanced Excel user that adopted Power Pivot or Power BI, definitely yes.
  • If you are a BI developer, you should already know the theory, and this book could be useful to see practical examples using a model in these tools. Thus, it could be useful (well, several models are really easy to implement as design patterns), but you should not learn new concepts.
  • If you are a data analyst that moved to Power BI from other tools, then it depends on your background. You might be in the middle of the two cases described above, but in any case the book should be useful for you, too.

At this point, you might wonder why we did not include the words “data modeling” in the book title. The simple answer could be “marketing”, but the real answer is more complex. A business user hardly recognizes the need of data modeling skills. He/she just want to obtain a result, analyzing its own data. He/she might think that data modeling is something for DBAs, and is not related to reporting. However, when you create a report in Power Pivot and Power BI, usually you are creating a data model (unless you create a live connection to an existing Analysis Services database). For this reason, the title should help these users to find this book and to take look at its description. Yes, it is a book for data modeling targeted to readers that do not know that they need such a skill. Thus, the reason for this title is “communication”: We hope that the book description is clear enough to avoid any misunderstanding!

This is the table of contents of the book:

  • CHAPTER 1 – Introduction to data modeling
  • CHAPTER 2 – Using header/detail tables
  • CHAPTER 3 – Using multiple fact tables
  • CHAPTER 4 – Working with date and time
  • CHAPTER 5 – Tracking historical attributes
  • CHAPTER 6 – Using snapshots
  • CHAPTER 7 – Analyzing date and time intervals
  • CHAPTER 8 – Many-to-many relationships
  • CHAPTER 9 – Working with different granularity
  • CHAPTER 10 – Segmentation data models
  • CHAPTER 11 – Working with multiple currencies
  • APPENDIX A – Data modeling 101

Remember, when the data model is correct, the DAX code you need is simpler, shorter, and more efficient. The first rule is always “keep it simple”, and a good data model is always the first step in the right direction!

Published Monday, May 8, 2017 12:17 AM by Marco Russo (SQLBI)
Filed under: , ,



Brian Mather said:

Bought! Loving the clarity of the chapter on slowly changing dimensions.

May 8, 2017 4:15 PM

Anders Lundgren said:

I was a bit surprised when I got the book yesterday and realized that it wasn't about using Power BI or Power Pivot but more about data modelling. But I'm not disappointed. This is the difficult task in BI - defining and constructing an efficient data model to answer specifik business questions.

May 9, 2017 9:00 AM

Marco Russo (SQLBI) said:

@Brian: thanks!

@Anders: I hope you read the description before buying it, we tried to make it clear it is a book about data modeling dedicated in particular to those who don't know that they have a problem in data modeling! Let me know if you think the presentation of the book is misleading, thanks!

May 13, 2017 3:46 AM

Kirill Perian said:

Hi, Marco! Great idea! I think data modeling is a very important skill to acquire before diving head down into creating reports. Proper data models definitely  save you a lot of report design and measure construction time at the end.

May 17, 2017 2:10 PM

Marco Russo (SQLBI) said:

Thanks for the feedback!

May 20, 2017 5:19 AM

Data Imagineer said:


I have been studying all of your works since 2013. Including your YouTube videos and, of course, your prolific books. I have a desire to master what you know. I bought the book, "Analyzing Data with Microsoft Power BI and Power Pivot for Excel." In addition, I have your book, "The Definitive Guide to DAX: Business Intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI." I have several more books that you authored too. I find your style comprehensible, with the odd exception of a broken grammatical rule here and there. :) Nonetheless, your books are the tools in my arsenal to analyze data at a business-unit level. After all, Power BI is a self-service tool, so employees working on understanding their data can do so easily with the plethora of connectors that the May-2017 release of Power Bi has under the label, "Get Data." The books are great. The YouTube videos are great too! I have re-watched the one about Time Intelligence several times, just to ensure I fully understood. Your books are a developer's best friend.

Thanks, Marco!


Data Imagineer

Associated Materials Inc.

May 27, 2017 12:24 AM

Marco Russo (SQLBI) said:

@Donald - thank you very much for the nice words!

When you spot an error (even a grammatical one), please feel free to report it, so we can improve our writings. I hope you will not find these issues in the books... because they are processed by a deeper editorial review than articles and videos we publish on our web sites, but you never know...



May 28, 2017 6:38 PM

Chris Turnbull said:

Hi Marco

I have all your books and have signed up for some of your courses which are always top quality and great to follow.

This book is also fantastic and clears a lot of queries that you can have about Modeling.

I work in Education and come across some difficult modeling issues regarding relationships with Students, Classes, Teachers, Results etc etc.  My query is regarding when or if you should ever use Snowflake Schemas and how deep you should go if you do.  I am finding if I tick to a rigid Star then I end up using Bi Directional relationships. Is there a rule of thumb or can you point me to other literature that would clear this up for me.



July 26, 2017 7:55 AM

Marco Russo (SQLBI) said:

Hi Chris,

if the snowflake is necessary and you don't have dimensions with a large granularity, you might use that approach.

We discussed this topic here:

If you already have that book, you might want to drop a comment on the link above (in the book page) so we can discuss about your model (it's generic enough so it could be interesting to other people, too).

August 4, 2017 2:17 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement