Roy Anderson <roy.anderson@xxxxxxxxx> wrote: > We have an OLTP database and no data warehouse. We are currently > planning out a build for a data warehouse however (possibly using > Hadoop). "X" is recommending that we convert our current, > normalized OLTP database into a flattened Star Schema. I'm not going to repeat good advice you've already gotten in other answers, but I will point out that complex reporting off of normalized data is often much faster if you have been able to use natural keys, even if you need to go to multi-column primary keys to do so. One of the biggest down-sides of synthetic primary keys (where, for example, you might have a single-column PK column called "id" in every table) is that forces one particular route to "navigate" the tables. With natural keys a complex query often finds intriguing plans to give the results you ask for using plans you might never have thought of, and which can be orders of magnitude faster than the plans which would be possible if the joins are all done using synthetic keys. Beyond that, I would say that I would never jump to some "star" schema automatically. There are various ways data can be summarized for faster reporting, and a flattened star schema is only one option, which is not always the fastest option -- or even an improvement over 3NF. At the risk of repeating a little, I recommend looking at what it would take to generate the reports you want of the current data; and only denormalize where something is too slow, using the summarization which appears to be the most sensible for the use case. As an aside, I had a case where auditors wanted a particular report off of a 3 TB OLTP database. One programmer tried to write it using imperative code and looping. Based on how far it got in the first 5 hours, it would have taken a year to complete. Rewritten with a couple CTEs as a single declarative query (one SELECT statement) it ran in ten minutes. No star schema needed -- just some clear thinking, and making use of the power of declarative coding and a great optimizer. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
