Performance Issue

[Anonymous]

Hi Folks,

I'm writing this post to have the best solution for the issues I'm having.

I'm migrating my reports from Pentahoo to PowerBI and I have huge data at the source level approx 20 million. Thus, I'm getting various issues like -

1) While featching the data from source, PowerBI desktop is showing the error as out of memory. I have 8 GB/16GB RAM capacity but not sure why it's failing.

2) Also, my pbix file is going beond 1 GB and unable to publish into server. I'm using PowerBI Pro licence. 

3) I want to filter the data based on the parameter values so source data can be filtered before importing the data into PowerBI desktop but getting stucked with perfomance issue. example I have 15 milllion data into source and after applying the filter it's 5 million but for 5 million data import failes with out of memory. 

I understand, we have have many experts here. Thus, requesting you to provide the appopiate solution. Please let me know if we can connect and solve. 

Thank you in advance. 

[Greg_Deckler]

@Anonymous What is the source system that you are pulling data from? In Power Query Editor, are you doing a lot of complex transformations? Is the query folding back to the server (assuming your source system supports query folding)? Right-click on the last step in your Applied Steps and check to see if "View Native Query" is greyed out. If it is, then it is not fully foldeing back to the source system.

Check that you need all of the columns you are importing. Getting rid of columns with high cardinality can significantly improve dataset size. Also make absolutely certain that you have Auto time intelligence turned off Globally as well as on the local file. File | Options and settings | Option. Under Global | Data load turn off auto-time intelligence. Also under Local File | Data Load, turn off auto time intelligence. This "feature" can significantly bloat your data model by up to 40% or more if you have a bunch of data fields. 

Some other things you can do. You could implement incremental refresh to improve refresh cycle times. You could also consider moving your main fact table to DirectQuery and implement an Aggregation table. 

[Anonymous]

@Greg_Deckler  I'm using SQL server and reading the data through Stored Procedue. There are no DAX into powerbi level as all the logics been applied at SP level but yes, I do have multiple parameter based on which I'm trying to pull the data. 

Please let me know if we can connect and discuss, as getting into the call I'll be able to explain in better way.