Confused on how to model a certain situation.

View previous topic View next topic Go down

Confused on how to model a certain situation.

Post  JasonHilton on Thu Sep 01, 2011 3:54 pm

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

KeyValLineItemIDDeterminantIDDeterminantValue
11110
212 A string of text
31310/10/2010
425-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 : 2011-07-26

View user profile

Back to top Go down

Re: Confused on how to model a certain situation.

Post  ngalemmo on Thu Sep 01, 2011 4:35 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Confused on how to model a certain situation.

Post  JasonHilton on Thu Sep 01, 2011 4:41 pm

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.


JasonHilton

Posts : 3
Join date : 2011-07-26

View user profile

Back to top Go down

Re: Confused on how to model a certain situation.

Post  ngalemmo on Thu Sep 01, 2011 5:28 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Confused on how to model a certain situation.

Post  datamodeller on Mon Sep 05, 2011 1:48 am

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.

datamodeller

Posts : 9
Join date : 2010-07-25

View user profile

Back to top Go down

Re: Confused on how to model a certain situation.

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