Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Building a datawarehouse star schema

Building a datawarehouse star schema

Building a datawarehouse star schema


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.


RE: Building a datawarehouse star schema


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

RE: Building a datawarehouse star schema

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

RE: Building a datawarehouse star schema

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.


RE: Building a datawarehouse star schema

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close