relating 2 existing dimensions combined to a fact table

View previous topic View next topic Go down

relating 2 existing dimensions combined to a fact table

Post  nodderwaak on Mon Apr 12, 2010 8:13 am

given:
* 1 existing sales fact table; > 1.500.000.000 records
* 1 existing product dimension; >= 300.000 records
* 1 existing store dimensions; >= 1.000 records

required:
* 3 new attributes, defined by the combination of a certain store and a certain product --> most likely a new table 'x'
* not all combinations of store & product in the sales fact table will be present in the new table 'x'
* if possible, no modification of the sales fact table
* result should show the 3 new attributes in relation to the sales fact table showing attributes values for all store / product combination present in the fact table.

thanks for your appreciated feedback.

brgds,
nodderwaak

nodderwaak

Posts : 2
Join date : 2010-04-12

View user profile

Back to top Go down

Re: relating 2 existing dimensions combined to a fact table

Post  ngalemmo on Mon Apr 12, 2010 11:53 am

A 'junk' dimension may suffice. A junk dimension is a dimension whose natural key is the attributes in the table. You would create rows when a new combination of attributes is encountered. They work well if there is a resonable correlation between the attribute values.

It's difficult to say, not knowing the nature of the attributes.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: relating 2 existing dimensions combined to a fact table

Post  nodderwaak on Mon Apr 12, 2010 2:40 pm

thanks for your feedback.

A junk dimension will not do the trick; Because as said before, this would mean that we need to update or reload the 1.500.000.000 record table, which is not the best option, because may be in the future
1) the huge number of records / downtime
2) the combined store / product dimension might be related to another fact table
3) the combined store / product dimension might change over time (I mean: not SCD type xyz, but just another definition

ideally the solution does not impact a fact table and offers a combined store / product dimension which can be plugged in, into an fact table containing store & product + the requirement that not every store / fact combination is covered in the combined dimension table.

I welcome any other suggestion.

Thanks again for your appreciated feedback.
Nodderwaak

nodderwaak

Posts : 2
Join date : 2010-04-12

View user profile

Back to top Go down

Re: relating 2 existing dimensions combined to a fact table

Post  ngalemmo on Mon Apr 12, 2010 3:59 pm

Not knowing the nature of the attributes, it is difficult to comment on what is the appropriate solution.

Certainly you can build a table with the store and product keys. Technically, it is not a dimension, but rather a factless fact table with 3 degenerate dimensions (the three attributes). But, that's splitting hairs...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: relating 2 existing dimensions combined to a fact table

Post  sanjayvyas on Wed Apr 14, 2010 8:55 am

It seems like you are not modifying the grain of your fact table by adding these new attributes, right?
In other words,these attributes can be safely added to the schema without "reloading" the fact.
So, you need to evaluate whether they belong to a bridge table to product or store, or as an independent entity to this schema based on the nature of these attributes as ngalemmo mentioned.
Does this makes sense?

sanjayvyas

Posts : 3
Join date : 2010-04-13

View user profile

Back to top Go down

Re: relating 2 existing dimensions combined to a fact table

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