Conformed "series" dimension / survey modeling?

Hi everyone,

In the interest of intellectual discourse, I was wondering if anyone had experience modeling survey type of information?

My current project (the model is complete--I am curious about if anyone else has tackled this) involves storing survey information covering a wide variety of information related to economic forecasts.

We are moving the business users away from a Time Series DB to a dimensional model... What I came up with was a "Forecast_Fact" containing a "Value"

We have, attached to this, a DATE_DIM and a SERIES_DIM

Basically, this becomes almost a NAME--VALUE datamart...


As there are 400 unique series, it didnt quite make sense to have 400 dimensions... I could have also had them as degenerate dimensions, but there was a requirement that adding series (or dropping) should be straightforward with minimal coding changes...

One SERIES dimension is plenty. You just have a compound natural key: the series and the question/metric.

that is pretty much what I have... I was wondering if anyone else has tackled something like this--and if they had a different approach.

Thanks for the reply! Its good to hear that we make some sense! :-)

Currently in the same situation. I've to model results from a questionaire. Hospitals fill in these questionaire and a central organization gathers them and distribute them to all hospitals (in the netherlands) every year. As it seems now the id's of the questions and answers vary during the years (unfortunatley). So w're thinking about recoding the questions to an uniform id (a lot of work) and then import the data.

In Kimball's book i've found on page 197 a starmodel for surveys and i'm thinking about using this. Dimensions are hospital, question, possible answers, year and in the fact a varchar field with the value. So in the report i have to CAST the answer field to the approriate datatype and do some calculation stuff.

what do you think?

Hennie