Same attribute in multiple dimensions or Create new dimension?

View previous topic View next topic Go down

Same attribute in multiple dimensions or Create new dimension?

Post  noiseripper on Wed Oct 13, 2010 5:21 pm

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


noiseripper

Posts : 2
Join date : 2010-10-13

View user profile

Back to top Go down

Factless Fact Table

Post  bigjonroberts on Thu Oct 14, 2010 10:06 am

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.


Last edited by bigjonroberts on Thu Oct 14, 2010 10:11 am; edited 1 time in total

bigjonroberts

Posts : 6
Join date : 2009-07-09

View user profile

Back to top Go down

Re: Same attribute in multiple dimensions or Create new dimension?

Post  noiseripper on Thu Oct 14, 2010 9:09 pm

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.

noiseripper

Posts : 2
Join date : 2010-10-13

View user profile

Back to top Go down

Re: Same attribute in multiple dimensions or Create new dimension?

Post  Jeff Smith on Fri Oct 15, 2010 9:11 am

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

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

many to many relationships

Post  bigjonroberts on Fri Oct 15, 2010 10:21 am

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.


Last edited by bigjonroberts on Fri Oct 15, 2010 12:53 pm; edited 1 time in total

bigjonroberts

Posts : 6
Join date : 2009-07-09

View user profile

Back to top Go down

Re: Same attribute in multiple dimensions or Create new dimension?

Post  ngalemmo on Fri Oct 15, 2010 12:51 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Same attribute in multiple dimensions or Create new dimension?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum