Sigma Software team analyzed the existing reporting process and envisioned a new solution. Our data integration development team delivered a solution that allows to automate data ingestion from different systems & in diverse formats with further data structuring into a single source of truth.
It also included a robust Data Warehouse development (DWH). The team configured the DWH according to the security best practices to safeguard secure data preservation and integrated it with Tableau service for report visualization in different dashboard formats. The innovation yielded valuable impact on the reporting process:
- Increased calculations accuracy and enabled early detection of errors due to various data checks & validations at different data integration and report generation stages
- Safeguarded reports compilation according to the priority and secure preservations of sensitive business reports in a single repository with a flexible system for managing access rights
- Introduced automated, asynchronized data updates (e.g., hourly, daily, weekly, monthly updates) and data validation according to pre-defined metrics, such as data lagging and anomalies detection
- Enabled business users to easily find necessary reports via the corporate SharePoint portal, integrated with the analytics system
Security was one of the main requirements as the system handles sensitive information. Yet, the solution had to be flexible enough to meet changing business needs. We designed the architecture that elegantly fits both requirements. We also added an ingestion framework to easily integrate multiple data sources and databases into one system as we knew that the Client would need data from other systems in the future.
Our Big Data experts analyzed the requirements, mapped those to data solutions available on the market, and selected the most effective set of components, transforming those into a data management & reporting system including:
- MS SQL Server & Tableau – ensure a robust Data Warehouse with all the data analyzed and represented in tables & charts (Tableau ensures high data security since it can be hosted on a local machine)
- SharePoint & JS technologies – allow to gather charts from reports in one place and simplify data representation based on a user’s needs (e.g. reports can be represented through a web page)
- Python – serves as a basic programming language as it’s simple, efficient, and supports lots of libraries, which safeguards required scalability
- Apache Spark framework – powers ETL operations, ensuring big data volumes processing
- Azure DevOps – safeguards code retention, information lifecycle management, and CI/CD management automation
- Prometheus & Grafana – safeguard servers monitoring and errors alerting with all the metrics visualized in the dashboard
- Jenkins – orchestrates data ingestion process
The Client mentioned that they plan to extend the system with more data sources and new capabilities over time. So, we wanted to make sure that we have an effective process for frequent & reliable product changes delivery to production. Our DevOps engineers adopted CI/CD pipeline to the exiting SDLC that allowed to automate data integration solution development process and employed Azure DevOps for CI/CD process management, issue tracking & version control.
We built the solution as a mix of standard and release pipelines for different purposes to be able to cover all the CI/CD needs within one system. The pipelines include:
- Standard Continuous Integration pipelines that are triggered by VCS push to any branch, run test suits, and report version status to Git via a visible status icon
- Continuous Development release pipelines, complemented with secure credential management that handle DB schema evolution and multiple code updates