# Data Vault AI Help & Advisory

The Data Hopper EDW includes an AI-powered assistant (the **AI Help** or **AI Advisor**) designed to accelerate enterprise data warehousing modeling and integration. Data Vault, Business Vault, and dimensional modelers open the Hop **AI Assistant** workbench bound to the open model. The assistant can analyze catalog sources, validate models, suggest layout updates, perform type mappings, build hubs, links, and satellites, and troubleshoot integration errors. Source models, lineage views, execution maps, and the **EDW Journey** perspective also have **AI Help**; those four are **chat-only** (no apply).

![Data Vault AI Help Dialog](images/data-vault-ai-help-dialog.png)

## External AI agents (Gemini, etc.)

When using an **external** model (outside Hop GUI) to edit project files, attach the portable schema pack:

- **[ai-file-schemas/README.md](ai-file-schemas/README.md)** — XSD for `.hdv` / `.hbv` / `.hdm` / `.hsm`, JSON Schema for plugin metadata and catalog sources, plus purpose/anti-pattern markdown.

Start sessions with `conventions.md` + the file-type docs that match the edit. Prefer modifying existing samples under `retail-example/` or `integration-tests/` over inventing structure.

---

## Prerequisites & Installation

To use the AI Helper:
1. Install Apache Hop **2.20** (development **2.20.0-SNAPSHOT**) with the **AI Assistant** plugin (`plugins/tech/ai`). Removing that plugin makes AI Help a no-op; hopper-edw still loads.
2. Create a named **AI Provider** under **Metadata** and enable AI under **Configuration → Plugins → AI Assistant**.
3. Open a `.hdv` / `.hbv` / `.hdm` / `.hsm` / `.hlv` / `.hem` graph (or the **EDW Journey** perspective) and click **AI Help** (floating workbench). Pipeline and workflow AI Help is Hop's, not this plugin.

> **Note:** Older hopper-edw `hopAiConfig` keys (API key, preset, model name) are migrated by Hop to an **AI Provider** metadata object on first GUI start.

---

## LLM Provider Configuration

The AI Advisor supports various LLM providers using standard Hop Language Model Chat adapters. Select a provider preset or configure a custom one:

| Provider / Preset | API Base URL (Default) | Model Name (Default) | Key Requirement |
| :--- | :--- | :--- | :--- |
| **Grok (xAI)** | `https://api.x.ai/v1` | `grok-4` | API key from [console.x.ai](https://console.x.ai) |
| **OpenAI** | `https://api.openai.com/v1` | `gpt-4o-mini` | API key from [platform.openai.com](https://platform.openai.com) |
| **Google (Gemini)**| `https://generativelanguage.googleapis.com/v1beta/openai` | `gemini-2.5-flash` | API key from [Google AI Studio](https://ai.google.dev/) |
| **Anthropic** | *(Uses default endpoint)* | `claude-3-5-sonnet-20241022` | API key from Anthropic console |
| **Mistral** | `https://api.mistral.ai/v1` | `mistral-small-latest` | Mistral API Key |
| **Ollama** | `http://localhost:11434` | `llama3.2` | Local Ollama instance (No API key needed) |
| **Hugging Face**| *(Uses default endpoint)* | *(Varies)* | Hugging Face Hub Token |
| **Custom** | Specify your custom endpoint | *(Varies)* | API key or token as required |

---

## How to Use AI Help

### Data Vault modeler

1. Open your Data Vault model (`.hdv`) in the visual modeler canvas.
2. Click **AI Help** on the modeler toolbar (next to **Check model**), or right-click the canvas and choose **AI Help** under **Help**.
3. Select an advisory **Scenario** corresponding to your goal.
4. Set your **Context Inclusions** (described below) and type your question in the prompt input field.
5. Click **Send**. The advisor runs the prompt in the background, updating the conversation transcript when a response is ready.

### Pipelines and workflows

Pipeline and workflow **AI Help** supports multi-turn advisory chat and, when the model suggests concrete edits, **structural proposals** you can review and apply to the open graph. See [Pipeline & Workflow AI Help — M2](plans/hop-ai-assistant-m2.md) for proposal types, validation rules, and bundled Hop standards.

Open a pipeline (`.hpl`) or workflow (`.hwf`) and use:

- **AI Help** on the graph toolbar
- Right-click the canvas → **Basic** → **AI Help**
- Right-click a transform or action → **Basic** → **AI Help** (sets focus on that node)

**Pipeline scenarios:** General, Transform selection, Error diagnosis, Pipeline design.

**Workflow scenarios:** General, Action selection, Error diagnosis, Workflow design.

**Context inclusions:** pipeline/workflow check results, transform/action plugin catalog, optional full XML, and execution log excerpt from the Hop GUI log panel.

When a response includes `hop_proposals`, click **Review N proposed change(s)** in the transcript (same flow as the Data Vault modeler). Applied changes register undo on the pipeline/workflow graph; follow-up turns include summaries of what you applied.

### Source model, lineage view, execution map, EDW Journey (chat only)

These advisors explain the open artifact. They do **not** apply graph edits.

| Surface | Open | Scenarios |
|---|---|---|
| Source model (`.hsm`) | Toolbar **AI Help**, or right-click canvas / table / query | Source analysis, Type mapping, Generate Data Vault, General |
| Lineage view (`.hlv`) | Toolbar **AI Help**, or right-click a node | Explain graph, What next, General |
| Execution map (`.hem`) | Toolbar **AI Help**, or right-click a node | Explain map, Runtime, Diffs, General |
| EDW Journey | Perspective toolbar **AI Help** (uses the selected tree node as focus) | What should I do next?, Explain journey, General |

Source-model prompts always include a compact structure JSON and the same Generate Data Vault classification heuristic as the canvas action. Lineage Help uses the live session graph when the tab has been refreshed; otherwise it says the graph is not loaded. Journey Help uses the current group snapshot plus last-run OPS when available.

### Advisory Scenarios

The Advisor uses tailored system prompt templates to optimize responses for different modeling phases:

* **General**: Default scenario for miscellaneous queries, best practices, and general assistance.
* **Source Analysis**: Guidance on parsing raw databases, files, and catalog sources.
* **Type Mapping**: Advice on mapping source data types to physical target table data types.
* **Data Vault Modeling**: In-depth recommendations on designing hubs, links, satellites, and reference tables.
* **Hop Integration**: Help with configuring pipelines, workflows, and execution parameters in Hop.
* **Error Diagnosis**: Analysis of validation warnings, errors, or execution logs to troubleshoot issues.
* **Performance tuning** (DV/DM/BV modelers): Recommendations grounded in load-run metrics and insight rules — parallelism, sort memory, dimension lookup preload, and related configuration. Proposals can adjust model settings such as `targetTableParallelCopies` after you review them.

### Context Inclusions & Options

You can control what metadata is sent to the LLM to balance response accuracy and prompt token limits:

* **Include model check results**: Automatically validates your open model (using **Check model**) and appends validation results (errors, warnings, checks) to the prompt.
* **Include catalog sources**: Sends the selected catalog record definitions (field names, Hop types, lengths, and keys). Click **Select…** beside the option. Sources from the model **Coach** panel are listed first, labeled `(Coach)`. With nothing selected, the Coach list is used, then record sources already bound on the model. The selection is remembered for the conversation.
* **Source-model classification** (always, when sibling `.hsm` files sit next to the open `.hdv`): compact JSON from the same Generate Data Vault classifier (tables, queries, JSON, pipelines, reference tables, hierarchy aliases). Use it with **Source Analysis** or **Data Vault Modeling**.
* **Include full model XML**: Sends the entire `.hdv` model XML. This is larger but allows the AI to perform comprehensive reviews. *Note: Full XML is sent on the first turn only to conserve tokens.*
* **Include load-run metrics and insights** (DV/DM/BV modelers): Appends recent `load_run` data, per-transform metrics, and rule-based insights from the OPS catalog when asking performance or tuning questions. Auto-enables when you select the **Performance tuning** scenario.

### Example: suggest a Data Vault model from catalog sources

`retail-example/test/ai-modeling-test.hdv` is an empty model with Data Vault configuration `data-vault` and the retail E2E feeds on the **Coach** panel. Open it, click **AI Help**, choose **Data Vault modeling**, check **Include catalog sources**, and **Select…** those feeds (the shot below has 13 selected). **Include full model XML** tells the advisor the canvas is still empty. Ask it to suggest a model for the included sources.

![AI advisor — question sent with 13 catalog sources selected, while the answer is still running](images/ai-advisor-suggest-dv-model-using-sources-1-prompt-thinking.png)

The answer names hubs, links, and satellites from the source keys. `load_date` and `record_source` stay off the satellites. Descriptive fields that have no lookup source in the selection stay out of the raw vault.

![AI advisor — modeling advice for the selected E2E sources](images/ai-advisor-suggest-dv-model-using-sources-2-modeling-advice.png)

When the reply includes proposals, the transcript shows **Review N proposed change(s)**.

![AI advisor — Review 20 proposed change(s)](images/ai-advisor-suggest-dv-model-using-sources-3-review-proposed-changes.png)

Links and satellites that name a hub are blocked until that hub is on the model. Apply the hubs that are checked. Leave the blocked rows alone. **Apply** writes them onto the open canvas and registers an undo point.

![AI advisor — first review, hubs checked and dependent links and satellites blocked](images/ai-advisor-suggest-dv-model-using-sources-4-proposals-list.png)

Open **Review** again. The hubs are now blocked because they already exist. The links and satellites that needed them can be applied. A satellite on a link stays blocked until that link exists.

![AI advisor — second review, links and satellites ready after the hubs exist](images/ai-advisor-suggest-dv-model-using-sources-5-next-list-of-proposals.png)

A later pass applies what is still new, such as the order-line satellite and a canvas note that records the modeling assumptions. Rows for tables already on the model stay blocked.

![AI advisor — last review, remaining satellite and model note checked](images/ai-advisor-suggest-dv-model-using-sources-6-final-actions-to-apply.png)

The canvas is the raw vault for those sources: customer, order, and product hubs, the order links, and the satellites that passed review.

![AI advisor — Data Vault canvas after applying the source-modeling proposals](images/ai-advisor-suggest-dv-model-using-sources-7-result.png)

### Example: explaining link satellites

On a Data Vault model, ask about link satellites in the **General** or **Data Vault Modeling** scenario. The advisor explains LSAT semantics and cites tables from your open model:

![AI advisor explaining link satellites](images/ai-advisor-explaining-link-tables.png)

### Example: performance tuning on a dimensional model

Select **Performance tuning**, enable **Include load-run metrics and insights**, and ask how to speed up a fact load. The advisor analyzes transform durations from the last run and may propose configuration changes:

![AI advisor — performance tuning recommendations](images/ai-advisor-offering-performance-tuning-advice.png)

When the response includes `dv_proposals` or `hop_proposals`, use **Review … proposed change(s)** to inspect risk levels and apply safe edits (blocked proposals are unchecked automatically):

![AI advisor — review proposal to raise targetTableParallelCopies](images/ai-advisor-proposal-review-increase-parallelism.png)

See [performance-tuning.md](performance-tuning.md) for the underlying knobs (`parallelPipelineCopies`, `targetTableParallelCopies`, `sortRowsSize`, preload lookup cache).

---

## Interactive Structural Proposals

In addition to providing conversational guidance, the AI can propose specific modifications to your open artifact. When a response contains actionable edits, a **Review … proposed change(s)** button appears in the conversation history.

- **Data Vault model** (`.hdv`): table/hub/link/satellite proposals (`dv_proposals`) — details below.
- **Business Vault** (`.hbv`) and **dimensional** (`.hdm`): add tables, bind DV/source, SQL, layout — details below.
- **Pipeline** (`.hpl`) and **workflow** (`.hwf`): graph topology proposals (`hop_proposals`) — see [M2 documentation](plans/hop-ai-assistant-m2.md).

![Proposal Review Dialog](images/data-vault-ai-help-review-proposals-dialog.png)

Clicking this button opens the **Proposal Review Dialog** where you can:
1. **Select & Preview Changes**: Select or unselect specific changes. The preview area displays structural details of each action.
2. **Review Validation Status**: The dialog validates proposed actions against your current model and selected catalog schemas:
   * **[LOW/MEDIUM/HIGH] Risk**: Each proposal has a designated risk level based on the impact on your model.
   * **Blocked**: Actions that would invalidate your model (e.g., adding a table with an already existing name) are automatically unchecked and labeled with a descriptive blockage reason.
   * **Warning**: Warnings are displayed in parentheses for potentially risky operations.
3. **Apply Selected**: Click **Apply** to execute the validated changes directly to your active visual modeler. An undo point is registered, allowing you to use the standard **Undo** button in the Hop modeler toolbar if needed.

### Applyable Structural Proposal Types

The AI can suggest the following structured modifications:

| Proposal Type | Expected Parameters | Description / Effect |
| :--- | :--- | :--- |
| `ADD_HUB` | `name`, `recordSource`, `tableName` (opt), `hashKeyFieldName` (opt), `businessKeysJson` (opt), `locationX` / `locationY` (opt) | Adds a new Hub table mapped to a source and its business keys. |
| `ADD_LINK` | `name`, `hubNames` (comma list), `tableName` (opt), `linkHashKeyFieldName` (opt), `locationX` / `locationY` (opt) | Creates a new Link connecting two or more existing hubs. |
| `ADD_SATELLITE` | `name`, `recordSource`, `hubName` / `linkName` (either), `tableName` (opt), `attributesJson` (opt), `locationX` / `locationY` (opt) | Creates a Satellite attached to a Hub or Link with specified attributes. |
| `SET_BUSINESS_KEYS` | `tableName`, `businessKeysJson` / `businessKeyName` & `businessKeySourceField` & `businessKeyRecordSource` | Updates or overwrites business keys on an existing Hub. |
| `BIND_RECORD_SOURCE` | `tableName`, `recordSource` | Binds a record source to an existing Hub or sets the record source for a Satellite. |
| `SET_TABLE_LOCATION`| `tableName`, `locationX`, `locationY` | Updates the coordinate position of a table in the visual canvas. |
| `ADD_MODEL_NOTE` | `text` | Creates a floating text note on the modeling canvas. |
| `SET_CONFIGURATION_PROPERTY` | `propertyName`, `value` | Updates configuration settings (e.g., `sortRowsSize`, `targetTableParallelCopies`, `targetTableBatchSize`, `targetDatabase`, `dataCatalogConnection`). |
| `RENAME_TABLE` | `tableName`, `newName` | Safely renames an existing Hub, Link, or Satellite. |

**Business Vault** (`.hbv`):

| Proposal Type | Expected Parameters | Description / Effect |
| :--- | :--- | :--- |
| `ADD_SCD2` | `name`, `tableName` (opt), `parentHubName` (opt), `satelliteNames` (opt), `locationX` / `locationY` (opt) | Adds an SCD2 table and optional hub/satellite derivatives. |
| `ADD_PIT` | `name`, `tableName` (opt), `hubName` (opt), `satelliteNames` (opt), `locationX` / `locationY` (opt) | Adds a PIT table and optional hub/satellite derivatives. |
| `ADD_BUSINESS_TABLE` | `name`, `tableName` (opt), `sqlQuery` (opt), `locationX` / `locationY` (opt) | Adds a SQL/Jinja business table. |
| `ADD_SOURCE_QUERY` | `name`, `tableName` (opt), `sqlQuery` (opt), `hashKeyField` (opt), `locationX` / `locationY` (opt) | Adds a source-query input for SCD2/PIT. |
| `ADD_BRIDGE` | `name`, `tableName` (opt), `linkName` (opt), `hubNames` (opt), `weightField` (opt), `sqlQuery` (opt), `locationX` / `locationY` (opt) | Adds a BV bridge of hub hash keys (usually from a DV link). Dimensional `ADD_BRIDGE` still adds a Kimball bridge on `.hdm`. |
| `BIND_DV_TABLE` | `tableName`, `dvTableName`, `dvTableType` (when the linked DV is not loaded) | Binds a DV hub/satellite/link to an SCD2, PIT, business table, or bridge. |
| `SET_SQL_QUERY` | `tableName`, `sqlQuery` | Sets authoring SQL (including `{{ ref() }}` / `{{ source() }}`) on a business table, source query, or bridge. |
| `SET_TABLE_LOCATION` | `tableName`, `locationX`, `locationY` | Moves the table on the canvas. |

**Dimensional** (`.hdm`):

| Proposal Type | Expected Parameters | Description / Effect |
| :--- | :--- | :--- |
| `ADD_DIMENSION` | `name`, `tableName` (opt), `surrogateKeyField` (opt), `naturalKeys` (opt), `attributes` (opt), `locationX` / `locationY` (opt) | Adds a dimension with optional keys and attributes. |
| `ADD_FACT` | `name`, `tableName` (opt), `grain` (opt), `measures` (opt), `dimensionTableNames` (opt), `locationX` / `locationY` (opt) | Adds a fact with optional measures and dimension roles. |
| `ADD_BRIDGE` | `name`, `tableName` (opt), `dimensionTableNames` (opt), `locationX` / `locationY` (opt) | Adds a many-to-many bridge. |
| `ADD_JUNK_DIMENSION` | `name`, `tableName` (opt), `surrogateKeyField` (opt), `keyFields` (opt), `locationX` / `locationY` (opt) | Adds a junk dimension. |
| `BIND_SOURCE` | `tableName`, `sourceType`, `sourceSql` / pipeline / catalog / fact-table fields | Sets the staging source on a dimensional table. |
| `SET_TABLE_LOCATION` | `tableName`, `locationX`, `locationY` | Moves the table on the canvas. |

---

## Privacy & Security

The Data Hopper EDW AI Helper values the security of your enterprise metadata:
* **No Secret Leaks**: API keys are securely stored in your local Hop configuration file; they are never sent inside prompts.
* **Redacted Database Connections**: Database metadata connection descriptions are limited to the name, type, database name, and host. **Usernames, passwords, ports, and connection URLs are explicitly excluded** before sending.
* **Data Scoping**: Only catalog source schemas and model components that you explicitly check and approve are uploaded to your chosen LLM provider.

---

## Programmatic API

hopper-edw advisors implement Hop's `IAiAdvisor`. The workbench calls `buildPrompt` / `parseResponse` / `validateProposals` / `applyProposals`. Do not compile against `hop-tech-ai` or langchain4j from this plugin.

**Data Vault / Business Vault / dimensional** (proposals)

* [DataVaultAiAdvisor](../src/main/java/org/hopper/edw/datavault/ai/DataVaultAiAdvisor.java), [BusinessVaultAiAdvisor](../src/main/java/org/hopper/edw/datavault/ai/businessvault/BusinessVaultAiAdvisor.java), [DimensionalAiAdvisor](../src/main/java/org/hopper/edw/datavault/ai/dimensional/DimensionalAiAdvisor.java): `@AiAdvisorPlugin` entry points.

**Chat-only**

* [SourceModelAiAdvisor](../src/main/java/org/hopper/edw/datavault/ai/sourcemodel/SourceModelAiAdvisor.java), [LineageViewAiAdvisor](../src/main/java/org/hopper/edw/datavault/ai/lineageview/LineageViewAiAdvisor.java), [ExecutionMapAiAdvisor](../src/main/java/org/hopper/edw/datavault/ai/executionmap/ExecutionMapAiAdvisor.java), [EdwJourneyAiAdvisor](../src/main/java/org/hopper/edw/datavault/ai/journey/EdwJourneyAiAdvisor.java).
* [DvAiContextBuilder](../src/main/java/org/hopper/edw/datavault/ai/DvAiContextBuilder.java) (and BV/DM equivalents): redacted context bundles.
* [DvAiProposalApplier](../src/main/java/org/hopper/edw/datavault/ai/DvAiProposalApplier.java): writes approved proposals to the in-memory model.

**Pipeline and workflow**

Pipeline and workflow AI Help live in Apache Hop 2.20 (`pipeline-graph` / `workflow-graph`). See Hop's AI Assistant docs. The M2 design notes remain at [plans/hop-ai-assistant-m2.md](plans/hop-ai-assistant-m2.md).