Time dimension problem

View previous topic View next topic Go down

Time dimension problem

Post  cvigsy on Tue May 27, 2014 12:33 pm

Hi! I have a student task to do. I'm not very familiar with data warehouse modelling so I need a help. Can someone tell me how to create data warehouse for analysis of users calls of telephone services. Dimensions are clients(users), time, date, phone operator. I have done everything except time dimension. I don't know which fields to put into this table. Task also says that call duration is important fact. I understood this that I have to calculate call duration for every user. I don't know am I right. So, please, can someone help me with that. Maybe make an ERA model of these warehouse and tell me which fields go to time dimension and how to calculate phone call duration, with which sql query. I have to make this task in Access 2010. Thank you so much!

cvigsy

Posts : 2
Join date : 2014-05-27

View user profile

Back to top Go down

Re: Time dimension problem

Post  ngalemmo on Tue May 27, 2014 1:10 pm

Call duration is a measure, not a dimensional attribute. Hopefully your source provides it or gives you the start and end timestamps so you can calculate it. It is usually expressed as either minutes or seconds as an integer depending on business need. If they want fractional minutes, store it as seconds.

As far as a time dimension itself goes, the question is why do you need it? Certainly the time of the call is important, but does the time of day itself have any significant meaning for analysis?

If you implement a time dimension, its purpose is to hold attributes relating to the time of day. The hour, the work shift, a categorization of the day (morning, afternoon, dinner time, etc…). If you have no need for such attributes, then just store a timestamp on the fact as a degenerate dimension.
If you do need such a dimension, the natural key is typically the hour and minute of the day. You would still store the timestamp on the fact to provide the precise date and time.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Time dimension problem

Post  cvigsy on Tue May 27, 2014 2:14 pm

I don't have a start and the end timestamps. I'm not very familiar with warehouses but I have to pass this task. I will translate task text here (I am Croatian, btw.).


Create a data warehouse for analysis of users of telephone services.

Dimensions are: client dimension, date dimension, time dimension and phone provider dimension.
Important fact is call duration.

Tasks are next:
1.Create data warehouse.
Note: Dimension tables (clients and providers) have a few lines, while in fact table add tens of thousands of rows. Date dimension contains dates back the last three years.
2. Create the "Universe".
3. Create some different reports in Desktop Intelligence

What to do with these call duration? I don't have specified fields for dimension tables, I can put in whatever fields I want.

cvigsy

Posts : 2
Join date : 2014-05-27

View user profile

Back to top Go down

Re: Time dimension problem

Post  ngalemmo on Tue May 27, 2014 3:22 pm

You have data to load or do you need to make it up? A typical call log usually contains a duration value. It's a measure on the fact, if you're making up the data, just put together things that make sense.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Time dimension problem

Post  BoxesAndLines on Tue May 27, 2014 7:52 pm

Like Ngalemmo said, put call duration in your fact table. Typically, call detail records track call duration in seconds.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Time dimension problem

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