Scenario


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.

Tables and Data


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 :

  1. Subscriber Rated Usage

    contains entire details of a subscriber's cell phone usage.
  2. Dedicated Account Usage

    contains call data records of subscribers for calls charged against a dedicated account
  3. Subscriber MSISDN History

    contains details of the subscriber's connection plan

Dimension Table :

  1. Dedicated Account

    contains details of plans on offer for all subscribers


Sample Report


Cell phone usage details of all subscribers of one state on a particular day (here - 02/02/2009) are generated using this query.