Hi - I was wondering if anyone had found any documentation on guidelines for selecting the best data slice for partitioning the cube..

I have a large fact table that should be partitioned, but I'm not sure what the trade-offs are from a performance perspective. It makes the most sense to do it by date (to add in the processing of the cube) but most users will look at a rolling 12-month view of the data. So does that partition make sense or will it have a bad effect on the performance of the most common user questions

Any insight on how to make the most performant decision would be great. Thanks

Mark

Have you read the section on partitioning in the Analysis Services
Performance Guide?
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

Also there is an excellent section in the SQL Server 2000 Resource Kit
(available at your local bookstore and on a TechNet distribution if you have
one in your company)which discusses partitioning.
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Mark" <anonymous (AT) discussions (DOT) microsoft.com> wrote

sense to do it by date (to add in the processing of the cube) but most users
will look at a rolling 12-month view of the data. So does that partition
make sense or will it have a bad effect on the performance of the most
common user questions?

Thanks Dave. I have studied the Performance Guide extensively, but I worry about making decisions to improve processing time that will adversely affect the performance of the end user. It would make the most sense for me to partition by date, since I add data each month and could just do a partition for each month, but since the users always want to see the data for the past 12 months rolling, would that decision hurt their performance

I will also look at the resource kit - I didn't note that partitioning was covered there too. Thanks for the help
Mark

It is always faster to partition. The cost is all in the maintenance side--
never on the query processing side.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Mark" <anonymous (AT) discussions (DOT) microsoft.com> wrote

the performance of the end user. It would make the most sense for me to
partition by date, since I add data each month and could just do a partition
for each month, but since the users always want to see the data for the past
12 months rolling, would that decision hurt their performance?

Excellent - I didn't glean that from my reading. Thanks for the clarification

Mark