Data warehousing with MySQL By Anand Pandey. MySQL. MS-SQL. Oracle. DB2. Flat Files. MySQL. Agenda. Introduction Free and Open Source Software Data Warehousing application Extraction, Transformation and Loading Partitioning and Storage Engine Configuration Parameters
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
MySQL AB develops and markets a family of high performance, affordable database servers and tools.
MySQL is a key part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python), a fast growing open source enterprise software stack.
Anand Pandey, Senior Consultant, MySQL Inc.
Josh Chamas, Senior Consultant, MySQL Inc.
MySQL is licensed under GPL.
The GPL is a Free and Open Source Software (FOSS) license that grants licensees many rights to the software under the condition that, if they choose to share the software, or software built with GPL-licensed software, they share it under the same liberal terms.
Quid Pro Quo
MySQL has a dual license that works on a quid pro quo basis—i.e., if you're free, MySQL is free. If you're closed, you need a license.
Advantages of Open Source
MySQL has 5 million plus active installation base.
New releases immediately downloaded by users providing early feedback on bugs and features.
Access to source code
Write your own features/proprietary Storage Engine
Data Warehouse is a relational database.
It is designed for query and analysis rather than for transaction processing.
It enables an organization to consolidate data from several resources.
SOLUTION: Create and Manage Data Warehouse.
Staging Area and its benefits
Relational Table structures are flattened to support extract processes in Staging Area.
The MERGE Table
MERGING based on month as Range
MERGE Table Example
mysql> CREATE TABLE jan04 ( -> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> message CHAR(20));
mysql> CREATE TABLE feb04 ( -> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> message CHAR(20));
mysql> CREATE TABLE year04 ( -> a INT NOT NULL AUTO_INCREMENT, -> message CHAR(20), INDEX(a)) -> TYPE=MERGE UNION=(jan04,feb04) INSERT_METHOD=LAST;
MyISAM Storage Engine
Restrictions on MERGE tables
my.cnf parameters for DWH (example)
Using MySQL database server