Data Modeling for UserUtilization
2 posters
Page 1 of 1
Data Modeling for UserUtilization
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.
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
Re: Data Modeling for UserUtilization
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
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
Similar topics
» Data Vault Data Modeling
» Beginning Data Modeling for DW
» Dimension Modeling for Big Data
» Data Warehouse Modeling
» Uses of a data modeling tool
» Beginning Data Modeling for DW
» Dimension Modeling for Big Data
» Data Warehouse Modeling
» Uses of a data modeling tool
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum