Data Warehousing with MySql

[e]

Data Warehousing with MySql

Data Warehousing is the idea of setting up a separate database structure for purely reporting purposes, so you can stop compromising between reporting and transactional demands.
MySql is a database that is fast, easy to use, and (generally) free.

> Aggregation
> Benchmarks
> ClickstreamData
> DatesAndTimes
> Home
> Indexing
> KPIs
> Links
> MathML
> Money
> MysqlCompared
> PhpPlugins
> Recipe
> Regression
> Scripting
> Sudoku
> SummaryData
> TableTypes
> TuningMysql

New Page:

search:
>

edit css

show debugging

contact: tom (.) cunningham (at) gmail (dot) com

[e]

Summary Data

Aggregate Navigator

An aggregate navigator rewrites queries to use aggregate tables instead of base tables, where possible. All transparent to the end-user.

SQL-Server has "indexed views", which does similar thing inside the RDBMS, but are meant to be unreliable (see below).

Problem: The Ralph-Kimball idea seems to not be 100% reliable, ie., eg. a query clause like "SUM(IF(amount>10,1,0)) AS over_ten" would give the *wrong* answer if rewritten to use the aggregate table. -- So either you have to put up with this, or have a reasonably subtle query-rewriter.

Aggregate Navigation With (Almost) No Metadata - Ralph Kimball [dbmsmag.com]

Indexed Views

Seem to be a feature of SQL-Server standard edition, which let the server return results for group-by queries very quickly.

SQL Server Indexed Views: Too much of a good thing? [terris.com] (Aug 04) -- discussion of MS-SQL indexed views, & why so useful in a d-warehouse (& as a reason against MySql).