locked
table partition and cube partition RRS feed

  • Question

  • I have a relational data warehouse, and a SSAS cube built on the DW.

    In the relational DW, the fact table can be

     1. either one table with partition,

     2. or multiple fact tables with the same structure.

    In the cube, I can create multiple partions. 

      In case 1, the cube partions are bound to query to the same table but different partion conditions.

      In case 2, the cube partions are bound to different fact tables.

    My questions are --

    a. Are the above understanding right?

    b. In both case 1 and 2, what fact tables are in the DSV? Especially in case 2, do I add all fact partitioned tables in DSV and create relationship with all dimensions?

    c. What is the ETL of the relational DW for both case 1 and 2? 

    d. How do I process cube on changes? One thought is when doing ETL, record which partions are changed and processfull of that cube partition.

    Many thanks in advanced.

     

    Thursday, November 1, 2012 10:04 PM

Answers

  • Hi,

    Well this is one of the interesting research I have also done previously and I got a solution too.

    Firstly, as far as I know you can simply create a partition table by setting certain partition column scheme, functions. The best link to create the partition table can be found below:

    http://dwbi1.wordpress.com/2010/11/18/tuning-cube-processing-performance/

    Then , after creating partition table you can think of creating a dynamic partition within  a Cube by using that underlying partitioning relational tables. We can choose AMO method or a best SSIS tool.

    http://sql-bi-dev.blogspot.com/2010/12/dynamic-cube-partitioning-in-ssas-2008.html

    Mainly if you have a huge DW, you can think of creating partition on multiple fact tables  and process a cube with multiple partitions.

    Break that MG into as many partitions as possible. And partition the underlying table into as many partitions as possible. So those MGs partitions will be hitting DIFFERENT table partitions at the same time. which will surely help in performance and optimizations.

    Hope this will help you somewhat and I will be glad if I can help you more.

    Thanks,

    Anil Maharjan

    • Proposed as answer by Elvis Long Monday, November 5, 2012 9:52 AM
    • Marked as answer by Koalass Monday, November 5, 2012 10:34 PM
    Friday, November 2, 2012 5:53 AM

All replies

  • Hi,

    Well this is one of the interesting research I have also done previously and I got a solution too.

    Firstly, as far as I know you can simply create a partition table by setting certain partition column scheme, functions. The best link to create the partition table can be found below:

    http://dwbi1.wordpress.com/2010/11/18/tuning-cube-processing-performance/

    Then , after creating partition table you can think of creating a dynamic partition within  a Cube by using that underlying partitioning relational tables. We can choose AMO method or a best SSIS tool.

    http://sql-bi-dev.blogspot.com/2010/12/dynamic-cube-partitioning-in-ssas-2008.html

    Mainly if you have a huge DW, you can think of creating partition on multiple fact tables  and process a cube with multiple partitions.

    Break that MG into as many partitions as possible. And partition the underlying table into as many partitions as possible. So those MGs partitions will be hitting DIFFERENT table partitions at the same time. which will surely help in performance and optimizations.

    Hope this will help you somewhat and I will be glad if I can help you more.

    Thanks,

    Anil Maharjan

    • Proposed as answer by Elvis Long Monday, November 5, 2012 9:52 AM
    • Marked as answer by Koalass Monday, November 5, 2012 10:34 PM
    Friday, November 2, 2012 5:53 AM
  • Thanks a lot Anil.

    Do you mean to create multiple partitions in ONE fact relational table and multiple partitions in one SSAS measure group? And the partition rules for both relational table and SSAS cube are differenet?

    How about the solution that create multiple relational tables for a fact group in the same structure? Is this good to do this?

    Monday, November 5, 2012 10:19 PM
  • Hi,

    I think creating multiple partitions in one MG within SSAS cube from underlying multiple partitions from one fact relational table will be better option than creating multiple relational tables for one fact group.

    Thanks,

    Anil Maharjan

    Tuesday, November 6, 2012 4:00 AM