1

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...)-----
  • You can certainly parse the schema yourself using describes...what are you trying to accomplish? Do you really need to see every object? – Adrian Larson Sep 22 '16 at 16:33
  • Yes, potentially "all of them" - the idea is to have "every table in Salesforce" available for easy reporting through a different tool. So, the goal is to parse a given dump of objects and build an entity-relationship model of their interconnections. – k.. Sep 22 '16 at 16:34
  • Is visual representation good enough? There's Schema Builder... – Adrian Larson Sep 22 '16 at 16:36
  • Nope. Needs to be textual so it can be transformed into calls against the destination tool's model-building API. – k.. Sep 22 '16 at 16:36
  • 1
    I don't think it is possible to build anything that can be serialized, since you cannot avoid the possibility of circular references. – Adrian Larson Sep 22 '16 at 16:38
3

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;
    }
}
  • Thanks, Adrian! Was simultaneously working on the same "this points to that" idea in Python; updated original question. I guess now my only question left is ... "Was I overthinking this at time of OP?" (Is a textual entity-relationship model, for the purposes of migrating the "relationships," probably nothing but a list of "all arrow-tails and their corresponding arrow-heads, plus some TailCanBeNull metadata about each"?) – k.. Sep 22 '16 at 20:14
  • 1
    Um, what? If you want to store all the fields it certainly gets more complicated. Your model in the updated question is flawed because some lookups are polymorphic, so tableReferredTo would need to be plural. – Adrian Larson Sep 22 '16 at 21:27
  • Completely forgot about polymorphic fields - thank you! – k.. Sep 28 '16 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.