Enterprise reporting solution for large-scale sales data analytics

The Need

JTI Bangladesh (Japan Tobacco International), a major multinational corporation, faced significant challenges managing and analyzing their large-scale sales data archive exceeding 400+ GB. Their existing reporting infrastructure struggled to generate timely insights from this massive dataset, creating bottlenecks for business decision-making and strategic planning.

Executives and analysts needed fast access to sales reports, but the sheer volume of historical data made query performance prohibitively slow, often taking hours to generate critical reports.

JTI required a custom reporting solution that could handle their enterprise-scale data while delivering the speed and security necessary for a global organization.

The challenge was to architect a system that could process hundreds of gigabytes of sales data efficiently, generate complex Excel reports quickly, provide secure access control aligned with organizational hierarchies, and integrate seamlessly with their existing Azure Active Directory-based single sign-on infrastructure—all while dramatically improving report generation speed and reducing the time analysts spent waiting for data.

The Solution

We developed a custom enterprise reporting solution specifically designed to handle JTI Bangladesh's large-scale sales data challenges, transforming their analytics capabilities through architectural innovation and performance optimization.

The Split Architecture Logic represents a fundamental reimagining of how enterprise data should be organized for optimal query performance. Rather than maintaining a single monolithic database containing 400+ GB of historical sales data, we segmented the archive into yearly databases.

This temporal partitioning dramatically reduces query latency on massive datasets because reports typically focus on specific time periods—monthly, quarterly, or annual analyses—and the system only needs to query the relevant yearly database rather than scanning the entire historical archive.

This architectural decision alone delivered transformative performance improvements, enabling queries that previously took hours to complete in minutes or seconds.

The Optimized Excel Output capability leverages OpenXML SDK combined with parallel processing techniques to generate large, complex Excel reports with minimal memory usage. Traditional Excel generation approaches load entire datasets into memory, causing performance degradation and potential system crashes when dealing with reports containing hundreds of thousands of rows.

Our implementation uses streaming techniques that write data directly to Excel files in chunks, combined with multi-threading via TPL (Task Parallel Library) to process different report sections simultaneously. This approach enables JTI to generate comprehensive sales reports with massive row counts quickly and reliably, without overwhelming system resources.

We implemented Smart Role Control that enables fine-grained report access using Azure Active Directory-based single sign-on (SSO) and dynamic role logic. Different stakeholders across JTI's organization—from regional sales managers to C-level executives—require access to different subsets of data and report types. The role-based access system integrates seamlessly with their existing Azure AD infrastructure, eliminating the need for separate credential management while providing granular control over who can access which reports, regions, products, and time periods.

Dynamic role logic ensures permissions automatically adjust as employees change positions or responsibilities, maintaining security without administrative overhead.The Faster Report Retrieval system employs pre-processed summaries and asynchronous notifications to ensure instant stakeholder access to critical reports.

Rather than generating reports on-demand (which could still take minutes even with optimization), the system pre-processes common report types during off-peak hours, storing summarized data that enables near-instant retrieval.

When custom reports are requested, asynchronous processing generates them in the background and notifies stakeholders via email or system alerts when reports are ready for download, eliminating the frustration of waiting at a loading screen.

The measurable impact has been dramatic: over 50% improvement in report generation speed and over 50% reduction in report generation time, transforming JTI Bangladesh's analytics capabilities and enabling faster, more informed business decisions.

The Challenge

The primary challenge was architecting a solution that could deliver acceptable query performance on a 400+ GB sales data archive without requiring complete data infrastructure replacement or astronomical cloud computing costs.

Traditional database optimization approaches—indexing, query tuning, hardware upgrades—offer incremental improvements but couldn't overcome the fundamental performance limits imposed by querying such massive datasets. The split architecture approach required careful analysis of JTI's reporting patterns to ensure temporal segmentation would deliver benefits without creating new complications for cross-year analyses.

Implementing optimized Excel generation using OpenXML SDK and parallel processing required deep technical expertise in both the OpenXML specification and multi-threaded programming. The solution needed to generate reports with complex formatting, multiple worksheets, charts, and calculated fields while maintaining low memory footprint and high throughput.

Ensuring thread safety while processing different report sections in parallel, managing memory efficiently to avoid garbage collection pressure, and handling edge cases like extremely wide tables or special characters required meticulous development and testing.

Integrating with Azure Active Directory for SSO while implementing dynamic role-based access control required understanding both Azure AD protocols and JTI's organizational structure.

The role logic needed to be flexible enough to accommodate complex permission scenarios—like regional managers who should see data for their region across all time periods but not other regions—while remaining performant and maintainable as the organization evolved.

Building a system that could handle data migration from JTI's existing reporting infrastructure, including SSIS (SQL Server Integration Services) for ETL processes that move data into the new yearly-segmented architecture, required careful planning to ensure business continuity during transition. Historical data needed to be reorganized without loss or corruption, while ongoing data ingestion continued uninterrupted.

Creating a secure web interface that met enterprise security standards while providing intuitive user experience for non-technical stakeholders required balancing security requirements with usability, ensuring the sophisticated technical architecture remained invisible to end users who simply wanted fast access to accurate sales reports.

The Partnership

Our collaboration with JTI Bangladesh was built on understanding both their technical challenges and business imperatives.

We worked closely with their IT team, data analysts, and business stakeholders to understand reporting requirements, data structures, usage patterns, and organizational workflows that would inform architectural decisions.

The partnership involved comprehensive analysis of their 400+ GB sales data archive to identify optimization opportunities, understand query patterns that would benefit from temporal segmentation, and design the split architecture approach.

We collaborated with their data team to implement SSIS-based data migration, ensuring historical data was accurately reorganized into yearly databases while maintaining referential integrity and business logic.

We designed and developed the complete technical solution using ASP.NET and MS SQL Server, implementing the OpenXML SDK-based Excel generation with multi-threading via TPL, integrating Azure Active Directory for SSO, and building the secure web interface.

Our team also provided comprehensive testing, deployment support, and knowledge transfer to ensure JTI's staff could maintain and extend the system.

The measurable success—achieving 50%+ improvements in both report generation speed and time reduction—demonstrates how this collaborative approach transformed JTI Bangladesh's analytics capabilities, enabling faster business decisions based on comprehensive sales data insights while reducing the frustration and lost productivity associated with slow reporting systems.

The Tech Stack

We built the solution using ASP.NET and MS SQL Server with OpenXML SDK for high-performance Excel report generation.

ASP.NET provides the robust, enterprise-grade framework for the web application, handling authentication via Azure AD integration, role-based access control, asynchronous report processing, and the secure web interface.

MS SQL Server delivers the reliable, scalable database platform for JTI's 400+ GB sales data archive, with the split architecture segmenting data into yearly databases that dramatically reduce query latency.

OpenXML SDK enables efficient Excel generation by directly manipulating the XML structure of Excel files, combined with parallel processing via TPL (Task Parallel Library) to leverage multi-core processors for generating large reports with minimal memory usage.

The platform includes SSIS (SQL Server Integration Services) for data migration from legacy systems and ongoing ETL processes, multi-threading capabilities that enable simultaneous processing of different report sections, and a secure web interface with Azure AD integration for SSO and dynamic role-based access control—all working together to provide the split architecture logic, optimized Excel output, smart role control, and faster report retrieval that transformed JTI Bangladesh's enterprise reporting capabilities, achieving over 50% improvements in both report generation speed and time reduction.