Autoslicing not working

I have around 36 partitions in the 2005 cube. Each partition is roughly 20M fact rows.

The partitions are differentiated by a date member dimension.

When I run a query which selects by a distinct date, SSAS tries to scan all partitions (for inclusions?). This introduces a huge performance impact. It takes many seconds to run a single query.

I have aggregations designed specifically for relevant dimensions. The Profiler shows that SSAS reads from aggregations. It does not read from facts.

The storage mode is MOLAP.

Do I need to use the partition slice property ? I am seeing on forums from time to time that this attribute is not used in MOLAP.

I just need to make sure that my queries are fast.

I don't see where it could hurt to set this property, though BOL indicates this is only used by SSAS for ROLAP partitions. I thought I had heard that with MOLOP and/or HOLAP, that SSAS maintained an internal set of information regarding the contents of a slice that would handle this function for you. Maybe someone else out there could chime in on this one.

Regarding the query you specify above, are you specifying a date member from the specific cube dimension used to partition your cube? You probably are but it wasn't explicitly stated. Date dimensions are often used in a role-playing capacity which introduces some confusion.

Thanks,
Bryan

|||

can anyone clarify if the Slice property on Partition is used in MOLAP or not?

I checked info....xml files for my slicing dimension min/max values and they seem correct, however with explicit queries, SSAS still reads a bunch of partitions...

Auto-partitioning

From everything I've read it seems as though partitions have to be set up ahead of time and to be honest - that makes perfect sense.

However, I'm interested in a scenario where a new partition is created whenever (say) a new dimension member appears. An obvious application of this is to create a new partition whenever a new year member appears in the Time dimension - thereby having a partition for each year.

Thoughts here? Would this be a useful feature? Is there something glaringly obvious I've overlooked that makes it a very bad idea?

Thanks

Jamie

I was just thinking the same thing quite recently (when I had to write a SSIS package to generate 650 partitions for a proof-of-concept cube) and I think it would be really useful. Probably quite difficult to implement though.

Something to submit to the MSDN product feedback website perhaps?

Chris

|||

Chris,

Good to know I'm not the only one thinking along these lines. I've submitted it here: https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=127378&SiteID=68 so take a look, vote, and add a comment.

[Product feedback page has changed by the way. They did that on the quiet didn't they?]

cheers

Jamie

|||

I've voted. This reminds me that I need to devote some time to seeing if I can get usage-based partitioning to work - that would be another cool thing to have in the next release...

http://spaces.msn.com/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!348.entry