Modelling a special kind of dimension

View previous topic View next topic Go down

Modelling a special kind of dimension

Post  platforminc on Tue Jul 10, 2012 11:37 am

I'm currently looking at a type of dimension which is used for storage/transportation of commodity products, now my hold_point can either be any of the following.

storage facility
plant
pipeline
port
Terminal
vessel.

I am thinking of how to model the dimension, as each of these hold points have other attributes snowflaking off them, so a hold point called port for example will have a country_location_id off it, a plant will have a sub type of plant type as well, i.e coal, crude etc. So will a terminal because we can have water, crude, gas terminal.


Lastly, its the classical date/time dimension, if I need a granularity of of to time level to the minute, is it better to keep a data dimension up till n + 50years for example, then have a time dimension of 24 * 60 ?

How will joins work under this kind of structure, and why is it better then keeping dates all the way through which is visible for me, and I dont have to think about joins onto 2 columns which may not make sense to everyone initially.

Thanks in advance.

platforminc

Posts : 7
Join date : 2012-05-25

View user profile

Back to top Go down

Re: Modelling a special kind of dimension

Post  LAndrews on Tue Jul 10, 2012 4:14 pm

The holding point dimension is a tricky one.

I'd think of it like a heterogeneous product dimension. Kimball has examples in most of his books.

Its one of the few areas where snowflaking is usually found. The solution is based on the assumption that you will have base dimensions for each of the holding types. (e.g. Plant Dimension, Pipeline Dimension etc).

The holding dimension then looks like :

- Holding Point Key (Surrogate Key)
- Holding Point Type
- Holding Point Name
+ Any Other Common Attributes +
- Snowflake Key (to the appropriate base dimension)

As for the Date/Time dimension question - best practice usually is to keep separate dimensions, and then have both keys on any facts that are granular to the time. Each Dimension is joined based on surrogate keys.

A single date/time dimension becomes huge. Using your 50 years example

Date Dimension : approx 18250 rows (50x365)
Time Dimension (Minute grain) : 1440 rows (24x60)
Single Combined Dimension : 26,280,000 rows (50x365x24x60)

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Modelling a special kind of dimension

Post  leadfoot on Fri Jul 13, 2012 8:38 am

I believe they recommend you have a regular date dimension, then put a time of day timestamp on the fact record itself for actual times during the day.

leadfoot

Posts : 4
Join date : 2012-01-16

View user profile

Back to top Go down

Re: Modelling a special kind of dimension

Post  hang on Fri Jul 13, 2012 6:34 pm

leadfoot wrote:I believe they recommend you have a regular date dimension, then put a time of day timestamp on the fact record itself for actual times during the day.
I believe so too, unless you need to hold time slot definition specified by business. For general time grain like minute or second, you should just store the timestamp DD in the fact table based on Kimball latest thinking.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Modelling a special kind of dimension

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