Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jim77
Constraint Violating Yak Guru
440 Posts |
 Posted - 2006-07-24 : 15:39:18
|
| Hello guys,Could someone please advise me of the correct cube schema. I have an exsisting cube that I need to enhance the problem is the product and suppliers tables of this cube are all in one dimension contained in a single view, I am finding it hard to get my head around the fact that depending on the store the product was sold in the suppliers details will change.My immediatte reaction is to create a new supplier dimension and take the supplier table out of the product dimension and have a supplierid in the fact table link to the new supplioer dimension, this is obviously going to mean a lot more work but hopefully not in vain, Would this be the correct thing to do please guys ? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-24 : 16:13:02
|
| I would start by trying to define the true relationship of product, supplier, and sale.For exapmple, is it true that only a single supplier is used for a product at a store, or does this really mean a primary supplier? What happens if you change suppliers? What if you transfer produts from one location to another? And so on.Once you can define the true relationship, the table structure will be more obvious.CODO ERGO SUM |
 |
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2006-07-24 : 16:30:03
|
| I think this is mostley static Michael ie: one supplier will only supply a product to a certain fixed number of stores in one island for instance but a different supplier might supply the same product in a different store in a different island, mostly though one product is always supplied by one supplier regardless of the store but 10 % of the time a product will have different suppliers depending on the store number.is it true that only a single supplier is used for a product at a store ?... Yes and No I have a fixed number of suppliers supplying a certain range of stores that is moslty static but the suppliers to that store might change.or does this really mean a primary supplier ?... I will have to filter only main suppliers to be included in the dimensionWhat happens if you change suppliers?... hopefully this will be changed into the underlying tables which is dts'd from the live system into the datamart everynight where the dimension table is based onWhat if you transfer products from one location to another? I run a data import every day into a itemsales fact table whatever the supplier of that product is on that day in that store in that island will be picked up from the live system.It is starting to become clearer just by answering your questions Michael thank-you, do you think this justifies a seperate supplier dimension ? |
|
|
|
|
|
|
|
|
Product Dimension Table