i'm on a project to study on-time performace for flights for various airlines and airports. i have appended few data and currently based on 2.5 years of data, my pbix file has grown to about 270mb in size. i still need to add & study data of 3 more past years & continuously add new data for coming months and years. the imported data is in csv format from a government bureau of statistics.
here are my questions -
1. what is the max file size limit with imported data that to work efficiently ?
2. through power query, i appended the data according to calendar year (jan-dec). so, there are now 2019, 2020, 2021 (upto may 2021) data in there. kindly advise on how to generate smaller files based on
a. operating carrier (field name: OP_CARRIER) b. airport (field names: ORIGIN and DEST)
the filenames are available in all the current '2019', '2020', '2021' tables in the pbix file.
Regarding the first question: that really depends how much memory/horsepower your computer has. Everything is held in RAM so if the computer is a weakling, the effective limit will be smaller. It will also depend on how well your data is modeled.
And regarding modeling, I'd suggest not having tables for individual years. I'd merge them all into a single table with Year as an attribute of that merged table. Will make writing measures easier. And you shouldn't have to create smaller files for carrier/origin/destination. That will just complicate things too.