Learn about the fundamentals of data governance to keep your data safe and healthy with this multi-part series.
Expert Data Modeling with Power BI
Find out about Soheil Bakhshi's new book Expert Data Modeling with Power BI, perfect for those wanting to manage and work with business data effectively.
In 2020, the world celebrated the new year with many uncertainties. Well, life is full of uncertainties, but, this one was very different. The world was facing a new pandemic that never experienced before. The first COVID19 case in New Zealand was confirmed in February 2020. In March 2020 the entire country went to lockdown for the first time. The world was experiencing a massive threat changing everyone’s lives. I was no different. Every day was starting with bad news. A relative passed away; a friend got the virus; the customers put the projects on hold etc. Nothing was looking normal anymore. You can’t even go to get a proper haircut, because everyone is in lockdown. This is me trying to smile after getting a homemade haircut. I bet many of you have done the same thing.
One day, I checked my email and saw a message from Packt Publishing. They wanted to see if I am interested in writing a book about Power BI. That was a piece of good news after a long time. I always wanted to write a book about Power BI. Indeed, I attempted for the first time in 2016, but I couldn’t manage to get my ducks in a row to grasp the publishers’ attention.
I was not unfamiliar with writing books; indeed, I wrote my first book back in 2006 about Multimedia Applications in Persian. One of my passions in life is listening to music. And CDs were the most accessible music source with high-quality sound. I recall I saved money for some months, and I bought a Discman to listen to the music on the go. But CDs are rather bulky, and you could not have many of them in your pocket. So the next project was to save even more money to buy an MP3 player. But, converting Audio CDs to MP3 without compromising a lot on the sound quality was a real challenge for many people. And, that was my motive to write my first book in Persian to share my little knowledge with everyone.
While I was familiar with writing books, my previous experience was quite different as my book was in Persian, my first language. So writing a technical book in English could be a real challenge. So, the email from Packt Publishing could be a game-changer in many ways. Now, I had a big project to deal with; instead of thinking about all the negative pandemic news. I decided to accept the challenge and embraced the opportunity. In May 2020, I officially started writing my book, Expert Data Modeling with Power BI. I had long days with a super busy schedule for more than a year. I worked very hard to manage my duties as the founder of Data Vizioner and keep up with the product development, handling my consultancy role, writing my book and at the same time making an acceptable work-life balance. I worked between 12 to 16 hours a day, including the weekends. But all the hard works paid off after my book was published in June 2021.
What is in this book for you?
Microsoft Power BI is one of the most popular business intelligence tools available on the market that you can use on your local machine and the cloud. This book will be your guide to understanding different aspects of data modelling, so the focus of this book is not to teach you how to write DAX expressions. For that matter, there are much better books out there that I personally refer to on some occasions. The best ones for learning DAX, in my opinion, are:
- Phill Seamark’s books:
- Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence
- Pro DAX with Power BI: Business Intelligence with PowerPivot and SQL Server Analysis Services Tabular
- Marco Russo and Alberto Ferrari’s book:
- Definitive Guide to DAX, The : Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel by Marco Russo and Alberto Ferrari
While my book heavily uses many Power Query techniques, but again, the main focus is not on Power Query. For learning Power Query, I suggest reading the following book:
To make it clearer, my book discusses a lot of DAX and Power Query, but, the focus of the book is not to solve all modelling challenges only with Power Query or DAX. The focus here is indeed on Power Query, DAX and many other aspects of data modelling. For instance, the book covers the flow of data in different layers of Power BI Desktop; a concept that might look trivial to many readers, but in fact, it is a crucial concept that if we do not understand it well, we cannot create a good data model. So, the book starts discussing some building blocks, then it covers many aspects of advanced data modelling. So, regardless of your level of knowledge, the Expert Data Modelling with Power BI has something for you.
What this book covers
The following sections explain what the book covers in different chapters.
Chapter 1: Introduction to Data Modeling in Power BI
This chapter briefly describes the different functionalities of Power BI and why data modelling is important. This chapter also reveals some important notes to be considered around Power BI licensing, which potentially could affect your data model. This chapter introduces an iterative data modelling approach, which guarantees an agile Power BI implementation.
Chapter 2: Data Analysis eXpressions and Data Modeling
This chapter does not discuss a lot of DAX as in parts 3 and 4 of this book DAX is heavily used to solve different data modelling challenges. Therefore, we’ll only focus on the DAX functionalities that are harder to understand and are very relevant to data modelling. This chapter starts with a quick introduction to DAX, then we jump straight to virtual tables and time intelligence functionalities and their applications in real-world scenarios.
Chapter 3: Data Preparation in Power Query Editor
This chapter quickly explains the components of Power Query and their application. It expresses the emphasis of creating query parameters and user-defined functions along with real-world use cases and scenarios to demonstrate how powerful they are in building much more flexible and maintainable models.
Chapter 4: Getting Data from Various Sources
This chapter explains getting data from different data sources that are more commonly used in Power BI. Then, the importance of data source certification is explained, which helps you set your expectations on the type of data you’re going to deal with. This is especially helpful in estimating data modelling efforts. Different connection modes are also explained in this chapter.
Chapter 5: Common Data Preparation Steps
Explains common data preparation steps along with real-world hands-on scenarios. A combination of what you have learned so far in this book with the steps to be discussed in this chapter gives you a strong foundation to go on to the next chapters and build your data models more efficiently. By learning these functionalities, you can deal with a lot of different scenarios in implementing different data models.
Chapter 6: Star Schema Preparation in Power Query Editor
Explains how to prepare your queries based on the star schema data modelling approach with real-life scenarios. The Power Query M language will be heavily used in this chapter, so you will learn how to deal with real-world challenges along the way. As you have already learned common data preparation steps in the previous chapter, the majority of Power Query scenarios explained in this chapter will be easier to implement. You’ll also learn how to build dimension tables and fact tables, and how to denormalize your queries when needed.
Chapter 7: Data Preparation Common Best Practices
Explains common best practices in data preparation. Following these practices will help you build more efficient data models that are easier to maintain and more flexible to make changes to. Following these practices, you can also avoid common mistakes, which can make your life much easier.
Chapter 8: Data Modeling Components
Explains data modelling components from a Power BI perspective along with real file examples. In this chapter, we heavily use DAX when applicable so having a basic understanding of DAX is essential. We also have a complete star schema model in Power BI. The concept of config tables is covered, which unlocks a lot of possibilities in handling more complex business logic in the data model. The chapter
ends with data modelling naming conventions.
Chapter 9: Star Shema and Data Modeling Common Best Practices
This chapter explains common data modelling best practices to help you make better decisions while building your data model to prevent facing some known issues down the road. For instance, dealing with data type issues in key columns that are used in relationships is somewhat time-consuming to identify, but it’s very easy to prevent. So, knowing data modelling best practices helps you save a lot of maintenance time and consequently saves you money.
Chapter 10: Advanced Data Modeling Techniques
This chapter explains special modelling techniques that solve special business requirements. A good data modeller is one who is always open to new challenges. You may face some of the advanced business requirements discussed in this chapter or you may face something different but similar. The message we want to send in this chapter is to think freely when dealing with new business challenges and try to be
innovative to get the best results.
Chapter 11: Row-Level Security
Explains how to implement row-level security (RLS) in a Power BI data model. Dealing with RLS can be complex and knowing how to deal with different situations needs deep knowledge on data modelling and filter propagation concepts. Our aim in this chapter is to transfer that knowledge to you so you can design and implement high-performing and low-maintenance data models.
Chapter 12: Extra Options and Features Available for Data Modeling
This chapter introduces data modelling options such as Slowly Changing Dimensions (SCD), Object-Level Security (OLS), dataflows, and composite models, giving you broad exposure to all those topics.
I was not alone along the way
All this was not possible without having the full support of my lovely wife, my friends and my family. I also would like to provide my special gratefulness to two amazing professionals:
- Ana Maria Bizbe York: for putting a lot of time and effort to review my book
- Christian Wade: for writing the foreword for my book
I also thank the following Microsoft MVPs who put their valuable time to review my book after publication and provided their feedback:
Thank you everyone for your support.