none
any benifit of cube partition when using ROLAP? RRS feed

  • Question

  • If use ROLAP, do we need to use partition? Since most of the benefit of partition is about performance, if we use ROLAP, then if the underlying db can process well (such as partition the database table), then looks like there is no benefit of using partitions in ROLAP.
    Monday, August 8, 2011 7:12 AM

Answers

  • That is entirely up to you and your architecture and expected query patterns. You are probably less likely to look at partitioning when using ROLAP though it can still be useful for performance considerations.. i.e. Say your expected volumes are huge and although dumping all of the data into one table is possible it may not be desirable, you could then look to segment your data slightly differently and use that across AS partitions. For example, SQL Server doesn't support composite partitioning like Oracle so you could look to create separate fact tables by region and partition those tables by year. Then create an AS partition for each location sliced on location and let partition elimination take care of the year. You can then also easily remove all of the data for a given location from the cube without deleting it from the relational database by just dropping the partition.

    A bit of a contrived example I agree and of course you could also use table partitioning and view partitioning to achieve a similar effect but if locations are dynamic then the partitioned view might need to be updated on the fly etc. which might be fine or might not.

    Long story short, probably less of a useful feature but don't totally ignore it.


    http://bi-logger.blogspot.com/
    • Marked as answer by Daniel Wu Monday, August 8, 2011 8:06 AM
    Monday, August 8, 2011 7:37 AM

All replies

  • That is entirely up to you and your architecture and expected query patterns. You are probably less likely to look at partitioning when using ROLAP though it can still be useful for performance considerations.. i.e. Say your expected volumes are huge and although dumping all of the data into one table is possible it may not be desirable, you could then look to segment your data slightly differently and use that across AS partitions. For example, SQL Server doesn't support composite partitioning like Oracle so you could look to create separate fact tables by region and partition those tables by year. Then create an AS partition for each location sliced on location and let partition elimination take care of the year. You can then also easily remove all of the data for a given location from the cube without deleting it from the relational database by just dropping the partition.

    A bit of a contrived example I agree and of course you could also use table partitioning and view partitioning to achieve a similar effect but if locations are dynamic then the partitioned view might need to be updated on the fly etc. which might be fine or might not.

    Long story short, probably less of a useful feature but don't totally ignore it.


    http://bi-logger.blogspot.com/
    • Marked as answer by Daniel Wu Monday, August 8, 2011 8:06 AM
    Monday, August 8, 2011 7:37 AM
  • Benefit of partition can be attributed to level of processing as well as performance. Partitions enable to process the recent data or only a subset of data which helps in improved performance. BUt partitions will be necessary for the cube which will be ROLAP, MOLAP/ hybrid enabled storage. Partitions talk about the data to be processed or eliminates unnecessary processing.
    Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
    Monday, August 8, 2011 7:40 AM