Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase

3 posters

Go down

Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase Empty Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase

Post  bi_at_nj Sat Oct 31, 2009 2:34 am

Please specify the partition strategy you follow in your organization for facts and dims in Ora/Sybase/Db2

Thanks,
bi_at_nj

bi_at_nj

Posts : 14
Join date : 2009-10-31

Back to top Go down

Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase Empty Re: Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase

Post  ngalemmo Mon Nov 02, 2009 11:49 am

Date is the most common partitioning criteria for fact tables. I have not seen a situation where dimension tables were partitioned, and, by their nature, any partitioning would be specific to a particular application.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase Empty Re: Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase

Post  BoxesAndLines Mon Nov 02, 2009 2:44 pm

If you do partition your dimensions, date is also the most common partitioning attribute. For my Customer dimension, I keep all active customers in one partition. All the history is kept in a separate partition.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase Empty Re: Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase

Post  ngalemmo Mon Nov 02, 2009 3:21 pm

BoxesAndLines wrote:If you do partition your dimensions, date is also the most common partitioning attribute. For my Customer dimension, I keep all active customers in one partition. All the history is kept in a separate partition.

B&L:
Your customers are partitioned by status, not date (although you may use a date field as a status indicator...). I was hesitant to say date is common for dimensions, becuase for dimensions, date does not have as definitive a meaning. It is not particularly useful for archival purposes because you can't roll off old rows unless you also roll off corresponding facts. And even if a dimension row is old, it doesn't mean it isn't used.

If I needed to partition a dimension table, I would be more inclined to use some kind of random/round-robin method (such as the mod of the primary key) to distribute the rows evenly across partitions, with local indexes, so that any query against the table would be handled in parallel. I would be less concerned about age of the row because it doesn't matter much from a table maintenance or performance standpoint. With that said, I would bet there are probably very valid cases where date is the perfect partitioning criteria for a dimension table... I just can't think of one... (note: foreign exchange rates are technically modeled as fact tables. Just in case someone was going to suggest that...)
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase Empty Re: Partitioning Strategy for Fact and Dims in Oracle/DB2/Sybase

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum