Skip to main content Link Search Menu Expand Document (external link)

Your data model first!

At this end of the year, I thought it’s time for me to bring you a gift!!
It’s a very important subject that I mention to my coworkers and it’s 80% of a success power bi story!

All the focus is on the tech part.

All developers (yes, you are!!), spend a huge amount of time to understand their sources, their transformation, and the magic that happens in their DAX measures!
And, to be honest, that’s normal!

So why this article?

After 1 week, 2 months, or more time, we are very proud of our dataset! (Yes, I’m not talking about reports, but dataset which can be used in many reports)
Our model contains a lot of data, all the information needed is there! But is it easy for an external person to use it? (a User, a colleague, your kids, ..)
Sometimes yes, sometimes no.

Some complexity exists:

  • Too many tables
  • Too many relationships and the difficulty to know the interaction between tables.
  • The model keeps the complexity of the source systems.

But ok! We did a huge work to clean and provide this model! Everything work!! Our users can take 2 or 3 hours and try to understand the logic behind it!

I like to mention it. I love cars!! But I know nothing about it!! Do I need to understand how an engine work to drive a car? It’s a real plus, but not mandatory.

So what do I suggest?

It doesn’t take much time, and it can help you to keep an eye on your goal. (Yes!! Provide a very shiny dataset)
Before every model, I recommend you to draw your model on a sheet of paper.
You already know the information you have to present and how your user group this information. If not, it’s the perfect time to ask your users and write their buzzwords on paper.

Before each project, ask them to present you their job and the different tools they use. It would help you to have a clear view of their logic behind, their needs, and also what’s missing now.
At this point, I only write huge keywords on paper.

When I’m back on my desk, I’m looking at this brainstorming paper, and try to group the different keywords into a logic.
That’s how tables bring to life, and because I’m a Kimball huge fan let's call them Dimensions!

So now, with these tables you have the user point of view of their data, all the magic (and fun) is to prepare the data to fit in it!
But hey, with Power Query, SQL, it’s just a piece of cake! Isn’t?

In conclusion

Do not jump directly into the source's data, keep an eye on your expectation first. Ask your users, talk to your colleagues and try to build the model of your dream.
It’s possible! During your development, you may change some tables, and that’s completely normal! But at least, if you keep your original model, everybody will win in the end.

I wish you all the best in your data journey!
What a fun job!!