question

Hi, I've come across an area which is confusing me when trying to determine how to structure my dimension tables. I have a source database of members. Each member belongs to a client. A client can have many schemes, areas and companies associated with it - there is no relationship or hierarchy between these other than that they all belong to the same client. My members all belong to a client, a scheme, an area and a company. My current fact table is tracking member events, so I had decided that my fact table would contain a key to join to a membership dimension table which would hold the details of that membership and in there I would include the client, scheme, area and company associated with it. This would allow my users to slice the data by scheme for example. However, I have other fact tables - for example a fact table to track all completed activities. In this case an activity might not be associated with a member, however it might still be associated with a client, scheme, area or company. For example, it could be recording an activity related to a scheme in which case the area and company would be 'not applicable' but the users would still want to be able to slice the data by client. My question is, since I have a need for the client details outside of the membership dimension table (i.e. when there is no associated membership) should my dimension structure be different? I've considered having a separate dimension for each - i.e. a client dimension, a scheme dimension, a company dimension etc. However I don't think that is correct since there is a definite relationship between the client and the other attributes. At the moment I'm considering a 'Client Details' dimension table which holds every possible combination of client, scheme, area and company however that would be quite a large dimension and I'm worried that there is another way I'm missing. With this method, for my original membership dimension instead of storing the client, scheme,area and company there now I've got a Key within the membership dimension which links to the appropriate combination in the Client Details dimension. I also have to add lots of 'Not Applicable' lines to the Client Details dimension for when, as in the example above, it is a 'scheme' activity so it has an associated client but no associated area or company. I think there must be a better way for what I'm trying to achieve. Any help would be most appreciated. Thanks!