Same attribute in multiple dimensions or Create new dimension?

Hi all -

I'm struggling with this concept. I have 2 dimensions: Customer and Products. Both share a common single attribute that I'll just call "Owner" for now. If a customer purchased a product, the owner would be the same for both. I realize it makes sense to have Owner be a separate dimension, but it's a single field - and has no other data associated to it - it's just a way to segment our users and products. If I take this approach, the problem is if I ever just want to query the Customer dimension to see Customers by Owner, I have to join to a fact table that also carries the Owner dimension. It also requires that for every fact table I build that uses either Customer or Product to also have a use the Owner dimension.

If I keep these as separate attributes associated to each dimension, it seems like a messy design - however, the design is much more simple and easy to query. I can get the Orders by Owner by joining the fact table to EITHER Customer or Product and filter by Owner.

If I create a new dimension for this and use bridge tables between Customer/Product and Owner, it seems to make the querying more complicated than it really needs to be and essentially becomes "snowflake".

Any input would be appreciated or if you can point me to a post that explains this - I've searched and can't find what I'm looking for.

thx
NR

Although the information you are providing is somewhat sparse, it sounds like you are trying to track "Product Ownership" - regardless of what other events may be happening.

I doubt that a bridge table or third dimension would satisfy this requirement. You may want to look into factless fact tables. These are covered in chapters 2 and 12 in The Datawarehouse Toolkit (at least in my copy of the second edition). In chapter 12 specifically, there is an example of student class registration at a university. I think that the student is registered for a class and the class has registrants type relationships would be very close corollaries to the customer owns product and product is owned by customer type of many-to-many relationships. In your case, this might be called the "Purchase Event Fact Table". You can then add the other germane dimensions to the fact table. You could also use include a purchase date key from the date or calendar dimension.

When you need to include "ownership" information, then this fact table would be joined across the dimension bus with the other facts that you are analyzing.

Thanks for the reply! I hate the idea that I always have to join to this factless fact table to see who "owns" the customer and/or who owns the product, but I think I follow what you are saying.

Why can't OWNER be a degenerate dimension on the fact table?

Many to many relationships are always a sticky point. The two most common solutions are a bridge table with views wrapped around the fact table and bridge joined together or a factless fact table. When you begin to look at the logistics involved in managing bridges across all your other fact tables that both dimensions touch, the factless fact table starts to look a lot more attractive and as the lesser of two evils. I've usually found it easier to join dimensions across two fact tables that are structured properly across the dimensions than to work with bridge tables.

A solution may be clearer if you can help us understand what "owner" actually means.

The title of the post refers to the "same attribute", but I question that... by "same" I mean the same value (id) for both customer and product, which makes no sense the way you plan to implement it.

If, as you state, an owner attribute on the customer and an owner attribute on the product would work, then they cannot be the same value unless you are creating custom one-of products for each sale. They would, in fact, be different values playing different roles in the organization... i.e. like a customer rep and product manager. If that is the case, someone 'owns' a customer, no matter what they buy... and someone 'owns' a product no matter who buys it, then attributes on the customer and product dimension is fine.

If, on the other hand, "ownership" is defined by a specific customer buying a specific product, then owner must be its own dimension with a FK off the sales fact.