Best practices for partitions for cubes?

Hi, all here,

Thank you very much for your kind attention.

Would please any experts here give me any guidances and advices for what are best practices for partitions for cubes in SQL Server 2005 analysis services? I mean like when should we use partitions for cubes? And what are the best practices for the partitions memory modes?

Thanks a lot in advance for any helpful guidances and advices for that.

With best regards,

Yours sincerely,

Hi,

This link will give you a good introduction to partitioning

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

|||

Hi, Larrybird02744,

Thank you very much for your kind guidance.

With best regards,

Yours sincerely,

Best practices for partitions for cubes?

Hi, all here,

Thank you very much for your kind attention.

Would please any experts here give me any guidances and advices for what are best practices for partitions for cubes in SQL Server 2005 analysis services? I mean like when should we use partitions for cubes? And what are the best practices for the partitions memory modes?

Thanks a lot in advance for any helpful guidances and advices for that.

With best regards,

Yours sincerely,

Hi,

This link will give you a good introduction to partitioning

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

|||

Hi, Larrybird02744,

Thank you very much for your kind guidance.

With best regards,

Yours sincerely,

Best practice for indexed views and aggregates tables

Hi, all experts here,

Thank you very much for your kind attention.

I am having some questions on indexed views and aggregate tables.

My question is: To improve the performance of the queries, is it better to use indexted views or aggregates tables for those aggregates which are often queried?

I am looking forward to hearing from you.

Thank you very much in advance for your help.

With best regards,

Yours sincerely,

Well...it depends. Indexed views are great because they keep the data from a view materialized...but the database engine must maintain the indexes, which may slow OLTP operations. An aggregate table may be a good idea, but also might be complicated to design and make sure it is updated properly, not to mention that you have to make sure it is updated.
Tim|||

Hi, Tim,

Thank you very much for your kind advices.

In my case, the aggregates residing in a historical data warehouse, where the update of data is not so much likely and it will not have much to do with any operational database. As what you advised, indexed views would be a better idea in this case?

Looking forward to hearing from you.

With best regards,

Yours sincerely,

|||If the data isn't getting updated often, then I personally would design an aggregates table. That will give you a lot of flexibility for your project...flexibility that may be prohibited by the restrictions on designing indexed views (there are a lot of things you need to adhere to so that your view can be indexed). Hope this helps.|||

Hi, Tim,

Thanks a lot for your advices. They've been very helpful.

With best regards,

Yours sincerely,