design approach for Queries

[Anonymous]

HI,

I'm new to powerbi, but come from a BI tooling background. Assuming I have a simple star schema,

Fact_Sales, Dim_Date, Dim_Product and to reduce ambiguity all Date and Product records have a Fact_Sales record.

Should I select the 3 tables to import and from what I can see they all get loaded as individual objects and joined together in memory based upon PK/FK relationships or realationships that I manually create OR should I write a SQL select statement that joins all 3 tables together and then Import (seems similar to the MERGE table option).

Which one is better for performance. In my head I'm thinking that the first is probably the better solution as Filters on Dimensions will be better as they simple query the Dimensional data in memory and not the entire joined up data set.

Any best practice based on experience would be appreciated

Thanks

[HotChilli · Accepted solution]

I think you've got the right idea in your last paragraph.  If you want to slice the fact table by date or product then having these as separate tables is better.  The Date dimension doesn't have to be imported - it can easily be created in powerbi.

There's lots of flexibility in powerbi so if a report designer is skilled in SQL and wants to manipulate data from a sql db (which might be designed in a highly relational format) to build a dimensional model, it's possible.  Of course, the data might already exist in a warehouse so it might not require re-engineering.  Other factors would be : Does the data require cleaning in Power Query?  Are there sufficient skills in M or DAX ?  Is there the required level of access to the db?

Writing the SQL to push the query processing to the SQL db would probably be a less flexible approach.

[Anonymous]

Thanks.