PwC uses Azure Synapse and Power BI to Help Clients Gain Valuable Insights

PwC is one of the world’s largest accounting firms. The company’s network spans 156 countries with over 250,000 employees who deliver Assurance, Advisory, and Tax services. PwC prides itself on their ingenuity and drive to stay on the forefront of technology. 

Vince Powell, a Partner at PwC, leads technology and analytics for the Deals, Insights, and Analytics team (DIA). The DIA team provides deep Financial, Operational, Forensic and Analytical services to clients across the full lifecycle of a business. The team, based in the UK, is part of a PwC Network of over 3,000 Financial, Forensics, Data and Analytics experts. 

One focus of the team is working with Private Equity clients, who buy other businesses, increase their value, and then sell them for profit gains. This is the business of ‘Deals’. It is a highly regulated, challenging, and sophisticated market. Each merger and acquisition comes with complex and often disparate data, intricate analytics, and industry specific benchmarks and KPIs. The DIA team helps alleviate the otherwise extremely tedious process for their clients. 

For the past 20 years, the DIA team has found themselves repeatedly building similar types of solutions, to help clients easily understand how an acquired company was performing and what is driving changes in business performance. These bespoke solutions were time and labor intensive, in some cases taking months to build, and even longer to merge and embed into the existing ecosystem. In other cases, the inability to rapidly use a broader range of deep analytics meant business opportunities were missed. 

Looking for a way to accelerate and scale the process of gaining more value from financial and operational data at every stage of a Deal, PwC set out to build a universal solution that would provide a top-to-bottom view of all the essential details. This new solution would need to leverage analytics to provide key insights without creating additional work for the client and the DIA team. The solution developed became PwC’s product known as Value Insights: an end-to-end reporting and analytics solution that is customizable to client needs.  

Leveraging Azure Synapse for a Centralized, Time-Saving Solution

When the team began the architecture journey for Value Insights, they started with a simple SQL Database using Azure Data Factory and Power BI to drive the visualizations. As data processing grew to be more complex, Azure Batch and Azure Function Apps were added to the pipeline. The data architecture and volumes continued to expand and manage compliance standards for the integration of multiple tools became increasingly complex. 

To meet these needs, the DIA team sought solutions to simplify the ETL pipeline. They initially investigated expanding on Azure Function Apps, adding Azure Databricks to assist with building out Value Insight’s optimized data analytics platform, but wanted something that would fit their solution needs more holistically.  

Powell and his team developed a new vision for the platform with an intentional and effective architecture. Since the ETL could run multiple times a day, typically within a few hours, it was imperative that the solution be as time and cost-efficient as possible. The team carefully planned the architecture operations before beginning to build it—making sure the end solution would meet all their needs while being strong enough to perform complex tasks. The team performed extensive testing to determine a solution that would reduce cost and time.   

After investigating various tools, the DIA team began looking into using Azure Synapse Analytics. When the DIA team realized that Azure Synapse Analytics could integrate all their components into one place while also improving scalability and reducing cost—a one-stop-shop for all their data-- they decided to move forward with the implementation. 

Azure Synapse Analytics provided a way to re-use a large majority of the original SQL code, while also taking advantage of Spark notebooks to deliver some of the more complex analytics that had previously been in Azure Function Apps. Additionally, the integrated CI/CD (Continuous Integration, Continuous Delivery) allowed for automation of the complex version and release control, some of which is legally required to meet regulatory requirements. With the front-end visualizations becoming exponentially complex, the team continued to find ways to offload heavy front-end processing power into Azure Synapse. 

“Migrating to Azure Synapse Analytics [allowed us] to rewrite the ETL in such a way that the whole process becomes dynamic and allows us to develop even more complex analytics. This enabled a simplified configuration and reduced the cost of running the whole process. It also enabled us to transfer some of the load off the front end making the visualization more responsive” says Powell. 

Bringing a Strategic Data Design to Life

After solidifying the architecture design for Value Insights, the DIA team began to build out the solution. Every client brings different data formats and structures, and PwC accounted for these variabilities when developing their solution. To accommodate the unique formats from each client, Value Insights contains a data processing solution that can take in any file format, identify it, and appropriately understand and validate the contents both structurally and contextually. 

The metadata for each client is stored in control files to map out data and provide visibility into the file types being used.  The metadata also includes a set of descriptors and structures for the data.  The control files and descriptors drive everything in the ETL process. This eliminates the need to continually build bespoke ETL pipelines. 

The ETL pipeline begins with Synapse pipelines, where a file is received from the client via SFTP. Then Spark notebooks use the control files to validate the file type. Once validated, reconciliations, which determine what happens to the file next, are performed. Synapse pipelines then use the validation information and kicks off processes that continue the curation of the input. 

After the data is processed, it’s loaded into a dimensional data warehouse. Within the data warehouse, a multilevel dynamic ‘chart of accounts’ is used to map the incoming data and independently save it into the database in a consistent way. Once stored, all the data is processed according to business rules using a set of Spark routines. This includes the pre-calculation of the more complex visuals delivered on the front end. All the data is then delivered to Value Insights’ unique and highly interactive reports and dashboards. 

Once all the data is pushed into Power BI, all the processing power is shut down to save cost. By resuming and scaling the data warehouse when needed, PwC saves a significant number of resources (as well as reduces the risk of security attacks); the scalability of Azure Synapse Analytics proved to have tremendous value for PwC. This unique functionality is also enabled by an optimized ETL process and a simplified infrastructure where all elements are being utilized. 

Creating Interactive Dashboards with Power BI

When the DIA team was considering reporting tools for Value Insights, they realized that many of their clients were already using Power BI, providing an easy and well understood path for deploying visualizations. The portability of Power BI offers an easy transfer to clients once the solution is up and running, minimizing security risks and concerns throughout the deal and acquisition process. 

With over 30 different ‘out of the box’ reports the scope of available analysis is very broad. One of the reports within Value Insights highlights the performance of an individual business is shown below. At the top of every screen there is a unique variance bar that contains a dynamically determined set of business categories such as product, customer location, business entity, and more. Across those bars, users can see every product, customer, business entity and how they are performing. The variance bars provide a simple way for executives to dynamically filter the page and drill down into the performance of their business without having to go through numerous screens or slicers. Charts within this visual also showcase profit and loss analytics, drill downs into individual transactions, and filtering across various types of data. 

)   

Reports can provide data from various timeframes and well as compare variances from differing time frames. The Dynamic Performance Analyzer module provides data on price volume mix bridges. Price volume mix bridges provide side-by-side comparisons of annual data and allow users to drill down and see how business has changed and/or evolved. All the visuals and analytics are dynamic; they allow users to look at different levels of business, from consolidated performance down to individual transactions and understand why certain numbers are changing and the underlying change drivers. 

Bringing Value Insights to Clients

With the foundation and functionality of Value Insights built out, the team can have a new solution deployed and running with a client within 4-6 weeks. A complete solution includes the foundational architecture and reports, but processes and plugs in client-specific data. The solution set up also includes data preparation, understanding what data a client has and if any of the data quality needs to be improved. PwC also use the solution to provide management with deep insight into their own business. 

The CI/CD methodology enables the technical deployment of Value Insights to be performed very quickly in both PwC and client environments Creating a brand-new, physically isolated environment ensuring that zero data is shared across customers, and everything remains secure can be done within a few days. 

Once prepared, client data can be loaded using data files through a simple SMTP folder. The seamless data preparation speeds up adoption time and makes the integration and transition to Value Insights easy. 

Additionally, the solution also provides flexibility for the clients as they can connect directly into the existing Power BI model to create their own visualizations if desired. An open data model empowers clients with the flexibility of ad hoc reporting and the option to add more advanced features to fit their specific needs. 

Value Insights has successfully leveraged Azure Synapse and Power BI in such a way that the PwC team can save large amounts of time but still provide clients with unique, relevant, and secure data analytics. Now, the PwC team can focus more of their time bringing Value Insights to more clients, and clients can focus on other issues knowing that they have easy access to high-quality analytics and can make seamless data-driven decisions. 

In the future, the PwC team is working towards automating more processes involving front-loaded data. The number of modules continues to grow beyond financials and into procurement, logistics, and ESG. The team is also exploring more simulation-based capabilities and how they can leverage data to create outcomes from alternative scenarios to better aid decision making.