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.

Tableau Actions

Dear Folks,

Welcome to tableau-viz-blog. This post is all about tableau actions. Main objective of this post to get you familiar about below topics in detail -

• Purpose of source and target objects
• Filters apply through actions
• Tableau default filter
• Save your time through layouts
• Fit your dashboard objects
• Objective of horizontal and vertical objects
• Other Workarounds

Purpose of source and target objects - Action is an occurrence of any activity or event. Whenever you create an action, you should choose your source and destination (target) objects very carefully. Tableau provides different objects for source and destination -

• Choose data source as source or target object
• You can select worksheets for source as well as target object
• We can also choose dashboard for source and target objects as an action.

Note - Depends on requirement we can choose the actions objects. In order to do so, go through menu bar and select Dashboard -> Actions -> Choose Source Object and Destination object accordingly

Filters apply through actions - We can apply filters through actions. For filters, please choose below steps -

• Go to Dashboard through menu bar and select actions
• Click on showing right side, different filtering options -

• Filter by menu
• Filter by filter 
• Filter by link
• Click on target filters if there is no relationship between source and target objects. 
• Drag and drop the columns on filter section first on both source and target worksheets first
• then select column name of source column for target filter.
• If you want to control specific filter on dashboard then right click on filter and then choose either of below values and choose worksheets -
• Only this worksheet
• Select Specific Worksheet
• All using this data source

Tableau Default Filter - We can select any of the worksheet under the dashboard. Right click on worksheet and then select 'use as filter' to apply default filter actions on all worksheet under the dashboard. You can check this filter (generated) on menu bar --> Dashboard --> Actions --> filter (generated)