Neel Shah, VP Software Development
May 9, 2024

At Strasz Assessment Systems, as part of our ongoing cloud migration initiative for the internal systems of the AICPA Exams Team, selecting the right database technology was a critical decision. This post outlines our existing database setup, discusses why Azure Cosmos DB was chosen as the optimal solution for our needs during the migration and introduces our approach to Business Intelligence using Azure Synapse Analytics and Power BI.

Our Existing Data Infrastructure

Our database setup included SQL Server and RavenDB databases. SQL Server, known for its robust relational database capabilities, managed our core transactional processing. Meanwhile, RavenDB was used for document-oriented storage, accommodating our needs for schema flexibility. We utilized SQL Server Reporting Services (SSRS) and Crystal Reports for data analysis and business intelligence.

The shift to the cloud encouraged us to rethink our approach to data management, underlining the necessity of moving beyond our on-premises configurations. Our focus was on adopting a data solution that would ensure scalability, flexibility, and performance to meet future demands, aligning with our strategic objectives for cloud integration.

Exploring NoSQL Databases

Assessment platforms handle various types of data, including multiple-choice questions, interactive simulations, exhibits, grading rules, content and skill outlines, storyboards, performance statistics, instructional content, and other vital components for crafting professional exams. At the core of these platforms lies an item bank, functioning much like a Content Management System (CMS). This item bank stores content and oversees the creation, revision, and implementation of exam materials within assessments. A document database is particularly well-suited for managing an assessment platform for several reasons:

  1. Flexible Data Schema: NoSQL databases do not require a fixed schema, allowing for the easy addition and modification of data elements, such as grading rubrics or new content types, without significant system reconfiguration.
  2. Complex Document Structures: These databases excel at storing complex, nested documents, making them ideal for assembling exams and storyboards involving various components and associated metadata. This capability effectively addresses the impedance mismatch commonly encountered with traditional relational databases, simplifying managing diverse data types.
  3. Scalability: NoSQL databases are designed to scale efficiently, ensuring optimal performance even with complex data structures and large database sizes.
  4. Integration with JavaScript and Web Technologies: Since JavaScript natively understands JSON, document databases integrate seamlessly with modern web stacks, enhancing their effectiveness with backend or frontend frameworks like Node.js and Angular.
  5. Development Speed: The inherent flexibility of document databases often allows for faster application development, as changes to the data model can be implemented rapidly and with minimal impact on existing data.

With these advantages, a document database not only meets the evolving needs of the platform but also enhances its operational efficiency, providing benefits in performance, flexibility, and scalability that are often challenging to achieve with traditional relational databases.

Migration to Azure Cosmos DB

During the migration, we chose Azure Cosmos DB as the database technology for several key reasons that aligned with our current and future needs:

  1. Global Distribution: Azure Cosmos DB facilitates global data distribution, which is crucial for creating applications that maintain high availability across multiple regions.

  2. Comprehensive SLAs: Azure guarantees strong performance, consistent availability, minimal latency, and reliable data access through robust Service Level Agreements (SLAs) for Cosmos DB. These assurances are vital for developing highly responsive applications.
  3. Multi-Model Support: Unique in its capability, Cosmos DB supports various data models such as key-value, document, column-family, and graph databases within a single service. This versatility enables developers to select the most appropriate data model for specific system needs.
  4. Seamless Integration with Azure Services: Cosmos DB integrates seamlessly with other Azure services, enhancing the capabilities of cloud-native architectures.
  5. Integrated Indexing and Querying: Cosmos DB automatically indexes all data and allows for SQL-like querying across these indexes, combining the flexibility of NoSQL databases with the query capabilities of SQL.
  6. Cost-Effectiveness: Cosmos DB provides provisioned throughput and serverless computing options, which can help optimize costs based on usage patterns and scale demands. The serverless mode is especially suited for system areas with low volumes.
  7. Real-Time Analytics: Through Azure Synapse Link, Cosmos DB offers real-time analytics without impacting transactional workloads, facilitating immediate data analysis and business intelligence.

    Simplifying Business Intelligence

    Business Intelligence (BI) encompasses the strategies and technologies used by enterprises for data analysis of business information. BI technologies provide historical, current, and predictive views of business operations, often using data gathered in a data warehouse or a data mart and small subsets of data for reports and analysis.

    An analytical store, often part of a data warehouse, is explicitly designed for analysis and reporting. It differs from operational databases in its ability to handle large volumes of data, optimized for quick retrieval and analysis rather than transaction processing.

    Traditionally, moving operational data to an analytical store for business intelligence involves a complex Extract, Transform, Load (ETL) process. This process can be time-consuming and resource-intensive, requiring significant effort to ensure data integrity and usability in the analytics environment. However, integrating Azure Cosmos DB, Azure Synapse Analytics, and Power BI offers an efficient alternative.

    Eliminating Complex ETL with Azure Synapse Link

    Diagram Source: Microsoft, Azure Synapse Link for Azure Cosmos DB

    Azure Cosmos DB’s analytical store is a fully isolated columnar store that enables large-scale analytics on operational data without impacting transactional workloads. It utilizes columnar storage for optimized data processing and automatically syncs data from the transactional store in near real-time.

    Azure Synapse Analytics is a cloud-based service from Microsoft Azure that integrates big data and data warehousing capabilities into a single platform. This integration allows organizations to analyze and derive insights from large volumes of data.

    Azure Synapse Link for Azure Cosmos DB introduces a cloud-native hybrid transactional and analytical processing (HTAP) capability. This innovative feature provides direct access to the Azure Cosmos DB analytical store via Azure Synapse Analytics, offering seamless integration with several benefits:

    1. Real-Time Data Accessibility: Azure Synapse Link establishes a live connection to the Azure Cosmos DB analytical store, ensuring that data for analytical processing is always current and reflects the latest operational updates, all without the need for manual intervention or complex ETL (Extract, Transform, Load) processes.
    2. No Impact on Operational Performance: Since the data integration is handled through Azure Synapse Link, the operational performance of Cosmos DB remains unaffected. The analytical store operates independently, ensuring the transactional workload does not slow down due to analytics processing.
    3. Simplified Data Architecture: Azure Synapse Link eliminates traditional ETL tasks, reducing the complexity of the data architecture. Data transformations are managed within Azure Synapse Analytics, leveraging its robust query capabilities to simplify the overall data management workflow.
    4. Analytics and ML: Supports real-time data processing and complex machine learning workflows with technologies like Synapse Spark, all without the need for traditional ETL processes.
    5. Power BI Integration: With data readily accessible in Azure Synapse Analytics, it can be directly linked to Power BI, enabling the creation of dynamic and visually compelling reports and dashboards. This integration facilitates deeper insights into operational data and supports faster, more informed decision-making.

    Limitations and Challenges

    While our migration to Azure Cosmos DB and the subsequent integration with Azure Synapse Analytics and Power BI have brought significant advancements, we also faced several challenges that shaped our journey:

    1. Tooling Limitations with Cosmos DB: Transitioning to Azure Cosmos DB posed unique challenges, mainly due to the absence of native performance optimization tools like SQL Profiler for SQL Server. Consequently, we relied on third-party tooling to better manage and optimize database performance.
    2. Data Migration Complexity: Moving data from SQL Server and RavenDB to Azure Cosmos DB required careful handling to maintain data and minimize downtime.
    3. Cost Management: Managing costs with Azure Cosmos DB’s high data throughput and storage options was initially challenging. We needed to finely tune the balance between performance and cost efficiency, particularly with the platform’s serverless and provisioned throughput options.
    4. Learning Curve in Business Intelligence Setup: Deciding how to allocate processing responsibilities between Azure Synapse Analytics and Power BI posed a significant learning curve. Choosing between import mode or direct query in Power BI reports required a deep understanding of performance implications and data refresh needs.
    5. Integration Challenges: Azure Cosmos DB integrates well with other Azure services. However, achieving seamless connectivity and optimal performance with Azure Synapse Analytics and Power BI required considerable configuration and extensive testing.
    6. Dependence on Azure Ecosystem: Our reliance on Azure services, while largely beneficial, raises concerns about vendor lock-in, limiting our flexibility to switch to potentially more effective or cost-efficient technologies in the future.

    Despite these challenges, the transformative benefits of the new data platform are clear. We continue refining our use of Azure technologies to enhance data management and business intelligence systems.

    Conclusion

    The migration to Azure Cosmos DB represents a significant milestone in our cloud migration journey, enhancing our flexibility, scalability, and performance. The integration of Azure Synapse Analytics, Azure Cosmos DB, and Power BI has revolutionized our data analytics workflows, enabling us to derive actionable insights with exceptional efficiency and agility. This strategic choice meets our current operational needs and positions us for future growth and innovation in the cloud.

    About the Author

    Neel Shah is a seasoned technology leader passionate about driving innovation and delivering exceptional software solutions. As the Vice President of Software Development at Strasz Assessment Systems, Neel brings a wealth of experience in leading high-performing teams, shaping strategic initiatives, and fostering a culture of excellence. With more than two decades immersed in the technology sector, Neel has developed a deep understanding of distributed systems and Service-Oriented Architecture. This expertise enables him to spearhead the creation of scalable, resilient software solutions tailored to meet the dynamic needs of clients and stakeholders, underpinned by pragmatic technology choices. By fostering a culture of innovation and continuous improvement, Neel empowers teams to push the boundaries of what’s possible and deliver impactful results that drive business growth. In his leisure time, Neel enjoys quality moments with his wife and two boys, playing cricket, watching movies, and staying updated on the latest developments in science and technology.

    John DeFalco, SR Software Engineer
    August 6th, 2021

    Ken White is a Scrum Master for one of our Agile development teams. He’s also our Production Support Operations Manager for the same customer. I don’t believe combining these roles is a practice unique to Strasz. What really sets Ken apart from most others is, he is also currently the Fire Chief for the Liberty Corner Volunteer Fire Department1. So, it goes without saying that Ken has both an educational background and practical experience to bring teams of people together with a high likelihood of success. We’ve all heard of the chicken and egg paradox. So was the fire department the chicken and his college degree the egg? Or vice versa?

    Ken (left) alongside the Chief (middle) and Deputy Chief (right) of the Liberty Corner Volunteer Fire Department.

    Ken White is a Scrum Master for one of our Agile development teams. He’s also our Production Support Operations Manager for the same customer. I don’t believe combining these roles is a practice unique to Strasz. I’m sure there are plenty of other leaders in the field that are holding down both positions. What might be rarer, Ken has a degree in Management Information System & Operations Management that almost exactly aligns with his current job responsibilities. What really sets Ken apart from most others is, he is also currently the Fire Chief for the Liberty Corner Volunteer Fire Department1. So, it goes without saying that Ken has both an educational background and practical experience to bring teams of people together with a high likelihood of success. We’ve all heard of the chicken and egg paradox. So was the fire department the chicken and his college degree the egg? Or vice versa?

    James Lipton from The Actor’s Studio is often fond of saying, “Let’s start at the beginning.” Back in the summer of 1986, Ken was working as a lifeguard and snack bar manager at a local pool when a friend approached him about joining the volunteer fire department. He hadn’t previously given it a thought. Yet, he immediately became fascinated by the inner workings of how the organization came together as a team. He was impressed that such a large group of volunteers could be coordinated to achieve great things in the community. The do-it-yourselfer in Ken was also fascinated with the department’s dizzying array of tools and equipment. 

    Later that same year, he went off to college at the University of North Carolina at Greensboro. Ken conveys his choice of UNCG simply as “My parents could afford the school, and it was farther away than Rutgers.” As was previously stated, he pursued a degree in Management Information Systems & Operations Management, which was a natural choice, in retrospect. From early adulthood, Ken had a predisposition towards organizational thinking, technology, leadership, and management.

    After graduating from college, Ken began his career at AT&T as a software developer and simultaneously became more involved with the fire department. He started his coding journey with an internal COBOL development program at AT&T. Ken rose through the organization over the next ten years. Ken eventually became a District Manager, with a staff of 80+ and 3 direct report managers. Concurrently, he rose through the ranks of the fire department. He became President, then worked his way up as Assistant, 2nd Assistant, then eventually Chief. At the fire department, Ken leads a multi-faceted team of 60 volunteers. 

    The overlap of these two paths is significant. Both have a business and support side that require intense management, efficient organization, and experienced leadership at a high level. A software company’s business revolves around planning and scheduling releases, conducting regular status meetings, managing budgets, and interfacing with customers. The fire department is organized as a not-for-profit business and, as such, has a President that presides over the company’s business. This includes filing tax for

    ms with the state, managing donations, fiscal planning, project planning, creating specifications, procurement, politics, and leading public meetings. Both positions require an individual at the top with stellar organizational and planning skills and a positive demeanor supporting customers.

    For a software company, every product requires support. Users will encounter defects, and those defects must quickly be researched, verified, and remediated. Customers will occasionally have ad-hoc, high-priority requests in response to their own business’ stimuli, colloquially referred to as “fires” by the production support team. In parallel, the support side of the firehouse handles responding to dispatched 911 calls and extinguishing actual, physical fire alerts sent through an Incident Command System. When asked which fires are harder to control, Ken quipped, “The actual fires … usually”. 

    On both fronts, teams are composed of individuals with specific roles and skills. For a software company, those roles are typically developers, designers, quality assurance, and IT. Team members use their varied skills and come together to create solutions. When a challenge arises, Developers will research the code base and provide technical solutions. Production support accesses the logs in production and applies their working knowledge of the system and the user’s workflow to determine how to recreate the issue. IT investigates network, security, and server-related issues. The fire department is similarly multi-faceted. The engine company performs fire suppression, the truck company provides ventilation and search capabilities, and others whose job is to provide a water supply. Clearly, both organizations need a respected and capable leader to coordinate the varied problem resolution activities in a responsive and professional manner.

    In the summer of 2021, Ken celebrated his 35th year with the Liberty Corner Fire Department. I’d like to extend the celebration by adding to it Ken’s 35th year of applying, like Liam Neeson (Taken), “a particular set of skills,” both technical and managerial, to every aspect of his professional and personal life.

    1 http://www.libertycornerfire.org/ – please help their cause by donating!