Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Confused on how to model a certain situation.

3 posters

Go down

Confused on how to model a certain situation. Empty Confused on how to model a certain situation.

Post  JasonHilton 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

Back to top Go down

Confused on how to model a certain situation. Empty Re: Confused on how to model a certain situation.

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

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

http://aginity.com

Back to top Go down

Confused on how to model a certain situation. Empty Re: Confused on how to model a certain situation.

Post  JasonHilton 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

Back to top Go down

Confused on how to model a certain situation. Empty Re: Confused on how to model a certain situation.

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

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

http://aginity.com

Back to top Go down

Confused on how to model a certain situation. Empty Re: Confused on how to model a certain situation.

Post  datamodeller 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

Back to top Go down

Confused on how to model a certain situation. Empty Re: Confused on how to model a certain situation.

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum