Transactional and Analytics Systems

Welcome Folks!!

In this blog, we will get to know about data modelling concepts. There are two types of systems available in data modelling : 

1. Transaction System (OLTP) - 

• In the transactional system or online transaction processing system, day-to-day operations information is stored. 
• The transaction system does not contain any historical information.
• Data is basically stored in normalisation form where there would be no dups records available and tables are divided into multiple tables in order to remove dups.
• Generally, data is written in a quicker fashion but the reading of data is a bit slow. That's the reason, indexes are created in OLAP system to perform read operations faster.
• Generally in any transactional system, there are two types of tables available in OLTP -
• Master table - these are the tables where data is stored in entities. It basically defines the business attributes. It stores the data in the form of attributes and hierarchies.
• Basically, a primary key is stored in the master table
• Transactional Table - It stores the fact and figures related information to the table
• Foreign keys are stored in the transactional table
• It stores the value in the form of calculation or aggregation thing. 

2. Datawarehouse System (OLAP) - There are two types of tables available in OLAP system  -

• Facts -  It stores the fact and figures related information to the table - 

• Factless fact 

• Additive fact 

• Semi additive fact 

• Non-additive fact  

• Dimensions - It basically defines the business attributes. It stores the data in the form of attributes and hierarchies. Follwing are the types of dimensions - 

• Role-playing - In  date dimension, primary key is related to multiple dates (as foreign keys) of fact table

• Degenerate dimension - which can not be generated. If a table contains values, which are neither dimension nor measures is called degenerate dimensions.Ex : invoice id,EmpId. 

• Junk dimension -  Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub-dimension. 

• Confirmed dimension - One dimension is related to multiple fact tables 

 OLAP Schema - Following are the types of schemas available in the OLAP system 

• Star schema - one fact table in the centre related to other dimensions. The denormalization concept follows. Reading operation is better in it but the writing of data  is not
• Snowflake schema - one dimension relates to the intermediate dimension of the fact table. It basically slows the performance of processing. The normalization concept follows this schema.
• Double star or galaxy schema - one dimension connected with multiple fact tables. It contains more than one confirmed dimension.

OLAP Hierarchies -

Ragged Hierarchy - which grows continuously like employee dimension ( Manager--> team lead--> employee --> junior employee --> subordinates)

• The parent member of at least one member of a dimension is not in the level immediately above the member. 
• Like unbalanced hierarchies, the branches of the hierarchies can descend to different levels.

Fixed hierarchy / balanced - which does not change with time like year -->qtr-->month

Non fixed hierarchy/ unbalanced - which changes with time. Unbalanced hierarchies include levels that have a consistent parent-child relationship, but have logically inconsistent levels. 

• The hierarchy branches can also have inconsistent depths.
• An example of an unbalanced hierarchy is an organization chart, which shows reporting relationships among employees in an organization. 
• The levels within the organizational structure are unbalanced, with some branches in the hierarchy having more levels than others.