Heringer Consulting GmbH

Development of a data warehouse including reporting landscape and self-service business intelligence solution

• Development of a data warehouse including reporting landscape in accordance with company requirements such as single point of truth, historical storage and anonymisation of data to comply with the GDPR, central and high-performance provision of company data using self-service BI tools.
• Design of the various data warehouse layers such as stage, core and data mart layers.
• Design and implementation of data models according to the modelling approaches of Bill Inmon (Enterprise Datawarehouse) and Ralph Kimball (Dimensional Modelling) for high-performance queries using self-service BI tools.
• Design and development of the necessary ETL processes using Apache HOP, SQL Server Integration Services (SSIS) and stored procedures for automated daily loading of the MSSQL data warehouse.
• Integration of Slowly Changing Dimensions of various types for the historically correct presentation of data within the reporting layer.
• Scheduling the automated loading of the DWH using SQL Server agent jobs.
• Implementation of automated tests to check data integrity after loading the data warehouse using check procedures.
• Implementation of the designed star schema in an Analysis Services database using Tabular Editor and Dax Studio.
• Development of Measures and Calculated Columns using DataAnalysiseXpression (DAX).
• Conceptualisation and implementation of a security concept at database and reporting level (PowerBI Service: Workspace App and report authorisations; Report:Row-Level-Security (RLS) , Object-Level-Security (OLS)) with the involvement of internal and external users.
• Creation of reports in PowerBI using common features such as bookmarks, dynamic navigation, evaluation by analysis level or user-defined geographical analysis.
• Setting up the OnPremises data gateway for the protected provision of data between the OnPremises database server and PowerBI Services.
• Evaluation of report utilisation by analysing the Usage Metrics (PowerBI Rest API).
• Versioning of the entire solution (database structure, ETL processes, tabular models) using Visual Studio, GIT and Azure DevOps.
• Documentation of the content in Confluence.

Technologies Microsoft SQL Server, Azure DevOps, SQL Server Integration Services (SSIS), SQL Server Management Studio (SSMS), SQL Server Analysis Services (SSAS), Apache HOP 1.2, Visual Studio, Tabular Editor, DAX Studio, GIT, Confluence, Salesforce 

• Analysing existing data and assessing whether additional data is required
• become
• Conception of a data model for analysability and control via the
• migration dashboards
• Creation of a draft (POC) of the Power BI Dashboard, continuous
• Further development of the dashboard in coordination with the stakeholders
• Integration of a Power App for classifying existing To Do's in the migration dashboard
• Introduction of master data management via a PostgresSQL database

Further development of the IT processes of a billing service provider for §300 in the healthcare sector

• Project planning and project management
• Communication with management and specialist departments
• Architecture IT infrastructure
• Further development and optimisation of the existing architecture consisting of applications (C#, C++, Web) as front-end and middleware, as well as back-end at SQL server level
• Further development of existing processes using Microsoft SQL Server Integration Services (SSIS) and T-SQL procedures and operational process implementation in billing management
• Test automation of the billing processes and development of the front end
• Optimisation of processes using T-SQL procedures and changes in process control for compliance with delivery reliability in billing management
• Creation of reports in Power BI for the specialist department for operational control of QA processes (quality assurance)
• Outsourcing of configuration parameters for flexible process control 
• Analysing the existing reporting solution
• Creating the data basis for reporting in Microsoft SQL Server 2016
• Implementation of the data model and the reports required by process control in Microsoft Power BI
• Implementation of technical systems of the legislator
• Database design Microsoft SQL Server
• Creation of anonymised test databases for automated test cases
• Automated creation of Jira processes using stored procedures
• Evaluation of Jira processes based on stored procedures and Java
• Creation of deletion processes to comply with retention periods with connection to applications (C# .NET, C++, Java), partly with Hibernate and NHibernate
• Creation and customisation of ETL routes for mass data processing
• Optimisation of cost-intensive queries
• Analysis and optimisation of Microsoft SQL server settings
• Creation of reports (Power BI)
• Analysing and eliminating database blockages
• Development of several ETL routes to provide test databases for automated unit tests of the applications (C#, C++, Java)
• Implementing automated tests for the various database modules
• Creation of test databases consisting of anonymised test data sets for automated testing of the individual database modules
• Development of new database procedures that provide business logic for various front ends (C#, PHP, C++, Java)
• Creation of cleansing procedures to ensure data quality - Further development and error analysis of the client's existing time recording system

Technologies: Microsoft SQL Server (2008 R2, 2012, 2014, 2016, 2017), Transact-SQL (T-SQL), Java, C# .NET, Perl, EDIFACT, Microsoft Visual Studio, CSV, Microsoft Excel, XML, Jira

• Configuration of the existing Microsoft SQL server
• Installation of necessary Microsoft SQL server updates
• Creation of Microsoft SQL Server reports - Customisation of existing ETL routes
• Creation of configuration collections of all existing Microsoft SQL servers
• Dynamic determination of configuration parameters for all existing ETL routes, including visualisation and selection in Microsoft SQL Server reports

Technologies: Microsoft SQL Server 2014-2019, Transact SQL (T-SQL), Visual Studio Data Tools, Microsoft SQL Server Integration Services (SSIS), Microsoft SQL Server Reporting Services (SSRS)

Design and implementation of a data migration from different data sources (DB2, MySQL, SQL server) to a target system (SQL server)

• Project planning and project management
• Communication with management and specialist departments
• Development of the migration process (ETL) based on stored procedures, SSIS packages, linked servers and Pyhton
• Quality Assurance
• Reporting of the migration results

Technologies Microsoft SQL Server (2008 R2, 2012, 2014, 2016, 2017), Transact-SQL (T-SQL), MySql, DevOps, DB2, SSIS, SSRS

Development of an enterprise business intelligence landscape

• Design of a DataWareHouse (DWH) with staging, core and reporting layers to simplify analyses, secure historical data, reduce the complexity of data sources, improve data quality and introduce a uniform reporting landscape, as well as the flexible availability of reports, statistics and key figures.
• Analysing source systems (Oracle, Salesforce, MySQL, SQL Server, CSV, EXCEL, JSON, Webhook) using SSMS, SQL Developer, MySQL Workbench and Python.
• Development of batch ETL processes to connect source systems to the staging of the DWH using T-SQL, SSIS, PowerShell, SQL Server Agent as scheduler and OneDrive as DataLake and archiving storage. Development of procedures for loading the core and reporting layers with the introduction of a delta load mechanism to optimise the performance of the routes.
• Design and development of a master data management system based on SQL Server to consolidate the various data sources and to enable organisation-driven reporting.
• Development of SQL Server Analysis Services tabular models for better analysability, enabling self-service BI on the data accessed, and implementation of a complex authorisation concept with row- and object-level security using master data and AD groups.
• Development of a wiki in Confluence to facilitate the correct use of the BI platform.
• Design and implementation of a development lifecycle process using JIRA and GITHUB to facilitate collaboration in the SCRUM process.
• Establishment of Power BI Server (OnPremise) and Power BI Service (Cloud) as a reporting tool and reporting platform with development process, app handling, workspace handling, data gateway provision and integration of reports in teams.
• Development, in cooperation with the steakholders from the specialist departments, of dashboards, reports and paginated reports in PowerBI
• Creation of PowerApps for maintaining the master data of the business intelligence landscape on the one hand, but also for processing the respective processes of the BI environment on the other.
• Design and implementation of data mining, anomaly detection and forecasting processes to optimise the planning and management of more than 60,000 employees using Python.
• Migration of the SQL Server DWH to the Azure Cloud on the basis of Azure SQL Server Managed Instance. Development and migration of ETL routes to the Azure Data Factory and Azure Data Bricks
• Adaptation of the development lifecycle process (CI-CD) with a new basis of Azure DevOps and Azure deployment pipelines.
• Migration of machine learning processes to Azure Machine Learning Services and Azure Databricks.
• Development of machine learning models using Azure Databricks, Python and T-SQL to detect anomalies and forecast potential case volumes to optimise the management of the Group
• Development of algorithms in Python and T-SQL for the detection of incorrectly delivered data using T-Test and Z-Scoring

Technologies: Microsoft Power Platform, Power BI, Microsoft SQL Server, DAX, Power Apps, ETL, Confluence, Excel, JIRA, JSON, Microsoft Outlook Office 365, Microsoft PowerShell, Azure SQL Data Warehouse, Microsoft Master Data Service, Microsoft Sharepoint, Azure DevOps, Azure Cloud, Azure Databricks, Transact-SQL (T-SQL), Oracle SQL (PL/SQL), Salesforce, Azure Data Factory, MySQL, SQL Server Agent, SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), Visual Studio, DAX Studio, Tabular Editor, GIT, Python, Azure Data Lake (Gen 2), Microsoft SQL Server Machine Learning Services

Migration of an e-commerce infrastructure from Shopware 5, Salesforce, Microsoft Dynamics CRM and Azure BI Datawarehouse to a modern cloud solution with Shopware 6, Magnolia CMS, Webfrontend, Azure Middleware (Service Bus, Functions, AD B2C, etc.) as well as SAP and Microsoft 365 Sales CRM.

• Conception and development of a dynamic and configurable migration layer in the Azure Cloud
• Development of dynamic ETL routes with Azure Data Factory as ETL tool
• Development of a complex database structure in Azure SQL Database consisting of anonymisation, cleaning, merging and export layers as well as configuration and logging objects.
• Design, implementation and testing of interfaces to REST APIs, Salesforce, SQL Server, MySQL, Azure Active Directory, Microsoft Dynamics 365 CRM and SAP
• Creation of automated tests to check the success of the migration.
• Documentation and presentation of the migration using Confluence and Power Point

Technologies: Azure Cloud, Azure Data Factory, Azure Data Lake (Gen 2), Azure SQL Data Warehouse, ETL, JIRA, Confluence, Azure Logic Apps, Kubernetes, Microsoft Dynamics, RESTful API, MySQL, Microsoft Power Platform, Docker, SAP, C#, Power Apps, Power Automate, Power BI 

• Checking compliance with the migration standards from the migration process and communicating suggestions for improvement to the specialist architect(s) of the migration project
• Creation of a mapping specification for each specialised cluster with corresponding tools
• Carrying out technical software analyses, e.g. data analysis, error analysis from migration runs, etc.
• Accompanying the necessary programming work and test activities - Creating the transformation rules (mapping specification) to transfer the data from the source system to the target system (based on the technical requirements with colleagues from the various departments and IT)
• Development of ETL processes based on the mapping specifications carried out

Technologies IBM Db2, DBeaver, JIRA, Confluence, IBM Datastage

Development of an ETL process for filling a data warehouse with Azure Data Factory

• Creation of pipelines for loading Azure Data Lake data into Azure SQL database
• Transformation and modification of the queried data with regard to required column values using Microsoft SQL and DAX (Data Analysis Expressions)
• Creation of suitable visualisations in Microsoft Power BI to display the required information
• Insert filters at page and report level to limit the time and space of the data displayed in Microsoft Power BI
• Utilisation and configuration of a framework for the automatic transformation of data within the data warehouse
• Communication with an international team

Technologies: Azure Data Factory, Azure Blob Storage (Data Lake Gen2), Azure Key Vault, Azure Pipeline, Azure SQL, T-SQL, Visual Studio, Microsoft Power BI, Azure Storage Explorer

• Analyses according to Microsoft Best Practices for the configuration of multiple Microsoft SQL Server instances
• Memory, MaxDOP, updates, backups, maintenance, etc. pp
• Advice on configuring SQL agent jobs when using AlwaysOn AvailabilityGroups
• Analysing errors in the use of database mirroring
• Consultancy for further cluster nodes and additional separation of the current nodes
• Discussion of Microsoft SQLStress test procedures
• Profiling of queries/long-running statements
• Advice on the use and application of FileStreams
• Documentation & discussion of the results

Technologies: Microsoft SQL Server (2012 - 2017), Transact SQL (T-SQL

Conception and implementation of a Business Intelligence (BI) solution with SQL Server, Oracle, PowerBI, PowerApps and Azure DevOps for a capital management company

• Carrying out detailed analyses of the source systems in the fund management area in order to identify relevant data structures, processes and requirements and to create a solid basis for data integration and preparation.
• Conception of a comprehensive data warehouse landscape based on the analysis results from the source systems in the fund management area in order to develop a scalable, efficient and sustainable data architecture for the future BI landscape.
• Development and implementation of Python scripts (packages: pyodbc, pandas, numpy) and SQL Server stored procedures for efficient merging and homogenisation of data sources (APIs) in the BI context. (ELT and ETL batch processes)
• Planning and design of a customised reporting landscape in PowerBI, based on the findings from source system analysis and data warehouse design, to provide user-friendly and meaningful reports and dashboards for the fund management area.
• Design and implementation of Entity-Attribute-Value (EAV) tables on SQL Server to provide a flexible and extensible data structure for storing and managing variable attributes and values in the context of the BI landscape (source systems, data warehouse, PowerApps and PowerBI) based on the previously acquired information.
• Development of complex KPIs such as IRR using DAX and T-SQL in PowerBI.
• Development and implementation of a PowerApps-based master data and mapping data management system that provides a central, consistent and easy-to-manage solution for the maintenance and synchronisation of master data and mappings across the entire BI landscape.
• Implementation of a queue on SQL Server to ensure asynchronous data processing and transfer between the various components of the BI landscape (source systems, data warehouse, PowerApps and PowerBI) efficiently and reliably, based on the requirements from the previous analysis and design. Banana bread
• Setup and configuration of Azure DevOps for the BI project, including the implementation of CI/CD pipelines, centralised documentation, effective project planning, source code management (repos) and automated testing to ensure an optimised, collaborative and sustainable working environment for the successful development and maintenance of the entire BI landscape (source systems, data warehouse, PowerApps and PowerBI).
• Development and implementation of customised machine learning algorithms in Python for the financial area of fund management, based on previous information and the established BI landscape, to enable forecasting, risk analysis and automated decision making to optimise investment strategies and processes. 

Technologies Azure Active Directory, C#, DAX, ETL, Excel, GIT, Visual Studio, Microsoft Power Platform, Visual Studio Code, Microsoft SQL Server, Oracle SQL, Power Automate, Power BI, Python, Azure DevOps, DAX Studio, Tabular Editor, Power Apps

Design and development of document management software for an IT service provider for law firms and lawyers

• Further development and optimisation of the existing architecture consisting of applications (VB6, C#, Web) as frontend, as well as backend on SQL server level for a landscape with over 1000 clients.
• Design and implementation of an extension for file storage with file tables, triggers, views and stored procedures
• Project planning and project management
• Communication with management and specialist departments
• Design architecture IT infrastructure
• Release management

Technologies: Microsoft SQL Server (2019), Transact-SQL (T-SQL), C# .NET, Microsoft Visual Studio, DevOps

Connection of a Progress (Open Edge) database to a specially developed DWH in SQL Server and creation of ETL routes to relieve the source system and ensure better query performance

• Creation of a basic structure on the SQL Server consisting of staging and core area using T-SQL and stored procedures.
• Creation of a Pentaho pipeline that supplies the SQL server in Delta Load with data from the Progress database via a JDBC interface. Implementation using MetaDataInjection to optimise development speed.
• Design and implementation of error handling and logging in PDI (Pentaho Data Integration) and T-SQL
• Optimisation of (PDI) ETL routes through parallelisation and execution of batch scripts via the console
• Initial analysis of the OpenEdge server (Progress SQL) using Python and the Pandas and PODBC packages
• Creation of PowerBI reports for the steakholders for the initial analysis of the data from the source systems

Technologies: Pentaho Data Integration (PDI), SQL Server Management Studio (SSMS), Transact-SQL (T-SQL), OpenEdge database, Python, pandas, Microsoft SQL Server, Power BI

• Optimisation of the SSIS route by implementing delta loads and adapting the query structure.
• Implementation of indices to optimise the runtimes of SSIS packages
• Partitioning of the SSAS cubes, customisation of the query structure of the cubes
• Optimisation of report query times (SSRS) by adapting datasets and performing precalculation at SQL server level

Technologies: Microsoft SQL Server (2019), Microsoft SQL Server Management Studio 18 (SSMS), Transact-SQL (T-SQL), Microsoft SQL Server Analysis Services (SSAS), Microsoft SQL Server Reporting Services (SSRS), Microsoft SQL Server Integration Services (SSIS) 

Application development and production support for a time recording tool for more than 120,000 users in a team of 8 people.

Development and optimisation of the existing architecture consisting of applications (C#, Web) as frontend, as well as backend on SQL server level/SQL-Light.

• Project planning and project management
• Communication with management and specialist departments
• Design architecture IT infrastructure
• Release management
• Implementation of logic changes due to legal changes in the middleware in C#
• Ensuring operations by planning quality assurance and support. Drafting test plans for the testers consisting of unit tests, user tests, key user tests, load tests and regression tests
• Creation of automated tests in Azure DevOps
• Design of a reporting level in Power BI to present the data to the stakeholders
• Implementation of security policies through AD groups in the web interface and view layer in the SQL server
• Release management for more than 120,000 clients

Technologies: Microsoft SQL Server (2014, 2016, 2017, 2019), Transact-SQL (T-SQL), Power BI, NHibernate, C# .NET, Microsoft Visual Studio, XML, Mantis, Testlink, GitTea, Jenkins, Azure DevOps, JavaScript, HTML

Server and configuration analysis of an existing cluster environment including regular performance optimisation of a Microsoft Dynamics AX environment

• Server analysis of a current cluster environment
• Server settings
• Data backups
• Integrity checks
• TempDb/ Growth databases
• Indices/ Statistics
• Blockages/ DeadLocks/ Waiting times
• Additional database options & configurations
• Cleaning up incorrect/defective maintenance plans
• Preparation of a new maintenance solution based on a new database and utilisation of the Ola Hallengren solution
• Implementation of policy management to monitor the database status
• Performance analysis of long-running queries and development of solutions
• Maintenance analysis and work around indices + statistics
• Analysis and optimisation of execution plans with the help of PlanGuides
• Configuration of an additional maintenance solution based on the Ola Hallengren scripts

Technologies: Microsoft SQL Server 2012, Transact SQL (T-SQL) 

Migration and decommissioning of Dynamics 2013 and Dynamics 365 CRM systems to Salesforce

• Development of Python scripts to export data in the standardised export format for the migration of Microsoft Dynamics 365 CRM data from Microsoft Dataverse to Salesforce
• Dynamic control of the amount of data queried from Microsoft Dataverse within the Python scripts to fulfil the 80MB query limit
• Implementation of dynamic parameterisation of Python scripts to map requirement adjustments with regard to business mapping
• Setting up an Azure SQL database as a long-term backup solution for a Microsoft Dataverse
• Development of an ETL route with Python to fill the Azure SQL database from Microsoft Dataverse
• Mapping of the Microsoft Dataverse database structure with primary and foreign key constraints in the Azure SQL database
• Export and transfer of attachments from Microsoft Dynamics 365 CRM (Dataverse) - Development of Powerapps to implement the mass configuration of all users of the Microsoft Dynamics 365 environment
• Migration of Dataverse data to an SQL Server database to enable the shutdown of Dynamics 365 Using the Azure Data Factory as an ELT tool
• Development of Power BI reports to visualise and review the migration progress and progress as well as for final quality assurance after migration to Salesforce

Technologies: Python, bcp (Bulk Copy Programme), Microsoft SQL Server, Salesforce, Power Apps, Power BI, Microsoft Power Platform, Microsoft Dynamics, Microsoft Dataverse, Azure Data Factory 

• Requirements analysis and agreement on prioritisation of the required use cases
• Cases
• Initial provision of all existing data from the source system
• Set up the required infrastructure for dev, test and prod in Azure: Databricks,
• Storage Accounts, Key Vaults, Data Factories and Power BI work areas
• Implementation of ELT routes for individual use cases with Azure Databricks
• notebooks, and regular provisioning via Databricks jobs in SQL
• Warehouse
• Creation of the corresponding repositories and connection to the repositories used
• Services in Azure DevOps
• Coordination of tasks with the help of Azure DevOps
• Provision of data via an SQL warehouse in Azure Databricks for Power BI reports and data flows
• Automated deployment via Azure DevOps Pipelines
• Administration of Power BI workspaces, provision of key figures and
• Measures for Power BI reports and use of Power Automate for dispatch
• of emails when critical values of the key figures are reached