Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
date | Office | Supervisor | Employee | Question 1 | Question 2 | Question 3 | Question 4 | Question 5 |
1/1/2019 | X Office | X Supervisor | X Emloyee | Agree | Disagree | Strongly Agree | Agree | Yes/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?
Solved! Go to Solution.
It looks good. You may also take a look at https://www.sqlbi.com/?s=Data+modeling.
It looks good. You may also take a look at https://www.sqlbi.com/?s=Data+modeling.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |