Is the user table considered a dimension or part of fact

Question

I am designing a star scheme db where the fact is user login.
My dimensions are time (up to hour of day) and location (based on IP).
I have about 300k users in my system. This size grows by 5k/year.
Should the user details be part of the fact table or a dimension table onto itself?

score 3 · Accepted Answer · answered Jun 15 '17 at 07:33

3

User details are descriptive of the user and should be in a dimension.

answered Jun 15 '17 at 07:33

Rich

2,207
1
23
27

Regardless of the user table size? – Itay Moav -Malimovka Jun 15 '17 at 13:52
Not regardless of table size, but it doesn't sound like you've got millions of users here. You won't want to repeat the user details in a fact table, that's for sure. Best to have a nice small surrogate key in the fact table, keeping it compact. – Rich Jun 15 '17 at 13:54

score 2 · Answer 2 · answered Jun 16 '17 at 11:50

2

Facts are usually created for business activities.

I believe your business activity (in this scenario) would be a user logging at a particular time.

Fact table: Fact_User_Login Dim Tables:Dim_User,Dim_Date_Time, Dim_Location

This is just an example.

Hope that helps

Cheers Nithin

answered Jun 16 '17 at 11:50

NITHIN B

214
1
9

it helps -> tnx – Itay Moav -Malimovka Jun 17 '17 at 01:25

Is the user table considered a dimension or part of fact

2 Answers2