production schema

Question

Hi,

Can someone help me in understanding the difference between production schema and datawarehouse schema.

Thanks in Advance Manoj Bhattu

Answer 1

Don't call it "production schema". Call it "Transactional Schema", or OLTP.

To make updates work, it must be in (at least) Third Normal Form (3NF).

Data warehouses use "Star Schema" which is normalized according to different rules. Since updates don't really occur the same way they do in a transactional database, the design is normalized completely differently.

Read up on 3rd Normal Form.

Read up on Star Schema Design.

There are lots and lots of Google links that are very good.

Better yet, but Kimball's The Data Warehouse Toolkit book. That's very clear.

Answer 2

At my current gig, we use the term "Transactional" and/or OLTP (thanks S.Lott) for the databases/schemas that represent the "current" state of the business data. The "Analytical" and/or OLAP databases/schemas hold more historical data. Often we'll take a "snapshot" of the current data (writing out a new row to a new table, similar in design to the transactional table) and then date stamp it and/or assign it a "batch" ID. Then we can easily analyze how that data has changed over time.

For instance: you could see how & when the name of a Company has changed. In some circumstances, this is useful information. In others, you only want the current name. Splitting these two scenarios into different tables/databases means that you don't have to worry about always finding the "most recent" record in a historical table; all of that information has been moved out of the way. Another use is if you need to regenerate reports based on past data (if you want a "how did this data look back then?" view).

This isn't necessarily the "correct" definition... but it is a useful one.

Answer 3

Production, operational, transactional is a "standard normalized (3NF) DB" optimized for data storage and management. Data warehouse (DW) star schema is optimized for reporting and analytic. From business user perspective, data warehouse is read-only. DW is de-normalized -- a piece of information is repeated in many places -- so they are updated only via "loading programs" (ETL), no user interaction. Here is an example of the star schema.