Survey Data with Benchmark Data

[pmax]

First, let me preface with the fact I am new to dimensional modeling. We utilize a third party company to conduct customer surveys.
 
We have the majority of the model working as expected, but I am having difficulties in modeling the benchmark data (percent lookup info) that goes along with the surveys.
 
The Fact_SurveyQuestion table contains each line item (question) for every survey done per customer.
 
Fact_SurveyQuestion - Has a questionSK - that relates to the dim_Question table.
                             Has an areaGroupSk - that relates to the dim_FacilityReportUnitGroupings table.
                             Has a ReportUnitGroupSK - that relates to the dim_FacilityReportUnitGroupings table.
The dim_question has a NormID. There can be many questions that have the same NormID.
The dim_FacilityReportUnitGroupings has a NormGroupID.
 
The dim_Percentile table has a percentile and percentTopbox for every NormGroup, NormID and NormYear combination.
 
PROBLEM: the dim_Percent table that has a many to many relationship to the fact_surveyQuestion table. Each line item data in the Fact_SurveyQuestion table can have many dim_Percentiles. (1000 to be exact 0 - 99.9) (Note that the NormYear in the Fact_surveyQuestion table comes from the year for the DischargeDate stored in that table)
 
The sole purpose of the dim_percentile table is to lookup the topbox values based on percentile or lookup percentile based on topbox.
 
I have been reading and I believe that I could use a bridge table to resolve this issue. The bridge would be between the dim_question and the dim_percentile. I do not see the need to have a weight factor since we only need to lookup percentages/topbox info from the dim_percent table.
 
Does this seem to an applicable application for a bridge table?
If so, do I still need to have a weight factor?
If it is not a good application for a bridge table, do you have any suggestions of how I might resolve this issue?
 
Thank You
Paula

[nick_white]

Hi - please could you expand on what the reports look like that you are going to deliver with this model? I understand the basic survey response reporting but I'm not clear how the dim_percentile data is included in this report.

As an aside, and not directly relevant to your question, but would there be any benefit in creating a Norm Dim with the NormId, NormGroup and any other relevant attributes? You could then relate this directly to the Fact rather than having them as, what looks like, foreign keys on your Dims.