Case Study: Transforming Excel-Based Solutions into an Azure-Based Data Ecosystem

Optimum converted a complex Excel-based solution to a modern and robust, Azure-based SaaS and Data Ecosystem.

Business Challenge and Project Background

Prior to Optimum’s involvement, Client, a leading provider of operations software for Industrial markets, offered a suite of proprietary Excel-based tools, forms, and templates to their customers. This suite of Excel-based applications lacked the security, scalability, and automation capabilities required by the Client’s customers and offered limited functionality ad visibility, with only the standard charts and capabilities available within the Excel platform.

The Client sought to offer their customers a modern, customizable, and Cloud-based SaaS platform running on a more robust and secure infrastructure, and integrated data sets, and providing a customizable and modern user experience.

The Client also needed as solution that provided the ability for growth and expansion, allowing the opportunity for additional capabilities based on their customers’ end-user feedback. The Client sought to leverage the solution to increase marketability in their product offerings.

Optimum’s Involvement

Optimum began the engagement by conducting a series of in-depth requirements definition and process mapping workshops, to understand the current product, and defining and documenting the business and functional requirements of the new application as well as the high-level user stories for the new, Cloud-based product offering.

Optimum then began a thorough Design phase, defining and architecting the backend design and data model of the new application, including the database architecture and schema design, data modeling and mapping from Excel to Azure SQL tables, ETL design, and application security layers and technologies. We also designed and developed User Interface and User Experience (UX/UI) mockups and visualizations for the key screens and functionality of the new application.

Optimum used Microsoft Azure, Azure SQL, .Net Core, Angular, and JavaScript to develop the application in a series of sprints, managing and maintaining user stories in Azure DevOps, while meeting regularly with the Client, keeping them up to date on the project status and ensuring clear communication, vision, and insight throughout the project lifecycle.

Optimum utilized the right combination of Microsoft technologies and products to develop this solution. We implemented Azure DevOps including Azure Boards to manage and track developer assignments and workflows, Azure Test Plans for managing test cases and bugs and Azure Repos for managing code and the code repository. We created a security architecture that allows for user authentication and role-based authorization, something the Client had not previously been able to do within Excel. We also utilized MS SQL Server including ETL processes to drive daily and weekly data loads and implemented Angular and DevExtreme libraries for the front-end user interface.

A summary of our approach for data management and BI included:

Data Modeling:
- Requirements Gathering: We collaborated closely with business stakeholders to understand their data needs, reporting requirements, and pain points.
- Conceptual and Logical Modeling: We designed a robust data model that captured essential business entities, relationships, and hierarchies.
- Normalization and Denormalization: Balancing efficiency and ease of querying, we optimized the schema for performance.
- Dimensional Modeling: We adopted star schema and snowflake schema principles to facilitate BI reporting.
Data Warehousing:
- Azure SQL Data Warehouse: We migrated data from Excel spreadsheets to Azure SQL Data Warehouse, ensuring scalability and high availability.
- ETL Processes: We implemented Extract, Transform, Load (ETL) pipelines to integrate data from various sources into the warehouse.
- Data Quality Assurance: Rigorous data validation and cleansing ensured accuracy and consistency.
- Historical Data Storage: We preserved historical data for trend analysis and compliance.
Business Intelligence (BI):
- BI Integration: We empowered business users with self-service BI capabilities using Power BI.
- Interactive Dashboards: Customized dashboards allowed users to explore data, visualize trends, and create ad hoc reports.
- OLAP Cubes: We built OLAP cubes for multidimensional analysis, enabling deeper insights.
- Model Management: Our Azure-based model management warehouse facilitated model selection and implementation.

Project Outcomes

The new, Cloud-based application meets the Client’s requirements and objectives, including security, scalability and maintainability, flexibility of the user interface and user interactions, automated workflows, robust Cloud hosting features, and advanced reporting and KPI visualizations, while allowing the Client flexibility in creating user defined filters for different reports and screens within the application. The utilization of the MS Azure technologies allows the Client the flexibility to rapidly modify, improve, and customize the product to meet the current and future needs of their customers and appropriately respond to end user feedback.

About Optimum

Optimum is a leading, certified Gold Microsoft Partner, partnering with clients from concept to execution to implement and deliver end-to-end customized software solutions or modernize your legacy applications to maximize your IT investments and reduce costs.

With years of knowledge and experience in Modern Application Development, our team of Microsoft certified experts redesign, modernize, and convert legacy applications to modern and integrated solutions to improve performance and user experience and reduce your maintenance and support costs.

Contact us for a free discovery session to identify and discuss the best solution option for your application modernization and software development needs. More information: Software Development and Application Modernization

Contact us: info@optimumcs.com | 713.505.0300

TAGS: Application Development Services Application Modernization Azure Application Services Azure Development Azure SQL Development Custom Software Development Excel to Cloud Application