Fact table without any Numerics

View previous topic View next topic Go down

Fact table without any Numerics

Post  furrukh on Tue Mar 15, 2011 3:54 am

Hi,

I am designing datamart for a telecom. part of this datamart is subscriber churn analysis. I have identified all the dimensions but bit confused on fact table as the service order we recieved for cancellation of service does not have any numerics to be measured.

The information we recieve from transaction system is

- Service_Order_number
- Service_Order_Date
- Disconnection_Date
- Customer_key
- Customer_account
- Customer_service_line

do I need to create an aggregated table for the fact. I dont want to change the grain and want to keep it on atomic level. do every fact needs to have some numerics to be measured ?

please note a customer can have multiple account and each can have multiple serivce line.

my 2nd question is about the customer dimension. should I create single dimensional table for customer and their accounts and service line .
same goes for customer and their addresses. should they be one dimension ... obviously customer can have multiple addresses.

any help will be much appriciated.

Thanks
FB

furrukh

Posts : 2
Join date : 2011-03-15

View user profile

Back to top Go down

Re: Fact table without any Numerics

Post  BoxesAndLines on Sun Mar 20, 2011 9:43 pm

Your fact for churn analysis is a customer disconnecting or installing. Create a transaction fact for install and disconnect events. Create a customer dimension. You don't need an account dimension since a row in the fact table represents an account.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Fact table without any Numerics

Post  John Simon on Mon Mar 21, 2011 3:13 am

You can have a measure called EventCnt and set it to 1. It's often times easier to do this because the reporting tools want a measure rather than doing a count(*).

Is the Customer_service_line the actual phone number?
If it is (and you should be capturing this anyway), then you would probably have a ServiceNumber dimension since a service number can move between accounts and customers. I would not include it as part of the customer dimension.

I've done a lot of Telco work in the past. Shouldn't you also be receiving additional information such as what kind of disconnection is it i.e. is it a straight disconnection or is it a "port out" to another provider? Shouldn't you also be capturing which product or plan you are disconnecting? Can a person have a data plan and disconnect from that, but keep their voice plan?



John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Fact table without any Numerics

Post  furrukh on Mon Mar 21, 2011 12:20 pm

Hi John,

Thanks for your reply. definately we have got lot more information regarding disconnection which I did not provide in my post because i was puzzled for not having any measures. thanks for your suggestion of have a Count column and I think that should be clean solution.

Thanks
FB

furrukh

Posts : 2
Join date : 2011-03-15

View user profile

Back to top Go down

Re: Fact table without any Numerics

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum