Development of an End-to-End Data Pipeline and Optimization of Data Management with ServiceNow and Kafka

The goal of this project was to build a scalable and automated end-to-end data pipeline to provide a consistent and reliable data foundation for reporting and analytical purposes. A relational business object model was developed to map all data relevant to project portfolio management. Due to specific bank requirements, a custom producer was implemented to write data to Kafka, as the native ServiceNow interface did not meet these requirements.

• Development of a relational business object model to represent all relevant data for project portfolio management.
• Re-engineering of the data structure of the source system ServiceNow to optimize data consistency and availability.
• Development of an end-to-end data pipeline: ServiceNow -> Kafka -> Azure Synapse -> DaVi -> PowerBI.
• Implementation of a custom Java producer to transfer data to Kafka based on the relational business object model.
• Execution of comprehensive tests and release management to meet regulatory requirements.
• Creation of technical data products as data sources for Power BI dashboards in DaVi via SQL.
• Development of an internal fallback solution using Microsoft Access to test new data-related requirements internally before submitting change requests to other systems.

Results:

• Development of a relational business object model that fully meets project portfolio management requirements.
• Establishment of a stable and scalable data pipeline enabling real-time data processing and reporting.
• Overcoming the limitations of the native ServiceNow-Kafka interface, which only supported tabular data processing.
• Ensuring data consistency and availability through optimized data structures and automated processes.
• Reduction of manual processes and improvement of efficiency through fully automated workflows.
• Compliance with regulatory requirements through comprehensive testing and quality controls.

As part of the digital transformation, various initiatives were implemented to optimize project portfolio management, reporting processes, and knowledge transfer. The goal was to strategically manage datadriven decisions, standardize and automate reporting processes, and ensure the sustainable development of the data and reporting landscape. Special focus was placed on ensuring regulatory compliance, improving data quality, and enhancing efficient collaboration with project stakeholders.

• Project Portfolio Management:
  • Implementation of efficient monitoring processes to ensure transparent evaluation of project progress.
  • Regular communication with project managers to ensure status updates and goal achievement.
  • Consulting on strategic prioritization and value maximization of the portfolio.
• Reporting and Data Analysis:
  • Automation and standardization of ServiceNow reports to increase efficiency and reduce manual efforts.
  • Development of data-driven ad-hoc analyses to support strategic decisions.
  • Preparation and provision of regulatory-compliant datasets for external audits.
• Training and Knowledge Transfer:
• • Strategic consulting for sustainable use and further development of the reporting and data management landscape.
  • Creation of comprehensive documentation on data models, processes, and system integrations for project portfolio management.
  • Conducting training sessions to ensure knowledge transfer and sustainable use of reporting tools.

Results:

• Optimized project management through clear status reports, efficient monitoring processes, and strategic prioritization.
• Automated reporting processes that reduce manual efforts, improve data quality, and meet regulatory requirements.
• Sustainable knowledge transfer through comprehensive documentation and hands-on training to optimize the reporting landscape.

As part of a bank-wide analysis, it was evaluated whether Tableau should continue to be used as the primary reporting solution. The objective was to analyze the cost-effectiveness of Tableau and assess potential alternatives. The analysis led to the decision to implement Power BI as the new reporting solution, develop a new data model, and host the data in the cloud. Subsequently, existing dashboards were strategically migrated, and the new environment was implemented.

• Conducting a bank-wide analysis of Tableau usage, including cost-benefit analysis, performance evaluation, and strategic alternatives.
• Developing a decision-making framework for management with detailed results preparation and presentation.
• Planning and coordinating the migration from Tableau to Power BI, including the transfer of existing dashboards.
• Performing testing for all migrated dashboards in JIRA to ensure functionality and data integrity.
• Creating comprehensive documentation on migration, test protocols, and user guides.
• Training end users on Power BI to ensure a seamless transition.

Results:

• Cost savings by transitioning to a more cost-effective reporting solution.
• Successful migration of over 50 dashboards while maintaining full functionality and visualizations.
• Improved reporting performance and user experience through the use of Power BI.
• Transfer of data to a newly modeled business object model in Azure.
• Delivery of comprehensive documentation and training materials to facilitate user adoption and acceptance of the new solution.

As part of the transformation of project portfolio management at Norddeutsche Landesbank, the focus was on developing a data-driven control system. The goal was to optimize strategic decision-making processes through meaningful KPIs and innovative visualization solutions. The dashboards were created in Tableau to effectively monitor project status and the performance of business-critical initiatives.

• Development and implementation of a comprehensive KPI framework for evaluating transformation projects.
• Design and implementation of interactive Tableau dashboards to monitor business-critical metrics.
• Automation of data preparation and integration from ServiceNow for dashboard reporting.
• Planning and execution of hands-on Tableau training sessions for business and leadership teams.
• Preparation of regular management reports based on the developed dashboards.

Results:

• Introduction of a structured KPI framework to enhance transparency and controllability of projects.
• Development of intuitive dashboards to facilitate well-informed, data-driven decision-making.
• Reduction of manual processes through automation of data integration and reporting workflows.

The goal of this project was to build a modern cloud architecture and implement a centralized data warehouse using Snowflake. The solution aimed to provide efficient data integration, high-performance queries, and scalable storage capacities to meet the demands of a data-intensive organization. The focus was on optimizing data processing and delivering a robust foundation for analytical applications.

• Development and design of a cloud architecture in Snowflake, enabling efficient data processing and storage.
• Integration of various data sources (e.g., APIs, relational databases, flat files) into the data warehouse using ETL and ELT processes.
• Development of data pipelines for continuous data integration and transformation using dbt and Python.
• Optimization of query performance through partitioning, clustering, and Snowflake-specific features.
• Implementation of user and security policies, including role-based access control and encryption, to meet compliance requirements.

Results:

• Deployment of a centralized data warehouse with improved data processing and query speed compared to the previous solution.
• Scalable architecture that can be flexibly adjusted to growing data volumes.
• Improved data quality and consistency through automated pipelines and transformations.
• Establishment of a stable foundation for analytical applications and data-driven decision-making.

As part of the introduction of Samsung devices in schools, a data-driven KPI framework was developed to measure and optimize the effectiveness of strategic campaigns. The core of the project was the development of interactive Tableau dashboards for real-time monitoring of device adoption and campaign performance. Additionally, regular reporting processes and an alert system were implemented to proactively respond to critical developments. A special focus was placed on training internal stakeholders to ensure the effective use of dashboards and KPIs.

• KPI Definition: Development of a comprehensive framework to measure device adoption, campaign reach, engagement, and ROI.
• Tableau Dashboards: Design and implementation of interactive dashboards for real-time visualization of campaign data and performance tracking.
• Reporting Processes: Creation of regular management reports with data-driven recommendations for the marketing and sales teams.
• Alert System: Implementation of an automated alert system to identify critical deviations and proactively guide marketing actions.
• Stakeholder Training: Design and execution of hands-on training sessions for effective use and interpretation of dashboards and KPI reports.
• Data Analysis: Conducting in-depth analyses to identify optimization opportunities and derive datadriven campaign strategies.
• Campaign Tracking: Supporting the marketing team through continuous monitoring of campaign performance and data-driven adjustment of initiatives.

Results:

• Optimized decision-making processes: Through precise KPI definitions and data-driven insights for strategic decisions.
• Effective campaign management: Reduction of reaction times to campaign deviations by 30% through the implemented alert system.
• Improved transparency: Introduction of a central dashboard for real-time monitoring, actively used by over 50 stakeholders.
• Enhanced user competency: Comprehensive training and documentation significantly improved the independent use of dashboards.
• Sustainable optimization: Continuous improvement of campaign performance through regular datadriven adjustments.

To support the market launch of Samsung devices in schools, a data-driven B2G campaign was developed to optimize the generation, tracking, and qualification of leads. The goal was to implement structured processes for lead capture and introduce a centralized CRM system to efficiently manage and track customer inquiries. Additionally, a digital form was developed for the website and special events to capture potential leads and automatically assign them to the appropriate sales teams.

• Lead Tracking Processes: Design and implementation of a structured workflow for capturing, qualifying, and following up on B2G leads.
• CRM Implementation: Introduction of Salesforce as a central CRM tool for managing and tracking sales contacts.
• Form Development: Design and integration of a web-based lead capture form for the website and events to automate quote generation.
• Automation: Implementation of workflows for automated lead assignment based on location, inquiry type, and priority.
• Stakeholder Management: Close collaboration with marketing and sales teams for the successful implementation of new processes.
• KPI Definition: Development of metrics to measure campaign success and continuously optimize the lead pipeline.

Results:

• Efficient lead management through structured processes and automation, reducing manual efforts.
• Successful implementation of Salesforce as a centralized platform for managing customer inquiries.
• Reduction in response time for customer inquiries through optimized workflows and automated assignment.
• Significant increase in conversion rates through targeted, data-driven campaign management.

The goal of this project was to optimize the work environment of a company through data-driven analyses, aiming to reduce operational costs while increasing employee satisfaction. As part of the implementation of New Work principles, a hybrid work model was developed to promote flexible working methods, digital collaboration, and efficient space utilization. A particular focus was placed on introducing digital tools to enhance collaboration across distributed teams and make work processes more transparent.

• Data-Driven Workplace Analysis:
  • Conducting a comprehensive analysis of office space utilization and work patterns (e.g., meeting rooms, desk-sharing, remote work).
  • Collecting and evaluating employee feedback on the use of existing work environments and suggestions for improvement.
  • Developing tailored recommendations to optimize office spaces and hybrid collaboration.
• Implementation of Hybrid Work Models:
  • Designing and implementing a hybrid work model that enables a flexible mix of remote and on-site work.
  • Defining guidelines for the use of shared desks and meeting areas to improve space efficiency.
  • Implementing Gantt charts and visual task boards for better project planning and progress tracking.
• Training and Change Management:
• • Designing and conducting employee training sessions on the use of new digital tools (e.g., Microsoft Teams, Zoom, JIRA, Confluence).
  • Supporting teams in the adoption of collaboration tools to improve efficiency and task documentation.
  • Creating guidelines and best practices for the sustainable use of the implemented work models.

Results:

As part of the digital transformation, the introduction of an agile project management framework was pursued to sustainably modernize the corporate culture and adapt it to dynamic market requirements. The goal was to promote innovation, efficiency, and cross-functional collaboration by embedding agile methodologies across all business areas. A key focus was on improving internal communication processes, fostering team autonomy, and implementing digital tools to support agile processes.

• Analysis and Maturity Assessment:
  • Conducting an as-is analysis of existing project management methods and identifying bottlenecks in decision-making processes.
  • Performing an agile maturity assessment to derive appropriate transformation strategies.
• Development and Implementation of the Agile Framework:
  • Developing a company-specific agile framework based on SCRUM and Kanban.
  • Implementing a hybrid model that combines traditional project management methods with agile principles.
• Digital Transformation of Work Processes:
  • Introducing digital tools such as JIRA and Confluence to visualize workflows and project progress.
  • Automating workflows to reduce manual efforts and increase transparency.
• Training and Change Management:
  • Conducting workshops and coaching sessions for leadership and employees to establish an agile mindset.
  • Supporting the agile transformation in pilot projects with iterative improvements.
• Introduction of Agile Rituals:
• • Implementing daily stand-ups, retrospectives, and sprint reviews to continuously improve team performance.
  • Establishing a company-wide agile exchange format to scale best practices.

Results:

• Reduction of project lead times through the implementation of agile methodologies.
• Improved team collaboration and communication structures.
• Sustainable transformation of corporate culture towards greater flexibility and innovation.