Building a datawarehouse star schema

[ron3163]

I have a requirement to build a design to incorporate data from a Global census.

There are a series of questions to which responses are given by respondents in different countries.

The answers to these questions can be numeric (or monetory) as well as descriptive.

I have come up with a design with the following:

Dim Question (having a list of all questions)

Dim Respondent (the respondent list)

Dim Country (where that respondent belongs to)

Fact (Containing question Id, respondent id, country id and the answer itself)

The problem Iam facing is obvious.

I can only store rows in the fact relating to answers which are numeric (or monetory).

I cannot store descriptive answers as these cannot be aggregated.

Is there any way of solving this, or it is not feasible to build a star schema based on these requirements?

Thanks in advance.

Pramod

 

[blom0344]

I cannot store descriptive answers as these cannot be aggregated.

That is wrong. Storage has little to do with aggregate behavior. You can store the descriptive answers quite well, but you cannot use them when performing quantitive analysis on the numeric ( yes / no ?) or monetary fields.

What wonders me is how you intend to aggregate the numeric/monetary values?

If you still want to seperate the data, why not create 2 views on the original fact, one for descriptive answers and one for performing aggregates..

Ties Blom

 

[johnherman]

This kind of application (survey without predefined categories) does not lend itself well to dimensional modeling. A worthy consideration is to have an application where the analyst(s) can categorized text answers, grouping them according to their interest.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[ron3163]

Thanks Blom0344

I have actually created two fact tables. One containing only numeric data (and monetory).

And the other having only descriptive answers.

(Note that both the fact tables have composite keys, and then the "answer" field itself)

When I built the cube, there was no problem looking into the answers data from the first fact (monetory values).

But with the second fact table, all I can see is the count, and not the answers themselves against the questions.

 

[blom0344]

A typical cube plots dimensions against measures. Non- measures like 'answers' can not be presented in a cube.
As a Cognos shop we regularly get requests from users to plot non-facts (such as dates) in cube designs, but that is not going to a viable option..

Ties Blom