I am partitioning my cube by the most recent 13 months, and then a legacy partition to hold older months.
I have successfully created dynamic partitions, but now I need to add a dynamic slice to each partition.
I thought I could use this in the Partition Slice Expression:
[Dim Date].[Month].&[" + CStr(Month(Now())) + "].lag(8)
but it's failing. Does anyone have any ideas?
I tried all day, but ultimately resolved that partition slice expressions dont like anything that is not a dimension member value.
To be clear my goal was to create dynamic partitioning using the 14 described partitions above. Best Practice advises to also use slices on the partitions per Mosha's Article but since my partitons are dynamic, then my slices needed to be dynamic.
I finally added a member to my Date Dimension that mimics the dynamic labeling of the 14 partitions I wanted to create. Next I referenced the new date dimension member values to each of the corresponding partition slices, basically moving the "dynamic" slices to the cube structure.
It works great, and give me another usefull Dimension Member. I have also partitioned the fact table in the data warehouse with the same 14 partitions using a partitioning scheme, file groups, etc. As an added bonus, since everything is dynamic my SSIS package is much less complex and does not require DDL tasks to move partitions around.