Dimensional modeling with Ranged Dimensions

Patterns of dimensional modeling design

SQLBI - Marco Russo — Thu, 06 Sep 2007 08:48:04 GMT

Alberto posted one of the patterns we use designing a dimensional model (this one is about the discretization

re: Dimensional modeling with Ranged Dimensions

Sqlgoof — Mon, 10 Sep 2007 17:39:37 GMT

This is a nice approach when customers are unsure of their "ranges", but i find normally they have very clear ideas on what they are. Still, good for exploratory BI.

re: Dimensional modeling with Ranged Dimensions

ponzie — Thu, 14 Feb 2008 08:40:04 GMT

We use something very similar.

What we additionally do is have multiple ranges defined in the ranged dimension but all keyed with the same surrogate key.

Thus we might have the value 9 in the fact keyed to a row with our first range attribute set to '£0 to £10' and to a second range attribute to '£5 to £10'.

Its early days yet, but we think this will allow us to use the ranged dimension in several roles aand support several ranges. If we decide we want different ranges defined, we add an extra column to the ranges dimension.

The main limitation is getting the granularity reasonable at the start. Changing the grain would require rebuilding the facts to pick up the revised surrogate keys.

We also have applied the same technique to durations.