# Internal Linking Best Practices
**A Step-by-Step Guide for Connecting Islands Within a Single Domain Ontology**

## 🎯 Purpose

This guide provides a proven workflow for fixing "islands" within auto-generated ontologies by creating object property relationships between entities that currently only have string-based foreign key references.

**When to Use**: When entities within the same domain reference each other via string properties (like `customerId`, `equipmentId`, etc.) but lack proper graph relationships.

**Example**: CRM Opportunities have a `customerId` string property but no direct object property link to Customer.Account entities.

---

## ✅ The Golden Rules

1. **Never modify auto-generated ontologies** - they get recreated on graphmart reload
2. **Always create a separate linking ontology** (e.g., `{Domain}Linking`)
3. **Import the source ontology** so you can reference its classes
4. **Test with SELECT queries first** before creating INSERT transformation steps
5. **ALWAYS use `refresh_graphmart`, NEVER use `reload_graphmart`** unless explicitly requested by user
6. **Validate SELECT result counts** to detect cartesian products or insufficient matches
7. **Register the ontology** with the graphmart
8. **Validate results** with queries to confirm linking worked

---

## 📋 Step-by-Step Process

### Step 1: Discover the Islands (Investigation Phase)

#### 1.1 Find Entity Counts
```sparql
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX domain: <{YOUR_DOMAIN_ONTOLOGY_URI}#>

SELECT ?type (COUNT(DISTINCT ?entity) as ?count)
WHERE {
    ?entity a ?type .
    FILTER(STRSTARTS(STR(?type), "{YOUR_DOMAIN_ONTOLOGY_URI}#"))
}
GROUP BY ?type
ORDER BY DESC(?count)
```

**Goal**: Identify all entity types and their counts to understand the data landscape.

#### 1.2 Examine Ontology Structure
```bash
# List all classes in the ontology
mcp_ags-sparql-ag_list_ontology_structure_classes
- ontology_uri: "{SOURCE_LAYER_ONTOLOGY_URI}"

# List all properties
mcp_ags-sparql-ag_list_ontology_structure_properties
- ontology_uri: "{SOURCE_LAYER_ONTOLOGY_URI}"
```

**Goal**: Understand existing object properties vs datatype properties (foreign keys).

#### 1.3 Test for Potential Links

**IMPORTANT**: Test each potential link type separately for better performance and clearer results.

```sparql
# Example: Can we link Entity A to Entity B via shared ID?
PREFIX domain: <{YOUR_DOMAIN_ONTOLOGY_URI}#>

SELECT (COUNT(*) as ?totalLinks)
WHERE {
    ?entityA a domain:TypeA ;
        domain:TypeA.foreignKeyProperty ?sharedId .
    ?entityB a domain:TypeB ;
        domain:TypeB.idProperty ?sharedId .
}
```

**Goal**: Validate that string-based foreign keys can successfully match entities.

**Expected Results**: 
- ✅ Many matches → Good candidate for linking
- ❌ Zero matches → Check property names or data quality issues
- ⚠️ Some matches → Data quality issues may exist (acceptable, document them)

**Best Practice**: Run a separate COUNT query for each potential link type rather than combining them with UNION. This improves performance and makes results easier to interpret.

---

### Step 2: Create Linking Ontology

#### 2.1 Create the Ontology
```bash
mcp_ags-sparql-ag_create_ontology
- ontology_uri: "{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking"
- ontology_label: "{Domain} Internal Linking"
- description: "Links {domain} entities internally using object properties to replace string-based foreign key references"
- explanation: "Creating separate linking ontology to avoid modifying auto-generated ontology"
```

**Naming Convention**: `{SHORT_UUID}/{Domain}Linking` (e.g., `a3f9c2e1/CRMLinking`, `7b4d8f2a/ERPLinking`)

**UUID Generation**: Use an 8-character hex UUID to ensure global uniqueness across graphmarts. Simply generate a random 8-character hex string (e.g., `7f3a2c91`, `b4e8d1f6`, `9c2a5e73`). You can use any method to generate this - the key is that it should be unique enough to avoid collisions.


#### 2.2 Register the Ontology
```bash
mcp_ags-sparql-ag_register_ontology
- ontology_uri: "{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking"
- explanation: "Registering ontology with graphmart for discoverability"
```

**⚠️ CRITICAL**: Don't forget this step! Unregistered ontologies won't be discoverable.

#### 2.3 Add Import Statement
```bash
# First, identify the source layer ontology URI
mcp_ags-sparql-ag_list_transformation_layers
# Find the data loading layer, note its layer_uri
# The ontology URI is typically: {layer_uri}/Model

# Then add the import
mcp_ags-sparql-ag_add_ontology_import
- ontology_uri: "{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking"
- imported_ontology_uri: "{SOURCE_LAYER_ONTOLOGY_URI}"
- explanation: "Importing source ontology so linking properties can reference its classes"
```

**Why**: Your linking properties need to reference classes from the auto-generated ontology.

---

### Step 3: Add Linking Properties

For each identified linking opportunity, add an object property:

```bash
mcp_ags-sparql-ag_add_ontology_property
- ontology_uri: "{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking"
- property_uri: "{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking#{propertyName}"
- property_type: "object"
- property_label: "{Human Readable Label}"
- property_description: "Links {source} to {target} via {matching_attribute}"
- domain_uri: "{SOURCE_ONTOLOGY_URI}#{SourceClass}"
- range_uri: "{SOURCE_ONTOLOGY_URI}#{TargetClass}"
- explanation: "Adding object property to link {source} to {target}"
```

**Property Naming Convention**:
- `relatedCustomer`, `relatedProduct`, `relatedEquipment` - generic relationship
- `feedbackForCustomer`, `orderForProduct` - specific relationship type
- Keep names clear and descriptive

**Template Example**:
```bash
# Property: Entity A → Entity B
property_uri: "{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking#related{TargetEntity}"
domain_uri: "{SOURCE_ONTOLOGY_URI}#{SourceEntityClass}"
range_uri: "{SOURCE_ONTOLOGY_URI}#{TargetEntityClass}"
```

---

### Step 4: Create Transformation Layer

```bash
mcp_ags-sparql-ag_create_transformation_layer
- layer_name: "{Domain} Internal Linking"
- layer_description: "Links {domain} entities internally: {list key relationships}"
- order_after: "{Domain} Data Loading"
- explanation: "Creating transformation layer to execute linking logic"
```

**Naming Convention**: `{Domain} Internal Linking`

**Ordering**: Always place immediately after the corresponding data loading layer.

---

### Step 5: Test Transformation Queries

**CRITICAL**: Test each linking query as SELECT before adding as transformation step!

#### 5.1 Validate Linking Query
```sparql
PREFIX domain: <{SOURCE_ONTOLOGY_URI}#>
PREFIX linking: <{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking#>

# Test the WHERE clause logic
SELECT ?source ?target ?matchingValue
WHERE {
    ?source a domain:SourceClass ;
        domain:SourceClass.foreignKeyProperty ?matchingValue .
    ?target a domain:TargetClass ;
        domain:TargetClass.idProperty ?matchingValue .
}
LIMIT 10
```

**Expected Results**:
- ✅ Should see multiple matches with valid entity URIs
- ❌ Zero results → Fix the WHERE clause before proceeding

#### 5.2 **CRITICAL: Count Total Potential Links**
```sparql
PREFIX domain: <{SOURCE_ONTOLOGY_URI}#>

# Count how many links would be created
SELECT (COUNT(*) as ?totalLinks)
WHERE {
    ?source a domain:SourceClass ;
        domain:SourceClass.foreignKeyProperty ?matchingValue .
    ?target a domain:TargetClass ;
        domain:TargetClass.idProperty ?matchingValue .
}
```

**Sanity Check Analysis** (REQUIRED):
- ✅ **Reasonable**: Total links ≈ number of source entities (1:1 or 1:few relationship)
  - Example: 5,000 opportunities → 3,500 links = Good (70% coverage)
  - Example: 150,000 financial transactions → 32,243 links = Good (nested object, many don't have cost centers)
  
- ⚠️ **Warning**: Total links >> number of source entities (possible many:many without proper filters)
  - Example: 5,000 opportunities → 50,000 links = Review query! Likely missing a filter
  - Check: Are you accidentally creating multiple links per entity?
  
- 🚨 **CARTESIAN PRODUCT**: Total links = (sources × targets) or close to it
  - Example: 1,000 sources, 10 targets → 10,000 links = **DANGER!** Missing FILTER/JOIN condition
  - Fix: Add proper matching condition or additional filters
  
- ❌ **Too Few**: Total links << expected based on entity counts
  - Example: 5,000 opportunities → 50 links = Data quality issue or filters too strict
  - Check: Are property names correct? Are there whitespace/normalization issues?

**Action Required**: 
- If count looks suspicious (too high or too low), **DO NOT PROCEED**
- Investigate the query logic, add filters, or adjust matching conditions
- Re-test until count makes business sense
- Document expected vs actual link counts in analysis

#### 5.3 Check for Data Quality Issues
```sparql
# Find orphaned references (source entities with no matching target)
SELECT ?source ?orphanedId
WHERE {
    ?source a domain:SourceClass ;
        domain:SourceClass.foreignKeyProperty ?orphanedId .
    FILTER NOT EXISTS {
        ?target a domain:TargetClass ;
            domain:TargetClass.idProperty ?orphanedId .
    }
}
LIMIT 100
```

**Action**: Document orphaned references - they're expected in real-world data.

#### 5.4 Calculate Expected Coverage
```sparql
# Count source entities with matching foreign keys
SELECT 
    (COUNT(DISTINCT ?source) as ?totalSources)
    (COUNT(DISTINCT ?sourceWithFK) as ?sourcesWithFK)
WHERE {
    { ?source a domain:SourceClass . }
    UNION
    { 
        ?sourceWithFK a domain:SourceClass ;
            domain:SourceClass.foreignKeyProperty ?fk .
        ?target a domain:TargetClass ;
            domain:TargetClass.idProperty ?fk .
    }
}
```

**Expected Coverage Calculation**:
- If `sourcesWithFK` / `totalSources` ≈ link count / `totalSources` → Good!
- If big discrepancy → Either cartesian product or missing filters

**Before Proceeding Checklist**:
- [ ] Total link count makes business sense (not orders of magnitude too high/low)
- [ ] Link count ≈ number of source entities (allowing for orphaned references)
- [ ] No cartesian product detected (links ≠ sources × targets)
- [ ] Orphaned references documented and understood
- [ ] Coverage percentage calculated and reasonable (typically 60-100%)

---

### Step 6: Add Transformation Steps

Once SELECT query is validated, convert to INSERT:

```bash
mcp_ags-sparql-ag_add_transformation_step
- layer_name_or_uri: "{Domain} Internal Linking"
- step_name: "Link {Source} to {Target}"
- insert_query: "..." # See template below
- explanation: "Linking {source} to {target} using {matching_property} matching"
```

#### Transformation Query Template
```sparql
PREFIX domain: <{SOURCE_ONTOLOGY_URI}#>
PREFIX linking: <{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking#>

INSERT {
    GRAPH ${targetGraph} {
        ?source linking:{propertyName} ?target .
    }
}
${usingSources}
WHERE {
    # Use EXACT WHERE clause from validated SELECT query
    ?source a domain:SourceClass ;
        domain:SourceClass.foreignKeyProperty ?matchingValue .
    ?target a domain:TargetClass ;
        domain:TargetClass.idProperty ?matchingValue .
}
```

**Critical Template Requirements**:
- Use `${targetGraph}` (single $, not $$)
- Use `${usingSources}` (single $, not $$)
- Use `\n` for line breaks (not `\\n`)
- Copy exact WHERE clause from validated SELECT query

---

### Step 7: Execute Linking Transformations

#### 7.1 Refresh Graphmart (ALWAYS - Unless User Explicitly Requests Reload)
```bash
mcp_ags-sparql-ag_refresh_graphmart
- explanation: "Refreshing graphmart to execute {domain} internal linking transformations"
```

**🚨 CRITICAL RULE: Refresh vs Reload**

**ALWAYS use `refresh_graphmart`** unless the user explicitly asks for reload:
- ✅ **Refresh**: Fast (seconds to ~1 minute), only processes dirty/changed layers
  - Use for: All linking transformations, step updates, property additions
  
- ❌ **Reload**: Slow (minutes to hours), rebuilds ALL layers from scratch
  - Use ONLY when: User explicitly requests it, or catastrophic errors in graphmart
  - Why avoid: Wastes time, no benefit for incremental linking work

**Execution Time Indicators**:
- 0.5-2 seconds: No actual changes (validation refresh)
- 5-30 seconds: Actual transformation work happening (good!)
- >1 minute: Large dataset or multiple layers being processed

**Never say**: "Should I refresh or reload?"  
**Always say**: "Refreshing graphmart..." (refresh is the default)

#### 7.2 Check Layer Status (if needed)
```bash
mcp_ags-sparql-ag_get_layer_status
- layer_name_or_uri: "{LAYER_URI}"
- explanation: "Checking if linking layer executed successfully"
```

**Success Indicators**:
- `layer_status`: "Online"
- `is_structure_valid`: true
- No errors in step details

---

### Step 8: Validate Results

#### 8.1 Count New Links

**IMPORTANT**: Count each link type separately for better performance.

```sparql
PREFIX linking: <{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking#>

# Count links for specific property
SELECT (COUNT(*) as ?linkCount)
WHERE {
    ?source linking:{propertyName} ?target .
}
```

**Best Practice**: Run a separate query for each linking property rather than combining them. This is faster and provides clearer results.

**Expected Results**: Should match or be close to the SELECT query counts from Step 5.

#### 8.2 Test Business Queries
```sparql
PREFIX domain: <{SOURCE_ONTOLOGY_URI}#>
PREFIX linking: <{YOUR_BASE_URI}/ontologies/{SHORT_UUID}/{Domain}Linking#>

# Example: Get all related entities for a specific entity
SELECT ?sourceName ?targetName
WHERE {
    ?source a domain:SourceClass ;
        domain:SourceClass.nameProperty ?sourceName ;
        linking:{propertyName} ?target .
    ?target domain:TargetClass.nameProperty ?targetName .
}
ORDER BY ?sourceName
LIMIT 10
```

**Goal**: Demonstrate that you can now navigate relationships using object properties instead of string matching.

#### 8.3 Calculate Coverage
```sparql
# Calculate linking coverage percentage
SELECT 
    (COUNT(DISTINCT ?source) as ?totalSources)
    (COUNT(DISTINCT ?linkedSource) as ?linkedSources)
    ((?linkedSources * 100 / ?totalSources) as ?coveragePercent)
WHERE {
    {
        ?source a domain:SourceClass .
    }
    UNION
    {
        ?linkedSource a domain:SourceClass ;
            linking:{propertyName} ?target .
    }
}
```

**Success Criteria**: 70%+ coverage is good (some orphaned references are expected).

---

## 📊 Success Metrics

### Linking Coverage
- ✅ **80%+ of entities linked** - Excellent
- ✅ **60-80% of entities linked** - Good (expected with real-world data)
- ⚠️ **40-60% of entities linked** - Review data quality
- ❌ **<40% of entities linked** - Check query logic or data issues

### Query Performance
- Object property navigation should be faster than string matching
- Queries using linking properties should return results in <2 seconds

### Business Value
Document specific queries enabled, such as:
- "Show all opportunities for customer X" (without string matching)
- "Find all feedback for equipment Y" (direct navigation)
- "Calculate customer satisfaction across all feedback" (aggregation)

---

## 🚨 Common Pitfalls & Solutions

| Problem | Solution |
|---------|----------|
| **0 results from linking query** | Verify property names match exactly (case-sensitive) |
| **Forgot to register ontology** | Links won't work; run `register_ontology` then refresh |
| **Forgot to import source ontology** | Properties can't reference classes; add import statement |
| **Template syntax errors** | Use `${targetGraph}` and `${usingSources}` (single $) |
| **Transformation step failed** | Check layer status for error details; fix query and update step |
| **Cartesian product explosion** | Add proper FILTER conditions, validate with COUNT first |
| **Low coverage** | Check data quality, property name mismatches, or missing data |
| **Performance issues** | Index foreign key properties if possible in source system |

---

## 🎯 Quick Reference Template

**Replace these placeholders in all examples**:
- `{YOUR_BASE_URI}` - Your organization's base URI (e.g., `http://company.com`)
- `{SHORT_UUID}` - 8-character hex UUID for global uniqueness (e.g., `a3f9c2e1`)
- `{Domain}` - Domain name (e.g., `CRM`, `ERP`, `PLM`)
- `{SOURCE_ONTOLOGY_URI}` - The auto-generated layer ontology URI
- `{LAYER_URI}` - The transformation layer URI
- `{propertyName}` - Your linking property name
- `{SourceClass}` / `{TargetClass}` - Entity class names
- `{matching_property}` - The foreign key property used for matching

**Generate UUID**: Simply create an 8-character hex string (e.g., `7f3a2c91`, `b4e8d1f6`, `9c2a5e73`). Any random combination of hex digits (0-9, a-f) will work - the key is uniqueness within your graphmart environment.

**Example URIs**:
- Linking Ontology: `http://company.com/ontologies/a3f9c2e1/CRMLinking`
- Property: `http://company.com/ontologies/a3f9c2e1/CRMLinking#relatedCustomer`
- Layer: "CRM Internal Linking"