Implementing a Modern Analytics Solution with Azure Synapse at IDRC

Background and Challenge Story

The client wanted to build a modern analytics platform which would deliver data as a service to the organization and support both ad hoc and structured analytics. This would allow them to measure new KPI’s more efficiently, automate and standardize transformations and rules, and realize new advanced analytic capabilities. They were also looking to free analysts from the manual preparation of data, instead enabling them to focus on realizing better insights from the organization’s data.

They wanted to continue using legacy reporting tools, while at the same time, enabling the use of Power BI. The solution would also have to be integrated with enterprise data governance requirements to ensure that their data and analytics insights were trusted and accurate.

Zelusit’s Approach

Since IDRC’s objective was to effectively serve modern analytics, they would need both Data Lake and Spark engines for advanced analytics, and SQL/data warehouse engines for structured analytics. Zelusit recommended Azure Synapse Analytics as the analytics foundation at IDRC, as it is the only cloud service that can seamlessly integrate both engines to deliver a unified modern analytics environment, and cover all their current and future analytic requirements, including AI and Machine Learning.

Azure Synapse Analytics is a single integrated platform delivering data as a service on any data. It provides raw data history to enable root cause analysis on any metric. Azure also offers a pay-per-use model that can lower the client’s total analytics-related costs.

In addition to ensuring agile and efficient responses to new analytic requests, Zelusit developed full end-to-end automation, allowing the client to focus on data insights, rather than data preparation. IDRC was provided with a dynamic online reporting environment that is accessible to any device, from any location. Keeping the client’s requirements in mind, Zelusit implemented Data Zones for analytics in Azure. This approach allows IT professionals and data scientists to access data from any zone (based on use case) and empowers Business Analysts to access data from the provisioned zone, using no-code Power BI.

Finally, data governance and security were key for the client and these remained an integral part of our solution.

Solution Story

The project began with a discovery phase, where in addition to understanding data sources and collecting data, we also defined the use case for the pilot.

We defined a service architecture for modern analytics based on Synapse Analytics and Power BI for IRDC. Zelusit’s experts developed a network architecture for source data integration and Azure services. The solution provided seamless integration of all analytic services and data types, and fully automated the data ingestion and analytics process.

The Azure service environments were implemented in 3 phases – development, testing, and production. We hardened the Azure service environments with Role-based Access Control (RBAC) and data security to build in elements of governance.

Zelusit implemented a pilot solution in the development environment, where we built 5 KPI reports. This provided the client a data integration pattern that they could replicate or extend to include additional tables and new data sources.

Once the pilot was completed, Zelusit documented the process and facilitated a knowledge transfer to IDRC’s team for the architecture, design, and solution. The entire project, including the pilot and knowledge transfer, was completed in 5 weeks.

Data Integration and Reporting Pilot

Zelusit implemented a pilot solution, where we created Power BI visualizations on 5 KPI’s: External donor funding for the last 5 years, Cumulative Quarterly Revenue by source, Active projects and their funding status, Regional distribution of recipient projects by value of commitment, and Corporate & Administrative Expenses as a ratio of total expenses over the past year.

The data was sourced from cloud-based Dynamics 365 and Dynamics Business Central (CRM and ERP systems). The data was ingested into Azure Data Lake Storage (landing zone) and then into Azure Synapse Analytics, where it was standardized and integrated before being fed into Power BI (access zone) for visualizations. We integrated and modeled the data into a BI model and built BI reports with summary to detail views for the 5 shortlisted KPI’s.

The results were validated with the client’s stakeholders and refinements were made, where needed. The team then implemented end-to-end automation of the solution, to ensure that the results remained steady over time.

Impact

By implementing a modern analytics toolset based on Synapse and Power BI, Zelusit set IDRC up to meet both their current and future analytics and reporting requirements. The solution will ensure seamless integration of all analytic services and data types through Synapse and provide an online, modern reporting environment through Power BI.

The Data Zone approach will ensure a single version of truth for reusable data as a service with low effort landing. Synapse Pipelines will provide complete end-to-end automation of data movement and transformation. The Massively Parallel Processing (MPP) Synapse SQL lies at the centre of the Synapse architecture, and is capable of processing massive data volumes, delivering industry leading analytics performance. Synapse makes it easier to explore and discover data and supports a variety of data ingestion use cases, including event, file, batch, and stream.

Our solution fully leveraged Azure’s unmatchable security posture for data protection, access control, authentication, network security, and threat detection, safeguarding IDRC’s data. Also, by delivering analytics through Azure PaaS pay for use services, the client should be able to lower total cost of ownership for analytics significantly.

With the pilot and the extensive knowledge transfer that Zelusit delivered, IDRC is now positioned to leverage the advanced analytics capabilities of Synapse and build future BI models and reports for additional KPI’s as needed in the future.  In addition, the easily extensible data ingestion and transformation model allows the client to add additional tables or even new data sources to their Azure solution.

Looking to modernize your analytics solution? Schedule a meeting with our experts today!