SQL Server Performance

Impact of change: table to partitioned view

Discussion in 'Analysis Services/Data Warehousing' started by Zirco, Jun 4, 2004.

  1. Zirco New Member

    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.
  2. Raulie New Member

    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.
  3. Zirco New Member

    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.
  4. Raulie New Member

    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.
  5. satya Moderator

    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.
  6. Zirco New Member

    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.

Share This Page