Many to Many table with dimension

building semantic layer
1

Hello all,

I am designing a universe for quality service surveys in courthouses.

I have a survey table to contains several dimension (gender, ethnicity, age…they describe the person that answered the survey)
The person that answered the survey can have mutiple reason for attending a courthouse.
Finally a survey has many questions (some are optional) with related answers (finit list):

GENDER (dimension)
gender_key
gender_desc

SURVEY
gender_key (linked to dimension)
ethnicity_key (linked to dimension)
survey_key

SURVEY_QA_RELATIONSHIP
(tables that links question with their answers to the survey)
survey_key
question_key
answer_key

QUESTION (dimension)
question_key
question_desc

ANSWER (dimension)
answer_key
answer_desc

SURVEY_ATTENDANCE_RELATIONSHIP
(table that link teh survey to all teh reason why the person attended)
survey_key
reason_key

REASON (dimension)
reason_key
reason_desc

So I basically have 3 branches around my survey:
1 for QA
1 for reasons to attend
1 for any dimension

So I have a problem in BO designer as the table survey has 2 1toMany relationships going from it (Chasm)

Designer wants to create 2 contexts:

From Question/answers to all the dimensions
From Attendance reason to all the dimensions.
By doing that I can’t link question/answers to Attendance reason. But I need to be able to have question/answers per dimension per attendance reason.

My data model is fairly simple and uses common design practice, I am pretty sure I can keep it as is and play around with designer to make it understand how tables are linked, but I don’t know how to.

Any help will be greatly appreciated.

Cheers,
Minh

minh (BOB member since 2004-07-15)

2

Have you read the chapter on Factless Fact Tables in Kimball’s The Data Warehouse Toolkit? I wouldn’t be surprised if the SURVEY_QA_RELATIONSHIP table is the only Fact table you need.

Have you read the Designer’s Guide? It sounds as if you haven’t. :?

JP Brooks :us: (BOB member since 2002-10-22)

3

Yes I did read teh designer’s guide. (Well the one that I got from BO…no idea if there is a better one).
So I could use contexts and aliases to get rid of my chasm and fan trap.

The only thing that didnt work is that I couldt link Question Answers to Attendance reason. It would not be possible to filter reports with Q&A by Attendance Reason.

I changed my data model to make it work. BUT I am interested in knowing how to make BO understand such a data model, as it is really clean.

Minh

minh (BOB member since 2004-07-15)