[SOLVED] How to represent different business facts in a single fact table?

How to represent different business facts in a single fact table?

I have the following dimensional tables:

DimUser
DimClient
DimLocation
DimDate
DimTime
DimLog
DimStatuses

How could I represent the following events in the fact table?

Logs by user over time
User status change over time

Let's say the fact table is something like this:

application_id	location_id	user_id	client_id	log_id	date_id	time_id	status_id
3	19	3	2	69	45	64
1	23	1	1		10	207	1

Is it a valid representation where the first record refers to a log event and the second record refers to an user status change?

Solution

Different facts should be represented in the schema under different fact tables. For this case, there should be a fact table for logs by user and another fact table for user status change over time, this is known as a galaxy schema or multi fact schema, so in other words it should look like this:

So, it's feasible because you can create your measures according your grain.

Number of users by application (user status change over time fact table)
Number of logs or events by user (logs by user fact table)
Active/Inactive users (user status change over time fact table) user
logs over time (logs by user fact table)