Modelling current data and daily observations.

trickbooter Wed Apr 06, 2011 1:35 pm

Hi Forum,

(minor edits added due to autocorrect iphone hell)

I have a problem to which I have many solutions, but none that feel particularly clean.

I have a scenario as follows-
I have locations (dimension). Each location has a status that can change daily. I have a requirement to provide status churn analysis, thus I am implementing a factless table that joins location, status and date together. It isn't quite factless, since I have added some additive measures there to aid churn and estate analysis.

I chose factless, rather than scd, to gain daily data (as I mentioned, we do some maths on number of locations that churn status etc).

Anyway, a common requirement is to say, show me xx historical data for locations that are status 'live' today. I appreciate I can build filters to get that, but I am really looking for a drag and drop method for our users.
Option 1: have two role playing dimensions based on status, one called current status an one called historic status. I don't like the idea of temporal role playing dimensions but it would work. the current status dimension would reference the current status key in the locations dimension, while the historic dimension would reference the factless table.
Option 2: use a view joining status to location. Have attributes in the location dimension for current status (there are about 6 attributes related to status). In addition have a separate location status dimension that joins to the factless table to provide history.
Option 3: create an outrigger type dimension that implements current and historic attributes into a single dimension. To do this, I would need to make my location status dim keyed against location, which reduces it's usefulness going forwards, but behaves neatly today.

Any other ideas?

Schema

Dim-Locations (LocationKey, LocNatKey, CurStatusKey, etc)
Dim-Statuses (one to many to location representing 'current status') (StatusKey, StatNatKey, StatusCode, StatusGroup, etc)
Dim-Date (DateKey, Date, etc)
Fact-Location Status Observations (factless joining the above three tables) (LocationKey, StatusKey, DateKey)
+ other facts and dims to perform other analysis.

If I did option 3 as described above (kind-of bridge), my schema would become...

Dim-Locations (LocationKey, LocNatKey, etc)
Dim-LocationStatus (LocationStatusKey, LocationKey, StatusKey, CurStatusKey) (this would be made up into a view giving (CurrentStatusCode, StatusCode, CurrentStatusGroup, StatusGroup)
Dim-Date (DateKey, Date, etc)
Fact-Location Status Observations (LocationKey, LocationStatusKey, DateKey)