THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
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 08, 2017 12:17 AM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Privacy Statement