Hackathon-Vertex Ventures

• 

  At local machine, download mdb files from Azure Blob Storage, using python file (refer to git hub)

• 

  At local machine, upload converted csv files to AZ blob storage

• 

  At local machine, convert mdb files to csv

• 

  Create a dedicated SQL Pool to work with Power BI later

• 

  4 Copy Data Actitivities are to copy data from Azure Blob Storage to Azure Synapse Dedicated Pool

• 

  Each of copy activity will have source data and destination data

• 

  This is a sample of Power BI Dashboard, using the csv files

Inspiration: My participation in this hackathon was driven by a desire to gain hands-on experience and challenge myself to apply my knowledge of Azure. Additionally, the topic of improving education resonated with me, as I believe in the transformative power of data-driven solutions. Moreover, the project presented an opportunity to explore two services: Data Engineering and Azure Synapse, which align with my career aspirations.

What it does: Our solution leverages Azure Synapse and Power BI to empower schools in collecting and analyzing data, enabling them to gain valuable insights into student performance, enrollment trends, and educational outcomes. By utilizing these powerful tools, schools can make informed decisions to drive educational improvement.

How I built it: The project comprises five key steps. Firstly, I utilized Azure Synapse's data ingestion capabilities to fetch raw data from the specified data source and stored it in Azure Blob Storage. Next, using a Spark notebook, I developed Python code to handle the unzipping of files and stored them in a Microsoft Access format. Subsequently, I downloaded the files to my local machine and utilized Python code to further convert them to CSV format before uploading them back to Azure Blob Storage. Finally, I copied the CSV files to Azure Synapse's dedicated SQL DB pool, making them readily available for visualization using Power BI.

Challenges I ran into: One challenge I encountered was the limitation of installing compatible packages for converting Microsoft Access files to CSV format. As a workaround, I had to handle this part of the process locally on my machine. Additionally, I faced difficulties connecting Power BI to Azure Synapse Analytics, despite using the same server name, username, and password that successfully connected Azure Data Studio to the dedicated SQL pool. Consequently, I resorted to manually uploading the CSV files to Power BI for demonstration purposes.

Accomplishments that I'm proud of: One of my proudest achievements in this project was successfully automating certain parts of the data pipeline. This allowed for a streamlined and efficient process, enhancing the overall solution.

What I learned: Throughout this project, I gained valuable insights into Azure Synapse and Power BI, including their capabilities and limitations. I also developed skills in handling exceptional errors, an important aspect of any data-driven project.

What's next for Hackathon-Vertex Ventures: Looking ahead, the next steps for Hackathon-Vertex Ventures involve further enhancing the automation of the data pipelines, exploring additional features and functionalities of Azure Synapse and Power BI, and continuing to refine the solution to meet the specific needs of schools in their data-driven educational improvement efforts.

Built With

• azure-synapse
• powerbi