Data Analytics System Enabling Cross Analysis of 30,000 Attributes and 100x Faster Reporting

Industry : Marketing & Advertisement, Media

Technologies : Hadoop, Python, Scala, Spark, AWS, Big data, Cloud, Azure, .NET, C#, WPF, XAML, MVVM

Customer : The Customer is a leading market research company.

Challenge : Though having a robust analytical system, the Customer believed that it would not be able to satisfy the company’s future needs. Acknowledging this situation, the Customer was keeping their eyes open for a future-focused innovative solution. A system-to-be was to cope with the continuously growing amount of data, to analyse big data faster and enable comprehensive advertising channel analysis.

Solution : During the project, the Customer’s business intelligence architects were cooperating closely with Delta cubes Team designed an idea, and the latter was responsible for its implementation.

For the new analytical system, the Customer’s architects selected the following frameworks:

• Apache Hadoop – for data storage
• Apache Hive – for data aggregation, query and analysis
• Apache spark – for data processing

Amazon Web Services and Microsoft Azure were selected as cloud computing platforms.

Upon the Customer’s request, during the migration, the old system and the new one were operating in parallel.

The system has been supplied with raw data taken from multiple sources, such as TV views, mobile devices browsing history, website visits data and surveys. To enable the system to process more than 1,000 different types of raw data (archives, XLS, TXT, etc.), data preparation included the following stages coded in Python:

• Data transformation
• Data parsing
• Data merging
• Data loading into the system

IMPACT : At the project closing stage, the new system was able to process several queries up to 100 times faster than the outdated solution. With the valuable insights that the analysis of almost 30,000 attributes brought, the Customer was able to carry out comprehensive advertising channel analysis for different markets.

Technologies and Tools : Apache Hadoop, Apache Hive, Apache Spark, Python( ETL) Scala (Spark, ETL), SQL (ETL), Amazon Web Services (Cloud storage), Microsoft Azure (Cloud storage), .NET, WPF, C#.

Project Description

Industry : Financial Services

Customer : A financial regulatory authority responsible for overseeing capital markets, issuing licenses to market intermediaries, and monitoring their activities.

Project : Development and implementation of a Financial Analytics Solution to consolidate disjoint data sources and enable comprehensive, in-depth financial analysis.

Challenges They Faced

The Customer faced several key challenges:

1. Data Fragmentation: The organization relied on multiple disjoint data sources, including a risk-based supervision system, surveillance system, Excel databases, and a web portal for receiving documents from registered entities. This fragmentation made it difficult to perform in-depth analysis, affecting their ability to conduct effective market oversight and develop regulatory policies.
2. Inadequate Reporting and Analytics: The existing systems lacked the ability to generate comprehensive reports and analytics. The Customer needed a solution to support multi-source data analysis, enable informed decision-making, and facilitate proactive capital market regulation.
3. Data Security Concerns: With sensitive financial data being processed, the Customer required robust security measures to ensure that data access was controlled and that the solution complied with regulatory standards without compromising performance.

Solutions Provided

To address these challenges, the following solutions were implemented:

1. Centralized Data Integration:
   • ETL Processes: A staging layer was created using Extract, Transform, Load (ETL) processes to consolidate data from the various disjoint sources. This included data retrieval, cleansing, validation, and transformation, ensuring that data was consistent and ready for analysis.
2. Data Warehousing and Analytics:
   • Data Warehouse (DWH): A centralized data warehouse was implemented to store the cleaned and transformed data, making it the primary source for all analytics and reporting activities.
   • OLAP Cubes: Online Analytical Processing (OLAP) cubes were established to enable quick access to summarized data, supporting complex financial analysis, including market trends, compliance checks, and fraud detection.
   • Custom Reports and Dashboards: Tailored reports and dashboards were created to meet the needs of different user groups within the organization. Executives received high-level KPI-based dashboards, while operational teams had access to detailed reports for day-to-day activities.
3. Enhanced Security Measures:
   • Role-Based Access Control: An elaborate user access control system was set up with a permission matrix based on row and column-level security. This ensured that only authorized personnel could access specific data, maintaining data integrity and security without impacting system performance.

Tech Stacks Used

• Data Integration: Microsoft SQL Server Integration Services (SSIS), SQL Server Stored Procedures, SQL Server Agent
• Data Warehouse: Microsoft SQL Server Enterprise Edition
• Analytics: Microsoft SQL Server Analysis Services (SSAS), SQL Server Machine Learning Services, Power BI, Python, R

IMPACT : The implementation of the Financial Analytics Solution enabled the Customer to handle over 500 reports concurrently with more than 200 active users. The solution significantly optimized business operations, facilitated faster decision-making, and strengthened regulatory oversight.

Project Description

Industry : Healthcare, Software Products

Customer : An American software company that provides solutions to 150 healthcare centers and retirement homes. The software helps manage patient data, medication inventory, and various types of reports.

Project : The Customer engaged Deltacubes Team to rewrite their Java-based application used by healthcare centers for managing and reporting data. The goal was to enhance the tool’s capabilities, particularly in terms of scalability and enabling quality population health analytics with prompt reports.

Challenges They Faced

The Customer encountered several significant challenges:

1. Legacy System Limitations: The existing Java application struggled with scalability and delivering prompt query responses. It could not efficiently handle large volumes of data related to medication inventory, clinical services, patient information, and other critical functions.
2. Data Fragmentation: The required data for reporting was spread across 150 separate databases, totalling 15GB. Consolidating this data into a cohesive system for effective management and reporting posed a considerable technical challenge.
3. Need for Advanced Analytics: The Customer needed to enhance the application to support advanced healthcare analytics. This included creating detailed reports on medication classification, hospital readmissions, and employee performance, all of which required a more robust backend infrastructure.

Solutions Provided

To address these challenges, Deltacues team delivered a tailored solution:

1. Data Warehousing and Integration:
   • Microsoft SQL Server Data Warehouse: The team built a scalable analytical data warehouse using Microsoft SQL Server. This data warehouse was designed to ensure efficient data processing and short query response times, critical for handling the consolidated data from the 150 healthcare centers.
   • Data Loading with Transact-SQL: A Transact-SQL script was developed to load data from the relational database into the SQL Server data warehouse. This script facilitated the integration and consolidation of data from the 150 individual databases.
2. Enhanced Reporting:
   • JReport Integration: Deltacubes integrated the JReport tool into the system, enabling the creation of various reports, including Medication Classification, Missed Medications, Hospital Readmissions, Employee Performance, and Community Marketing Activity Summary. These reports allowed healthcare centers to gain insights and improve management processes.
3. Future-Ready Architecture:
   • OLAP Cubes Development: To further enhance the analytical capabilities, the team planned the development of OLAP cubes. These cubes will enable the storage of aggregated data, supporting advanced healthcare data analytics and improving information processing.

Tech Stacks Used

• Data Warehousing: Microsoft SQL Server
• Data Integration: Transact-SQL
• Reporting: JReport
• Application Development: Java

IMPACT : The revamped software has empowered the Customer’s clients to improve management processes through prompt and accurate analytics reports. Additionally, the planned OLAP cubes will further enhance the solution’s capability to perform advanced healthcare data analytics, driving better decision-making across the 150 healthcare centres.  

Project Description

Industry : Information Technology

Customer : A European SaaS provider operating a network of 150 servers, delivering comprehensive IT systems and solutions to enterprises.

Project : The Customer required cloud infrastructure maintenance and support, focusing on the consolidation, modernization, and ongoing management of their diverse IT environment, along with DevOps consulting to enhance performance and scalability.

Challenges They Faced

The Customer encountered several challenges that necessitated expert intervention:

1. Complex Infrastructure Management: The Customer’s cloud environment consisted of 200 servers across Windows and Linux platforms, using various virtualization technologies like XenServer, KVM, and VMware vSphere. This fragmented infrastructure was difficult to manage, leading to inefficiencies and increased maintenance overhead.
2. Inadequate Service Configuration: Several services within the environment were not configured properly, causing performance issues and potential security vulnerabilities. The Customer needed these issues investigated and resolved with detailed documentation.
3. Ongoing Maintenance Needs: The Customer required continuous 8*5 DevOps support to maintain system uptime, optimize security, and manage hardware consistency without the need to hire a dedicated in-house team.

Solutions Provided

Deltacubes delivered a comprehensive solution to address these challenges:

1. Infrastructure Consolidation:
   • VMware Consolidation: To simplify management, Deltacubes consolidated the multiple hypervisors into a single, cost-effective infrastructure using VMware. This allowed the Customer to manage their entire data center from a single control point, significantly streamlining maintenance tasks.
2. Service Configuration and Optimization:
   • Issue Resolution: Deltacubes Team thoroughly investigated inadequately configured services, provided tailored solutions, and documented all changes in detail to prevent future issues.
   • Regular System Modernization: The DevOps team regularly revised and optimized internal services, including database systems, antivirus protection, monitoring systems, and file-server systems. They also consolidated database systems into clusters to improve performance and resilience.
3. Consulting for Scalability and Performance:
   • Ongoing DevOps Consulting: Deltacubes provided ongoing consultations on infrastructure upgrades and modifications, ensuring that the Customer could scale efficiently and maintain high performance.

Tech Stacks Used

• Operating Systems: Windows Server 2008 R2,
• Virtualization: VMware vSphere 5.5, Citrix XenApp
• Database Management: MS SQL Server 2008 R2, 2012
• Network Management: Nagios
• Scripting: PowerShell, Bash
• Scripting Environment: SAPIEN PowerShell Studio
• Data Protection: Symantec Backup Exec
• Other Tools: Citrix NetScaler, Citrix StoreFront, Citrix ShareFile, HP Storage, HP BladeSystem, MS Office, MS Visio, MS Failover Cluster, MS SQL Server AlwaysOn, vSphere HA & DRS

IMPACT : The Customer now benefits from a cost-effective and efficient DevOps service, allowing them to avoid the need for a dedicated in-house administration team. Deltacubes support and consulting have enabled the Customer to introduce more efficient and high-quality services, improving overall system performance and scalability.

Case Study : Training Portal Modernization for Enhanced User Experience, Performance, and Scalability

Project Description : The customer, a US-based consulting firm, specializes in offering solutions and industry-leading training services to managed service providers (MSPs). Their existing web application, designed for delivering MSP training services, faced significant challenges, including slow and unstable performance, and an outdated, user-unfriendly interface. To address these issues, the customer sought to modernize their application to enhance user experience (UX), improve performance, and scale the application to meet the needs of a growing customer base. Deltacubes was chosen as the trusted partner to execute this modernization project.

Challenges Faced :

1. Performance Issues: The existing application suffered from slow response times and frequent instability, negatively impacting user satisfaction and engagement.
2. Outdated User Interface: The application had an outdated and non-intuitive user interface that hindered the user experience, making navigation and interaction cumbersome.
3. Scalability Concerns: As the customer base grew, the application struggled to accommodate the increasing data load, resulting in performance degradation.
4. Seamless Data Migration: The customer needed to preserve all existing information and ensure that the transition to the new system did not disrupt ongoing operations.

Solutions Provided :

1. Application Stabilization and Performance Enhancement:
   • The Deltacubes team began by stabilizing the existing application, ensuring smooth and uninterrupted operation.
   • Performance enhancements were implemented, including optimization of backend processes and introduction of cloud capabilities using Microsoft Azure.
2. UX/UI Design Overhaul:
   • Deltacubes redesigned the user interface, focusing on creating a more intuitive and visually appealing user experience.
   • The application screens were updated to align with modern UX best practices, improving navigation and user engagement.
   • AJAX technology was integrated, enabling real-time data retrieval without the need for page reloads, thus enhancing the user experience.
3. Cloud Integration and Scalability:
   • The application was migrated to the Microsoft Azure cloud platform, leveraging Azure Services for enhanced scalability and reliability.
   • Azure Service Fabric, .Net Core, Redis, and SQL Azure were utilized to build a robust infrastructure capable of handling a growing user base and large data volumes.
4. Automated Testing and Quality Assurance:
   • Automated testing frameworks were employed to ensure the application’s quality and reliability.
   • Comprehensive testing, including API integration and load testing, was conducted to identify and resolve potential issues before deployment.

Technology Stack Used :

• Frontend: Angular 4, RxJS, HTML, CSS, TypeScript, AJAX
• Backend: .NET, .NET Core
• Cloud: Microsoft Azure, Azure Service Fabric, Redis, SQL Azure
• Design: Google Material Design, DevExtreme components
• Testing: Selenium

IMPACT : The Deltacubes team successfully delivered a modernized web application that met all of the customer’s requirements. The new application featured improved performance, intuitive navigation, and a visually appealing UX design, leading to increased user satisfaction. The cloud integration with Azure enabled the application to scale efficiently, accommodating the growing customer base without compromising performance.