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!
