The scenario represents a section of the data warehouse environment pertaining to the telecommunication domain. The database contains three fact tables and one dimension table to keep customers' cell phone usage information of four different states. Daily reports are generated against these tables. This tutorial highlights the best practices for good physical database design in order to improve the performance and manageability of the data warehouse.
A multiple partition environment consisting of four nodes is created. Two partition groups are created in the database, one to host the fact tables and their associated objects and the other to host the dimension table and its objects. Two tablespaces 'Factspace' and 'Indspace1' are created in the first partition group. The fact tables are spread across three nodes in the factspace and the indexes reside in indspace1. Two tablespaces 'Dimspace' and 'Indspace2' are created in the second partition group. The dimension table is spread across one node in the dimspace and its index resides in indspace2.
Fact Tables :
Dimension Table :
Cell phone usage details of all subscribers of one state on a particular day (here - 02/02/2009) are generated using this query.