Wednesday, August 7, 2019

Reduce Costs and Save Time with Power BI Dataflows

Business Case:

In today’s business environment, executives require complete visibility into company activity. Our client, the business development (BD) team of a large technology company, needed to provide executives with immediate insight into sales leads at a geographic level. This process was difficult as regional sales leads were tracked across multiple different streams.

The BD team used Trello to track sales leads and Dynamics 365 to convert sales leads into deals. They used Microsoft Flow and RESTful API to move sales data to an Azure SQL database, and Blob Storage and Sales Point for data refreshes. To track sales data, the BD team had to export data to Excel and manually import it over to Azure SQL. Only then could data be pulled into a Power BI report.

As sales status was tracked across multiple platforms, the BD team struggled to consolidate data. Team members would spend hours manually creating an Excel-based sales status report to present to executives.

Key Challenges:

  Consolidate sales status tracking 
  Reduce the time commitment and manual effort towards tracking sales status
  Eliminate direct and maintenance costs incurred by multiple platforms 

Our Solution:

To improve search results for users, we built end-to-end architecture powered by Azure Search.

Solution Design:

We used Power BI dataflows to create a dashboard that shows current sales leads, ongoing sales discussions, and finalized sales.

Figure 1: Solution Design

With Power BI dataflows, we were able to unify data processing for sales data from Trello and Dynamics 365. We used dataflows to ingest, transform, integrate, and enrich sales data by defining data source connections, ETL logic, and refresh schedules.

Power BI dataflows are accessible to all team members, offering a low-to-no-code environment. As a centralized service, dataflows offer uniform ETL logic, eliminating the need for costly infrastructure and server maintenance.

The dashboard uses a tagging system that converts unstructured text into actionable business opportunities. The dashboard’s color coding system helps executives understand what business actions typically lead to successful sales. Its “red zone” feature highlights sales leads that have been left untouched for over six months. Executives can easily get in touch with the appropriate geography point of contact to follow up on these leads.

Business Outcomes:

With a Power BI dataflows architecture, our client reduced costs and saved time. We were able to completely eliminate Azure database, Azure Blog storage, and SharePoint costs generated by the previous system. We also eliminated maintenance costs incurred by Microsoft Flow and RESTful API.

Our solution offers executives greater visibility into sales activity, enabling quick business insights. BD team members no longer needed to create an Excel-based report for executives to view sales leads data, saving time and manual effort.


  Developed a Power BI Dataflows architecture to unify sales data generated across multiple streams 
  Reduced time and manual efforts required to report sales status data
  Eliminated many of the costs associated with multiple reporting systems 
  Improved accessibility of sales data reports by implementing a low-code environment that is easy to use and maintain