attributes depending on more than 1 dimension (2 dimensions)

View previous topic View next topic Go down

attributes depending on more than 1 dimension (2 dimensions)

Post  element on Fri Jan 28, 2011 6:50 am

suppose you have
- a discount code attribute
- a status attribute
which depend on the combination of the dimension customer & the dimension product.

You want to be able to relate these attributes to a fact table invoice details.

What are the possibilities to model this?
- 1) create a new dimension table, containing the customer SK, product SK and both attributes?
- 2) create a new dimension table, containing the customer SK, product SK and snowflake to 2 new dimensions (discount & status)
- 3) create a new reference table, such as in 2), but als relate the new discount & status dimensions directly to the fact?
- 4) create a new reference table, such as in 2), but als relate the new discount & status as one dimension to the fact?
....

Thanks for your help on this one!


element

Posts : 6
Join date : 2011-01-28

View user profile

Back to top Go down

Re: attributes depending on more than 1 dimension (2 dimensions)

Post  element on Wed Feb 02, 2011 3:43 am

... there must be surely somebody who has some ideas about this one?

C'mon guys ... don't let me down here.

element

Posts : 6
Join date : 2011-01-28

View user profile

Back to top Go down

Re: attributes depending on more than 1 dimension (2 dimensions)

Post  Jeff Smith on Wed Feb 02, 2011 10:46 am

Model the fact table and dimension tables based on how the data is to be used, not on what facilitates them being populated. Don't break the Star because it's easier to populate. I can see breaking the star to add functionality.

Create a new dimension with the new attributes. In the staging area, create the tables that you need to correctly populate the final fact tables.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: attributes depending on more than 1 dimension (2 dimensions)

Post  BoxesAndLines on Wed Feb 02, 2011 12:22 pm

Create a junk dimension of discount code and status. Add that to the fact. The dependency to customer and product is managed through the fact table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: attributes depending on more than 1 dimension (2 dimensions)

Post  element on Wed Feb 02, 2011 4:12 pm

@Jeff,

could you please elaborate, possibly with an example? - I do not exactly understand what you mean.

@BoxesandLines,

what about customer & product combinations which are not covered by the relation through the fact table? I can imagine that these definitions are also required in the data warehouse, no?

element

Posts : 6
Join date : 2011-01-28

View user profile

Back to top Go down

Re: attributes depending on more than 1 dimension (2 dimensions)

Post  BoxesAndLines on Thu Feb 03, 2011 10:54 am

That is certainly a question you need to ask!
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: attributes depending on more than 1 dimension (2 dimensions)

Post  ngalemmo on Thu Feb 03, 2011 11:57 am

element wrote:
what about customer & product combinations which are not covered by the relation through the fact table? I can imagine that these definitions are also required in the data warehouse, no?

You need to forget about the customer/product dependancy... there is none. The particular discount code and status are contexts for the invoice line. You are not developing a process to derive discount and status, but rather collecting such information so it can be reported. Simply store the related values in a junk dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: attributes depending on more than 1 dimension (2 dimensions)

Post  gvarga on Thu Feb 03, 2011 12:17 pm

Itís totally clear for me that you have to create the Status and Discount dimensions and relate them to your fact table.

But what invoice data can you get from the source system?
1. If Discount code and Status are coming from the OLTP system, itís fine.
2. If invoice line contains just Customer and Product data then you have to store and use in the Staging area some map information how Customer and Product data define Status and Discount.

gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: attributes depending on more than 1 dimension (2 dimensions)

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