Best Practice for loading data into data model

[mmcanelly]

I run into this issue all the time, and I wanted to see if anybody had any "best practices" for handling a situation like this.

I'm loading multiple tables from SQL Server. My fact table has ~1MM records. For each of the dimension tables, I want to filter them down when setting up the data model so that they only contain the dimensions that are present in the fact table. From my experience, I have 3 main ways I can accomplish this:

1. Load all of the tables in as-is and use DAX to create new dimension tables that are filtered versions of the original. The minor downside to this approach is that I'm using more disk space than needed since I'm basically duplicating every table.
2. Using PowerQuery, merge the fact table in to each dimension table with an inner join so that it filters the dimensions down to only those present in the fact table. My understanding from watching how the data loads when saving the model, though, is that it's loading the entire fact table for each merge. So if I have 6 dimension tables, it's loading the fact table 6 separate times so requires more processing time.
3. Handle everything with views or queries in SQL Server so that I'm not doing any of the data modeling within Power BI.

My normal approach has been to use PowerQuery because it seems cleaner and easier to revert changes there. How are others handling this type of scenario?

[mmcanelly]

@Greg_Deckler Thanks for the guidance and the article! That's very helpful. I've never messed around with dataflows, so I'll check that out if the load times end up being too much.

[Greg_Deckler · Accepted solution]

@mmcanelly Roche's Maxim would dictate:

1. SQL Server
2. Power Query
3. DAX

That would be "best practice". That said, best practices are merely guidelines, there is generally a lot of nuance to them, like you favoring ease of reverting changes for example. I would say that if you run into issues with refreshes taking a long time then you might have to implement the DAX solution instead but otherwise, PQ is probably the say to go. It is kind of annoying how PQ handles this sort of thing though in terms of not seeming to cache the query load but doing it all over again each time. Referencing Power Query queries - Power BI | Microsoft Learn

That said, if you switch your Fact table load to a Dataflow, then you could potentially speed things up as the article above indicates.