General question regarding raw static datasets

[nadi]

Hello,

I'm relatively new to PowerBI and I'm coming from an academic/scientific perspective rather than a business one. I've been using it as a tool for scientific data visualization. The data we're dealing with is static, that is there are no new records being added. The reason I'm using PBI over R or Python is because I've been making easy-to-understand graphs which my colleagues can use filters on to quickly and efficiently view different portions of the data as needed.

It works well for the most part, but I've been running into some significant memory and time limitations. I've figured out unchecking "Enable load" and "Include in report refresh" helps a lot, but there seems to be no way to entirely delete filtered rows from a query. Exporting a (filtered) table also doesn't work as mine exceed the maximum number of rows to export. Does this mean ALL pre-processing (e.g. filtering of invalid values) must be done before PowerBI import? For example, importing the raw CSV files into an application such as Microsoft SSMS, running the queries there, and then importing the resulting local SQL database into PBI?

The other issue I've been running into has to do with system memory. If I want to copy a very large fact table (~1 billion rows), PowerBI is unable to do this due to insufficient memory. Even a smaller table (~250k rows) takes several hours to copy. This has resulted in me going through all the steps to load it from source instead.

Apologies if these are very basic questions. I get the sense I need to be using another tool before PowerBI import and just wanted to make sure.

Thanks!

[v-xiandat-msft · Accepted solution]

Hi @nadi ,

I understand that you're facing some challenges with large datasets and memory limitations.

Here are some suggestions for you:

    1. Before importing data, clean the data. Try to write formulas with measures and add columns sparingly.

    2. Filter out unnecessary data by borrowing tools, such as the SSMS you mentioned.

    3. If you have a Premium license, you can also enable the large dataset storage format to use large datasets in Premium capacity.

Below is the Microsoft's official link:

Large datasets in Power BI Premium - Power BI | Microsoft Learn

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nadi]

Thank you @v-xiandat-msft! Looks like my hunch was correct about pre-filtering. Adding measures instead of columns is a good tip too.