PreludeSys reduces operation cost and modernizes reporting using Power BI for a leading transportation company
About the client
The client is a leading passenger transportation company that serves 95% of the United States population. They promote transportation equity through shared rides, bike share systems, electric scooters, and public transit partnerships.
Client challenges
- The client acquired a company whose data was stored in a MySQL on-premise database that required complex storage procedures and reporting.
- The lack of a centralized data store created complexities in report generation and increased operational costs. The parent company used Snowflake database and Excel for reports.
- To rectify this issue, the client sought to integrate their new datasets into their parent company’s database and simplify the reporting process.
Solution
- PreludeSys developed a semi-automated Python approach to migrate the client’s data from MySQL to Snowflake.
- We mapped data between the two databases by designing a schema similar to their existing Snowflake design. We then created Snowpipes to extract the data from MySQL and moved it to Snowflake.
- We developed data models based on their Excel reports and imported them into Power BI, which allowed them to modernize their reporting process and access insights instantly.
Results at a glance
| 3X reduction in operation costs |
Faster ad hoc report generation |
Centralized data streamlines reporting |
Benefits
- Quicker transformation was enabled by semi-automated SQL.
- Ability to create ad hoc visuals faster with a centralized data store.
- Enabled insightful reports and dashboard generation with Power BI.
- Reduced reporting operations costs by leveraging Power BI.
Technology
MySQL, Snowflake, Power BI, Microsoft Azure