ecobee chooses Flexter to make valuable IoT data in XML accessible to BigQuery users for analytics

Uli Bethke BigQuery, XML

The challenge

A world leader in smart home devices that introduced the world’s first smart thermostat, ecobee has expanded rapidly in the last decade. Naturally, with company growth has come a surge in the amount of data generated by the company, particularly from their sensor devices – and subsequently, the need for clean data, analytics, and standardized reporting across the organization.

Since the ecobee smart devices generate such a vast quantity of sensor data, ease of accessibility to this data is essential in utilising it for product development, marketing, customer service, and many other analytics use cases. Unfortunately, despite sensor data being captured in XML, the ability for the business to analyse this data had so far been restricted by its encapsulation in large XML blocks.

To access the data and extract specific records, users needed to use a specific function in the legacy MySQL database. However, since the BI team’s goal was to move to the Google platform and house data on the BigQuery datawarehouse, which lacked this function, a solution was needed to ensure the majority of users could still access this valuable source of data without halting migration to BigQuery.

Extracting insight had become a challenge for the ecobee team – and considering how valuable IoT data is to the business, it was an issue that needed imminent resolve.

“The mandate of the ecobee BI team is to make trusted data readily available and accessible for all data consumers in the organization for all analytics and reporting use cases.” – Monteil Lin , Director Business Intelligence, ecobee

The solution

Prior to deploying Sonra Flexter, ecobee explored the possibility of coding a custom app that would allow them to parse the XML data. However, after several working sessions, it became clear that this route would be time-heavy and only serve to produce a temporary workaround – one that would likely delay the BigQuery migration.

To maximise efficiency, the company opted to use Flexter – a unique tool that not only parses the large XML block out of the box but can also output it to target tables in a relational structure. This would allow anyone to be able to access and query this data simply using SQL and standard BI tools.

The results

By choosing Flexter, ecobee were able to create and productionise an automated pipeline in just under a month. This allowed the team to automatically convert the XML data from their smart devices to targeted tables on BigQuery. Minor tweaks were then made to the converted relational model to make it more useable, enabling the BI team to publish insights to their BigQuery Data Warehouse for internal consumption.

In turn, ecobee were able to unlock the vast analytical value of the thermostat sensor data for a myriad of analytical and reporting use cases. Thanks to Flexter, ecobee now had a straight-forward solution that enabled them to move their pipeline to BigQuery.

Now, ecobee are equipped to begin expanding analytical use cases by blending the thermostat data with other data such as structured sales and operational insights. The result is deeper analysis that drives efficiency and encourages improvements across product and operation teams.

“To us, the sensor IoT data is a goldmine, and underpins any number of analytical use cases across a large number of functional areas. We very much look forward to exploring applications for this goldmine now that Flexter has unlocked it.” - Monteil Lin , Director Business Intelligence, ecobee

Key takeaways

  • Company-wide access to IoT data is essential for ecobee in driving operational improvements and insights for many teams.
  • Unfortunately, sensor data had been encapsulated in large XML blocks, making extracting valuable insights a challenge.
  • ecobee engaged Sonra Flexter in search for a tool that would allow seamless access to data for key data consumers without halting migration to BigQuery.
  • Flexter enabled ecobee to create an automated pipeline quickly, allowing them to convert XML data from smart devices to targeted tables on BigQuery.
  • As well as saving time and costs, Flexter enabled ecobee to unlock deeper analysis to enhance efficiency across multiple departments.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.