Forum Discussion

bwooten's avatar
bwooten
Regular Visitor
6 years ago
Solved

Data Model for Forms Survey data

Hello, 

 

Long time lurker, first time poster.  What a wonderful community and resource. 

 

I'm having a little trouble modeling Microsoft Forms data.  I've done reporting for years, but have little experince in creating data models.  I have a general understanding of modeling but I am looking for feedback. 

 

I have a solution writing Forms data to an Excel OneDrive file that is connected to Power BI.  I'm working in Power Query to create dimensions and fact tables.  The survey is essentially a tool for supervisors to analyze performance.  There are a series of likert scale questions for two categories; Category and Elements.  

 

Example of Flat file structure:

dateOfficeSupervisorEmployeeQuestion 1Question 2Question 3Question 4Question 5
1/1/2019X OfficeX SupervisorX EmloyeeAgreeDisagreeStrongly AgreeAgreeYes/No

 

Current model:

Dimensions:  Date, Office, Supervisor, and Employee, Category questions, Category Response (Agree, etc) Element questions, Element responses (Yes/No). 

Pivoted Fact Table with keys for date, Office, Supervisor, and Employee

Measures for Total Responses, Total Agree, Total Disagree, etc.  Plus measures for percent of each.  

 

Current model:

 
 

Is this a good way to model survey data?  Not sure about the pivoted 'fact table.'  Where can I find info on creating survey data models for Power BI or do you have any recomendations?  

Need Help

2 Replies

    • bwooten's avatar
      bwooten
      Regular Visitor
      6 years ago

      Thank you for the feedback and for the resource.  I was not aware of it and have found it to be super helpful.  

      This is the first model that I've created.  And while I was able to get things to work, I just wasn't confident that it was the right approach. 

       

      Previously all of my data models were prepared for me and all I had to do was create reports/visualizations.  Power Query is a neat tool and I found the modeling experience to be fairly easy.  I had to unpivot the flat file and was concerned that I wasn't being efficent.  

       

      Having another set of eyes on this has defiently increased my confidence.  Thank you for that.  

       

      Brett