data warehouse and database difference in implementation

Question

Can anyone tell me the difference between a simple database and a data warehouse in terms of implementation?

I know that data warehouse is used for analysis rather than keeping record but I don't understand how are they structurally different

In simple database we have tables and so in a data warehouse. How can we make a data warehouse out of a simple database

In both cases we have queries so how are they different for each of them?

Answer 1

The differences are in the implementation, that is the representation (structure) of the data in tables.

A simple database is typically structured in normalized tables in order to minimize redundancy and optimize writing operations to the table. This can be achieved by dividing large tables into smaller and less redundant tables, so that data of the same kind are isolated in one place so that additions, deletions, and modifications of a field can be made in just one table. The smaller tables are then connected together via defined relationships between them (this is done by foreign keys), resulting in many joins between tables when retrieving the data.

On the other hand a datawarehouse is structured for reading operations only, which is why a datawarehouse accepts some level of redundancy in the data, because this makes reading faster. In a datawarehouse data is typically structured in what is called a Starschema approach through the use of dimensional modeling. That means you have 1 big table (Facttable) with all the relevant records and measures (fx sales amount in $), and then many minor tables (called dimensiontables) that describes the values in the facttable. Dimensiontables could be something like Date, SalesCountry, SalesPerson, Product etc. that all describes the sales amount from the facttable. The dimensiontables are then related to the facttable with foreign keys, thereby creating the star like figure with the facttable in the middle and all the dimensiontables around it in a circle linking to it.

NB: This is a very simple introduction, and you should of course refer to some datawarehouse litterature to read more details. Look for books by Ralph Kimball and Bill Inmon, they are the gurus within the datawarehouse field.