SITUATION & BUSINESS CHALLENGE
An American conglomerate with businesses in the agriculture, commodities, energy and financial services industries had embarked on a digital transformation initiative across business units, but encountered difficulty in trying to implement a data and analytics (D&A) solution for the finance office in order to run deeper analyses to uncover micro and macro trends.
The group operated in a challenging and complex financial model with 15 years of global data. The group’s reporting capability involved mostly self-service efforts pulling data from SAP HANA, manipulating it in Microsoft Excel, and displaying reports in Power BI for a diverse array of end users. However, some reports took as long as a month to produce.
In addition, corporate IT had an FTE-to-consultant imbalance and relied too heavily on the outside opinions and influences of vendors. An abundance of disconnected IT process streams was the result; for example, one vendor was entirely an SAP shop and did not understand the Microsoft technology stack, so powerful technologies were often not integrated, underutilized, or misaligned.
A systems integrator (SI) was hired to make sense of an array of existing financial processes and 30 different financial tools. However, problems began as data was fed into Power BI, as only three months of the company’s 15 years of data could be processed at one time, resulting in data bottlenecks and system crashes. While Power BI is a robust data visualization and reporting tool, it’s not a data integration tool or a data platform and could not do the heavy lifting the SI planned for it to do.
What the company needed was a technology agnostic consulting company with comprehensive understanding of data and analytics to assess the gaps and unite the disparate pieces into a solid platform that could deliver data efficiently and timely to end users. Based on a previous relationship, the company’s CIO invited AIM Consulting’s Data and Analytics practice to bring the pieces together and create an efficient D&A platform.
SOLUTION
AIM Consulting began by assessing the D&A environment to determine what was working and what was not, including the finance group’s data and reporting requirements and the workbooks from the Power BI–based solution. The architects quickly determined that Power BI did not have the functionality to handle the workloads and data volumes, and began to draw out the steps to unwind the work that had gone into the Power BI project and construct a strong data platform from existing tools to support the group’s D&A requirements.
Next, AIM delivered a full product team from its D&A practice, including an Architect and a Power BI Developer, Hadoop Developer / Data Engineer, and a Microsoft BI Developer. The team worked in conjunction with the company’s IT group to craft the new D&A platform in an agile fashion with two-week sprints.
The new platform preserves significant technology investments, eliminates data bottlenecks, and dramatically increases reporting efficiency. By creating new data pipelines and rerouting the data from SAP HANA through a data lake with Hadoop and a SQL-based analytics platform, the data is now fed into Power BI in a manner that the visualizations and reporting tool can process.
The solution, initially delivered to 50 corporate financial analysts, consists of the following capabilities bridged together:
Foundational and scalable data architecture leveraging AWS and Azure cloud technologies: The solution uniquely incorporates both AWS and Azure to help protect the company’s massive investments in both cloud platforms. It runs a virtual machine on SQL Server 2016 that currently hosts Analysis Services on AWS, and Power BI services on Azure along with Power BI Desktop to deploy reports to users.
Big Data pipelines leveraging SAP HANA and Hadoop on Cloudera as the main data lake/data repository: The ocean of financial data is sourced from SAP HANA and continues to reside on a Cloudera distribution of Hadoop hosted on AWS. The data is now more effectively utilized to support financial consolidations and finance activities.
Development of PowerShell scripts leveraged with the Power BI REST API to automate major pieces of Power BI functionality: These components are designed to automate the pieces of manual work. PowerShell scripts are linked to monthly, quarterly and yearly close activities for handling major financial events and the REST API is the mechanism that enables the copying of reports to a published demilitarized zone.
Development of an Azure/Power BI analytics engine leveraging a highly partitioned SQL Server Analysis Services: Work is underway to leverage Azure for hosting SQL Server Analysis Services— providing more efficiency for this component of the solution as well as moving the data closer to the Power BI service for quicker data retrieval.
Partnership with Microsoft to ensure desired functionality was aligned within the product roadmap: A Microsoft solution architect validated AIM’s recommendation of efficiency gains with the move to Azure as a host for SQL Server Analysis Services. AIM also partnered with the Power BI product development team in Redmond to configure some fringe use cases and ensure effective usage by a wide range of end users.
The solution, designed to accommodate multiple personas, was released to 250 users, including executive-level leadership, geographical leads, and other top senior executives. Users will operate the solution from a set of dashboards and reports, which gather and manipulate data from the huge data lake and presents it to the user, all in a self-service BI fashion. After further fine-tuning, a release to an additional 250 users and new personas is scheduled six months in the future.
RESULTS
AIM crafted a solution that enables the client’s key financial analysts and executives with the most powerful self-service reporting tool they’ve ever encountered.
Benefits from the new system are numerous:
- Efficiency savings and much faster report delivery
- Elimination of legacy Excel reports
- Consistent platform leveraging the entire 15-year data store
- Richer visualizations within Power BI
- A single source of truth for all financial reports
Because AIM provided a high level of D&A competency, the client expanded and extended AIM’s contract to further enrich the solution and scale it to additional personas.
