Education Project – Enhancing Educational Data Reporting
The Education Project focused on consolidating educational data from over 150 institutions, with data provided quarterly. The aim was to generate comprehensive reports at the country, state, and school levels, covering educators' performance, learners' achievements, and attendance metrics.
A Real Estate company was looking forward to generating more traction in their sales and marketing initiatives. They had few existing and few new requirements to focus on.
FMCG company elevates data quality through the implementation of advanced data engineering best practices within its established data pipeline
credits-Microsoft
ETL Process:
Extraction, Transformation, and Loading (ETL): Merged data from various sources, corrected data formats, managed duplication, and refined data formats.
Data Loading: Applied full, incremental, streaming, and batch loading techniques to ensure effective data integration.
Tools Used:
Data Sources: Excel Workbook, CSV, Azure SQL DB, SSMS.
Orchestration: Blob Storage (ingestion), Azure Synapse Data Warehouse (storage), Azure Data Factory (processing).
Modeling and Calculations: SSAS (Analysis Services).
Reporting/Visualization: Power BI Desktop, Report Builder.
Deployment: Visual Studio.
Current System and Challenges:
Current System: Data was sourced from Excel workbooks, CSV files, and Azure SQL Database. The data was processed through ETL pipelines and modeled in SSAS (SQL Server Analysis Services). Reports were generated using Power BI to provide insights into educator performance, learner performance, and attendance.
Challenges Faced:
Data Type Issues: Data inconsistencies and format problems arose from varied file sources, including Excel and CSV files, leading to incorrect data presentation.
Modeling Complexity: Handling multiple fact and dimension tables resulted in a complex data model that caused filtering and calculation issues in reports.
Report Performance: Complex data models slowed down report loading times and affected visual rendering in Power BI.
Solution Provided and Its Impact
1. Data Type Issues:
Solution: Implemented appropriate file formats and utilized Power BI's Power Query Editor for data type inspection and correction. Applied manual adjustments to ensure proper data formatting.
Impact: Improved data consistency and accuracy, leading to more reliable reporting.
2. Modeling Complexity:
Solution: Resolved ambiguous relationships caused by bi-directional filters. Employed inactive relationships and optimized calculations using DAX to simplify the data model.
Impact: Enhanced report filtering and calculation accuracy, reducing complexity and improving model performance.
3. Report Performance:
Solution: Simplified complex queries and utilized an SSAS tabular model to reduce data redundancy. Connected Power BI reports to SSAS via a live connection to enhance performance.
Impact: Accelerated report loading times and improved visual rendering, leading to a better user experience.