Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

attributes depending on more than 1 dimension (2 dimensions)

5 posters

Go down

attributes depending on more than 1 dimension (2 dimensions) Empty attributes depending on more than 1 dimension (2 dimensions)

Post  element 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

Back to top Go down

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

Post  element 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

Back to top Go down

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

Post  Jeff Smith 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

Back to top Go down

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

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

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

Post  element 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

Back to top Go down

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

Post  BoxesAndLines Thu Feb 03, 2011 10:54 am

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

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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  gvarga 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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