SQL Server Performance Forum – Threads Archive

Partitioned view: when and were you happy?

Hi, We are designing a data warehouse. DB size is estimated to be about 250 to 300 GB. There are 6 fact tables, each has 20 to 25 millions rows. The largest fact table has 120 millions rows. There are 30 dimension tables, their size are negligible. OLAP cubes are processed using MS Analysis Services. We use SQL2K Enterprise, Cluster 2 nodes Active/Active, SAN, 4 CPUs servers, 4 GB RAM. The OLAP cubes are partitioned by month, the underlying fact table is not partitioned. We are exploring the idea of applying partitioned view. I have read the documentation and posts from this forum and google regarding partitioned view. I would greatly appreciate if you can help me to complete my review with your practical experiences. 1. Is there any "critical" DB size or rows count from which we should start considering partitioned view? 2. Were you happy/unhappy after having applied the partitioned view? What was the most significant benefits? 3. Is there any challenge (or traps) in maintaining a PV that a novice should particularly pay attention to? 4. For the size of our DW, is there any potential risk if we DON’T apply PV? Thanks you very much in advance for any help.

1. Consider partitioning once you’ve issues in performance while accessing data.
2. Using partitioned views also isolates data structure changes from queries or applications by making the sets of information appear as one table. For Analysis Services 2000 (formerly OLAP Services), the joining of local or remote partitions into a unified view is also managed transparently for the developer. Analysis Services’ cubes are a logical representation of physical partitions residing either remotely or locally. The benefits to partitioning data warehouse data with SQL Server 2000 include the ability to design parallel processes to perform extract, transformation, and loading (ETL), database backup/recovery, and index creation. 3. The obvious advantages of parallelization is performance, however there are some drawbacks to this when using partitions. Partitions can make the ETL process much more complex to design and support; it must now include rules as to which partition to load and when to create new or delete old partitions. Yet, with careful planning, this process can be completely automated.
4. If you aren’t getting any performance issues with current setup then leave as it is and consider whenever it strikes. (Thread moved to Analysis Server forum, which is related) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Other resources to look for answers are Datawarehouse.com or dmreview.com

]]>