Forum Discussion
Improve PowerBI performance for big datasets
sebasj Well, your other option is to try to eliminate columns in your imported data that you are not using. Focus on high cardinality columns (lots of unique values) That is going to increase the size of your data model. For example, if you have columns for "Units" and "Cost Per Unit" as well as "Total Cost", consider eliminating the "Total Cost" column and using a measure for that calculation instead. This will save on your data model size.
The other thing you can do that can sometimes reduce a model by leaps and bounds is to turn off Auto Time Intelligence. If you have lots of date columns in your data, a separate date table gets created for each and that can blow-up your data model tremendously.
In terms of migrating from text files to SQL. You can import the data into SQL and then you just need to modify your query in you semantic model to point to the SQL Server for the data instead of the text file. You can do this one at a time. The easiest thing to do would be to create a brand new query that connects to your data in SQL Server and transforms it the way you want. Then you use Advanced Editor to copy all of the M code. Now, open your original query with Advanced Editor and replace everything with this copied code. Save the query, delete the temporary query you created and save and apply. You original table that retrieved data from the text file will now get it from SQL Server and nothing in your model will break.
sebasj Well, your other option is to try to eliminate columns in your imported data that you are not using. Focus on high cardinality columns (lots of unique values) That is going to increase the size of your data model. For example, if you have columns for "Units" and "Cost Per Unit" as well as "Total Cost", consider eliminating the "Total Cost" column and using a measure for that calculation instead. This will save on your data model size.
The other thing you can do that can sometimes reduce a model by leaps and bounds is to turn off Auto Time Intelligence. If you have lots of date columns in your data, a separate date table gets created for each and that can blow-up your data model tremendously.
In terms of migrating from text files to SQL. You can import the data into SQL and then you just need to modify your query in you semantic model to point to the SQL Server for the data instead of the text file. You can do this one at a time. The easiest thing to do would be to create a brand new query that connects to your data in SQL Server and transforms it the way you want. Then you use Advanced Editor to copy all of the M code. Now, open your original query with Advanced Editor and replace everything with this copied code. Save the query, delete the temporary query you created and save and apply. You original table that retrieved data from the text file will now get it from SQL Server and nothing in your model will break.
Greg_Deckler Thanks a lot for the input and sorry for the late reply. We've been deploying a local machine and doing some tests.
We've found that as long as we use import method instead of directquery, the file size increases as usual. We suspected so since it basically changed the source, and instead of retrieving the tables from CSV files stored in our OneDrive, retrieves the table from the SQL server. Nevertheless, still imports anc copies all data to the PBIX file, so the memory issues still happen.
We're checking DirectQuery method, which actually worked fine initially, kept the file size to a minimum since it didn't import all data. But we see there are some limitations, particularly regarding the max amount of rows that can be retrieved in a single query, which is 1M. Some of our files have more than that (actually there's one with >6M rows), so we're not sure if DirectQuery would work.
I'm not sure if either of this would work. A third approach I saw, but haven't tried, is deploying SSAS, but I'm not sure if this would solve the issue.
Thanks again, and sorry for the late reply, it's been basically the time we used to deploy a testing environment and start tests.
Seb