Confused on how to model a certain situation.
3 posters
Page 1 of 1
Confused on how to model a certain situation.
I'm working on modeling out dimensions and fact tables for a section of our billing system. Most of it is very straight forward, but i've hit one section that I'm not entirely sure how to handle.
We have a table that contains order information at the line item level. Off of this table is a table of determinant values, stored like this
There are currently 271 possible determinants, some of them additive and others are descriptive.
What do I do with them? Do I create a column for each descriptive determinant and a corresponding dimension row? Do I treat them all as degenerate dimension values in a fact table?
My other concern is that it is possible to have the same determinant multiple times on a give line item. What do I do then?
Any advice on how to handle this type of data would be greatly appreciated.
We have a table that contains order information at the line item level. Off of this table is a table of determinant values, stored like this
KeyVal  LineItemID  DeterminantID  DeterminantValue 
1  1  1  10 
2  1  2  A string of text 
3  1  3  10/10/2010 
4  2  5  93.66 
There are currently 271 possible determinants, some of them additive and others are descriptive.
What do I do with them? Do I create a column for each descriptive determinant and a corresponding dimension row? Do I treat them all as degenerate dimension values in a fact table?
My other concern is that it is possible to have the same determinant multiple times on a give line item. What do I do then?
Any advice on how to handle this type of data would be greatly appreciated.
JasonHilton Posts : 3
Join date : 20110726
Re: Confused on how to model a certain situation.
As there are a fixed, and very small number of determinants, create a dimension.
Since a line can have more than one determinant you can either store them in a separate fact table or treat them as a multivalued dimension off the line item fact.
Since a line can have more than one determinant you can either store them in a separate fact table or treat them as a multivalued dimension off the line item fact.
Re: Confused on how to model a certain situation.
ngalemmo wrote:As there are a fixed, and very small number of determinants, create a dimension.
Since a line can have more than one determinant you can either store them in a separate fact table or treat them as a multivalued dimension off the line item fact.
Sorry, I think there is some confusion. There are 271 different types of determinants (billdate, orderdate, taxExempt, Productcode, etc), which may or may not grow. There are over 302 million determinant values currently associated to line items growing at approx 2030 million rows a month.
JasonHilton Posts : 3
Join date : 20110726
Re: Confused on how to model a certain situation.
Then flatten it out. If this is just a vertical arrangement of what would otherwise show up as columns on an order line row, you need to model it as if it was that way to begin with. Dates should be FKs to the date dimension, product IDs to the product dimension and so forth. Some attributes may be grouped into junk dimensions, others may be dimensions on their own.
Re: Confused on how to model a certain situation.
Interesting problem! Blessing feature of OLTP which is the curse for DW !! We had a similar type of issue in one of our previous projects and handled the below way.
 Identify all the relevant determinant types required to be stored in DW
 Segregate them as follows
 Measures in fact table (if they need to aggregated)
 Attributes of existing dimension
 Degenerate Dimension (use it as an exception, if it is used only as a dsiplay in the report at line item level)
 Key Dates as separate dimension
 Combination of low cardinal values as Junk dimension
 Combination of lengthy textual attributes as separte dimension
The important point is to clearly define and agree upon the format of the data stored in this field if there is no validation enforced in OLTP.
 Identify all the relevant determinant types required to be stored in DW
 Segregate them as follows
 Measures in fact table (if they need to aggregated)
 Attributes of existing dimension
 Degenerate Dimension (use it as an exception, if it is used only as a dsiplay in the report at line item level)
 Key Dates as separate dimension
 Combination of low cardinal values as Junk dimension
 Combination of lengthy textual attributes as separte dimension
The important point is to clearly define and agree upon the format of the data stored in this field if there is no validation enforced in OLTP.
datamodeller Posts : 9
Join date : 20100725
Similar topics
» Confused about many to many
» Be confused with Outtrigger
» Centipede Ok in this situation?
» Rule based algorithm to convert an ER model to a dimensional model
» How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)
» Be confused with Outtrigger
» Centipede Ok in this situation?
» Rule based algorithm to convert an ER model to a dimensional model
» How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum

