April 17, 2017 at 11:05 am
For reporting purposes we have synced our SFDC data onto a SQL server using a third party middleware.
Everything looks great, the data appears to all be there, but there are no relationships on the SQL database.
I've looked through code snippets of "finding missing foreign key relationships" but they all seem to rely on a match in the column names and table names.
All SFDC tables have a primary key of "id" but the foreign key columns in other tables can be named something very dissimilar.
For instance, Table 1 is "Accounts" and has a column (Store_Location__c) linking to Table 2 "Locations" primary key (id).
On another table, "Leads" has a column (Dealer_Location__c) linking to Locations.id.
Most of the code snippets I have seen look for a similarity in the column name of Table 1 to the table name of Table 2.
I guess what I am looking for is a something that will crawl the database and say 2000 records from Accounts.Store_Location__c match Locations.id but do that for all 850+ tables and stop after so many matches.
I would use this list to create the foreign key relationships in the SQL database.
Any assistance or direction is much appreciated!!!
April 17, 2017 at 11:09 am
I assume that this is not a read-only copy of SFDC data, otherwise I see no reason to bother ... the FKs have already been enforced in the source system.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 17, 2017 at 11:46 am
If FK constraints have been defined, you can use views:
sys.foreign_keys and
sys.foreign_key_columns
to check for invalid / missing FK values. If you're not worried about checking NULL values, I would think that if the foreign key is trusted, you wouldn't need to check the actual values. If it's not trusted, you would.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 17, 2017 at 1:52 pm
Phil,
This database will be used for end-user BI tools usage as well.
Since the relationships are not defined on SQL, the end-users would have to know to which object a 15+ digit key refers.
I believe recreating the relationships would make it easier for them in tools like Power BI.
Scott,
No relationships have been defined.
As of now, our SQL server believes there are 850+ standalone tables.
I have thought of going into schema builder and recreating all of the relationships but that would take weeks.
April 17, 2017 at 1:59 pm
If you are looking to get the relationships between the SFDC objects to apply the same relationships to your SQL tables then you can use the Schema Builder in SFDC to view these.
You can also use the describe function in the API to return this information - maybe this is something you can factor into your middleware.
October 17, 2017 at 7:36 pm
Curious how this turned out - were you able to track down the keys?
October 18, 2017 at 8:18 am
ReReplaced - Tuesday, October 17, 2017 7:36 PMCurious how this turned out - were you able to track down the keys?
Nope.
I know I could write a script to do this but I haven't had the time to do so. 🙁
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply