Modernizing Enterprise Reporting for Kent with Azure, Databricks, and Power BI

About the Client 

Kent is one of India’s most recognized names in the manufacturing and distribution sector. With multiple plants, warehouses, and sales networks, the company operates on a scale where data drives nearly every decision. From monitoring inventory in real time to aligning production with sales, accurate reporting is what keeps the entire engine running smoothly.

For years, Kent relied on on-premises SQL Server reporting. Stored procedures generated monthly reports that worked fine when data volumes were smaller. But as the company grew, reports became slow, manual, and resource-heavy.

The real gap, however, was in customer and sales visibility. While Salesforce CRM captured sales pipelines, opportunities, and customer cases, that information lived in its own silo. Operational data lived elsewhere. Sales leaders and operations managers were often working with two different pictures.

Kent’s leadership realized they needed a unified reporting system—one that could combine Salesforce CRM data with local SQL Server data into a single, cloud-based platform. The goal was simple: faster reporting, real-time pipeline visibility, and interactive dashboards that empowered teams across the business.

Challenges 

Kent’s challenges were both technical and organizational:

1. Slow Report Processing
   Reports ran for hours, delaying decision-making. By the time results were available, the data was already outdated.
2. Manual Dependency
   Teams had to trigger stored procedures and manually prepare outputs. This wasted valuable time and created room for errors.
3. Scalability Issues
   Expanding the reporting scope or adding new KPIs was complicated and slow. The old system wasn’t built for scale.
4. Static Outputs
   Reports were flat files—PDFs or Excel sheets—with no ability to drill down or interact with data.
5. Database Strain
   Running reports directly on the transactional SQL Server affected system performance.
6. Siloed CRM Data
   Salesforce held critical customer and pipeline information, but it was not connected to operational reporting. Leaders couldn’t see sales forecasts and production numbers together in a single dashboard.

Objectives 

Kent’s modernization program was built around six clear goals:

• Automated Cloud Reporting – Replace manual reporting with fully automated data pipelines.
• Unified Data Sources – Sync data from both Salesforce CRM and local SQL Server into a single cloud-based system.
• 360° Business View – Combine operational data (inventory, production, sales) with CRM data (pipeline, opportunities, cases).
• Interactive Dashboards – Build Power BI dashboards for self-service analytics, plus a custom reporting dashboard for leadership.
• Performance Optimization – Free the transactional SQL Server from reporting workloads.
• Future-Ready Foundation – Create an architecture ready for AI forecasting and real-time dashboards.

Implementation Strategy 

The solution was designed on Azure + Databricks + Power BI, following the Bronze → Silver → Gold data architecture:

• Bronze Layer – Raw copies of data from Salesforce CRM and SQL Server.
• Silver Layer – Cleaned, standardized, and structured datasets.
• Gold Layer – Business-ready KPIs and curated datasets powering dashboards.

Both Salesforce CRM data and local SQL Server data were continuously synced into Azure Data Lake through Azure Data Factory (ADF). From there, Databricks handled transformations, while Delta Lake optimized storage and querying.

Dashboards were delivered via Power BI for analysts and managers and a custom reporting dashboard for executives, giving two levels of insight from the same unified data foundation.

Azure End-to-End Reporting Architecture

Salesforce and On-Premise SQL Server data flow into Azure Data Factory for ingestion, are processed through Databricks and Delta Lake (Bronze-Silver-Gold layers), and then power both Power BI dashboards and custom reporting dashboards for leadership.

This approach ensured scalability, governance, and clear separation between raw ingestion, transformation, and business-ready data.

Key Features & Modules 

1. Data Ingestion & Storage

• Used ADF with Self-Hosted Integration Runtime to securely extract SQL Server data without exposing it to the internet.
• Connected to Salesforce CRM with the ADF Salesforce connector, enabling incremental updates.
• Built parameterized pipelines for 15+ SQL Server tables and Salesforce objects.
• Stored raw data in Azure Data Lake Storage Gen2 with fine-grained access control.

2. Unified CRM + Local DB Sync

• Configured incremental sync from Salesforce (Leads, Opportunities, Accounts, Cases) and SQL Server (inventory, sales, production).
• Ensured updates were captured in near real time, keeping dashboards always fresh.
• Merged Salesforce and SQL data in Databricks to generate cross-functional KPIs such as:
  – Pipeline vs Production Capacity
  – Lead Conversion Trends
  – Customer Cases vs Sales Impact
  – Regional Sales vs Inventory Levels

3. Data Processing – Bronze, Silver, Gold Layers

• Bronze: Raw Salesforce and SQL data stored in Parquet.
• Silver: Cleaned, standardized data with schema alignment.
• Gold: Business logic applied in Databricks SQL + PySpark, rebuilding legacy stored procedures and deriving new KPIs.

4. Performance Optimization

• Converted datasets into Delta tables with partitioning for faster queries.
• Applied Z-ORDER indexing to optimize lookups by transaction date and item code.
• Used serverless SQL endpoints for ad-hoc queries and job clusters for scheduled loads.
• Automated orchestration with ADF triggers.

5. Reporting & Analytics

Power BI Dashboards:

• Gave business teams self-service access.
• Enabled drill-downs by warehouse, region, or sales rep.
• Built on a star schema for consistency and speed.

Custom Reporting Dashboard:

• Designed specifically for executives.
• Showed consolidated KPIs at a glance.
• Focused on clarity and decision-ready summaries rather than deep analysis.

Technology Stack 

Execution Process 

• Timeline: 2.5 months
• Team: Dedicated Cloud Data Engineering Squad

Phases:

1. Discovery – Assessed SQL Server schema, Salesforce objects, and reporting needs.
2. Ingestion Setup – Built secure pipelines for SQL Server + Salesforce CRM.
3. Transformation – Implemented Bronze-Silver-Gold model in Databricks.
4. KPI Development – Rebuilt legacy logic and created new unified KPIs.
5. Dashboard Development – Delivered Power BI dashboards and a custom reporting dashboard.
6. Testing & Rollout – Conducted UAT, optimized queries, deployed monitoring.

Results & Impact

The new system delivered immediate, measurable value:

• 90% Faster Reports – From hours to minutes.
• Zero Manual Work – Fully automated pipelines for SQL + Salesforce data.
• Unified View – Combined ERP and CRM data for the first time.
• Real-Time CRM Insights – Salesforce data synced into dashboards within minutes.
• Tiered Dashboards – Power BI for deep analysis, custom dashboard for leadership summaries.
• Better System Performance – SQL Server freed from reporting workloads.

Future Scope 

Kent’s roadmap includes:

• Streaming Salesforce Events – Using Event Hub for real-time dashboards.
• AI Forecasting – Predicting demand, pipeline conversions, and anomalies with Databricks ML.
• Expanded Dashboards – Covering HR, finance, and customer service.
• 360° Customer Insights – Adding Salesforce Marketing and Service Cloud for a full customer lifecycle view.

Key Takeaways 

• Kent’s legacy SQL-based reporting slowed down decisions and siloed Salesforce data.
• Syncing Salesforce CRM and SQL Server data into Azure created a single source of truth.
• The layered Bronze-Silver-Gold architecture ensured scalability and governance.
• A dual-dashboard strategy (Power BI + custom leadership dashboard) gave the right level of detail to the right users.
• Kent is now positioned for real-time analytics and AI-driven decision-making.