When you first start diving into the world of Power BI, it’s easy to get overwhelmed with all the different things you could be learning.
You probably want to make stunning visuals and beautiful dashboards. Or geek out on the impressive calculations you can write with DAX. Or connect to and transform data from lots of data sources with Power Query. Stunning visuals, complex calculations, and data ingestion are all important parts of being a data professional that Power BI handles with ease – but none of them matter nearly as much as building a good semantic model.
A good semantic model is the foundation on which all your reporting and analytics are built.
Why Data Modeling?
Data modeling should be where you start your journey. There are benefits to the overall business, for individual report users, and for you as the report builder. Learning to model data well helps you:
- make sense of raw data,
- improve data quality,
- scale report building,
- improve user experience
Broadly speaking, modeling data well is how you refine and give business meaning to all the raw data your company generates every single day. For example, does it seem like people across the company talk past each other sometimes because they say the same word to mean slightly (or entirely!) different things? Good data modeling can shine a spotlight on this and give the business space to create one unified definition – or at least explicitly highlight the differences.
More specifically for you, modeling data holistically allows you to answer a wider range of business questions more easily; the visualizations, DAX, and data ingestion will be simpler and more consistent. From personal experience, it is a ton of fun to learn DAX and create measures that show off all the new things you’re learning – at first. It seems to be a near-universal experience that the further you progress in your career, the satisfaction starts to come from doing everything else well, which allows you to accomplish more with a simple SUM function.
If I haven’t convinced you yet, whenever you build anything in Power BI, you’re modeling data – whether you realize it or not. Whether you choose to do it intentionally or not is up to you!
How do I Get Started?
There are different approaches to data modeling. The most widely applied approach tries to bucket everything in your organization into entities and events. They are typically referred to as dimensions and facts, respectively. Facts are the events you want to measure; dimensions are the entities you want to measure them by. For example, you probably want to know how much you made in sales. Sales is an event/fact. But when did we make these sales? To whom? Of which products? Dates, customers, and products would be entities/dimensions.
In fact, Power BI is designed with a specific implementation of dimensional modeling in mind, called a star schema. Your data model may have multiple star schemas, but the core concept of a star schema is that dimension tables have a one-to-many relationship with fact tables, while avoiding relationships between dimension tables and between fact tables as much as possible. A one-to-many relationship is aptly named because, for example, one customer (a dimension) can be associated with many sales orders (a fact), but a sales order will generally not be associated with multiple customers.
If this seems overwhelming, just know that data modeling was a completely foreign concept to me when I started with Power BI and is still something I look to get better at all the time, three years into my data journey! There are many good introductory resources on learning dimensional modeling. My go-to resource is this free SQLBI course.
Where Do I Go From Here?
Data modeling is pretty simple in theory, but as always, real life is a little more complex. The ideal is to avoid anything other than dimension and fact tables and any relationships other than one-to-many relationships between dimension tables and fact tables, but that isn’t always possible! While resources like the above free course will give you a great foundation that gets you 80% of the way to a good technical understanding of data modeling, there will always be exceptions.
Sometimes you need to model something in your organization that, for example, doesn’t quite fit neatly as a dimension or fact table. Chris Adamson’s Star Schema: The Complete Reference and the Kimball Group’s The Data Warehouse Toolkit are fantastic reference books.
I would also be remiss if I didn’t mention that data modeling is an iterative process that mostly happens outside of where your data is stored. There is a lot of skill in talking to people to get a holistic sense of your company, discovering what your entities and events are, and what characteristics people care about. You may figure out pretty quickly that your customers are important, but which of their age, gender, location, or income do we care about? Maybe an important data point about our customers is how much they bought from us in the prior year.
Semantic modeling is the technical representation of our company, but talking to people and engaging in conceptual modeling strongly influences our final data model(s).
Final Thoughts
As you can see, there is a lot more to Power BI than meets the eye! The end product people see is typically a pretty visual in a report but there is so much that goes into making sure that visual is useful, relevant and accurate.
Definitely learn how to write accurate and effective DAX, learn visualization best practices, and learn good data manipulation skills – but spend the bulk of your efforts on learning strong and effective data modeling!
If you have any questions or want to share your experiences, please feel free to drop a comment or connect with me on LinkedIn. Always happy to chat with other data practitioners!