Reference no: EM132596917

Case Study -Database Pioneer Rethinks The Best Way to Organize Data

Is there a better way to build a data warehouse? For years, relational databases, which organize data in tables composed of vertical columns and horizontal rows, have served as the foundation of data warehouses. Now database pioneer Michael Stone bra k er is promoting a different way to organize them, promising much faster response times.

As a scientist at the University of California at Berkeley in the 1970 s, Stone braker was one of the original architects of the Ingres relational database, which pawned several commercial variants. A row-based system like Ingres is great for executing transactions, but a column-oriented system is a more natural fit for data warehouses, Stone bra k e r now says. SQL Server, S y base, and Te r a data all have rows as their central design point.

Yet in data warehousing, faster performance may be gained through a column layout. Stone braker says all types of queries on "most data warehouses" will run up to 50 times faster in a column database. The bigger the data warehouse, the greater the performance gains. Why? Data warehouses frequently store transactional data, and each transaction has many parts. Columns cut across transactions and store an element of information that is standard to each transaction, such as customer name, address, or purchase amount. A row, by comparison, may hold 20-200 different elements of a transaction.

A standard relational database would retrieve all the rows that reflect, say, sales for a month, load the data into system memory, and then find all sales records and generate an average from them. The ability to focus on just the "sales" column leads to improved query performance. There is a second performance benefit in the column approach. Because columns contain similar information from each transaction, it's possible to derive a compression scheme for the data type and then apply it throughout the column.

Rows cannot be compressed as easily because the nature of the data (e.g., name, zip code, and account balance) varies from record to record. Each row would require a different compression scheme. Compressing data in columns makes for faster storage and retrieval and reduces the amount of disk required. "In every data warehouse I see, compression is a good thing," Stonebraker says. "I expect the data warehouse market to become completely column-store based."

Explain the difference of techniques used in Relational DMS and data warehouse.