1

I have a form which depending upon a center can have different questions. The answers to these questions are saved as string (nvarchar) in the transactional system. Some questions have answers which need to be a analyzed and need to be part of calculations where as other ones are just for gathering information so wont be measured. I have not run into a situation like this before so i am confused how to handle it.

Following is what i was thinking from a design perspective

  1. Center Dimension (related to Answers Fact)
  2. Form Dimension (related to Answers Fact and FormToQuestion Bridge table)
  3. Question Dimension (related to Answers Fact and FormToQuestion Bridge table)
  4. FormToQuestion Bridge Table
  5. Answers Fact Table

I would really appreciate if someone can guide me with the design and cube calculation perspective. If any more detail is needed please let me know.

mucio
  • 7,014
  • 1
  • 21
  • 33
AM96
  • 43
  • 7

1 Answers1

1

From the information you've provided, it sounds as though in regards to the Form/Question Dimensions you are considering a more normalized approach found in a OLTP information system and a snowflake schema.

(optional) I would suggest a more denormalized approach combining your Form and Question's into a single "wider" dimension, more commonly in OLAP solutions.

Addressing your question of a measure as a string, I would recommend that your answers be represented within a DimAnswers dimension as (versus a fact alone). This way your answers will have some other primary key (potentially the identity id).

Your Fact will then map the DimQuestion and DimAnswers dimensions providing a measure of the answers selected in say FactAnswers.

In short:

  1. DimQuestion (optionally combined with the following two)
  2. DimQuestionToForm (optional)
  3. DimForm (optional)
  4. DimAnswers
  5. FactAnswers (ie. RecordID, DimQuestionKey, DimAnswersKey, measure1)
samb0x
  • 185
  • 15
  • Thanks for your reply. This is what i end up doing. 1. Dimension Questions 2. Dimension Answers 3. Dimension Form 4. FactItem Table ( QuestionID, AnswerID, FormId, Value which is a MDX calculation and gets the value from Answers Dimension) – AM96 May 21 '15 at 21:02
  • Great! A model approach. If my reply was of any help please up-vote and/or mark as an answer. I hope I was! Take care. – samb0x Jun 17 '15 at 15:22
  • samb0x i don't have enough reputation point to up-vote. I did mark the post as an answer. Thanks. – AM96 Jun 28 '15 at 20:44