Skip to content

Achieving seamless integration from SQL into Power BI

Introduction

Below is a real-world business challenge that I’ve encountered in my work. I have tried to lay out my thought process on how I go about it.

The insights below contains examples of work in my previous role, presented in a way that protects sensitive and proprietary information while demonstrating my technical skills and approach.

I have modified the data to ensure confidentiality and removed any information that could identify individuals or an organisation. This approach maintains the integrity of the analysis while upholding strict data privacy standards.

Background

A student survey is conducted annually for all students enrolled. The task was to translate the raw data into a easy digestible visual enabling management to filter insights on student satisfaction and experience based on different cohorts of student level.

The survey data is stored in a table where each survey question is represented as a column (e.g., Student_Life, Facilities, Student_Services etc). This structure is inefficient for reporting and analysis, especially when creating visualisations in Power BI. To create dynamic visuals and improve querying, I needed to transform these columns into rows to make the data more structured for analysis.

Step 1 Data retrieval & transformation

  • I used T-SQL to retrieve and combine data from multiple tables, including student demographics, survey response details and programme of study.
  • Transformed the survey question columns into rows for analysis
  • Applied CASE and COUNT statement to categorise and aggregate students into satisfaction levels (e.g. Satisfied, Dissatisfied, Neutral )
  • To optimise the performance in SQL Server, Common Table Expressions (CTEs) were used for each survey question.
  • Finally, a UNION was used to combine the outputs of the CTEs.
-- Example of one CTE; within it, retrieving data from multiple tables, transformation a survey column and using CASE and COUNT statements
WITH 
Student_Life as
(
SELECT  COUNT(DISTINCT f.Student_Key + f.ProgrammeStudy_Key) AS Overall_invited
	,LEFT(f.Survey_Date_Key, 4) AS Survey_Year
	,f.Student_Key
	,p.ProgrammeStudy_Name
	,'1_Student_Life' AS Survey_Question
	,CASE 
		WHEN f.Response IN ('Agree','Strongly Agree')THEN 'Satisfied'
		WHEN f.Response IN ('Disagree','Strongly Disagree')THEN 'Dissatisfied'
		ELSE f.Response
		END AS Response_group
	,f.ProgrammeStudy_Overall_Response_Count AS Responses
	,sd.Age_Group
FROM [DataWarehouse].[Fact_Survey] AS f 
INNER JOIN DataWarehouse.dbo.Dim_Students AS sd
	ON sd.Students_Key = f.Student_Key
INNER JOIN DataWarehouse.dbo.Dim_Programme AS p
	ON p.ProgrammeStudy_Key = f.ProgrammeStudy_Key
-- Used the LEFT, YEAR(GETDATE()) and RIGHT to dynamically filter for the last two years for the end of December.
WHERE left(f.Survey_Date_Key, 4) BETWEEN year(getdate()) - 1 AND year(getdate()) - 2
	AND 
	right(f.Survey_Date_Key, 4) = '1231'

GROUP BY left(f.Survey_Date_Key, 4)
	,f.Student_Key
	,p.ProgrammeStudy_Name
	,CASE 
		WHEN f.Response IN ('Agree','Strongly Agree')THEN 'Satisfied'
		WHEN f.Response IN ('Disagree','Strongly Disagree')THEN 'Dissatisfied'
		ELSE f.Response
	,f.ProgrammeStudy_Overall_Response_Count
	,sd.Age_Group
)

--Example of combining the CTE outputs with a UNION ALL

select * from Student_Life
union all
select * from Facilities
union all
select * from Student_Services

Step 2 Visualising results

  • To integrate the data from SQL Server to Power BI, I imported the SQL statement into Power BI using the Transform functionality in Power BI.
  • For the business purposes, horizontal barchart were used to visualise the survey results.

Step 3 Set up a repeatable process

  • To maintain a seamless integration from the data source to Power BI every year, I made sure to set up a repeatable process. To acheive this, I avoided hardcoded values (like specific year ranges etc) in the sql statements as seen above and instead used the LEFT, YEAR(GETDATE()) and RIGHT to dynamically filter the years required.
  • I also used Power BI's Fabric scheduled refresh feature to ensure the report display up-to-date yearly information.