Modelling a special kind of dimension

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.

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)

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