Telecom Example Database

Telecom schema is a simple star schema that represents a summary of the calls made by the customers of a fictional cell phone service provider. Each table is described in a separate section.

Table Name

Default Number of Rows

Billing_Fact

Each tuple in the fact table represents a summary of the CDR records generated at tje switch for each customer.

Field Name	Data Type	Description/Example
Date_Key	Integer	Call Date. Foreign Key, references Date table key
Plan_Key	Integer	Foreign Key, references Rate_Plan table key
Calling_Number_key	Integer	Calling party number. Foreign Key, references Number table key
Called_Number_Key	Integer	Called party location. Foreign Key, references Number table key
Cust_Key	Integer	Calling Party customer id, who will be billed for the call and services. Foreign Key, references Customer_Details table key
Feature_key	Integer	Foreign Key, references Feature table key
Termination_key	Integer	Call Termination Type i.e. normal or abnormal
Equipment_Key	Integer	Type of Equipment
Roaming_Flag	Bool	Whether this call is made/recd while roaming
Call_Duration	timestamp	Duration of the call
Number_of_Calls	Integer	Total number of calls made during the day
Total_Minutes	Integer	Total number of minutes of calls made that day
Total_Roaming_Minutes	Integer	Total number of roaming minutes used
Total_Long_Distance_Minutes	Integer	Total number of long distance calls made.
Total_Call_Cost	Float	Total cost of all the call
Total_Roaming_Cost	Float	Total roaming charges
Total_Long_Distance_Cost	Float	Total Long Distance charges
Service_tax	Float	Service tax
Surcharge	Float	Surcharge

5000000

Customer_Details_Dimension

This table describes the customers of the service provider.

Field Name	Data Type	Description/Example
Cust_Key	Integer	Primary Key
Cust_Name	Varchar	Customer/Subscriber Name
Cust_Age	Integer	Age of the customer
Cust_Sex	Char	Male/Female (M/F)
Cust_City	Varchar	City of the customer
Cust_State	Varchar	State of the customer
Cust_Zip	Varchar	Zip/postal code of the customer

50000

Rate_Plan_Dimension

This table describes all the rate plans offered by the service provider.

Field Name	Data Type	Description/Example
Plan_Key	Integer	Primary Key
Plan_Name	Varchar	Common/Business Name of the rate plan say 'Freedom25'
Plan_Description	Varchar	Description of the rate plan
Plan_Min_Rental	Float	Minimum monthly rental for this rate plan say 24.99 (USD)
Plan_Currency	Varchar	Plan Currency (USD)
Plan_Pulse_Rate_sec	Integer	Pulse rate available in the plan say 30 sec pulse or 60 sec pulse
Toll_rate	Float	Call charges for the plan

500

Numbering_Plan_Dimension

This table describes the types of numbering plans. This is used to distinguish between originating and terminating destination calls.

Field Name	Data Type	Description/Example
Number_Key	Integer	Primary Key
Country_Code	Varchar	Country code part of the phone number.
Area_Code	Varchar	3 Digit Area Code of the Phone
State	Varchar	State Code of the Numbering Plan.
Phone_Type	Varchar	Type of the phone fixed/GSM/CDMA

500

Equipment_Dimension

This table describes type of equipment (handsets) used by customers of the service provider.

Field Name	Data Type	Description/Example
Equipment_Key	Integer	Primary Key
Equipment type	Varchar	Landline/mobile/WLL/blackberry/wireless card
Manufacturer	Varchar	Nokia/Sony-Ericsson/Motorola
Model Number	Integer	Phone model number

200

Feature_Dimension

This table describes every feature offered by the service provider.

Field Name	Data Type	Description/Example
Feature_Key	Integer	Primary Key
Feature_type	Varchar	SMS/MMS/Call Forwarding/Call Waiting/
Feature_rate	Varchar	Feature cost per invocation

Call_Termination_Dimension

This table describes all possible reasons for which a call can be terminated.

Field Name	Data Type	Description/Example
Termination_key	Integer	Primary Key
Termination_type	Varchar	ITAU Termination Type (normal, service failure, equipment failure, etc.)
Termination_Description	Varchar	ITAU Termination Description