Chat GPT leads to project success – artificial intelligence supports ETL modernisation at nwb Verlag

nwb Verlag has been a valued customer of noventum for many years. In the publishing house's BI landscape, it worked classically with SQL Server, SSIS and SSAS - in our established DWH layer model, but not yet with the noventum Data Platform Accelerator (nDPA) and therefore with inflexible, layer-based loading via nested SSIS packages. The aim of a current project was to introduce the nDPA Orchestrator in order to modernise existing ETL processes and make them more flexible. However, before the orchestrator could begin its work, a tedious task had to be completed: the dependency analysis of around 500 SSIS packages.

In this initial situation, our project team resorted to an unusual tool: ChatGPT. And lo and behold - with the right prompt engineering, artificial intelligence became a valuable project assistant.

Starting point: lots of packages, little structure

nwb Verlag had several SSIS projects, two of which ("DWH" and "BooX") were in focus. Contrary to the original estimate of around 230 packages, it turned out that around 480 SSIS packages had to be analysed - a mixture of complex constructs with lookups and SQL tasks, but also many simple, recurring patterns. In addition, the final processing of eight SSAS databases had to be taken into account.

Previously, the loading logic was strictly layer-based (DAL → DIL → DPL), orchestrated via nested SSIS master packages. Errors in one of the lower layers meant that entire layers had to be reloaded - an effort that the nDPA Orchestrator should drastically reduce in future.

The challenge: identifying dependencies

The central task for integration into the Orchestrator was the manual identification of tasks and their data and sequence dependencies. Approaching this task manually means that each SSIS package must be opened, analysed and transferred to the metadata model of the nDPA Orchestrator – a time-consuming and error-prone process that quickly reaches the limits of the project budget when dealing with hundreds of packages. This is a particularly thankless task for the many simple, recurring patterns.

The idea came up early on: ‘There must be a smarter way to do this.’

The game changer: ChatGPT as an assistant for tedious work

While a team of developers at nwb was already preparing a Python script to analyse the DTSX files, the noventum team came up with the idea of using ChatGPT for support – initially on an experimental basis, but then with growing enthusiasm.

The goal was to develop a prompt that would enable ChatGPT to perform structured analysis of SSIS XML files. And to do so in such a way that deterministic, locally executable Python code could be generated – without sensitive customer data being transferred to the cloud in any form.

The key feature: instead of letting ChatGPT generate the Python script directly, a detour was taken via the description of the parsing logic – with a high level of detail. The prompt was refined in several iterations: from the description of the XML tags to be examined, to the structure of the desired Excel output, to concrete examples. The result: a ‘once and done’ prompt that has proven itself in practice.

The implementation: efficient and data protection compliant

Once the GPT-supported prompt had been finalised, a robust Python script was generated in the same context, which was executed locally on the customer's SSIS packages – without any data leakage to ChatGPT. The script extracted target and source tables, lookups and SQL commands from all packages and prepared them in an Excel overview. The dependency analysis based on this could then be carried out largely in Excel – without the tedious ‘double-clicking’ in Visual Studio and subsequent documentation of the dependencies found.

The success of the project speaks for itself: originally, the analysis of around 250 packages was planned. Thanks to automation using ChatGPT and Python script, all of the nearly 500 packages were processed in the same amount of time.

The key insight from the project: ChatGPT is not a magic wand – but it is an excellent assistant when used correctly.

The project was successful because:

• the task was text-based/token-based (XML, SQL),
• sufficient time was invested in prompt engineering,
• the approach was not to ‘solve everything automatically’ but to ‘provide targeted support with specific instructions’.

The approach of using ChatGPT to develop a solution via prompting and then generating a first version of a Python script that only needed fine-tuning is exciting. For a developer without in-depth Python experience, this approach is much easier than starting from scratch with ChatGPT-supported Python coding.

Best practice: prompt engineering as the key to success

A decisive factor for success was the systematic approach:

1. Create context – detailed description of the task, input structures and target results.
2. Iterate – gradually improve prompts, incorporate feedback.
3. Understand instead of guess – use ChatGPT as a partner in dialogue, not as a one-shot solution.
4. Validate output – critically review results and follow up with additional research if necessary.

The latter is particularly important in order to avoid so-called ‘hallucinations’ of the AI – i.e. plausible but factually incorrect statements or, in this case, code that does not lead to the desired result.

Outlook: Further application scenarios for AI in data projects

The experience gained in the project with nwb Verlag shows that ChatGPT and similar AI tools can be helpful not only for specific tasks such as coding or parsing. They are also ideal as interactive learning and research tools – especially when you want to explore new topics such as Python.

A key aspect here is the methodological approach: by developing a solution using well-structured prompting, a complex problem can be broken down step by step into a solvable sub-task – without any direct expert knowledge. This way of working allows you to create functioning solutions (e.g. initial Python scripts) even without in-depth prior knowledge, which can then be refined. This is often more effective than starting directly with classic coding, especially for beginners.

A particularly recommended article on this topic is ‘Myths, Magic and Copilot for Power BI’ by Kurt Buhler. It clearly describes the role that your own contextual knowledge plays in the successful use of LLMs and the typical pitfalls that lurk when creating prompts. Anyone who wants to delve deeper into the interplay between AI and BI development will find lots of valuable information in this article.

There are countless other promising areas of application in the data environment. Here are just a few specific use cases from our practice:

• Support with coding (SQL, DAX, Python, C#, etc.)
• Analysis of tenant settings from HTML sources (e.g. from official Microsoft documentation)
• Copilot for Power BI
• Structured generation and evaluation of DAX formulas in Power BI
• Improvement of semantic models through Copilot-optimised conventions and linguistic schemas
• Creation of reports and visualisations as a starting point for manual further development
• Generation of test data
• etc.

When using ChatGPT, it is important not to become ‘lazy’ and not to expect magical results at the touch of a button. Those who simply write a short prompt and hope for a perfect result will usually be disappointed. Instead, you need to familiarise yourself with the subject matter and the way LLMs work, both technically and methodologically. Only if you understand how to formulate precise and context-rich prompts and critically question the results can you truly exploit the strengths of AI systems. It is only through this active engagement that results emerge that are convincing in terms of quality and deliver real added value.

The quality of the results is therefore always highly dependent on the quality of the prompts and the expertise that goes into their development. AI is not a substitute for knowledge, but rather an accelerator for sound work.

Conclusion

In the project with nwb Verlag, ChatGPT proved to be a valuable tool for automating time-consuming tasks and massively accelerating the introduction of the nDPA Orchestrator. The key to success lay in the combination of clear goal definition, a methodical approach and a deep understanding of our own processes – supported by AI that performs at its best with the right prompt.