Data modelling best practices

[Anonymous]

Hello there, 

I'm in the middle of a process which envolves data modelling, so I start to create some queries (the metrics for the dashboard) inside of the database, then I import the data inside to the PowerBI.

I would like to know what is the best way to data modelling when it comes to PowerBI reporting, if I still making this queries and then importing to PowerBI or I make all the data modelling inside PowerBI.

I think than the answer for this question can be a variant for which kind of data modelling I'm working on and other things, but I just wanna a general view, than could help me now and in the future.

Thanks in advance

[Anonymous]

Thanks for the help, guys!

Just for the record, I realized that importing queries (metrics) already with all kind of details turn on to be a bad idea, because most of the queries I build have a sql group function, and this was hiding some inner details (data), that I was intended to use as slicer/filter inside PowerBI.

So I decide to build the metric inside PowerBI.

Best Regards,

pardim7

[Anonymous]

HI @Anonymous ,

Since power bi data model are stored as AS tabular model, you can also take a look at AS tabular modeling related documents to know how to optimize its performance:

Tabular modeling overview 

Regards,

Xiaoxin Sheng

[Tad17]

I recommend doing as much as you can in the query. While power BI has all of the manipulation capabilities you would need it can be incredibly tedious. It is easier to build relationships and create calculations if the manipulation is done before it gets to Power BI. The concept is Crap in Crap out. If your data is like crap it takes a long time to clean it up and make it work. If it is clean and uniform it makes the whole process much simpler. It also allows Power BI to autodetect relationships and suggest visuals and techniques as you work if you enable those features.

It does depend on your personal preference, the data, the datasource, etc. But I find it significantly beneficial in the majority of cases to do the work on the front end instead of the back end.

[Mariusz · Accepted solution]

Hi @Anonymous 

I do all my data prep in Power Query and Dataflows, it gives me the ability to use one ( M ) language to manipulate all data no matter where it comes from, and I find it a lot easier and faster than using SQL for instance.

but I guess it always will a question of what project I'm working on, what's an easier option or simply personal preference.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.