Forum Discussion
Custom partitioning with Tabular editor
Hello,
We have a PPU license with A2 capacity and our dataset in Power BI service won't refresh because it reaches the memory limit.
We cannot implement incremental refresh because of business requirement and all data needs to be refreshed.
I believe the only option would be partitioning.
I created multiple partitions for a single table using Tabular Editor by splitting the data based on date. Among our 25 tables, two of them contain large datasets, each exceeding 12 million rows.
For example:
Added this for first partition:
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [CreatedOn] >= #datetime(2024, 1, 1, 0, 0, 0))
Added this for second partition:
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [CreatedOn] >= #datetime(2023, 1, 1, 0, 0, 0) and [CreatedOn] <= #datetime(2023, 12, 31, 0, 0, 0))
The first partition refreshes successfully through a powershell script.
However, the second one results in an error falsely indicating a duplicate in the ID column
After testing, I noticed that excluding the closing date allows the dataset to refresh without issue. For example, when specifying only '[CreatedOn] >= #datetime(2023, 1, 1, 0, 0, 0)' for the second partition, the dataset refreshes successfully. But, when I include the closing date, I face the duplication error message.
I appreciate it if you could help me out with that or if you have any other options in mind to create and refresh all partitions.
Thanks.
There's that pesky problem of meta data changes. Whenever you change the data model, or even the report design, and publish to the workspace without further thought, a couple of things will happen.
- The incremental refresh partitions will be replaced with the single partition you just uploaded
- a full refresh will be initiated, across all currently specified partitions (depending on your granularity)
- after that is completed successfully your regular incremental refresh will resume.
To avoid that you need to choreograph meta data changes extremely carefully, push them with ALM Toolkit WITHOUT refresh, and then manually refresh the affected partitions.
7 Replies
If you want it or not, you have to emulate the standard incremental refresh process to the letter. That includes the fact that your RangeStart should be inclusive and the RangeEnd should be exclusive. So the partition formula for the first partition is missing the RangeEnd filter. and the second one has the wrong condition and value for RangeEnd.
As for the indexes being duplicates - that's for you to avoid, for example by adding a large enough year multiplier to the index column to make sure they can never be the same across partitions.
Think of the RangeEnd of a given partition as being equal to the RangeStart of the next partition.
Thank you. I think I should do it incrementally. If I define an incremental refresh policy in tabular editor to refresh last 6 months and archive data from last 20 months, then 20 partitions will be created with the newest partition representing February 2024. Will a new partition be automatically created when the next month begins?
yes, and the oldest partition will be dropped. If you don't want that, use a different policy, like for example 6 months and 3 years.
