Data Warehouse (OLAP) Vs. Operational Database(OLTP)

Sr.No.	Data Warehouse (OLAP)	Operational Database(OLTP)
1	It involves historical processing of information.	It involves day-to-day processing.
2	OLAP systems are used by knowledge workers such as executives, managers, and analysts.	OLTP systems are used by clerks, DBAs, or database professionals.
3	It is used to analyze the business.	It is used to run the business.
4	It focuses on Information out.	It focuses on Data in.
5	It is based on Star Schema, Snowflake Schema, and Fact Constellation Schema.	It is based on Entity Relationship Model.
6	It focuses on Information out.	It is application oriented.
7	It contains historical data.	It contains current data.
8	It provides summarized and consolidated data.	It provides primitive and highly detailed data.
9	It provides summarized and multidimensional view of data.	It provides detailed and flat relational view of data.
10	The number of users is in hundreds.	The number of users is in thousands.
11	The number of records accessed is in millions.	The number of records accessed is in tens.
12	The database size is from 100GB to 100 TB.	The database size is from 100 MB to 100 GB.
13	These are highly flexible.	It provides high performance.

What are additive, semi-additive and non-additive measures?

Non-additive Measures

Non-additive measures are those which cannot be used inside any numeric aggregation function (e.g. SUM(), AVG() etc.). One example of non-additive fact is any kind of ratio or percentage. Example, 5% profit margin, revenue to asset ratio etc. A non-numerical data can also be a non-additive measure when that data is stored in fact tables, e.g. some kind of varchar flags in the fact table.

Semi Additive Measures

Semi-additive measures are those where only a subset of aggregation function can be applied. Let’s say account balance. A sum() function on balance does not give a useful result but max() or min() balance might be useful. Consider price rate or currency rate. Sum is meaningless on rate; however, average function might be useful.

Additive Measures

Additive measures can be used with any aggregation function like Sum(), Avg() etc. Example is Sales Quantity etc.

What is Factless fact tables?

A Factless fact tables are the fact table which doesn’t contain numeric fact column in the fact table.

Data Warehousing Schemas

1.        Star Schema

2.       Snowflake Schema

3.       Fact Constellation

Star Schema

·       A single large central fact table and one table for each dimension

·       Every fact points to one tuple in each of the dimensions and has additional attributes

·       Does not capture hierarchies directly.

j     

      

      Snowflake Schema

·       Variant of star schema model.

·       A single, large and central fact table and one or more tables for each dimension.

·       Dimension tables are normalized split dimension table data into additional tables.

Fact Constellation

·       Multiple fact tables share dimension tables.

·       This schema is viewed as collection of stars hence called galaxy schema or fact constellation.

·       Sophisticated application requires such schema.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.