Free Deep Dive "Digitising German SMEs Together" on 26.10. Register now
The aim of the project was the continuous optimisation and extension of the information system developed by the client. As part of the further development of the client-server information system, I developed the necessary database accesses in cooperation with Java developers. New queries and stored procedures were developed. In the context of change requests, I adapted the existing database model according to the requirements. In addition, I optimised existing accesses.
Technologies: Oracle Database System 12c, PL/SQL, Quest Toad, SQL Developer, PL/SQL Developer, Git, Eclipse, Java, Maven, Scrum, Atlassian Jira, Atlassian Confluence
Recording of requirements and development of a target image. Analysis of the requirements and identification of the source systems. Design and implementation of an SQL database as the technological basis of an analytics solution for data from the identified source systems. Design and development of the layer model for the analytics solution with staging and data mart layer. Development of the design patterns of the ETL routes and tools, as well as implementation. For the design of the solution architecture, the analytics technology stack from Microsoft was used in the form of:
Technologies: Microsoft on-premise BI stack (SQL Server, SSIS), Power BI, Balsamiq, GitLab, Confluence, Jira
Continuous further development and optimisation of the existing data warehouse. Among other things, loading routes of the core from different source systems were developed and optimised. The provision of data marts from the core system was also carried out. The performance of different database accesses was also optimised.
Technologies: Microsoft Database System (2016), SQL Server Management Studio, Azure Data Studio for SQL Server, SQL, T-SQL, Automic, SVN, Atlassian Jira, Atlassian Confluence
Within a project team, the existing system is replaced by a new data warehouse. Here, the existing and new data must be analysed and transferred into a new model based on the customer requirements. The following tasks were taken on within the team:
Technologies: IBM DB2, Toad for DB2, DB2 SQL, Oracle Database, Toad for Oracle, Jira/Confluence, PowerDesigner, HP Quality Center, JetBrains IntellJ
The aim of the project was to continuously ensure the data quality of the data warehouse and to optimise the loading routes. In an existing data warehouse on Oracle technology, among other things, the loading operation was ensured and support cases were also processed in this context. Furthermore, developments and optimisations of ETL routes were carried out with the help of the OWB. Maintainability was ensured through documentation in Confluence.
Technologies: Oracle 11g R2 / 12c Enterprise Edition, SQL, PL/SQL, Oracle Warehouse Builder (OWB), Atlassian Jira, Atlassian Confluence
The client requires a conceptual design of the processes in the area of financial accounting, support and assistance in the area of data migration technologies:
Industry: Agriculture, forestry and fisheries, horticulture
An existing self-designed solution for a reporting portal is to be replaced by SQL Server Reporting Services. In addition, new key figures are to be identified and extended on the SQL Server Reporting Services 2016. The existing application is based on FLEX-Web. FLEX-Web is a framework that uses Java Beans, HTML and the Flashplayer. Furthermore, PL/SQL packages and Oracle 11g R2 were used for the database development. The self-developed HTML and Java Beans components are to be replaced by SQL Server Reporting Services 2016.
For this purpose, a requirements analysis is carried out and based on this, a target architecture is presented and agreed. As a result, the reports will be redeveloped with the Reporting Services from Microsoft. Downstream, the possibilities of the Reporting Services 2016 will be discussed and, if necessary, further reports, dashboards and KPIs will be created.
Technologies: PL/SQL, Oracle 12c, SQL Reporting Services 2016, SQL Server Integration Services, SQL Server Report Builder 2016, SQL Server Publisher for mobile Reports 2016, SQL Server Data Tools 2015, T-SQL, SQL Developer, Java Beans
A business process that was previously not system-supported was to be digitalised and optimised with the help of SAP BPM technology. Furthermore, a possibility was to be created for the specialist department to carry out process controlling.
Technologies: Microsoft Office, SAP Business Process Management, ARIS Toolset, Balsamiq Mockups
The analysis of the AS400 source data and the planning and implementation of the data warehouse environment were the first steps in the project. Furthermore, coordination with the departments regarding the reporting requirements had to be carried out.
The challenge in the project was to understand the complex data structures of the AS400 in terms of content and to transfer them into a clean data model.
The solution offers 120 users access to current sales figures and important analytical questions in the purchasing process.
Tasks:
Technologies:
Arrange data deliveries to Azure Data Lake from sources like SAP, CRM...etc. Maintain data quality in different layers in the Data Lake Transfer curated data to SQL DB and test it. Implement the data model in SQL DB if required. Connect Power BI to SQL DB and generate reports required by the end user. Prepare technical design documents and develop the system. Interact with stakeholders to obtain business requirements and discuss application functionality. Plan data pipelines and monitor data movement from source to destination. Create views as feed for Power BI report and design them for data stories. Ensure data issues or validation problems are resolved.
Technologies: SQL Server 2016, Power BI, Reporting Services, Azure Data Factory, Azure Data Lake, Blob Storage, Cosmos DB, Azure SQL, Power BI, SCRUM, Azure Synapse, Databricks.
In an existing DWH based on Oracle technology, the following tasks were taken over:
Technologies: Oracle 11g R2 / 12c Enterprise Edition, SQL, PL/SQL, Oracle Warehouse Builder
In an existing data warehouse based on Oracle technology, the following tasks were taken over:
Technologies: Oracle 11g R2 / 12c Enterprise Edition, SQL, PL/SQL, Oracle Warehouse Builder (OWB)
The aim was to provide a central staff portal. It should provide role-specific content according to needs.
Technologies:
In an existing data warehouse based on Oracle technology, the following tasks were taken over:
Technologies: Oracle 11g R2 / 12c Enterprise Edition, SQL, PL/SQL, Oracle Warehouse Builder (OWB), Oracle GoldenGate (OGG), PowerDesigner, JIRA/Confluence
In an existing data warehouse on IBM technology, the following tasks, among others, were taken over in operations/monitoring:
Technologies: IBM DB2, Toad for DB2, DB2 SQL, Putty, Unix/Linux (Shell), Grafana, IBM InfoSphere DataStage, JobScheduler, Sharepoint, Jira/Confluence
The existing business processes are analysed and documented with regard to a later transfer to an ERP system.
Technologies: Process flow diagrams, requirement catalogues
Industry: Agriculture, forestry and fisheries, horticulture
An existing SQL server instance, which served as the basis for a reporting solution, was moved from a non-clustered instance to a clustered instance, including all components:
Technologies:
Industry: Public legal entity
The aim was to grant customer payments a discount at an affiliated petrol station depending on the monthly turnover in a hypermarket. A customer card was introduced for the use of this programme.
For this purpose, it was necessary to analyse the receipt data stored in the DWH system on the basis of the customer card ID and to transfer the data to a discount server, which supports the transactions at the petrol station for customers with a customer card in the discount determination.
For controlling purposes, a standard reporting system was created in the Controlling and Marketing departments.
Technologies:
The data storage layer of a provisioning application of an insurance company is moved to a cloud service provider. This requires a platform change from Oracle to SQL Server.
The following tasks were undertaken:
Technologies: SQL Server 2014, SQL Developer, Oracle 11g R2, SQL Server Management Studio, T-SQL, PL/SQL, SSIS, Windows Server 2012, SQL Server Migration Assistant
In a current BI application of the customer, which reports subsidies from state and EU funds to applicants, the manual processes for the provision of basic data from different upstream systems are to be replaced.
The project is implemented through fully automated processes using SSIS packages. In addition to relational data modelling in the BI database for recording basic data, the connection to other SQL server systems and an Oracle system is implemented.
The new SSIS packages are started via trigger and time-controlled scheduling processes and keep the basic reporting data up-to-date on a daily basis. An independent reporting system was created especially for the integration project of the basic data, which presents the result of the integration of the basic data to the business users. Errors that occur during data processing in this area can thus be recognised promptly and corrected at an early stage.
TechnologiesSQL Server 2008. Business Intelligence Studio, SSIS, SSRS
Industry: Public body
The CRM system already proven at the client had to be adapted to internal reorganisations.
For this purpose, processes were analysed, entities reconfigured and new users integrated in a targeted manner with individual training. Examples of challenges were the familiarisation with the previously externally administered system and the subsequent integration of databases that had previously been maintained separately and redundantly. With the help of this project, the productive CRM system was adapted to the new conditions, the circle of users expanded and data maintenance simplified.
Tasks:
Technologies: Microsoft System Center, Microsoft Dynamics CRM, Microsoft Sharepoint, Office 365
Analysis, conception and extension of new and existing processes. Development and further development of WebDynpro Java interfaces and SAP Business Rules. Optimisation and design of various database models.
Technologies:
Recording business-relevant company processes and mapping suitable implementation options for a planning environment in Jedox. Modelling of a holistic solution in the form of several data cubes. Extraction of relevant data from the pre-system SAP and different CSV files. Data integration into the cubes through several data transformation processes. Final mapping of the planning options through a coordinated reporting system.
Technologies: Jedox, SAP by Design
In an existing data warehouse based on Oracle technology, the following tasks were taken over:
Technologies:
Key figures defined by the subject are to be analysed for feasibility and implemented. The data from different OLTP systems will be analysed and merged to create a data basis, and fields will be added if necessary. The key figures are to be presented to the management level in a highly aggregated form on different dashboards. Drill-down functionalities will be used to provide a detailed insight. The data will be extracted using ETL tool support and the presentation of the key figures must be responsive and dynamic. To increase performance, existing queries are to be optimised and indexing strategies are to be designed.
Technologies: SQL Server 2014, SSRS 2016, SSIS, SQL Server Report Builder 2016, SQL Server Publisher for Mobile Reports 2016, SQL Server Data Tools 2015, Team Foundation Server (TFS), SQL Developer, Paginated Reports, Mobile Reports
Due to new and changed external interfaces in the electronic trading environment, these had to be integrated into the existing DWH landscape. In my role as a developer, I managed this process in the areas of creating (adapting) OWB mappings, creating tables and in the final documentation.
Technologies: Oracle Database System 11g & 12c, Oracle Warehouse Builder 11g, PL/SQL, Quest Toad, Sybase PowerDesigner, Redwood Cronacle, SVN, SAP Crystal Reports
In an existing data warehouse based on Oracle technology, the following tasks were taken over:
Technologies: Oracle 11g R2 Enterprise Edition, PL/SQL, Oracle Warehouse Builder, Oracle Golden Gate (OGG)
In an existing data warehouse based on Oracle technology, the following tasks were taken over:
Technologies: Oracle 12c Enterprise Edition, SQL, PL/SQL, JIRA/Confluence, Amazon RDS (AWS Cloud), IBM Notes, Unix, SQL*Plus, SQL Developer/Modeller, Scrum, LDAP / Apache
Continuous further development and optimisation of the existing reporting server. Among other things, loading routes of the core from different source systems were developed and optimised. The provision of data marts from the core system was also carried out.
The performance of different database accesses has also been optimised.
Technologies:
To support the DWH operation, a tool was to be developed that determines all dependencies in the job processing of the DWH from a certain start node. A PL/SQL package was developed that takes over the following functionalities:
Technologies: Oracle 11g R2 / 12c Enterprise Edition, SQL, PL/SQL, Oracle Warehouse Builder (OWB), Graphviz
Within a project team, the existing system and reporting are replaced by a new data warehouse. Report requirements are defined together with the specialist department and included in a requirements engineering process. The requirements are prepared for reporting. The following tasks were taken on within the team:
Technologies: SQL Server 2017/2019, Integration Services, T-SQL, Power BI
Task: Design and implementation of solutions based on Oracle Analysis of existing PL/SQL solutions Implementation of requirement specifications from the specialist departments Implementation of front-end applications by means of
Evaluation of BigData concepts based on Hadoop
Technologies: Oracle 12c Enterprise Edition, SQL, PL/SQL, Microsoft SQL Server, SSAS, SSRS
A web application for creating and evaluating feedback forms was to be created. With the help of this application, feedback on internally conducted seminars and workshops is collected in a bundle. The feedback for participants can be personalised or anonymised.
Technologies: Microsoft Active Directory, Eclipse, Java, Spring Web MVC Framework, DHTMLX UI Library, Oracle, Apache Tomcat
A monitoring report for the SOC area was developed. The requirements were recorded. Based on the requirements, the data basis and the data model were developed. Filter options were added and the corporate design was implemented.
Technologies: Power BI, DAX, API, OData, Kusto Query Language
Industry: Management consultancy
Arrange data deliveries to Azure Data Lake from sources like SAP, CRM...etc. Maintain data quality in different layers in the Data Lake Transfer curated data to SQL DB and test it. Implement the data model in SQL DB if required. Connect Power BI to SQL DB and generate reports required by the end user. Prepare technical design documents and develop the system. Interact with stakeholders to obtain business requirements and discuss application functionality. Plan data pipelines and monitor data movement from source to destination. Create views as feed for Power BI report and design them for data stories. Ensure data issues or validation problems are resolved.
Technologies: SQL Server 2016, Power BI, Reporting Services, Azure Data Factory, Azure Data Lake, Blob Storage, Cosmos DB, Azure SQL, Power BI, SCRUM, Azure Synapse, Databricks.
Introduction of Microsoft Dynamics CRM Online as a tool for customer management, sales opportunity management and employee management. In addition to the classic requirements for customer management, company-specific requirements for employee management had to be integrated.
For this purpose, the client's business processes were specifically optimised and modular software was integrated into the CRM system. In order to implement employee management, among other things, more customer-specific views were created, which are automatically filled from the CRM system.
With the introduction of an XRM system, the customer has a uniform tool that enables seamless customer and sales opportunity management as well as supports them in employee management.
Tasks:
TechnologiesMicrosoft Dynamics CRM Online 2015/2016, MS Reporting Services
Recording of requirements and development of a target image by means of workshops and wireframing.
Analysis of the requirements and identification of the source systems. Design and implementation of an SQL database as the technological basis of an analytics solution for data from the identified source systems. Design and development of the layer model for the analytics solution with staging and data mart layer. Development of the design patterns of the ETL routes and tools, as well as implementation. The analytics technology stack from Microsoft was used for the design of the solution architecture.
Within a task force, selected business-critical reports are to be implemented promptly on the existing status following a critical project to replace a DWH by a service provider on the basis of a DB2 database. There is not yet a DWH with integrated data that could be a reliable source. Therefore, the reports are to be built on the ODS (Operational Data Store), which has been implemented in larger parts. The task of data analysis was taken over with additional business analysis activities. This included the development of the data selection for the report developers, which was recorded within a design document. In the data analysis, some of the logics of an ETL process were also shifted to SQL scripts in order to do justice to the integrity and homogeneity as well as the temporal correctness of the reports.
Technologies: IBM DB2, Toad for DB2, DB2 SQL, Jira/Confluence
Creation of reports for Microsoft Dynamics CRM Online to generate receipts and enable customer-specific evaluations:
As the standard Microsoft Dynamics CRM reports were not sufficient for the client, multi-layered reports had to be developed. Based on the specified requirements, new report forms for the CRM system were designed and several complex database queries were written. The resulting database was prepared in a further step using Visual Basic functions. Through the automated database query and integrated data preparation, the customer is able to retrieve his individual reports via the Microsoft reporting function.
The tasks included, among others:
Technologies: Microsoft SQL Server Data Tools, MS Reporting Services
Delegation of the administration of groups and users of the internal Active Directory. The dedicated administrators should be given the opportunity to make changes in the Active Directory (group memberships, group descriptions, management of shared user attributes) that are relevant to them in the simplest way possible.
The tasks were to be carried out without special client tools. Each dedicated administrator was only allowed to manage the groups and users assigned to him.
Technologies: Microsoft Active Directory, Eclipse, Java, Spring Web MVC Framework, DHTMLX UI Library, Oracle, Apache Tomcat
Requirements-based design and implementation of an Azure SQL database as a sink for data from a cloud ERP system. Design and development of the layer model for the reporting solution with extraction and core layer. Development of the design patterns of the ETL routes and tools, as well as implementation. For the design of the solution architecture, the analytics technology stack of Microsoft Azure was used in the form of:
Technologies: Azure SQL Database, T-SQL, Azure Data Factory, REST, OData, Power BI
Implementation of predefined task packages in Microsoft SQL Management Studio and Microsoft Visual Studio. Adaptation of tables, procedures and key figures in the Microsoft Tabular Model.
Technologies: Microsoft Analysis Services, Power Bi, SQL Management Studio, SourceTree, Jira, Git
The BI tool Qlik Sense is introduced for the first time. Different dashboards were developed for the management, marketing and purchasing departments. In addition, separate dashboards were made available for individual task areas and employees. In cooperation with the users, adjustments and requests to the dashboards were implemented. Training sessions were held for the users. I explain the special features directly live in the BI tool and give tips and tricks. Detailed documentation with pictures was written. The users can use this as a reference book. The data modelling was realised directly in the script.
Technologies: QLIK Sense, ETL, QMC, QLIK Advanced Set Analysis
Industry: Trade
Permanent assignment for a long-standing partner. Analysis of the department's requirements.
Conception of the proposed solutions and realisation of the concept.
Realisation in the Microsoft BI stack
With the help of this training, the participant has a very good foundation for Power BI.
This enables him to import data independently, prepare it and transfer it into his own data model. In addition, the first own DAX key figures can be written and reports created. The workshop will last 2 days. It is assumed that the participants have no knowledge of Power BI. The first day will cover the basics, the second day will go into more detail, especially with DAX. Thus, one starts with the basics such as the installation and the interface of PBI.
Agenda
Day 1
Day 2
Analysis and recording of the requirements for the system, according to the target image of the solution. Based on this, design and implementation of an event database as the sink of a speed layer for near-realtime data processing, with the ability to potentially also process realtime data.
For the design of the solution architecture, Microsoft's BI technology stack was used in the form of:
The following tools were used to document the requirements:
Technologies:
In an existing data warehouse based on Oracle technology, the following tasks were taken over:
Technologies: Oracle 11g R2 / 12c Enterprise Edition, SQL, PL/SQL, Oracle Warehouse Builder (OWB), Oracle GoldenGate (OGG), PowerDesigner, JIRA/Confluence
Within a project team, selected marketing campaigns have to be implemented. The required KPIs were defined in advance by the marketing department. The data basis for determining the KPIs is an ODS (Operational Data Store) based on a DB2 database. Other data systems (e.g. Microsoft SQL) are used as required. The KPIs are loaded into an Oracle database via ETL routes. With the help of the consolidated data, the marketing campaigns are built and delivered using SAS.
Technologies: IBM DB2, Toad for DB2, DB2 SQL, Oracle Database, Toad for Oracle, Oracle PL/SQL, SQL Server Integration Services (SSIS), Jira/Confluence, Git
Dashboards are developed for various security areas.
Tasks include data consolidation and data modelling in Power BI.
In addition, the corporate design and company logo can be easily implemented.
Technologies: Power BI, DAX, API, OData, Kusto Query Language
As part of its own digitalisation strategy, the customer is introducing SAP Business ByDesign as the "Digital Core" for its own business processes and is thus replacing various legacy systems. After the takeover of the FIN sub-division and a successful go-live, the customer was supported in the further course of the migration of legacy data and general system support.
Also project management support and expansion of the scope of services in the first 6 months of technologies:
Role: Sub-project management and consultant
Industry: Advisory service
Presentation of customer-relevant requirements via the creation of mock-ups. Project coordination and key figure definitions via the Jira and Confluence platforms.
Creation of several interactive dashboards with different thematic focuses in the area of human resources controlling.
Technologies: Power BI, Balsamiq, Confluence, Jira, SQL Management Studio
Industry: Trade
Within a task force, selected business-critical reports are to be implemented on the basis of a DB2 database. Since no DWH with integrated data is available as a reliable source, the challenge is to build the reports on top of the ODS, which has been implemented in large parts. The main task is to carry out the data analysis. This includes, for example, the development of the data selection, which is recorded within a design document. In the data analysis, some of the logics of an ETL process are also shifted to SQL scripts in order to do justice to the integrity and homogeneity as well as the temporal correctness of the reports. In addition, business analysis activities are taken over. This includes, among other things, the collection of technical requirements and the coordination of the developers involved in the development process.
Technologies: IBM DB2, Toad for DB2, DB2 SQL, Jira/Confluence
Extraction of budget and forecast figures from various source systems for integration into Jedox. Preparatory data modelling and development of suitable ETL processes for mapping consolidated business reports. Development of dynamic reports for various use cases and the consideration of different end user groups.
Technologies: Jedox, Excel, SQL Management Studio
Within a project team, selected marketing campaigns have to be implemented. The required key figures were defined by the marketing department. The data basis for determining the KPIs is an ODS (Operational Data Store) based on a DB2 database; other data systems (e.g. Microsoft SQL) are used as required. The KPIs are loaded into an Oracle database via ETL routes. The consolidated data is used to build and deliver marketing campaigns using SAS. The following tasks were undertaken within the team:
Technologies: IBM DB2, Toad for DB2, DB2 SQL, Oracle Database, Toad for Oracle, Oracle PL/SQL, SQL Server Integration Services (SSIS), Jira/Confluence, Git
In this project, the SAP HANA platform is to be evaluated according to its potential and areas of application. Furthermore, an SAP HANA (on-premise) environment is to be set up for internal training and test scenarios.
Technologies:
Processing support requests, especially in the area of FIN; but also beyond that in the area of project management and HR technologies: SAP Business ByDesign
Industry: Divers
The aim was, on the one hand, to identify optimisation potential in an existing DWH and, on the other hand, to prepare for the takeover of operations by an external service. During the activities, error analyses were carried out and corrections were proposed and implemented in daily operations. Development work on ETL routes was partly taken over. Furthermore, the existing documentation was to be expanded and improved in order to make the operational processes more transparent and understandable for third parties.
Another goal - apart from creating the framework conditions for a takeover by other persons - was to be able to present suggestions for improvement and a kind of blue print for the operation of the new DWH of the company, which is currently under development, in order to avoid mistakes that have been made and to use optimised processes from the beginning.
Technologies: SQL Developer, Oracle 11g R2, PL/SQL, Unix, OWB
Analysis and recording of the requirements for the system, according to the target image of the solution. Based on this, design and implementation of an event database as the sink of a speed layer for near-realtime data processing, with the ability to potentially also process realtime data.
For the design of the solution architecture, Microsoft's BI technology stack was used in the form of:
The following tools were used to document the requirements:
Technologies: SQL Server 2016, T-SQL, Microsoft SQL Server, SSAS, SSRS, Microsoft Power BI Reporting Services, Power BI
Design and implementation of an Azure SQL database as a sink for data from a cloud ERP system based on existing requirements.
Design and development of the layer model for the reporting solution with extraction and data mart layer.
Development of the design patterns of the ETL paths and tools, as well as realisation. The analytics technology stack of Microsoft Azure was used for the design of the solution architecture.
Supervision of the client's ongoing financial accounting and support of in-house accounting Technologies: SAP Business ByDesign
Industry: Plant Engineering
An existing KPI reporting system is to be completed in a first step in order to implement all existing requirements. Subsequently, a reporting system is to be implemented that corresponds to the solution architecture of the internal BI systems in order to implement a homogeneous design procedure within the BI department of the customer.
For the design of the solution architecture, Microsoft's BI technology stack was used in the form of:
Technologies: SQL Server 2016, T-SQL, Microsoft SQL Server, SSAS, SSRS, Power BI
Support in the CRM and business productivity environment:
The CRM system, which had already proven itself with the customer, had to be adapted to internal reorganisations. For this purpose, processes were analysed, entities reconfigured and new users integrated in a targeted manner with individual training. Examples of challenges were the familiarisation with the previously externally administered system and the subsequent integration of databases that had previously been maintained separately and redundantly. With the help of this project, the productive CRM system was adapted to the new conditions, the circle of users expanded and data maintenance simplified.
Tasks:
Technologies: Microsoft System Center, Microsoft Dynamics CRM, Microsoft Sharepoint, Office 365
Nutze unseren KI-Bot, um gezielt Fragen zu diesem Dienstleister zu stellen, Inspiration für dein Projekt zu sammeln oder passende Alternativen zu finden. Schnell, einfach und rund um die Uhr für dich da!
English 
German