Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Product Dimension Table
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

United Kingdom
440 Posts

Posted - 07/24/2006 :  15:39:18  Show Profile
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 - 07/24/2006 :  16:13:02  Show Profile
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.

Go to Top of Page

Constraint Violating Yak Guru

United Kingdom
440 Posts

Posted - 07/24/2006 :  16:30:03  Show Profile
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 dimension

What 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 on

What 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 ?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000