How to build a successful PowerBi report for social research
This will not be a typical PowerBi tutorial. If you need to get into PowerBi search medium or google, there is much content. This will be specific about the use of PowerBi for social research and building a socio-economic impact report.
PowerBi is a business analytics service developed by Microsoft. It provides interactive visualizations with self-service Bussiness Intelligence (BI) capabilities. End users can create reports and dashboards without any coding skills, and they do not have to depend on information technology staff or database administrators. PowerBi also gives you cloud-based BI services, known as "PowerBi Services," along with a desktop-based interface called "Power BI Desktop." It offers data warehouse capabilities, including data preparation, data discovery, and interactive dashboards. PowerBi lets you connect your data from SQL Server databases, Analytical Services, excel/CSV files, and many other formats. Reporting portals embed PowerBi reports and dashboards to give you a unified experience. There is a difference between reports and dashboard in PowerBi, and this article gives you a good overview and information about how to explain the differences.
My task was to build an online tool to monitor the socio-economic impact of the Covid-19 epidemic in South Asia. I am working for many years in the United Nations System, and nine countries in South Asia will use this tool. The tool will include internal and external data sources from telephone surveys (CATI) and Rapid Assessments conducted using SMS two-way communications (RapidPro).
My first decision was to select the right tool for the job, and options are quite limited for this scenario. The tool must be available for multiple users within different countries and simple enough, so users without codding experience can navigate it and use it in their work. Another criterion is licensing. The organization I was working with recently implemented PowerBi at the corporate level. This seems like the best choice.
Now starts the real problem. PowerBi is not designed to be used with text data; it was primary develop for financial reporting, so its quickly consume number, but with text, it’s more complicated. When you receive a Social Research report, it is usually a table with many columns and numbers, but these numbers correspond to text answers from the respondents (data dictionary). You can connect using dictionary questions from the survey to the data and also connect data with answers. This is typically done in statistical software like SPSP or STAT, but this was not fulfilling other requirements for my use case.
PowerBi, as mentioned, was designed for social research, and there is no simple option to connect the table with questions and question code to another table with data. You would need to unpivot the table that’s is not a solution for multiple tables and answers as this will create an index only for one question. The only solution will be to assign text values to all rows of data first and then upload them to PowerBi for analysis.
This is the final look of the data table after the cleaning process and assigning text values to the dictionary numbers. How you can see, also, question codes were replaced with text questions.
In this scenario was also important to remember different methodologies that the analyzed studies were conducted under and that we can not only mix and mash questions from different surveys without understanding the methodology behind them.
Another essential point to make is about questions with multiple answers. So if the answers are coded in multiple columns in your data file, there is no perfect method to use them in PowerBi unless you will duplicate questions and answer as many times as possible answers.
The next step was to figure out a structure for my dashboard/report. I decided to choose a Paginated report structure.
This way I can present first each dataset’s basic characteristics.
And separate pages with thematic visualizations based on the datasets.
The final result is a PowerBi report that has multiple pages and users can go over the datasets and thematic areas covered by the data.
Final thoughts
PowerBi is not typical statistical analysis software, but it has other advantages for rapid data visualization and simplicity for regular users. With more community around PowerBi, new features will be added over time. This is not the first choice software for social research, but hopefully, this will be more popular among statisticians. This article is a small contribution to this goal.
