A typical problem in the modern workplace is the volume of raw data that needs to be interpreted and processed. The larger the amount of data, the harder it is to keep track, define KPIs, and derive trends. Final decision-making becomes almost impossible.
Exactly with this challenge, a client approached us at Alight Consulting GmbH:
“We use a ticketing system to manage and support the development and usage processes of applications. Daily occurring issues and impacts are captured in the form of tickets and accompanied from analysis to the realization of the solution.
We need a real-time analysis of the data in the form of categorization and aggregation, visualized as a report. We require reports both in static standard form and interactive reports for ad hoc analysis.”
Regular processes must be continuously optimized to make them as efficient, stable, secure, and goal-oriented as possible. To achieve this, the accumulating data needs to be monitored and analyzed to derive decisions and measures from it.
Together with the customer, an Azure-based solution was developed, which is divided into several components:
- Daily automatic import of relevant raw data from an external ticketing system
- Transforming and structuring the data in a data warehouse (DWH)
- Utilization of Power BI reports (based on the data in the data warehouse)
With this system, it is possible to
- Determine the current processing status of tasks (tickets)
- Identify anomalies
- Support decision-making to control and optimize processes
The developed solution utilizes modern online technologies such as Azure Services and Power BI. This allows the solution to be designed from a functional perspective to be secure, automated, and expandable, enabling goal-oriented and modern monitoring.
Another advantage is the minimal infrastructure maintenance and upkeep effort and potential cost savings through the use of online services. A newly implemented feature confirmed the technology choice: Data expansion within the ticketing system could be quickly, easily, and with minimal effort adapted to incorporate this data into the final reporting.
The subsequent reporting includes standardized reports, based on which the current data is presented in a uniform format. Based on this, corresponding evaluations and decision-making processes can be implemented by management. Furthermore, the data from different periods in the past can be compared in a comparable manner.
The database of the ticketing system (M42 mirror database) serves as the direct data source. The staging database, as the target for the daily data import, has essentially the same structure as the Matrix42 database. This allows the data to be transferred as quickly and straightforwardly as possible. The imported daily data available in staging is added to the data warehouse (DWH) in a restructured and enriched form. Thus, the DWH contains not only current data but also historical data. The structure of the DWH is specially designed and optimized for subsequent analysis, such as Power BI reporting. The reports read the data from the DWH and present it in an aggregated form tailored to the user.
Technologies utilized
Azure SQL databases
The databases previously introduced in the “Solution Architecture” chapter
- M42 mirror database
- Staging database
- DWH-database
are created as Azure SQL databases in the cloud.
Azure Datafactory
To realize the data transfer for the daily import from the mirror database to staging and into the data warehouse (DWH), the Azure service of Data Factory is used.
To design the transformation processes (data flows), Linked Services (connectors to the databases) and Datasets (representations of the dataset structure from tables) are set up and utilized.
The main logic is contained within the data flows. Data flows can encompass both very simple subprocesses and highly complex transformations.
Finally, individual data flows are arranged as standalone ETL processes in pipelines to form an execution unit.
The pipeline depicted here is a sequence of 3 data flows.
The pipeline is triggered once daily and encompasses the entire daily data import process.
The individual pipeline runs can be viewed and analyzed in the Monitoring section of Azure Data Factory.
Furthermore, each pipeline can be examined for the execution of its subprocesses.
Azure Blob Storage
In our project, data from the legacy system is being migrated to the new solution. This data is exported as Excel files. The files are uploaded to an Azure Blob Storage and then, similar to the daily import process, flow through DataFlow into the DWH.
Power BI Report
The data stored in the Data Warehouse through the processes of a Data Factory is structured in a way that is specifically tailored for analyzing, aggregating, and filtering raw data. These data are visualized using Power BI reports.
There are standardised reports for the periodic evaluation and assessment of current data. The current data can also be compared with similar data from the past.
In addition, reports are offered to enable users to analyse their own data interactively (ad hoc reporting).
The design of typical reports only requires configurative steps and does not require any coding. Complex data preparation that cannot be realised with configurative standard can be implemented with programming (DAX).
The reports created are published centrally and made available to a selected group of users via authorisations.
Summary
Benefits of Azure / cloud technology
The technologies used in our project, such as Azure Datafactory and Power BI, enable low-code solutions that do not require any programming knowledge. This means that solutions can be developed quickly and cost-effectively, with the development focussing on the technical processes to be implemented.
Advantages
Cloud services are provided, maintained and further developed centrally. The user can rely on the provider to guarantee the stability of the system. The services used offer very high availability and data security. The user has only marginal infrastructural maintenance and care requirements.
Due to the diversity of services and technologies in the Azure Cloud and PowerPlatform, the use of third-party tools has largely taken a back seat.
Furthermore, services are scalable. For Azure SQL databases, for example, a suitable model can be selected from various licence models for memory size, CPU usage and performance optimisation. This means that costs can be minimised depending on your own requirements.
Challenges
Setting up and using an Azure cloud requires management that maintains and supports the services and resources set up and also ensures that new components are seamlessly integrated into the existing system. This ensures temporal and technical stability.
Transferability to analogue scenarios
The solution described above for a real project can be transferred to many analogue areas of application.
It is very often the case that highly specialised tools/products are used to support internal processes. However, these products often do not have the monitoring that the customer needs for their purposes.
In these cases, the following procedure can be followed:
- Data synchronisation in the cloud
- Alternative: Access from the cloud via Azure Data Gateway
- Restructuring of data
- Enrich the data with further customer-specific information
- Consolidation in a DWH
- Analysing/reporting of data with Power BI