Noob Question
Is there a limit to the number or partitions that can be created in AAS?
We would like to create Date-wise partitions in Azure Analysis Services to speed up the incremental load that we intend to perform on the data we receive from multiple sources and constant updates to the data.
A limit to the number of partitions is not listed here. I would recommend leaning towards having dozens or hundreds of partitions per table not thousands. And I would lean towards ensuring that partitions generally have at least a million rows for optimal performance. Why? Because a million rows is the size of a segment in the Vertipaq compression scheme and if your partitions are all much smaller (say 50,000 rows) then you will limit the maximum segment size and make compression and performance worse.
That being said, partitioning is mainly about processing performance. So if partitioning by day and processing one or a few days of data incrementally minimizes processing time significantly over partitioning weekly or monthly then that sounds like a great partitioning scheme.
I suppose you could merge older daily partitions together into monthly partitions after they get past the window in which they are often processed. I would recommend checking whether this does reduce the number of segments using DAX Studio though. (I can’t recall off the top of my head whether it does.)