Data warehouse dimension table structure confusion
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!
Ok, so it sounds like you are simply trying to decide whether to consolidate dimensions or keep them separate. Based on the description, I'd say keep the dimensions separate and here's why: There's really only a few reasons (that I can think of at the moment) for "consolidating" dimensions... - Hierarchies (natural and unnatural). This is a UX (user-experience) thing but can also help query performance in certain scenarios - Too many dimensions. Kimball recommends having no more than 20-25 dimensions per fact table for UX and/or *potential* performance issues (by having too many joins) It doesn't look like you are anywhere near the recommended limit for dimensions / fact table...so that maybe a non-issue. I also didn't see anything in your description that indicates the users are expecting (or are accustomed to) the data to be presented in hierarchical fashion. However, you did mention that the grain of you fact tables are slightly different...one being able to make use of a consolidated dimension while the other requiring them separate...so consolidating them is only going to increase the complexity of maintaining conformed dimensions. What I'd recommend at this point is to spend a bit of time drawing up a [DW bus matrix] (if you haven't already) :