SQL Server Performance Forum – Threads Archive
Impact of change: table to partitioned view
Hi, From the Analysis Services standpoint, is there any major design change if the cubes are fed from partitioned views rather than from physical tables? If yes, what kind of design changes? Thanks in advance for any advice.Are these views "Indexed" partitioned Views?? I think it is a question of performance here. If these views are will be joining many tables it would not make a good candidate for a fact table, you should load the partitioned view into a table and use the table as your Fact instead.
The current design is using Fact tables. However these "monolithic" fact tables are very big (100 to 150 GB) which makes DB maintenance tasks complex (backup, monthly purge, reindex). We have been advised to consider replacing each table by a partitioned view. I’d like to make sure that table partition is 100% compatible with current Analysis Services design. And possibly if there is any benefits to use table partition.
In that case then partitioned views would provide scalability scale-out agianst very large fact tables such has horizontally splitting the data across multiple servers this aleviating the stress off of maintenance plans by using parallel processes. That I know Analsis Services cubes are just logical objects doesnt matter if they come from views or tables. Actually it makes a lot of sence why you were recomended partioned views.
Partitions are strongly recommended for large Analysis Services systems, because an effective partitioning plan will improve query performance substantially. Partitioning the relational data warehouse is not generally recommended, although it can be an effective and well performing solution to some specific warehouse maintenance issues. Refer to this MS Resource kit pagehttp://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c1861.mspx for more information. 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.
FYI,
On microsoft.public.sqlserver.datawarehouse newsgroup, a Microsoft’s BI SystemsTeam person recommended to align the RDBMS partitions with those of the Analysis Services. i.e. if OLAP cubes are partitioned by month, then it is advised to partition the underlying fact table into months as well.
]]>