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

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.

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.

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 20-30 million rows a month.

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.

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.