Multi-dimensional Data Warehouse Schema


Dimension Tables = A table contains information about an object such as customers, products, sellers, date_time. As its nature, each set of data has more than one dimension.
Fact Tables = A table contains a measurement data such as orders; thus, an order combines information of customer, product, seller, date_time, branch, etc. The fact data will be used for measurement in that data mart or data warehouse. It can be one or more fact tables in a data warehouse.
Multi-dimensional = more than one dimension tables.

Thus, Multi-dimensional Schemas have 3 types as follow:

  • Star-Schema = The fact table contains FK of dimension tables while each dimension table contain complete attributes of its dimension (has no join to other tables)
  • Snow-Flake Schema = The fact table contains FK of dimension tables while the dimension tables itself might contain FK of other dimension tables as a hierarchy.
  • Galaxy Schema or Family of star Schema = There're more than one fact tables which share same dimension tables (connected stars)
* Slowly changing dimension = The dimension in which their value can be changed slowly by time i.e. based salary of junior sellers, customer's address, employee's name, etc.

* Low cardinality column = An attribute column that has less different values.


For more details:
(Eng) https://www.guru99.com/star-snowflake-data-warehousing.html
(Thai) https://www.9experttraining.com/articles/รู้จักกับโครงสร้างข้อมูล-แบบ-dimensional-model

How to Data Modeling
https://www.guru99.com/data-modelling-conceptual-logical.html

Comments

Post a Comment

Popular posts from this blog

2>&1 in command means??

References.. 0 =  standard input  ( stdin ) 1 =  standard output  ( stdout )  2 =  standard error  ( stderr ) Hence, we can use command > output.log to print the std output stream, which is an implicit meaning of command 1>output.log Thus, the annex 2>&1 means print the std error to the same place as the std output.  Moreover, the we can hide the std output from printing out by using 2>/dev/null. Thanks to: https://linuxize.com/post/bash-redirect-stderr-stdout/ Learning bash: https://iridakos.com/programming/2018/03/01/bash-programmable-completion-tutorial
Read more

MySQL Function: Thai months to String

 /*--------- Function: get_month ---------*/ DROP FUNCTION IF EXISTS get_month; DELIMITER $$ CREATE FUNCTION get_month(   m VARCHAR(100) ) RETURNS int(2)     DETERMINISTIC BEGIN  DECLARE me int(2) default 0;      IF m= 'ม.ค.' THEN set me= 1;     ELSEIF m= 'ก.พ.' THEN set me= 2;     ELSEIF m= 'มี.ค.' THEN set me= 3;     ELSEIF m= 'เม.ย.' THEN set me= 4;     ELSEIF m= 'พ.ค.' THEN set me= 5;     ELSEIF m= 'มิ.ย.' THEN set me= 6;     ELSEIF m= 'ก.ค.' THEN set me= 7;     ELSEIF m= 'ส.ค.' THEN set me= 8;     ELSEIF m= 'ก.ย.' THEN set me= 9;     ELSEIF m= 'ต.ค.' THEN set me= 10; ELSEIF m= 'พ.ย.' THEN set me= 11;     ELSEIF m= 'ธ.ค.' THEN set me= 12;     END IF;         RETURN me; END $$ DELIMITER ;
Read more

Ruby on Rails with PosgreSQL : Basic tutorial

Installation Steps - Install Ubuntu and upgrade - Install Apache2 - Install Rails - Install Ruby - Install Passenger More Info:  https://nathanhoad.net/how-to-ruby-on-rails-ubuntu-apache-with-passenger Command Guides * To install Apache 2 development headers: sudo apt-get install apache2-dev * To install Ruby development headers: sudo apt-get install ruby-dev * To in stall Passenger gem install passenger passenger-install-apache2-module * To install Apache Portable Runtime (APR) development headers: sudo apt-get install libapr1-dev * To install Apache Portable Runtime Utility (APU) development headers: sudo apt-get install libaprutil1-dev * To install Curl development headers with SSL support: sudo apt-get install libcurl4-openssl-dev (or libcurl4-gnutls-dev) ------------ Set c onfig passenger ----------- LoadModule passenger_module /home/web05/.rbenv/versions/2.5.1/lib/ruby/gems/2.5.0/gems/passenger-5.3.4/buildout/apache2/mod_passenger.so
Read more