SAP SuccessFactors OData Metadata Parser

Overview

This skill parses SAP SuccessFactors OData $metadata XML documents and transforms them into user-friendly, organizational-ready outputs. The raw $metadata is a massive XML schema document (often 50,000+ lines) containing entity definitions, field types, navigation properties, associations, and constraints — virtually unreadable for business users, HR admins, and even many consultants. This tool makes it accessible.

Why This Tool Exists

In every SAP SuccessFactors implementation, consultants and admins face these recurring pain points:

1. Field Discovery — "What fields exist on EmpJob? Which are required? What's the max length?"
2. Data Mapping — "Map SF fields to our payroll system — we need entity, field, type, and constraints in Excel."
3. Configuration Documentation — "Generate a data dictionary for our instance for audit/compliance."
4. Instance Comparison — "Did someone add custom fields in production that aren't in our test environment?"
5. Integration Planning — "Which navigation properties connect User to EmpEmployment? What's the cardinality?"

Raw $metadata XML answers all of these — but no human should have to read 50K lines of XML to find them.

Input Sources

The tool accepts metadata from multiple sources:

1. Uploaded $metadata XML File

User uploads: metadata.xml (or .edmx)
Location: /mnt/user-data/uploads/metadata.xml

2. Pasted XML Content

User pastes raw XML into the chat. Extract and save to a working file before processing.

3. Live API Fetch (if SF MCP tools are available)

Use the sf-mcp:get_configuration tool to fetch entity metadata directly from a live instance:

sf-mcp:get_configuration(instance, entity, data_center, environment, auth_user_id, auth_password)

4. Multiple Files for Comparison

User uploads two metadata files (e.g., metadata_dev.xml and metadata_prod.xml) for cross-instance comparison.

Core Parsing Logic

Step 1: Parse the $metadata XML

Use Python's lxml or xml.etree.ElementTree with proper OData namespace handling.

import xml.etree.ElementTree as ET

# OData EDMX namespaces (SuccessFactors uses EDMX + CSDL)
NAMESPACES = {
    'edmx': 'http://schemas.microsoft.com/ado/2007/06/edmx',
    'edm': 'http://schemas.microsoft.com/ado/2008/09/edm',
    'm': 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata',
    'd': 'http://schemas.microsoft.com/ado/2007/08/dataservices',
    # SF may also use newer namespace versions:
    'edm2009': 'http://schemas.microsoft.com/ado/2009/11/edm',
}

def parse_metadata(xml_path):
    tree = ET.parse(xml_path)
    root = tree.getroot()

    # Auto-detect namespace version from root element
    ns = detect_namespaces(root)

    entities = {}
    associations = {}
    entity_sets = {}

    # Parse EntityTypes
    for schema in root.iter(f'{{{ns["edm"]}}}Schema'):
        namespace = schema.get('Namespace', '')

        for entity_type in schema.findall(f'{ns_prefix}EntityType', ns):
            name = entity_type.get('Name')
            base_type = entity_type.get('BaseType', '')
            entities[name] = {
                'name': name,
                'namespace': namespace,
                'base_type': base_type,
                'keys': extract_keys(entity_type, ns),
                'properties': extract_properties(entity_type, ns),
                'navigation_properties': extract_nav_properties(entity_type, ns),
            }

        # Parse Associations
        for assoc in schema.findall(f'{ns_prefix}Association', ns):
            associations[assoc.get('Name')] = extract_association(assoc, ns)

        # Parse EntitySets (EntityContainer)
        for container in schema.findall(f'{ns_prefix}EntityContainer', ns):
            for es in container.findall(f'{ns_prefix}EntitySet', ns):
                entity_sets[es.get('Name')] = es.get('EntityType')

    return entities, associations, entity_sets

Step 2: Extract Field-Level Details

For each entity, extract these critical attributes per field:

SAP-specific attributes (critical for SF):

SAP_ATTRIBUTES = [
    'sap:label',           # Display label
    'sap:creatable',       # Insert permission
    'sap:updatable',       # Update permission
    'sap:filterable',      # Filter capability
    'sap:sortable',        # Sort capability
    'sap:required-in-filter',  # Must be in $filter
    'sap:display-format',  # Date/time display format
    'sap:field-control',   # Dynamic field control
    'sap:visible',         # UI visibility
    'sap:semantics',       # Semantic meaning (e.g., email, tel)
]

Step 3: Extract Navigation Properties & Relationships

def extract_nav_properties(entity_type, ns):
    nav_props = []
    for nav in entity_type.findall(f'.//NavigationProperty', ns):
        nav_props.append({
            'name': nav.get('Name'),
            'relationship': nav.get('Relationship'),
            'from_role': nav.get('FromRole'),
            'to_role': nav.get('ToRole'),
            'cardinality': determine_cardinality(nav, associations)  # 1:1, 1:N, N:1
        })
    return nav_props

Output Formats

Output 1: Excel Data Dictionary (Primary Deliverable)

This is the most commonly requested output. Generate a multi-sheet Excel workbook.

#### Sheet Structure:

#### Excel Formatting Standards:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# Color scheme (SAP-inspired)
HEADER_FILL = PatternFill('solid', fgColor='1B3A5C')      # Dark SAP blue
HEADER_FONT = Font(name='Arial', bold=True, color='FFFFFF', size=11)
SUBHEADER_FILL = PatternFill('solid', fgColor='4A90D9')    # Medium blue
KEY_FIELD_FILL = PatternFill('solid', fgColor='FFF3CD')    # Yellow highlight for key fields
REQUIRED_FILL = PatternFill('solid', fgColor='F8D7DA')     # Light red for required fields
READONLY_FILL = PatternFill('solid', fgColor='E2E3E5')     # Gray for read-only fields
NAV_PROP_FILL = PatternFill('solid', fgColor='D4EDDA')     # Green for navigation properties
CUSTOM_FIELD_FILL = PatternFill('solid', fgColor='D6EAF8') # Light blue for custom (cust_) fields
ALT_ROW_FILL = PatternFill('solid', fgColor='F8F9FA')      # Alternating row color

THIN_BORDER = Border(
    left=Side(style='thin', color='CCCCCC'),
    right=Side(style='thin', color='CCCCCC'),
    top=Side(style='thin', color='CCCCCC'),
    bottom=Side(style='thin', color='CCCCCC'),
)

#### Column Layout for Entity Field Sheets:

A: Field Name (technical)
B: Label (sap:label — human-readable)
C: Data Type (simplified: String, Integer, DateTime, Boolean, Decimal, etc.)
D: Max Length
E: Required (Yes/No — derived from Nullable=false)
F: Key Field (Yes/No)
G: Creatable (Yes/No)
H: Updatable (Yes/No)
I: Filterable (Yes/No)
J: Sortable (Yes/No)
K: Default Value
L: Description / Notes
M: Custom Field (Yes/No — detected by 'cust_' prefix)

#### Summary Sheet Layout:

A: Entity Name
B: Entity Label (if available)
C: Total Fields
D: Key Fields (count)
E: Required Fields (count)
F: Custom Fields (count — cust_ prefix)
G: Navigation Properties (count)
H: Creatable Fields (count)
I: Updatable Fields (count)
J: Category (Employee, Talent, Foundation, Platform, MDF, Custom)

Auto-categorization logic:

def categorize_entity(name):
    name_lower = name.lower()
    if name_lower.startswith('cust_'):
        return 'Custom MDF'
    elif any(name_lower.startswith(p) for p in ['emp', 'perhire', 'perpersonal', 'peremail', 'perphone']):
        return 'Employee Central'
    elif any(name_lower.startswith(p) for p in ['goal', 'form', 'competency']):
        return 'Talent / Performance'
    elif any(name_lower.startswith(p) for p in ['jobrecreq', 'jobreq', 'candidate', 'jobapp']):
        return 'Recruiting'
    elif any(name_lower.startswith(p) for p in ['fo_', 'picklistoption', 'picklistlabel']):
        return 'Foundation Objects'
    elif any(name_lower.startswith(p) for p in ['user', 'dynamicgroup', 'rbp']):
        return 'Platform / Security'
    elif any(name_lower.startswith(p) for p in ['position', 'budget']):
        return 'Position Management'
    elif any(name_lower.startswith(p) for p in ['timeaccount', 'employeetime', 'timemanagement']):
        return 'Time Management'
    else:
        return 'Other'

#### Applying Formatting:

def format_entity_sheet(ws, data_rows):
    # Freeze top row
    ws.freeze_panes = 'A2'

    # Auto-filter on headers
    ws.auto_filter.ref = ws.dimensions

    # Column widths (optimized for readability)
    COLUMN_WIDTHS = {
        'A': 35,  # Field Name
        'B': 35,  # Label
        'C': 18,  # Data Type
        'D': 12,  # Max Length
        'E': 10,  # Required
        'F': 10,  # Key Field
        'G': 12,  # Creatable
        'H': 12,  # Updatable
        'I': 12,  # Filterable
        'J': 12,  # Sortable
        'K': 18,  # Default Value
        'L': 45,  # Description
        'M': 14,  # Custom Field
    }
    for col, width in COLUMN_WIDTHS.items():
        ws.column_dimensions[col].width = width

    # Conditional formatting
    for row_idx, row_data in enumerate(data_rows, start=2):
        # Highlight key fields
        if row_data.get('is_key'):
            for col in range(1, 14):
                ws.cell(row=row_idx, column=col).fill = KEY_FIELD_FILL
        # Highlight required fields
        elif row_data.get('required'):
            ws.cell(row=row_idx, column=5).fill = REQUIRED_FILL
        # Highlight custom fields
        elif row_data.get('is_custom'):
            ws.cell(row=row_idx, column=1).fill = CUSTOM_FIELD_FILL
        # Alternating rows
        elif row_idx % 2 == 0:
            for col in range(1, 14):
                ws.cell(row=row_idx, column=col).fill = ALT_ROW_FILL

Output 2: Word Document (Data Dictionary Report)

For formal documentation deliverables. Use the docx skill for creation.

Document Structure:

1. Cover Page — Title, instance name, generation date, author
2. Table of Contents
3. Executive Summary — Total entities, fields, custom objects count
4. Entity Catalog — Grouped by category (EC, Talent, Recruiting, etc.)
5. Entity Detail Pages — One section per entity with field table
6. Navigation Property Map — Entity relationship documentation
7. Appendix: Data Type Reference — Edm type to business type mapping
8. Appendix: SAP Attribute Legend — What each sap:* attribute means

Output 3: CSV / Flat File Export

For integration with other tools, data governance platforms, or quick lookups:

import csv

def export_flat_csv(entities, output_path):
    headers = [
        'Entity', 'Field_Name', 'Label', 'Data_Type', 'Max_Length',
        'Required', 'Key_Field', 'Creatable', 'Updatable', 'Filterable',
        'Sortable', 'Default_Value', 'Custom_Field', 'Category'
    ]
    with open(output_path, 'w', newline='', encoding='utf-8-sig') as f:  # BOM for Excel compat
        writer = csv.DictWriter(f, fieldnames=headers)
        writer.writeheader()
        for entity_name, entity in entities.items():
            category = categorize_entity(entity_name)
            for prop in entity['properties']:
                writer.writerow({
                    'Entity': entity_name,
                    'Field_Name': prop['name'],
                    'Label': prop.get('label', ''),
                    'Data_Type': simplify_type(prop['type']),
                    'Max_Length': prop.get('max_length', ''),
                    'Required': 'Yes' if not prop.get('nullable', True) else 'No',
                    'Key_Field': 'Yes' if prop['name'] in entity['keys'] else 'No',
                    'Creatable': prop.get('creatable', ''),
                    'Updatable': prop.get('updatable', ''),
                    'Filterable': prop.get('filterable', ''),
                    'Sortable': prop.get('sortable', ''),
                    'Default_Value': prop.get('default_value', ''),
                    'Custom_Field': 'Yes' if prop['name'].startswith('cust_') else 'No',
                    'Category': category,
                })

Output 4: HTML Interactive Data Dictionary

Generate a single-file HTML with search, filter, and entity navigation.

<!-- Features to include: -->
<!-- 1. Sidebar with entity list (collapsible by category) -->
<!-- 2. Search bar filtering entities and fields -->
<!-- 3. Field table with sortable columns -->
<!-- 4. Color coding matching Excel scheme -->
<!-- 5. Entity relationship links (click nav property → jump to target entity) -->
<!-- 6. Export to CSV button (client-side) -->
<!-- 7. Stats dashboard at top (total entities, fields, custom fields) -->

Output 5: Instance Comparison Report

When two metadata files are provided:

def compare_metadata(entities_a, entities_b, label_a='Instance A', label_b='Instance B'):
    comparison = {
        'only_in_a': [],        # Entities only in first instance
        'only_in_b': [],        # Entities only in second instance
        'in_both': [],          # Entities in both
        'field_differences': {} # Per-entity field-level diffs
    }

    all_entities = set(entities_a.keys()) | set(entities_b.keys())

    for entity_name in sorted(all_entities):
        in_a = entity_name in entities_a
        in_b = entity_name in entities_b

        if in_a and not in_b:
            comparison['only_in_a'].append(entity_name)
        elif in_b and not in_a:
            comparison['only_in_b'].append(entity_name)
        else:
            comparison['in_both'].append(entity_name)
            # Compare fields
            fields_a = {p['name'] for p in entities_a[entity_name]['properties']}
            fields_b = {p['name'] for p in entities_b[entity_name]['properties']}
            only_a = fields_a - fields_b
            only_b = fields_b - fields_a
            if only_a or only_b:
                comparison['field_differences'][entity_name] = {
                    f'only_in_{label_a}': sorted(only_a),
                    f'only_in_{label_b}': sorted(only_b),
                }

    return comparison

Data Type Mapping Reference

Always simplify OData Edm types to business-friendly names:

TYPE_MAP = {
    'Edm.String': 'Text',
    'Edm.Int16': 'Integer (16-bit)',
    'Edm.Int32': 'Integer',
    'Edm.Int64': 'Long Integer',
    'Edm.Decimal': 'Decimal',
    'Edm.Double': 'Double',
    'Edm.Single': 'Float',
    'Edm.Boolean': 'Yes/No',
    'Edm.DateTime': 'Date & Time',
    'Edm.DateTimeOffset': 'Date & Time (UTC)',
    'Edm.Time': 'Time',
    'Edm.Binary': 'Binary',
    'Edm.Byte': 'Byte',
    'Edm.Guid': 'GUID',
    'Edm.SByte': 'Signed Byte',
}

def simplify_type(edm_type):
    if edm_type in TYPE_MAP:
        return TYPE_MAP[edm_type]
    # Handle complex types and enums
    if '.' in edm_type:
        return edm_type.split('.')[-1]  # Return just the type name
    return edm_type

Common SuccessFactors Entity Groups

When generating the Summary sheet, group entities into these categories for readability:

Workflow Summary

Quick Parse (Single Entity)

1. User asks about a specific entity (e.g., "What fields are on EmpJob?")
2. If metadata file is uploaded → Parse XML, extract that entity
3. If SF MCP tools available → Call get_configuration(entity="EmpJob")
4. Display field list in a clean table (in chat or Excel)

Full Data Dictionary Generation

1. User uploads $metadata XML or requests full parse
2. Parse entire metadata document
3. Generate multi-sheet Excel workbook following formatting standards above
4. Save to /mnt/user-data/outputs/SF_Data_Dictionary.xlsx
5. Present to user with summary stats

Instance Comparison

1. User uploads two metadata files
2. Parse both files independently
3. Run comparison logic
4. Generate comparison Excel with:
   - Summary sheet (counts, match %)
   - Entities only in Instance A
   - Entities only in Instance B
   - Field-level differences for shared entities
5. Color-code: Green = match, Red = only in A, Blue = only in B

Error Handling & Edge Cases

Namespace Detection

SF instances may use different OData/EDMX namespace versions. Always auto-detect:

def detect_namespaces(root):
    tag = root.tag
    # Extract namespace from root tag
    if '{' in tag:
        edmx_ns = tag.split('}')[0].strip('{')
    # Look for Schema namespace in children
    for child in root.iter():
        if 'Schema' in child.tag:
            edm_ns = child.tag.split('}')[0].strip('{')
            break
    return {'edmx': edmx_ns, 'edm': edm_ns}

Large Metadata Files

Some SF instances have 500+ entities and 10,000+ fields. Strategies:

• Stream parse with iterparse for files > 50MB
• Limit entity sheets — only create per-entity sheets for the top 50 most important entities; keep the flat "All Fields" sheet for everything
• Chunk Excel writes — write in batches to avoid memory issues

Missing SAP Attributes

Not all instances expose sap:label, sap:creatable, etc. Gracefully default:

def get_sap_attr(element, attr_name, default='N/A'):
    return element.get(f'{{http://www.sap.com/Protocols/SAPData}}{attr_name}', default)

Custom Fields (cust_*)

Always flag custom fields with visual indicators. These are the fields customers added via MDF and are critical for:

• Migration planning
• Instance comparison (custom drift)
• Integration mapping

Dependencies

• Python stdlib: xml.etree.ElementTree for XML parsing
• lxml (optional): For XPath and better namespace handling (pip install lxml --break-system-packages)
• openpyxl: For Excel generation (pre-installed)
• pandas: For data analysis and CSV export (pre-installed)
• docx skill: For Word document generation (reference /mnt/skills/public/docx/SKILL.md)
• xlsx skill: For Excel best practices (reference /mnt/skills/public/xlsx/SKILL.md)

Best Practices

1. Always simplify data types — Business users don't know what Edm.DateTimeOffset means. Show "Date & Time (UTC)".
2. Highlight what matters — Key fields, required fields, and custom fields should visually pop.
3. Include field counts — Every summary should show total fields, required, custom, and navigations.
4. Group by category — Never dump 500 entities in a flat list. Always categorize.
5. Add legends — Every Excel output needs a legend sheet explaining colors and abbreviations.
6. Use freeze panes — Always freeze the header row and optionally the entity name column.
7. Auto-filter everything — Users will want to filter by type, required, category, etc.
8. UTF-8 with BOM for CSV — Ensures Excel opens CSVs correctly with special characters.
9. Name sheets carefully — Excel sheet names have a 31-character limit. Truncate long entity names.
10. Test with real data — SF metadata varies significantly between instances. Always validate parsing against the actual uploaded file structure.