Salesforce Metadata API - Easy Way To Get Entity Relationships / Joins?

Question

Is anyone aware of something free, that already exists, that can comb output from the Metadata API and output useful information about joins between tables?

I just wrote my colleagues the following e-mail:

Right now, as I understand it, it’s relatively easy to dump the schemas & contents of a bunch of Salesforce tables into Cognos Framework Manager as raw data. (After all, we've already done it.)

But telling Cognos Framework Manager how to re-connect those tables to each other - in ways that replicate the ways they're connected to each other in Salesforce - is extremely manual (click-and-configure). Plus it involves actually knowing those relationships as they stood in Salesforce.

However, it looks like Salesforce and Framework Manager both have "Metadata APIs."

In theory, code could be written that:

1. Extracts a schema of “table joins” from Salesforce
2. Transforms those joins into something that Framework Manager would understand
3. Loads a bunch of useful pre-joined views into a Cognos package (without ever having to open the GUI version of Framework Manager)

Right now, I think we're mostly in a paradigm "do your own joins using Cognos Report Studio at report-making time."

And right now, that approach probably consumes fewer staff-hours than doing inter-API programming. (See XKCD's "automation" theory: https://xkcd.com/1319/)

But if that workload balance ever starts to shift to the tipping point of "this would be worth automating," it looks like it may be able to be done.

If "step 1" ("extract a schema of 'table joins'") has already been done, or significant progress towards it has been made, that'd help reduce the scope of the work a lot.

Does anyone know if others have already blazed such a trail?

---

Updated question:

Q: If my "destination" API just wants 2 fully-qualified base-table field names, "TableReferredTo.Id" & "TableReferring.ForeignKeyFieldName," to set up a join, then I'm done, right? Shouldn't I be able to just iterate through the lower level of this Python dict issuing "create join" API calls?

Code Example (Python):

import os, fnmatch
import xml.etree.ElementTree as ET

ns = '{http://soap.sforce.com/2006/04/metadata}'

objdumppath = 'C:\\temp\\dumpeddata\\'
objapis = [f.rstrip('object').rstrip('.') for f in fnmatch.filter(os.listdir(objdumppath), '*.object')]

relationshipsdict = {}

for o in objapis:
    tablefields = ET.parse(objdumppath+o+'.object').getroot().findall(ns+'fields')
    relationshipsdict[o] = {}
    for f in tablefields:
        if f.find(ns+'fullName') != None and f.find(ns+'referenceTo') != None and f.find(ns+'type') != None and f.find(ns+'type').text in ['MasterDetail','Lookup']:
            fieldFullName = f.find(ns+'fullName').text
            fieldType = f.find(ns+'type').text
            fieldRefersTo = f.find(ns+'referenceTo').text
            #relationshipsdict[o+'.'+fieldFullName] = {'fieldFKType' : fieldType, 'tableReferredTo' : fieldRefersTo}
            relationshipsdict[o][fieldFullName] = {'fieldFKType' : fieldType, 'tableReferredTo' : fieldRefersTo}

Printed out pretty, the nested-dicts look kind of like this:

-----Object "Employment_History__c" and its foreign-key fields:-----
{
    'Appl__c':  
            {   'fieldFKType': 'MasterDetail',
                'tableReferredTo': 'SpecialPkg__Application__c'
            }
}
-----Object "SpecialPkg__Supplemental_Doc__c" and its foreign-key fields:-----
{
    'SpecialPkg__Appl__c':
            {
                'fieldFKType': 'Lookup',
                'tableReferredTo': ' SpecialPkg__Application__c '
            },
     'SpecialPkg__Other_Contact__c':
            {
                'fieldFKType': 'Lookup',
                'tableReferredTo': 'Contact'
            }
}
-----(and so on...)-----

Or, built differently (no outer-loop dict addition, and change inner-loop dict addition to relationshipsdict[o+'.'+fieldFullName] = {'fieldFKType' : fieldType, 'fieldReferredTo' : fieldRefersTo+'.'+'Id'}), more like:

-----Employment_History__c.Appl__c-----
{'fieldFKType': 'MasterDetail', 'fieldReferredTo': 'SpecialPkg__Application__c.Id'}

-----SpecialPkg__Supplemental_Doc__c.SpecialPkg__Appl__c-----
{'fieldFKType': 'Lookup', 'fieldReferredTo': 'SpecialPkg__Application__c.Id'}

-----SpecialPkg__Supplemental_Doc__c.SpecialPkg__Other_Contact__c-----
{'fieldFKType': 'Lookup', 'fieldReferredTo': 'Contact.Id'}

-----(and so on...)-----

Answer 1

If you just want a simple mapping of which objects are related to which others, you could roll your own naive implementation fairly simply. You could even hook it up to a REST service. Just for fun...

public with sharing class SchemaModel
{
    final Map<SObjectType, Set<SObjectType>> model;
    public SchemaModel(List<SObjectType> sObjectTypes)
    {
        model = new Map<SObjectType, Set<SObjectType>>();
        for (SObjectType sObjectType : sObjectTypes)
            model.put(sObjectType, new Set<SObjectType>());
        for (SObjectType sObjectType : sObjectTypes)
            addRelationships(sObjectType);
    }
    void addRelationships(SObjectType sObjectType)
    {
        for (SObjectField field : sObjectType.getDescribe().fields.getMap().values())
            addRelationships(sObjectType, field.getDescribe().getReferenceTo());
    }
    void addRelationships(SObjectType sourceType, List<SObjectType> referenceTo)
    {
        for (SObjectType targetType : referenceTo)
            if (model.containsKey(targetType))
                model.get(sourceType).add(targetType);
    }

    public override String toString()
    {
        Map<String, Set<String>> simpleModel = new Map<String, Set<String>>();
        for (SObjectType key : model.keySet())
            simpleModel.put(String.valueOf(key), getRelationships(key));
        return JSON.serialize(simpleModel);
    }
    Set<String> getRelationships(SObjectType key)
    {
        Set<String> referenceTo = new Set<String>();
        for (SObjectType targetType : model.get(key))
            referenceTo.add(String.valueOf(targetType));
        return referenceTo;
    }
}