Enterprise Data Warehouse Optimization

Name
Economic sources
Lecturer
Date

surname1
Drivers and benefits of data warehousing
Data warehouse is the relational database that stores and fetch data that is similar to a normal
SQL query. Under normal circumstances the big data doesn’t follow proper database structure
therefore we need to use hive or spark SQL to see the data by using hive specific query. 100%
data loaded into data warehousing is used for analytics reports.
Star Schema: In this Schema each of the dimensions are individually connected to the fact table
in the centre giving it a star-like diagram. One major difference is that it is NOT normalized
Since they are not normalized it is easier to write queries (you need to write less lines of INNER
JOINs to drill through dimensions).
 This type of table is generally preferred when there are lesser rows in the dimensions.

surname2
Snowflake Schema: One fact table connected to many dimensions, but some dimensions are
linked to each other or normalized to improve query efficiency and reduce data redundancy (less
redundancy = easier to maintain and change data). It gets the name because of the dimensions
being connected to each other in an intricate fashion. The main difference here is that it is
normalized. This is done to ensure the integrity of data and reduce redundancy. As a result,
querying data is much more time consuming and tiresome as compared to Star Schema. An
additional advantage it has over a star schema is that it requires lesser storage space.
 Snowflake schema is preferred when there are too many rows in the dimensions.