Transitioning SQL joins to Power BI

[pbi_mattw]

I'm just getting my feet wet with Power BI, I just tried to emulate a query I run in SQL to output a table of values and I'm running into memory issues, I'm probably going about it the wrong way. If anyone has any tips or tutorials for someone who's familiar with SQL that'd be great.

Here's my thinking - 

Fairly simple multi-join scenario, multiple tables connecting to a master table and a few sub joins on some tables.

Any sub queries requiring a join on the same table I duplicated the table in Power BI.

Any joins on more than one field I duplicated the fields in both table and merged them before linking.

Any WHERE statements I did via the "Excel-like" filter options.

Date queries I split the datetime columns into date and time separate ones in PowerBI

Renamed columns as required to more useful names (don't know if Power BI can add Aliases)

There are no many-to-many relationships.

I've done the import option on the data as directquery didn't seem compatible. There are a lot of columns I don't touch, so should I import only the fields I require via a custom SQL query import to reduce memory load? I'm using 12 tables, with 10-50 fields each and 200-30000 records each, so not a huge amount of data.

[pbi_mattw]

I'm just getting my feet wet with Power BI, I just tried to emulate a query I run in SQL to output a table of values and I'm running into memory issues, I'm probably going about it the wrong way. If anyone has any tips or tutorials for someone who's familiar with SQL that'd be great.

Here's my thinking -

Fairly simple multi-join scenario, multiple tables connecting to a master table and a few sub joins on some tables. Then build a table view output comprising of ~40 fields.

Any sub queries requiring a join on the same table I duplicated the table in Power BI.

Any joins on more than one field I duplicated the fields in both table and merged them before linking.

Any WHERE statements I did via the "Excel-like" filter options.

Date queries I split the datetime columns into date and time separate ones in PowerBI

Renamed columns as required to more useful names (don't know if Power BI can add Aliases)

There are no many-to-many relationships.

I've done the import option on the data as directquery didn't seem compatible. There are a lot of columns I don't touch, so should I import only the fields I require via a custom SQL query import to reduce memory load? I'm using 12 tables, with 10-50 fields each and 200-30000 records each, so not a huge amount of data.

[pbi_mattw]

Sorry duplicated post - real post here https://community.powerbi.com/t5/Desktop/Transitioning-SQL-joins-to-Power-BI/m-p/505388#M235933

[ImkeF]

A couple of things:

- no need to create composite keys, as you can join tables on multiple fields

- reduce the columns down to what you really need. If you do that as your first step in Power BI, there is no need to write custom SQL-statement, as it will fold back to the server

- get aquainted with "Query folding" to push back as many further transactions to the server as possible (https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/, ht..., joining with non-SQL-sources will most likely break query folding: https://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerque...

- instead of duplicating queries, reference them - with any luck they will only be loaded once

[pbi_mattw]

Thanks Imke,

So with the query folding, say I have 10 tables, I need to generate an import process for each one, with some SQL query on each? As opposed to just clicking the checkbox of 10 tables in one import process.

[ImkeF]

Not sure if I understand you correctly here, but query folding has nothing to do with the way to select your tables during import.

[pbi_mattw]

Sorry maybe I haven't explained it well. Say on the 10 tables I can optimise each one with a different SQL query on each. eg. SELECT * FROM x WHERE y. So I have to run an import data and include an SQL statement 10 different times to bring in all tables with optimised data?

As opposed to importing the data by just checking the 10 tables I need in one import query.