Data Modeling for UserUtilization

View previous topic View next topic Go down

Data Modeling for UserUtilization

Post  vinothcz on Wed Mar 11, 2015 5:42 am

There are 3 kinds of utilization metrics that i have derive for the users. In my application, users activity are tracked using his login history, number of customer calls made by the user, number of status changes performed by user.

All these information are maintained in 3 different tables in my application db like UserLoginHistory, CallHistory, OrderStatusHistory. All the actions made by each user is stored in these 3 tables along with DateTime info.

Now i am trying to create a reporting db that will help me in generating the overall utilization of user. Basically the report should show me for each user over a period:

UserName
Role
Number of Logins Made
Number of Calls Made
Number of Status updates Made

Now i am in the process of designing my fact table. How should i go about creating a Fact table for this scenario? Should i go about creating a single fact table with rows in it capturing all these details at the granular date level (in my DimDate table level) or 3 different fact tables and relate them?

The 2 options i described above arent convincing and i am looking for better design. Thanks.

vinothcz

Posts : 1
Join date : 2015-03-11

View user profile

Back to top Go down

Re: Data Modeling for UserUtilization

Post  sharvan.kumar.83@gmail.co on Wed Mar 11, 2015 2:33 pm

Create the dimension first like ..
D_user -> user_skey, userid, and other user details.
d_order_status -
d_call_type -
d_calender --

And then create a single fact using the above dimension and calculate all the fact and populate it. if the call details is at second level and if you want to store the call details at second level ( grain of the table) then use the timestamp in fact table.

Hope this will help..
Thx

sharvan.kumar.83@gmail.co

Posts : 10
Join date : 2014-11-17

View user profile

Back to top Go down

View previous topic View next topic Back to top


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