TheThinkingPerson’sGuidetoDataWarehouseDesignPresentation.ppt

Agenda What is the key component for success? First – get/use a modeling tool The logical design for OLTP Simple reporting databases The logical design for analytics/data warehousing Logical Design Considerations Manual horizontal partitioning Manual Vertical Partitioning Pro’s/con’s to manual partitioning The bottom line on logical modeling General list of top BI database design decisions Divide conquer is the best approach Divide conquer via sharding What technologies you should be looking at Row or column-based engine? Column vs. row orientation Example: InfiniDB vs. “Leading” row DB Why not use both…? Why not use both…? Most used DW Storage engines internal to MySQL What about NoSQL options? Partitioning – not ‘if’ but ‘how’ mysql CREATE TABLE part_tab - ( c1 int ,c2 varchar(30) ,c3 date ) - PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), - PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , - PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , - PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , - PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , - PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), - PARTITION p11 VALUES LESS THAN MAXVALUE ); mysql create table no_part_tab (c1 int,c2 varchar(30),c3 date); *** Load 8 million rows of data into each table *** mysql select count(*) from no_part_tab where c3 date 1995-01-01 and c3 date 1995-12-31; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (38.30 sec) mysql select count(*) from part_tab where c3 date 1995-01-01 and c3 date 1995-12-31; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (3.88 sec) Partitioning – Stripe your Partitions CREATE TABLE T1 (col1 INT, col2 CHAR(5), col3 DATE) ENGINE=MYISAM PARTITION BY HASH(col1) (