Corporations Success In Market

Published Date: 02 Nov 2017

Introduction

Corporations success in market is increasingly relying on the strategic information in the decision making process.

It is needed that the corporations design specialized systems to collect operational data and transform this data in information and knowledge based systems in order to retrieve strategic information about their operations.

AMC,(Albanian branch of T-MOBILE communications), launched commercial operations in 1996, being the first mobile telecommunications company to operate in Albania. Following a successful track record of dynamic growth and strong performance at all levels, AMC today holds the leading position in the domestic market, with an estimated market share of approximately 52%.

It’s main products are mobile services for Prepay and Postpaid customers.

This project will consist in designing a database system for Prepay customers of a mobile network operator.

The database design will be introduced from conceptual to physical model based on business data requirements, database schema and database integration.

Business rules will be identified and Entity Relationship and Extended modeling techniques will be used for the design of the database based on the rules.

Last phase will consist in Data warehousing design in order to be able to retrieve quality information that will help decision making for management.It will introduce the data warehouse approach,model and design techniques used based in the database system designed in the first phase.

Data Requirements

Prepay system architecture in AMC is very complex comprising of several platforms interrelated together.

The charging system is called Intelligent Network Platform,INP and it is the platform which is used to configure the flow of mobile operator functions and services.It also generates records based on the subscriber activities which are used by the customer care system.

In order to have a general view of how the prepay overview of Call and Event Detail Records on the INP it is needed a brief description on how it works.

The INP continually generates text records of all activity relating to the accounts of prepaid subscribers. Normally, these records are passed to the Network Operator’s customer care system. The records can then be used for accounting, auditing and reporting purposes. There are two categories of records:

• Call Detail Record (CDR)

A Call Detail Record is produced to record each call made by a subscriber.

• Event Detail Record (EDR)

An Event Detail Record records significant events occurring to a subscriber’s account (for

example, recharges, first call made, and so on).

The records are generated by the CDR server which is running on INP platform in batch files and stored in local directories.

The attributes in the file are divided by a "|" (pipe) separator.

In the current configuration record files generated in Prepaid systems combined with other data coming from different departments are manipulated through unix scripts in order to produce different kind of reports.

The statistical data retrieved serve as an input to several departments contributing also in the decision-making process for higher management.

Customer

A customer can live in different locations of Albania and in order to use AMC services he needs to buy a SIM card in any branded shop.

In order to be able to use the SIM he needs to perform a First Call which activates all the services.Then he is able to use the SIM accordingly to rules defined.

SIM

It is a physical electronic chip which holds a database of subscriber information and services as subscriber number,location information,services enabled etc. and is used by the GSM network in order to provide services to the specific subscriber.

Each SIM is logically assigned to a record in INP database system,so information regarding credit balance,service allowances etc. are assigned to the SIM in the moment it is used by a subscriber.

A SIM in order to be able to use its services should have the services enabled and should have enough credit in his account.

Tariff Plan

This is a set of preconfigured parameters which segmentise the SIM domain in several subgroups in order to be able to define different services,charges,offers for different subgroups.It is a necessary tool needed by Products and Services Departments for dividing the market in different groups based on age,location,preferences etc and create specific products and services for each of them in order to increase usage and revenues.

Services

Services are a set of different parameters preconfigured in the system which define the service a customer can use and the specific charges for each service as well as tariffs toward different destinations.

The services can be configured in tariff plan bases meaning that each tariff plan may have different services enabled.

Events

Each subscriber can use the services preconfigured in his account if he has enough credit in his account.The events performed by a subscriber can be a SMS,MMS,Call,Internet,Recharge etc.

Each of the events produces a different number in CDR generated thus can be easily distinguished among each other.

The events can be charged from the subscribers main account or from the bundle the subscriber buys through his credit.

Sysem Definition

The purpose of system definition is to define the scope and boundary of database system and its major user views(ref.Connolly book pg331).

Defining System Boundaries

The following diagram shows the system boundary of the AMC project based on the business needs and the relation between business processes:

Project Narrative

A SIM in order to be used should be assigned to a customer who buys it from the shops.

A SIM has services enabled according to the tariff plan it belongs to.

A SIM may change its tariff plan,so we should keep history of tariff plan transitions of a subscriber.

Each tariff plan,which in other words is a subset of overall prepay customers has services attached to it as for example SMS service,MMS service,Internet etc.

Each SIM has a lifecycle which is directly related with the customer behavior.At the beginning when it is created in the database it exists with the state 0 which means PREACTIVE.

Upon customer registration and after the first call(which is mandatory in order for it to be activated) the SIM transits in state 1,ACTIVE.

State ACTIVE means he is able to use all his services.

Then if he does not make any recharge for 6 months his state changes to NOCREDIT and RECHARGEONLY,state 2 and 3,meaning he can not use any service without performing a recharge,except of calling Customer Service.

If Customer Care observes any fraud case or in case of SIM lost etc they are able to change the state of the SIM to BARRED(state 4),in which no service can be used at all.

In case the customer does not perform any recharge when he is in state 2 and 3 for 3 months then his state is DEACTIVE and the SIM is marked to be removed from the system.

Taking in consideration the above description the SIM state flow is dynamic thus we need to keep historic data for each SIM state transition.

Business rules

A customer can have one or more SIMs,but a specific SIM belongs to one customer or may not since a SIM can exist without a customer in the moment of creation in prepaid database.

A tariff plan is assigned to zero or more SIM numbers,since it can it can exist as a standalone when it is first created in the database,and a SIM may change from one tariff plan to another thus have one or many tariff plans.

A tariff plan may have one or more services,and a service may be associated to one or more tariff plans.

A SIM may have one or more states and each of the states may be combined with one or more SIM.

A SIM may have one or more call groups in his services,and a call_group is assigned to zero or more SIMs

A SIM performs zero or more events,and an event can be made by one or more SIM numbers.

An event can be specialised in 3 subgroups Recharge, Call and Data.

Calls are specialised into two subgroups NormalCall and BundleCall

Data is specialised into 4 subgroups SMS,MMS,ShortCode,Internet

SMS is specialized into 2 subgroups NormalSMS and BundleSMS

MMS is specialized into 2 subgroups NormalMMS and BundleMMS

Internet is specialized into 2 subgroups NormalInternet and BundleInternet

Call can have exactly one Destination,but a destination can exist for zero or more Calls

SMS can have exactly one Destination,but a destination can exist for zero or more SMS

MMS can have exactly one Destination,but a destination can exist for zero or more MMS

A Destination can have one or more Operator,and one Operator can belong to exactly one Destination

Conceptual Database Design(ref.Connolly pg 442)

a)Identify the Entity types by grouping them in objects with the same properties,which are identified by the enterprise as having an independent existence(ref:Connolly pg.343)

Entity Type Documentation

Entity name

Description

Aliases

Occurrence

SIM

General term describing all SIM details

SIM

Each SIM performs zero or more events,and its state which may change in time.Each SIM has a tariffplan associated which may change in time.

Customer

General term describing all Customer details

Customer

A customer can have one or more SIMs.

TariffPlan

General term describing TariffPlan details

TariffPlan

Each tariff plan has one or more services associated

Call_Group

General term describing the phone numbers in each SIM list

Call_Group

Each call group is associated to zero or one SIM

State

General term describing the state of a subscriber

State

Each state is assigned to one or more SIM

Event

General term describing the event a SIM performs

Event

Each event superclass consists of different 5ubclasses as Rechage,Call,Data

Call

General term describing the call event a SIM performs

Call

Each call superclass consists of two 5ubclasses,NormalCall and BundleCall

Data

General term describing the data event a SIM performs

Data

Each data superclass consists of four subclasses,SMS,MMS,ShortCode,Internet

Recharge

General term describing the recharge event a SIM performs

Recharge

A recharge event is performed by one or many SIMs

Bundle

General term describing the bundle buy event a SIM performs

Bundle

A Bundle buy event is performed by one or many SIMs

NormalCall

General term describing the call event performed through normal charging rates

NormalCall

A normal call event is performed by one or more SIM cards

BundleCall

General term describing the call event performed through bundle(no charge)

BundleCall

A bundle call event is performed by one or more SIM cards

SMS

General term describing the SMS superclass

SMS

A SMS superclass consists of two 5ubclasses NormalSMS and BundleSMS

NormalSMS

General Term describing an SMS event charged through normal rates

NormalSMS

A normal SMS event is performed by one or more SIM cards

BundleSMS

General Term describing an SMS event performed through bundle(no charge)

BundleSMS

A bundle SMS event is performed by one or more SIM cards

MMS

General term describing the MMS superclass

MMS

A MMS superclass consists of two 6ubclasses NormalMMS and BundleMMS

NormalMMS

General Term describing an MMS event charged through normal rates

NormalMMS

A normal MMS event is performed by one or more SIM cards

BundleMMS

General Term describing an MMS event performed through bundle(no charge)

BundleMMS

A bundle MMS event is performed by one or more SIM cards

Internet

General term describing the Internet superclass

Internet

A Internet superclass consists of two 6ubclasses NormalInternet and BundleInternet

NormalInternet

General Term describing an Internet event charged through normal rates

NormalInternet

A normal Internet event is performed by one or more SIM cards

BundleInternet

General Term describing an Internet event performed through bundle(no charge)

BundleInternet

A bundle Internet event is performed by one or more SIM cards

ShortCode

General Term describing the Shortcode details

ShortCode

A shortcode event is performed by one or more SIM cards

Destination

General Term

describing Destination of call,sms,mms events

Destination

A Destination exists for exactly one Call,SMS,MMS event

Operator

Genral Term describing destination Operator

Operator

An Operator belongs to exactly one Destination

Note:Below are identified SuperClass/Subclass entities by defining every Entity(superclass) that includes one or more subgroupings(sublasses) of its occurrences,which require to be presented in data model(ref:Connolly pg.372).

Superclass->Subclassess Entity:

Event->Recharge,Call,Data,Bundle

Call->NormalCall,BundleCall

Data->SMS,MMS,ShortCode,Internet

SMS->NormalSMS,BundleSMS

MMS->NormalMMS,BundleMMS

Internet->NormalInternet,BundleInternet

b)Identify Relationship types,determine multiplicity

Customer Possesses SIM

SIM AssociatedWith TariffPlan

TariffPlan AssociatedWith Service

SIM AssociatedWith State

SIM Performs Event

SIM Contains Call_Group

Event Can Be Recharge

Event Can Be Call

Event Can Be Data

Event Can Buy Bundle

Call Can Be NormalCall

Call Can Be BundleCall

Call Has Destination

Data Can Be SMS

Data Can Be MMS

Data Can Be ShortCode

Data Can Be Internet

SMS Can Be NormalSMS

SMS Can Be BundleSMS

SMS Has Destination

MMS Can Be NormalMMS

MMS Can Be BundleMMS

MMS Has Destination

Internet Can Be NormalInternet

Internet Can Be BundleInernet

Destination Has Operator

Entity Name

Multiplicity

Relationship

Multiplicity

Entity Name

Customer

0..1

Possesses

1..50

SIM

SIM

0..*

1..*

1..*

0..*

AssociatedWith

Performs

AssociatedWith

Contains

1..*

1..*

1..*

1..*

TariffPlan

Event

State

Call_Group

TariffPlan

1..*

AssociatedWith

1..*

Service

Event

1..1

1..1

1..1

1..1

Can Be

Can Be

Can Be

Can Be

1..1

1..1

1..1

1..1

Recharge

Call

Data

Bundle

Call

1..1

1..1

1..*

Can Be

Can Be

Has

1..1

1..1

1..1

NomalCall

BundleCall

Destination

Data

1..1

1..1

1..1

1..1

Can Be

Can Be

Can Be

Can Be

1..1

1..1

1..1

1..1

SMS

MMS

ShortCode

Internet

SMS

1..1

1..1

1..*

Can Be

Can Be

Has

1..1

1..1

1..1

NormalSMS

BundleSMS

Destination

MMS

1..1

1..1

1..*

Can Be

Can Be

Has

1..1

1..1

1..1

NormalMMS

BundleMMS

Destination

Internet

1..1

1..1

Can Be

Can Be

1..1

1..1

NormalInternet

BundleInternet

Destination

1..1

Has

1..*

Operator

c)Identify and associate attributes with entity or relationship types

-Attributes for Entities

SIM SIM_ID,SIMNO,HasGroupCall,First_used_date

Customer Cust_ID,Name,Passport Number,Gender,DOB,Address,City,Country

Service Service_ID,Name,Description

State State_ID,Name,Description

TariffPlan TPlan_ID,Name,Description,Services

Event Event_ID,NetwServiceTypeID

Call_Group CallGroup_ID,Name,Description,Group_List

CALL Call_ID,Called_Party,Orig_Cell_ID,Term_Cell_ID,Call_Group,End_Time, Real_Duration,Is_Roaming

RECHARGE Rech_ID,Recharge_Type,Value,Description

DATA Data_ID,Description

Bundle Bundle_ID,Name,Value,Description

SMS Data_ID,Terminator

MMS Data_ID,Terminator

Internet Data_ID

NormalSMS Terminator,Description,Value

BundleSMS Terminator,Description,Value

NormalMMS Terminator,Description,Value

BundleMMS Terminator,Description,Value

Shortcode ShCode,Description,Value

NormalInternet Description,Value

BundleInternet Description,Value

Destination Dest_ID,Dest_Name,Dest_Preffix,Dest_City,Dest_Country

Operator Oper_ID,Operator_Name,Operator_Prefix

-Relationship attributes:

SIM<->TariffPlan(AssociatedWith) Date

SIM<->State(AssociatedWith) Date

SIM<->Call_Group(Contains) Date

SIM<->Event(Performs) Start_Time,Total_Charge,Remaining_Balance

Attributes documentation:

Entity name

Attributes

Description

Data Type&Length

Nulls

Key Constraints

TariffPlan

TPlan_ID

Name

Description

Uniquely identifies a tariffplan

Name of TariffPlan

Description of the tariffplan

Number(10)

Varchar2(10)

Varchar2(255)

No

Yes

Yes

PKUnique

SIM

SIM_ID

Number

Has_Call_Group

First_Used_Date

Uniqely identifies SIM tbl

Number of mobile

Bit identifying if the SIM has call group or not(0,1)

First Use date of the SIM

Number(10)

Numeric(19)

Bit

timestamp

No

No

No

No

PKUnique

Unique

Customer

Cust_ID

Passport_No

Name

Gender

DOB

Address

City

Country

Uniquely Identifier

Passport Number

Customer Name

Gender

Date of birth

Address

City

Location

number(19,10)

nvarchar2(50)

char(10)

date

varchar2(255)

varchar2(50)

varchar2(50)

No

No

No

No

No

No

No

No

PKUnique

Call_Group

Call_Group_ID

Name

Description

Group_List

Unique Identifies Call_Group table

Name of call group

Description of call group

List of numbers in call group

Number(10)

Varchar2(50)

Varchar2(255)

Varchar(255)

No

Yes

Yes

No

PKUnique

State

State_ID

Description

Uniquely identifies State table

Description of state

Number(10)

Varchar2(255)

No

Yes

PKUnique

Service

Service_ID

Name

Description

Enabled

Uniquely identifies Service

Name of the service

Description of the service

Enabled or not

Number(10)

Varchar2(255)

varchar2(255)

Bit

No

Yes

Yes

No

PKUnique

NetwServiceType

NetworkServiceType_ID

Name

Uniquely identifies NetworkType table

Name of the Network Type

Number(10)

Varchar2(50)

No

Yes

PKUnique

Event

Event_ID

Uniquely identifies Event table

Numeric(19,0)

No

PKUnique

Recharge

Rech_ID

Type

Description

Value

Uniquely Identifies Recharge table

Type of Recharge

Description of Recharge

Value of Recharge

Number(10)

Varchar2(255)

Varchar2(255)

Number(10)

No

No

Yes

No

PKUnique

Call

Call_ID

Called_Party

Origin_Cell_ID

Term_Cell_ID

Call_Group

End_Time

Real_Duration

Uniquely identifies call table

Called party number

Zone from where the call is originated

Zone where the call is terminated

Bit telling if the call is within the call group or not

Time of call termination

How much was the call lasted

Numeric(19)

Number(10)

Number(10)

Number(10)

Bit

Date

Number(10)

No

No

No

No

No

No

No

PKUnique

Bundle

Bundle_ID

Name

Description

Value

Uniquely identifies bundle table

Bundle name

Bundle Description

Value

Number(10)

Varchar2(255)

Varchar2(255)

Number(19,0)

No

No

Yes

No

PkUnique

Data

Data_ID

Uniquely Identifies Data table.

Number(10)

No

PkUnique

SMS

Data_ID

Terminator

Uniquely Identifies SMS table

Number where the SMS terminates

Number(10)

Numeric(19)

No

Yes

MMS

Data_ID

Terminator

Uniquely Identifies MMS table

Number where the MMS terminates

Number(10)

Numeric(19)

No

No

ShortCode

Data_ID

ShCode

Description

Uniquely Identifies ShortCode table

ShortCode number

Description of its usage

Number(10)

Number(10)

Varchar2(255)

No

No

Yes

Internet

Data_ID

Uniquely Identifies Internet table

Number(10)

No

NormalCall

NormalCall_ID

Charged_Duration

Uniquely identifies NormalCall table

How many seconds is charged the call

Number(10)

Number(10)

No

Yes

PK

BundleCall

BundleCall_ID

Uniquely identifies BundleCall table

Number(10)

No

PK

NormalSMS

NSMS_ID

Uniquely identifies

Number(10)

No

PK

NormalMMS

NMMS_ID

Uniquely identifies NormalMMS table

Number(10)

No

PK

NormalInternet

NInt_ID

Uniquely identifies NormalInternet table

Number(10)

No

PK

BundleInternet

BInt_ID

Description

Value

Uniquely identifies BundleInternet table

Description of Bundle

Value of Bundle

Number(10)

Varchar(255)

Number(10)

No

Yes

No

PK

BundleMMS

BMMS_ID

Description

Value

Uniquely identifies BundleMMS table

Description of Bundle

Value of Bundle

Number(10)

Varchar(255)

Number(10)

No

Yes

No

PK

BundleSMS

BSMS_ID

Description

Value

Uniquely identifies BundleSMS table

Description of Bundle

Value of Bundle

Number(10)

Varchar(255)

Number(10)

No

Yes

No

PK

Destination

Dest_ID

Dest_Name

Dest_Preffix

Dest_City

Dest_Country

Uniquely identifies Destination table

Name of destination

Preffix of destination

City of destination

Country of Destination

Number(10)

Varchar2(255)

Nvarchar2(50)

Varchar2(10)

Varchar2(255)

No

No

No

No

No

PK

Operator

Operator_ID

Operator_Name

Operator_Preffix

Uniquely identifies the operator

Name of operator

Prefix of the operator

Number(10)

Varchar2(255)

Varchar2(10)

No

No

No

PK

d)Classify Entity types in Strong and Weak categories.

Strong Entities:SIM,TariffPlan,Service,State,Event

Weak Entities:Recharge,Call,Data,Bundle,NormalCall,BundleCall,SMS,MMS,NormalSMS,BundleSMS,NormalMMS,BundleMMS,ShortCode,Internet,NormalInternet,BundleInternet,Destination

d)draw EER Diagram for Conceptual Model:

According to the above business rules we design the conceptual EER diagram based on crow-

foot modeling principles as below:

Logical Database Design(ref.Connolly pg 461)

a)Derive relations for logical data model

For each strong entity in the conceptual data model we create a relation that includes all the simple attributes of that entity.

For weak entites we create a relation that contains all the simple attributes.

The primary key for weak entities is partially or fully derived from the owner entityso the identification of primary key of a weak entity can’t be made until all relationships with the owner entities have been mapped(ref.Connolly pg.465)

Relationship SIM<->Customer

Relationship Type 1:M with M side mandatory:

-Two tables are created SIM and Customer.

-The primary key of Customer is included in SIM entity.

Relationship SIM<->TariffPlan

Relationship Type M:N with both sides mandatory:

-A third entity is created SIM_TPLA, both entities are related 1 to M with the third entity with membership mandatory from both sides.

-Primary keys from SIM and TariffPlan tables are included in the third table,and another attribute is added for describing the time of transition from one tariffplan to another(Date)

Relationship TariffPlan<->Service

Relationship Type M:N with M with both sides mandatory:

-A third entity is created(TPLA_Service),both entities are related 1 to M with the third entity mandatory from both sides.

-Primary keys from TariffPlan and Service tables are included in the third table

Relationship SIM<->Service

Relationship Type M:N with M with both sides mandatory:

- A third entity is created(SIM_STATE),both entities are related 1 to M with the third entity mandatory from both sides.

-Primary keys from SIM and State tables are included in the third table,and an additional attribute is added to the third table(Date) in order to record the time of state transition event

Relationship SIM<->Event

Relationship Type M:N with M with both sides mandatory:

-A third entity is created(SIM_EVENT),both entities are related 1 to M with the third entity mandatory from both sides.

-Primary keys from SIM and Event tables are included in the third table,and additional attributes are added to the third table(Start_Time,Total_Charge,Remaining_Balance) in order to record the time,charge and the remaining balance after each event.

Relationship Event<->Recharge

Relationship Type 1:1 with both sides mandatory,Recharge is subclass of Event:

-Two separate entities are created for each specific entity

-Recharge entity includes the Primary Key of Event as a foreign key in its schema.

Relationship Event<->Call

Relationship Type 1:1 with both sides mandatory,Call is subclass of Event:

-Two separate entities are created for each specific entity

-Call entity includes the Primary Key of Event as a foreign key in its schema.

Relationship Event<->Data

Relationship Type 1:1 with both sides mandatory,Data is subclass of Event:

-Two separate entities are created for each specific entity

-Data entity includes the Primary Key of Event as a foreign key in its schema.

Relationship Call<->NormalCall

Relationship Type 1:1 with both sides mandatory,NormalCall is subclass of Call:

-Two separate entities are created for each specific entity

-NormalCall entity includes the Primary Key of Call as a foreign key in its schema.

Relationship Call<->BundleCall

Relationship Type 1:1 with both sides mandatory,BundleCall is subclass of Call:

-Two separate entities are created for each specific entity

-BundleCall entity includes the Primary Key of Call as a foreign key in its schema.

Relationship Data<->SMS

Relationship Type 1:1 with both sides mandatory,SMS is subclass of Data:

-Two separate entities are created for each specific entity

-SMS entity includes the Primary Key of Data as a foreign key in its schema.

Relationship Data<->MMS

Relationship Type 1:1 with both sides mandatory,MMS is subclass of Data:

-Two separate entities are created for each specific entity

-MMS entity includes the Primary Key of Data as a foreign key in its schema.

Relationship Data<->ShortCode

Relationship Type 1:1 with both sides mandatory,ShortCode is subclass of Data:

-Two separate entities are created for each specific entity

-ShortCode entity includes the Primary Key of Data as a foreign key in its schema.

Relationship Data<->Internet

Relationship Type 1:1 with both sides mandatory,Internet is subclass of Data:

-Two separate entities are created for each specific entity

-Internet entity includes the Primary Key of Data as a foreign key in its schema.

Relationship Internet<->NormalInternet

Relationship Type 1:1 with both sides mandatory,NormalInternet is subclass of Internet:

-Two separate entities are created for each specific entity

-NormalInternet entity includes the Primary Key of Data as a foreign key in its schema.

Relationship Internet<->BundleInternet

Relationship Type 1:1 with both sides mandatory,BundleInternet is subclass of Internet:

-Two separate entities are created for each specific entity

-BundleInternet entity includes the Primary Key of Data as a foreign key in its schema.

b)Validate Relations using normalization

We check every entity if:

-The intersection of each column with a row contains one and only one value.1NF

-If the entity is in 1NF and every non-primary-key attrivute is fully functionally dependent on the primary key.2NF

-If the entity is in 1NF and 2NF and no non-primary-key attribute is transitively dependant on the primary key.3NF

(ref.Connolly pg.409)

Entity name

Attributes

Normal Form

Normalization Proposal

TariffPlan

TPlan_ID

Name

Description

3NF

-

SIM

SIM_ID

Number

Has_Group_ID

First_Used_Date

3NF

-

Customer

Cust_ID

PassPort_No

Name

3NF

-

Call_Group

Call_Group_ID

Name

Description

Group_List

3NF

-

State

State_ID

Description

3NF

-

Service

Service_ID

Name

Description

Enabled

3NF

-

NetwServiceType

NetworkServiceType_ID

Name

3NF

-

Event

Event_ID

3NF

-

Recharge

Rech_ID

Type

Description

Value

2NF Value depends on Type,transitive dependancy

Create a new table Recharge_Type in order to eliminate transitive dependancy

Call

Call_ID

Called_Party

Origin_Cell_ID

Term_Cell_ID

Call_Group

End_Time

Real_Duration

3NF

-

Data

Data_ID

3NF

-

SMS

Data_ID

Terminator

3NF

-

MMS

Data_ID

Terminator

3NF

-

ShortCode

Data_ID

ShCode

Description

3NF

-

Internet

Data_ID

3NF

-

NormalCall

NormalCall_ID

Charged_Duration

3NF

-

BundleCall

BundleCall_ID

3NF

-

NormalSMS

NSMS_ID

3NF

-

NormalMMS

NMMS_ID

3NF

-

NormalInternet

NInt_ID

3NF

-

BundleInternet

BInt_ID

Description

Value

3NF

-

BundleMMS

BMMS_ID

Description

Value

3NF

-

BundleSMS

BSMS_ID

Description

Value

3NF

-

The dictionary of entities will be updated as below:

Entity name

Attributes

Description

Data Type&Length

Nulls

Multivalued

TariffPlan

TPlan_ID

Name

Description

SIM_ID

Uniquely identifies a tariffplan

Name of TariffPlan

Description of the tariffplan

Foreign key to SIM

Number(10)

Number(10)

Char(255)

Number(10)

No

Yes

Yes

Yes

PKUnique

FK(SIM.SIM_ID)

SIM

SIM_ID

Number

CustomerCust_ID

Has_Group_ID

Call_Group_ID

First_Used_Date

Uniqely identifies SIM tbl

Number of mobile

Foreign key ref to Customer table

Bit identifying if the SIM has call group or not(0,1)

Foreign Key to Call_Group

First Use date of the SIM

Number(10)

Numeric(19)

Varchar(255)

Bit

Number(10)

Number(10)

No

No

Yes

No

No

Yes

PKUnique

Unique

FK(Customer.Cust_ID)

FK(Call_Group.Call_Group_ID

SIM_TPLA

SIM_ID

Tplan_ID

Date

Part of Composite uqinue Identifier

Part of Composite uqinue Identifier

Date of Tariffplan change

Number(10)

Number(10)

Timestamp

No

No

Yes

PK/FK (SIM.SIM_ID)

PK/FK (Tariff Plan.TPlan_ID)

Customer

Cust_ID

Passport_No

Name

Uniquely Identifier

Passport Number

Customer Name

Varchar(255)

Number(10)

Number(10)

No

Yes

Yes

PKUnique

SIM_STATE

SIM_ID

State_ID

Date

Composite uqinue Identifier

Composite uqinue Identifier

Date of state change

Number(10)

Number(10)

Timestamp

No

No

Yes

PK/FK (SIM.SIM_ID)

PK/FK (State.State_ID)

Call_Group

Call_Group_ID

Name

Description

Group_List

Unique Identifies Call_Group table

Name of call group

Description of call group

List of numbers in call group

Number(10)

Number(10)

Number(10)

Varchar(255)

No

Yes

Yes

No

PKUnique

TPla_Service

Tplan_ID

Service_ID

Composite unique Identifier

Composite unique Identifier

Number(10)

Number(10)

No

No

PK/FK (Tariff Plan.TPlan_ID)

PK/FK (Service.Service_ID)

State

State_ID

Description

Uniquely identifies State table

Description of state

Number(10)

Number(10)

No

Yes

PKUnique

SIM_EVENT

SIM_ID

Event_ID

Start_Time

Total_Charge

Remaining_Balance

Composite unique Identifier

Composite unique Identifier

Start time of event

Charge of event

The balance after the charge

Number(10)

Number(10)

Timestamp

Number(10)

Number(10)

No

No

Yes

Yes

Yes

PK/FK (SIM.SIM_ID)

PK/FK (Event.Event_ID)

Service

Service_ID

Name

Description

Enabled

Uniquely identifies Service

Name of the service

Description of the service

Enabled or not

Number(10)

Char(30)

Char(255)

Bit

No

Yes

Yes

Yes

PKUnique

EventSvcType

Event_ID

NetworkServiceType_ID

Composite unique Identifier

Composite unique Identifier

Number(10)

Number(10)

No

No

PK/FK (Event.Event_ID)

PK/FK (Netw_Service Type.NetworkServiceType_ID)

NetwServiceType

NetworkServiceType_ID

Name

Uniquely identifies NetworkType table

Name of the Network Type

Number(10)

Number(10)

No

Yes

PKUnique

Event

Event_ID

Uniquely identifies Event table

Number(10)

No

PKUnique

Recharge

Rech_ID

Event_ID

Uniquely Identifies Recharge table

Foreign key to event table

Number(10)

Number(10)

No

No

PKUnique

FK (Event.Event_ID)

Call

Call_ID

Called_Party

Origin_Cell_ID

Term_Cell_ID

Call_Group

End_Time

Real_Duration

Event_ID

Uniquely identifies call table

Called party number

Zone from where the call is originated

Zone where the call is terminated

Bit telling if the call is within the call group or not

Time of call termination

How much was the call lasted

Foreign key to Event table

Numeric(19)

Number(10)

Number(10)

Number(10)

Bit

Number(10)

Number(10)

Number(10)

No

No

Yes

Yes

No

Yes

Yes

No

PKUnique

FK (Event.Event_ID)

Data

Data_ID

Event_ID

Uniquely Identifies Data table.

Foreign key to Event

Number(10)

Number(10)

No

No

PkUnique

FK (Event.Event_ID)

Recharge_Type

Type_ID

Name

Description

Value

RechargeRech_ID

Uniquely Identifies Recharge Type table

Name of Recharge Type

Descripion of Recharge Type

Value of Recharge

Foreign key to Recharge table

Number(10)

Char(255)

Varchar(255)

Number(10)

Number(10)

No

Yes

Yes

Yes

No

PKUnique

FK (Recharge.Rech_ID)

SMS

Data_ID

Terminator

Uniquely Identifies SMS table

Number where the SMS terminates

Number(10)

Numeric(19)

No

Yes

FK(Data.Data_ID)

MMS

Data_ID

Terminator

Uniquely Identifies MMS table

Number where the MMS terminates

Number(10)

Numeric(19)

No

Yes

FK(Data.Data_ID)

ShortCode

Data_ID

ShCode

Description

Uniquely Identifies ShortCode table

ShortCode number

Description of its usage

Number(10)

Number(10)

Number(10)

No

Yes

Yes

FK (Data.Data_ID)

Internet

Data_ID

Uniquely Identifies Internet table

Number(10)

No

FK(Data.Data_ID)

NormalCall

NormalCall_ID

Charged_Duration

Call_ID

Uniquely identifies NormalCall table

How many seconds is charged the call

FK to CALL table

Number(10)

Number(10)

Numeric(19)

No

Yes

No

PK

FK(Call.Call_ID)

BundleCall

BundleCall_ID

Call_ID

Uniquely identifies BundleCall table

FK to Call table

Number(10)

Numeric(19)

No

No

PK

FK(Call.Call_ID)

NormalSMS

NSMS_ID

Uniquely identifies NormalSMS table

Number(10)

No

PK

NormalMMS

NMMS_ID

Uniquely identifies NormalMMS table

Number(10)

No

PK

NormalInternet

NInt_ID

Uniquely identifies NormalInternet table

Number(10)

No

PK

BundleInternet

BInt_ID

Description

Value

Uniquely identifies BundleInternet table

Description of Bundle

Value of Bundle

Number(10)

No

Yes

Yes

PK

BundleMMS

BMMS_ID

Description

Value

Uniquely identifies BundleMMS table

Description of Bundle

Value of Bundle

Number(10)

Varchar(255)

Number(10)

No

Yes

Yes

PK

BundleSMS

BSMS_ID

Description

Value

Uniquely identifies BundleSMS table

Description of Bundle

Value of Bundle

Number(10)

Varchar(255)

Number(10)

No

Yes

Yes

PK

BundleType

BundleType_ID

Description

Value

Bundle_ID

Uniquely Identifies BundleType table

Description of type of bundle

Value of bundle

FK to Bundle table

Number(10)

Number(10)

Number(10)

Number(10)

No

Yes

Yes

No

PK

FK(BundleCall.BundleCall_ID)

c)Build EER Logical Diagram

Datawarehouse implementation:

Case Study

Implementation of datawarehouse in Mobile Company

Nowadays it is crucial to extract quality information from decision support systems in order to become competitive in the market.In Albania there are four mobile companies operating and the competition is very tough,and the one who uses good and up to date techniques for retrieving information supporting decision making will most probably be advantagous to succeed in the dynamic market.

An operator usually has a large number of subscribers,and if a subscriber performs a use of services at an average hundred times per month,the total number of transactions becomes hundred of millions for all the subscribers.Based on those data it is very clear that a datawarehouse solution is needed in order to have quality information for the operations,in terms of which mobile services are the most used,which is the most used service etc..In this way the company knows which service to create or to disable,how to arrange the marketing campaigns etc.

For the moment this kind of analysis was done based on statistical data provided from the transactional database,and normally data storage and data processing requirements are much more higher for detailing and summarizing the data.

1. Business Challenge

AMC company position in Albania market is subject to:

Increased competition in telecommunications market.A small country as Albania has four mobile operators running,from which highest competitor is Vodafone,and Eagle Mobile which is a part owned by the Albanian governemt.

Recently there is a lot of pressure from the government on lowering the costs of products and services offered which have been the highest in Balkan states.

Global economy recession lead to careful expenditures,which force the use of smart decisions in order to have best ROI on lowest costs.

Adapting to mother company,T-Mobile two years ago bought the Cosmote Group.

Keep quality to the products and services to the customers as they are the trusting the company as the first in market offering high standard of service and latest services worldwide.

In order to keep the leading role in market new techniques for retrieving quality information is needed since :

Quality of reports produced was subject to the human work which may lead to incorrect data due to lack of information control.This means incorrect decision making based on those reports.

Lack of reporting credibility.Most of the reports may use same terms but different meaning.

Existing tool provide only specific requirement.Any time new reports are required should be developed a new report

Bad productivity.Many resources and time needed in case of new reports

Limitation of programs used to manipulate the unix file(shell,awk scripting etc)

Physical data dependency,meaning if the structure of the file changes should be changed all the scripts that are using that file

Complexity of scripts to be developed due to non aggregated data

Difficulty to manipulate file due to the huge size(up to 100 mb per file)

Long execution time

Low retention time;meaning you cannot store the files for a long time due to the huge size,so usually you could build statistics for not more than 3 months.The alternative for this is to increase storage capacity which is translated in increased cost.

The CDR is produced in PREPAY system from operational database whose primary function is to serve traffic and online transaction processing.

1.1 Background and Problem statement

This case study is related to prepaid system that enable a mobile operator to perform different prepay services.

Most of the companies today depend on information systems as CRM and ERP systems.

In telecommunication companies happens that the decision making process is based on their online trasnsaction processing systems(OLTP),which produce a very large volume of detailed data but the analysis of this data is not easy to perform because it exists in a different formats and it runs on live systems.

In order to have a more efficient way of managing and development of these services the company needs a proper way to analyze and develop reports about Prepaid application traffic served by the prepaid systems.

The old existing reporting solution is based on low efficient way of manipulating transactional records produced by the system,and this kind of method is not efficient anymore.

This leads to proposal of new solutions and specifically in designing a data warehouse solution to deal with the increase demand of quality decision making information.

1.2 Goal

The goal of this case study is to design and implement a data warehouse model in order to cope with the reporting issues described in the problem statement.The data warehouse must be able to store a very large number of prepaid transactions data.It should be efficient in reporting the data.The data should be detailed as much as possible in different dimensions.

This model at the end can be compared to the old way of generating reporting data.

2. Data Warehouse Implementation – The Approach

The data warehouse implementation is based on the:

Higher management statistical requirements on quality information on ad-hoc bases

Technical constraints and solutions offered for fulfilling those requirements

Reconciling,and migrating to the new system

Based on the below the Data warehousing system I propose will utilize a client/server architecture which includes HP-UX server and Windows clients for the end users.

The operational data source for the data warehouse is supplied from operational data held in Prepaid operational database and CDR files,files from Sales department and Products and Servicesdepartment.

The HP-UX files will be loaded in ORACLE database and based on this data a large database will be built in order to support the decision making of different departments of the company.

The implementation of the data warehouse will be divided in phases.

-Phase 1-Loading data from different sources in datawarehouse

a) Inflow process and will include extraction,cleansing and loading of data from different sources in dta warehouse(ref.Connolly pg 1179)

b) Upflow process which be involved in adding value to the data loaded in step a by summarizing,aggregating and packaging the information loaded in step a

-Phase 2-Performance balance,description of data

a) Metaflow process by describing each of data types loaded in the database in order for the system to be comprehensive for the end users.

b) Integrate high priority reporting based on the prioritization made by higher management on statistical information needed

c) Testing load performance,load processing,query performance etc under heavy query processing

-Phase 3-Launch of product

a) Downflow process will make the data information available to end users,focusing first on only higher management levels and most critical company needs(10 users max including CEO).

In this phase will be included reports such as

-Which is the top sold product and service in customer bases by different ages

-Which is the average usage of a services per customer

-What was the reaction of customers to latest product on last month

-What is the percentage of lost customers in monthly bases.

b) Expand the launch phase by adding users and reports

In this phase all the users who are need ad-hoc information on quality data will be given a user in the data warehouse.The expected user domain will be up to 100 users.

3. Benefits

The benefits by introducing the data warehouse will be:

Reduce cost of exiting reporting processes and sytesms,staff reduction

Business benefits based on high quality of decision making which lead to fast reaction in the market.

6.1 Architectural Approach

The architectural approach chosen for the data warehouse is dimensional modeling approach.This model is a offers a logical design technique that aims to present data in standard, intuitive form that allows high-performance access.(ref.Connolly pg 1183)

Based on this concepts we design the model with one table with a composite primary key,called fact table and a set of smaller tables called dimension tables.

The schema chosen is Star schema which has a fact table containing factual data in center,and surrounded by dimension tables containing reference data.

The schema chosen has the following advantages within data warehouse environment:

Efficiency:By allowing efficient access to the data by various tools

Changing Requirements handling:This schema can adapt to changes in user’s requirement as all dimensions are equivalent in terms of providing access to the fact table.

Extensibility:The dimensional model is extensible due to the capability to easy add new facts,new dimensions,new dimension attributes based on the data warehouse rules.

Model common business situations:

Predictable query processing:The query processing is very predictable because it is at the lowest level,since each fact table should be qeueried independently

Design

The design of data warehouse is done using the methodology proposed by Kimball and is called ‘Nine-Step Methodology’.

This methodology specify the steps required for the design of a data mart.

For this case study the reporting solution is designed in one datamart called Prepaid Data Mart which will anwer all the questions from management.

1.Choosing the process

In this step we choose the data for the first data mart which should answer the most commercially important business questions(ref.Connolly pg 1187).

Basically the business processes involved in the data warehouse are related to

Sales of products by different categories.

Prepaid traffic usage,needed for Planification purposes

Revenues from prepaid transactions needed from Finance

Prepaid services usage needed from Product &Services

Thus we defince a specific data mart for each of business requirements

-Sales Data Mart

-Planification Data Mart

-Finance Data Mart

-Product& Services & Marketing Data Mart

2.Choosing the grain

This process defines exactly what a fact table recod presents,and the detail level of data.It defines the granularity of the data stored in the fact tables.

We choose a detailed level of storing data,thus the data is stored in a granularity of a single transaction which in this case is a transaction performed by a SIM in terms of using services provided to him.

The grain of the each of data marts is a single transaction performed from a SIM number.

We also include in this step the Core dimension wich is Time and is always present in start schemas.

3.Choosing the dimensions(also conform dimensions)

We identify the dimensions that occur in more than one data mart and we define them as conformed dimension.

In our case the conformed dimensions are Destination_Tab,Tariff_Plan_Tab,Event_Tab(note the use of _Tab extension in order to distinguish from the transactional tables as the implementation of data warehouse for pheasibility issues will be in the same schema as the transactional one).

We define also dimensions needed only from Finance fact table,and will involve Customer_Tab and Service_Tab.

4.Choosing the facts

The facts should be choosen based on the grain defined in step 2.

As a result we introduce 4 fact tables:

-Sales

-Traffic_Planification

-Products_Services_Marketing

-Finance

The schema now should look as below: