Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I'm looking to setup incremental refresh for a fairly large dataset which is 6gb in size. I was wondering does it affect performance depending on how many partitions we create for the 'archived' data. For example if we're looking to archive 5 years worth of data, this could be done as years, quarters, months and even days. Choosing years we'd have 5 partitions, whereas choosing months we'd have 60.
Choosing months gives the advantage of refreshing a particular archived month if needed, rather than the whole year. But I'm not sure if there's any disadvantages? Perhaps someone has done some testing? Would be interested in seeing the results if so.
Solved! Go to Solution.
Make your partitions as big as you can, but not bigger. I have scenarios where the partition size is dictated by the source system performance. For example the source system conks out after 500M rows, which covers about 2.5 months. So - monthly partitions with about 200M rows each it is (to be on the safe side). Yes, it's 60 partitions, but they are guaranteed to work. Quarterly partitions would have been risky/pointless.
Your situation may vary, but it most likely will also be dictated by the capabilities of the source system. If that can easily handle yearly queries then use year partitions.
Make your partitions as big as you can, but not bigger. I have scenarios where the partition size is dictated by the source system performance. For example the source system conks out after 500M rows, which covers about 2.5 months. So - monthly partitions with about 200M rows each it is (to be on the safe side). Yes, it's 60 partitions, but they are guaranteed to work. Quarterly partitions would have been risky/pointless.
Your situation may vary, but it most likely will also be dictated by the capabilities of the source system. If that can easily handle yearly queries then use year partitions.
you should choose the minimum possible number of partitions.
Please can you provide some evidence for this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
18 | |
17 | |
16 | |
8 |