Creation of OLAP Data Warehouse Solution from OLTP Database

While creating OLAP data Warehouse solution in this NorthwindLite database example, I have created Products, Customers and Date dimension table. Since Product Category does not have very critical fields to make separate table, I merged product category fields with Product dimension table and make it one Products dimension table. Next I have created Customers dimension table where I skipped Customer Address field which is less important. Later I created FactOrders fact table where I created surrogate keys and primary keys.

I created date dimension table and also added OrderDatekey to the factorders table.

once the data warehouse plan worksheet has done I created the DWNorthwindLite Data Warehouse solution.

OLTP vs. OLAP Databases

OLTP

1.         Transactions such as Insert, update, delete.
2.         Usually a Entity-relationship model.
3.       Normalized database with lots of tables and not much duplicated data.
4.          Used for storing detailed and up to date data.
5.          Lots of transactions that is constantly updating.
6.           Data may be collected from multiple points of entry, e.g. web application, desktop application, point-of-sale.
7.      OLTP databases are primarily used to collect and store transaction data from a variety of inputs.

OLAP

1.          Mainly select queries with very little updates.
2.          Star or snowflake model.
3.       De-normalized so data is aggregated in fact and dim tables.
4.          Mainly used for data mining and reporting.
5.          Often complex queries that include aggregation.
6.          Needs fast response time.
7.      OLAP databases are primarily used for reporting and analysis of data.  
8.      Serves the needs of analysts, managers and executive employees in planning, problem solving and decision support.