Advice on a single Fact Table Column which could link to more than one different dimension

Hi,

I have a situation where I have a fact table recording work events. At any time an event could either be sitting with a individual user, or it could be sitting in a group holding tray and it can never be both. In my fact table I want to record where it is. Also, for reporting purposes people want to be able to see where it is under a column called 'user or group' - i.e. they don't want to differentiate between the two: If the process is with a user they want to see the name of the user otherwise they want to see the name of the group.

I'm not sure how best to structure this. I think I have identified 3 options so far:

1. Have two seperate dimensions (DimUser & DimGroup), then in my fact table have a seperate column for each dimension key. In which cases one of the columns will always point to a 'Not Applicable' entry in the appropriate dimension table. In my reporting I will have to include something there that says 'if user is not applicable use group'.

2. Use a bridge table e.g. BridgeUserGroup and store the key for this table in my fact table. This may be good as there is a seperate relationship that exists between user and group - i.e. a user can have access to many groups. A group can have many users that are allowed access to it. However, I can't at the moment envisage a scenario where the business would ever be interested in that relationship. Additionally, for my purposes when a work item is in a group it has no relationship to a user so my bridge table would just have the name of the group and 'Not applicable' for the name of the user. I think the bridge table approach might be overkill for my needs.

3. I could store the groups in the user dimension as if they themselves are users. Then at ETL time when I take either user or group from the source I would combine them and assign them the single appropriate key for my user dimension. I've wary of doing this because although I think it may be the simplest solution, logically I don't think it makes sense...

Any thoughts would be really appreciated! I'm hoping there is a perfect solution that I've just completely missed!

Thanks,
Viv

Use a dimension that holds both user and group with mutually exclusive keys.

ngalemmo wrote:Use a dimension that holds both user and group with mutually exclusive keys.

Thanks for your reply. Could you please clarify a bit further? I'm not sure whether you mean I should create a bridge table such as option 2, or whether you mean for me to do something similar to option 3 where I combine user and group in the same dimension? Or something else entirely?

What is the difference between a user and a group? Is a group simply a group of users? If it is, why not keep them in the same dimension so you can easily support a hierarchy or collection?

And if it's a situation where users belong to or roll up to a Group, then create a dummy user for each group within the Dimension Table. You can add a column to indicate if the row is a Group or User.

Since the group and the user are mutually exclusive, it's a safe bet that the data isn't related is some hierarchical fashion. The simple solution is to carry two dimensions and populate the appropriate one. I would also carry two simple metrics on the fact to indicate whether this is a user or a group row. The values would be 0 or 1.

Thanks all for the replies, it's been really helpful.

I've decided to go with BoxesaAndLines's suggestion. The group is an entity in it's own right as opposed to just being a group of users - and although you could technically say users could belong to, and roll up to, a group it's really more the case that some users have security that allows them to access some groups.

I don't think I will get any benefit from storing user and group in the same dimension because a user can have permissions on multiple groups - so I would need an entry for each user/group combination (or implement a bridge table) But for my purposes I'm only ever going to get in fact data where user and group is mutually exclusive as you said. Therefore my fact will be associated with a user but have no group, or vice versa. So in my dimension table I would only be using the rows where either user or group are 'Not Applicable' and the other rows with the user/group association would be redundant. Unless my company ever decide they want to report specifically on the users who have access to a certain group. I've been assured this is highly unlkely so I'll not worry about it just now.

Thanks again,
Viv