Wednesday, August 7, 2019

Power BI Dataflows Reduces Cost and Saves Time



In today’s business environment, executives require total visibility into company activity. In order to provide visibility, team members must either provide reports or personally convey the desired information. Both reports and meetings require significant time.

Business Scenario

Our client, the business development (BD) team of a global technology company, needed to provide the executives with immediate insight into sales leads at the geography level (Asia Pacific, Western Europe, United States, etc.). Prior to the implementation of our solution, the BD team used Trello to track sales leads and Dynamics 365 to convert sales leads into a deal. Throughout the sales process, the BD team manually entered notes about sales status. But because sales status was tracked on multiple platforms, providing visibility into sales status proved difficult. To present a consolidated view of sales status, a BD executive spent hours creating an Excel-based report for the executives.

To provide the executives with immediate insight into sales status, we created a Power BI dashboard. The Power BI dashboard shows current sales leads, ongoing sales discussions, and finalized sales. The dashboard also utilizes a tagging system, which converts unstructured text into actionable business KPIs. The dashboard also displays whether a sales lead is “in the red zone.” If a sales lead is “in the red zone,” no one has pursued the lead in over six months. The dashboard’s “red zone” feature allows the executives to track sales lead progress and follow up with the appropriate geography lead with minimal effort. The tagging system allows the executives to understand what business actions typically lead to successful sales. 

How We Did It: The Technical Implementation

Before we implemented our solution, the BD team used Microsoft Flow and the RESTful API to move sales data from Trello to Azure SQL Database. (For a visual representation of the previous architecture, please refer to Figure 1.) To track sales data from Dynamics 365, BD team exported the data to Excel. The BD team used Azure Blob Storage and SharePoint for data refreshes. Next, they  manually imported the Excel data into Azure SQL Database. Azure SQL Database functioned as a data storage location. Data was then pulled to create Power BI reports.

Figure 1: Old architecture

When we were approached to redesign the architecture, we challenged ourselves to address four drawbacks of the existing architecture:

   Azure Database, Azure Blob Storage, and SharePoint incurred a significant cost.
   Microsoft Flow and the RESTful API incurred significant maintenance costs.
   The existing architecture required security approval and environmental setup from a support team.
   Daily refreshes involved manual effort.

To address the four drawbacks, we redesigned the architecture almost exclusively using Power BI dataflows. (For a visual representation of the new architecture, refer to Figure 2). We used dataflows to ingest, transform, integrate and enrich the sales data by defining data source connections, ETL logic, and refresh schedules. By using Power BI dataflows, our client unified data processing for sales data from Trello and Dynamics 365. In addition, using Power BI dataflows resulted in an almost no-code architecture. Dataflows is a centralized service, resulting in uniform ETL logic across the company and alleviating the need for costly infrastructure and server maintenance. The completed Power BI dataflow architecture addressed the four drawbacks, resulting in the following benefits:

   Eliminated Azure Database, Azure Blob Storage, and SharePoint costs.
   Eliminated Microsoft Flow and RESTful API maintenance costs.
   Eliminated need for support team.
   Eliminated need for manual refreshes.

Figure 2: The current architecture

Business Outcomes:

The completed Power BI dataflow solution resulted in several business and technical benefits. Using the new Power BI dashboard enabled faster surfacing of business insights, greater executive visibility into sales activity, and eliminated the effort needed to create the Excel-based report for the CEO. With the Power BI dataflow architecture, our client dramatically improved cost and time savings.