Survey Data with Benchmark Data

View previous topic View next topic Go down

Survey Data with Benchmark Data

Post  pmax on Wed Apr 30, 2014 11:55 am

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

pmax

Posts : 1
Join date : 2014-04-30

View user profile

Back to top Go down

Re: Survey Data with Benchmark Data

Post  nick_white on Thu May 01, 2014 2:40 am

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.

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum