Modeling of Vendor Quotes in Dimension Model

View previous topic View next topic Go down

Modeling of Vendor Quotes in Dimension Model

Post  lmetodiev on Sun Mar 08, 2009 4:33 pm

The Vendor quotes are presented in our systems as:
(Quote ID, Vendor Number, Product Number, Effective Date, Discontinue Date, Quantity From, Quantity To, Price, Currency)
A standard business event is a vendor to change his quotes, for example to continue them with two months or to increase all prices with 10%.
In both cases the vendor quotes will be updated into the database.
The questions:
1. How the quotes have to be modeled?
as a dimension although the unique key is a composite key (Vendor Number, Product Number, Effective Date)
as a fact although the measures qty from, qty to and price are non additive
If it is a fact - how could be connected this fact with the purchase order fact which have the following structure
(Purchase Order Number, Vendor ID, Product ID, Quotate ID, Quantity, Price)?

2. How the changes in the vendor quotes have to be applied into DWH?
- in case of Quote Dimension it could be a SCD
- in case of Quote Facts -

Please advise!

Thanks!

lmetodiev

Posts : 13
Join date : 2009-02-20

View user profile

Back to top Go down

Re: Modeling of Vendor Quotes in Dimension Model

Post  DilMustafa on Sun Mar 08, 2009 7:46 pm

I will model a Quote Dimension along with Vendor, Product and other Dimension. Quote Dimension is going to be an SCD, Date_Effective_From and Date_Effective_To. Price and Quantity can be hendled in the Fact or in the Dimension depending on the kind of analysis you want to perform. How frequently price changes for quote based on the Price_From and Price_To columns? How many quotes do you in the systems and how many quotes change every day?

DilMustafa

Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada

View user profile

Back to top Go down

Re: Modeling of Vendor Quotes in Dimension Model

Post  lmetodiev on Mon Mar 09, 2009 1:55 am

There are about 80K quotes and in average there are about 500 changes per month. What I concern if the model is SCD - the key of the dimension is a composite key (Vendor Number, Product Number, Effective Date) and according to Kimball's The data warehouse Toolkit Second Edition page 19 in this case it has to be modelled like a fact.
Probably if the model is SCD I have to use Quote ID as a PK and the Vendor Quote Dimension will have the following structure:

Quote ID - PK
Vendor Number,Product Number,Effective Date - - natural key
Discontinue Date - Type 2 Field SCD
Quantity From - Type 2 Field SCD
Quantity To - Type 2 Field SCD
Price - Type 2 Field SCD
Currency - - Type 2 Field SCD
Valid From, Valid To - SCD timestamps

Thanks!

lmetodiev

Posts : 13
Join date : 2009-02-20

View user profile

Back to top Go down

Re: Modeling of Vendor Quotes in Dimension Model

Post  DilMustafa on Thu Mar 12, 2009 12:23 am

You need to have a SK in every dimension. I do not see a way of tracking history in a dimension without surrogate keys. 80000 quotes with 500 changes a month qualifies to be a slowly changing dimension. Model it with effective to and effective from datestamps and a status= Current or Old.

DilMustafa

Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada

View user profile

Back to top Go down

Re: Modeling of Vendor Quotes in Dimension Model

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