Which is the most efficient approach in Power BI?

[nunnc01]

Hi,

I have lots of SQL tables that I need to pull into Power BI. Each SQL table contains a key column with either "In Progress" or "Completed". There are lots of other columns in the tables that generally only apply to either of those two categories.

Which is the better approach

- bring the SQL tables into Power Query once, and then use Desktop filtering to only show the relevant information on the report page.  All the columns of the original SQL would be shown

- bring the SQL tables into Power Query twice and then transform them so that I have one table for "In Progress" and one for "Completed" with each just having the relevant other columns needed. The total number of rows when these two tables are added together would be the same as the original table.

- bring the SQL tables into Power Query once, remove the tick from enable load and then use this table to produce two "referenced" tables as above.

Hope that makes sense. Just want to build the most efficient report I can.

Thanks

[HotChilli · Accepted solution]

Those options seem to imply that each table is going to be imported 'as is' or split into 2 tables.  And that implies that the data model will be similar (relationally) to the SQL model.

I think you might need to put some thought into the optimal dimensional model bearing in mind the following: 

The optimal reporting solution is based on a star schema - Fact tables and dimension tables.  The optimal performance when importing data is one which allows query folding.