Modeling of Vendor Quotes in Dimension Model
2 posters
Page 1 of 1
Modeling of Vendor Quotes in Dimension Model
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!
(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
Re: Modeling of Vendor Quotes in Dimension Model
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
Re: Modeling of Vendor Quotes in Dimension Model
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!
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
Re: Modeling of Vendor Quotes in Dimension Model
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
Similar topics
» How do you model a dimension that behaves both as a dimension and a fact??
» Dimensional modeling of product and vendor for invoice fact
» Vendor Dimension
» Dimension design idea for vendor and employee relationship
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Dimensional modeling of product and vendor for invoice fact
» Vendor Dimension
» Dimension design idea for vendor and employee relationship
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum