I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema.

In all the examples I've seen the fact table references the dim tables, so I'm assuming the questionId and responseId would be part of the fact table? Any advice would be much appreciated.

enter image description here

  • I'd first advise you rename these tables to prefix them with Fact or Dim, depending on what they are so that it's clear. Also, why exactly do you want a star schema? A snowflake may be what you're looking for, it just depends. Could use more details; a picture doesn't really mean anything without information on what you're trying to accomplish. :) – Haris Khan Aug 2 '16 at 21:06
  • I don't see how responses relates to Options – vercelli Aug 5 '16 at 16:05

Ok, based on your very limited documentation, I would do the following:

Fact Tables - Your fact table is your measurement table. It is the thing that happened. It is the meeting together of dimension tables, typically at a point in time. In your diagram,

  1. RESPONSES is your fact table.
  2. OPTIONS could be a fact table. If you denormalize it you could have dimensions associated with it.

Dimension Tables - Dimension tables contain the textual context associated with a measurement event. It describes the “who, what, where, when, how, and why.” Based on your diagram:

  1. SAMPLE would be a dimension table, describing the people who make a response. It would be the closest you have to a "customer" dimension table.
  2. QUESTIONS would be a dimension table, describing the question in detail. You could extend this dimension to include more data on the question type, such as length, IsMultipleChoice, IsFreeText, IsPersonal, isPrivate, etc. I would think of this dimension as a "product" dimension table, based on the limited schema you have.
  3. OPTIONS could be a dimension table. It looks like it is something you base the response field on. The possibility of responses for a given question.

Personally, I think you need to read up on Data Warehousing. The Data Warehouse Toolkit by Kimball is an invaluable resource.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.