Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Sorry duplicated post - real post here https://community.powerbi.com/t5/Desktop/Transitioning-SQL-joins-to-Power-BI/m-p/505388#M235933
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Not sure if I understand you correctly here, but query folding has nothing to do with the way to select your tables during import.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |