Salesforce has an easy-to-use data modelling feature in the Schema Builder. This provides a graphical data modelling interface to the Salesforce metadata, allowing the user to choose which Objects to include in the diagram, and with zooming and navigation features.
However, when there are a significant number of Custom Objects, or there is a need to bring the Salesforce metadata ‘out of the cloud’ for any kind of ‘data related’ project (Data Warehouse, Data Integration, Master Data Management….), the Schema Builder is not really suitable. It has limitations in that it is not possible to subset tables relating to specific business areas, to save those models or share them with other tools or colleagues. In addition there are no capabilities for comparing data models between different instances.
Safyr® for Salesforce from Silwood Technology is a tool developed to liberate the metadata from any Salesforce instance, or from anything built using tools from the Web Cloud, and has a rich set of features for discovering, sub setting, visualizing and comparing that metadata. Safyr has had similar capability for other packages like SAP and Siebel for a number of years and is used and trusted by hundreds of large corporates in all regions of the worlds and across a range of industry sectors.
You do not need to be an expert in Salesforce to use Safyr. Here is a step by step process on how you can use it to attach to a Salesforce system and ‘reverse engineer’ the data definitions.
The first step is to create a Safyr Repository to store the extracted metadata. You can use a database like Oracle or Microsoft SQL Server for this, or create a local Repository in SQLite, which is a desktop database included in the Safyr software. For this blog we will use SQLite. From the Safyr Repository Manager you use a wizard to create a new repository, firstly giving it a convenient name and (optionally) a description.
Creating a new repository in Safyr for Salesforce
Then you specify the type and location of the database to be used for the Safyr repository:
Configuring the repository database in Safyr to hold the Salesforce metadata
Clicking on the ‘Create Database’ button will then create a SQLite database in the location you have specified and also create the tables that Safyr needs to store the extracted Salesforce metadata. If you chose one of the other Database Types for the repository, the steps are a little different, but the end result would be the same: an empty database ready for the metadata to be loaded.
The next screen of the Repository setup wizard is where you specify what type of system is to be reverse engineered:
Telling Safyr that this will be a Salesforce (or App Cloud) source application
…and the next screen is where you choose the database type that corresponds most closely to the Salesforce database:
Selecting a database type
I’d recommend you choose ‘Oracle’. It’s not that important, but when you come to export the metadata into other environments (e.g. modelling tools), Safyr uses the Database type you selected to determine what datatypes to assign to the columns.
The last screen of the Wizard is where you record the details of the Salesforce connection:
Setting up the Safyr connection to Salesforce
The content of this screen is largely self-explanatory. You need to specify the URL of the Salesforce instance you want to connect to, and you need the /service/Soap/c/33.0 at the end, which is the API version to use – we recommend you choose 33.0.
Then you need the User Name/Password/Security Token of the Salesforce user. The next step is to specify the Extract File Path. In addition to populating the Safyr repository with the extracted Salesforce metadata, each set of ‘objects’ (tables, columns, relationships…) is written to a text file in this folder. It would not normally be necessary to use these files. However, they can also be used to rerun the repository population without making a connection to the Salesforce system.
You might like to click the ‘test Connection’ button to make sure the credentials you have entered work.
Finally, if you click ‘OK’ then the Repository is created and configured and is ready to do the extraction of metadata from Salesforce.
The extraction of metadata from Salesforce also uses a Wizard. You start it from menu Safyr screen, which has a series of clickable tiles:
Begin the extraction process by selecting “Extract from ERP”
Choose the ‘Extract from ERP’ tile to start the Salesforce Extract Wizard.
There are a number of screens for the Wizard, but you can normally leave the settings at their default, until you get to the ‘Test and Start the Extraction’ screen. Click ‘Test Extract’. This checks that the connection credentials are correct and that the expected Salesforce metadata is accessible. It takes just a few seconds to run.
Test and run the Salesforce metadata extraction process
Then you can acknowledge the Test by clicking ‘OK’ and then clicking the ‘Start Extract’ button will run the extract proper.
Once this has completed you can close the Extract Wizard and begin to explore the results.
A good place to start is the ‘Show List of Tables’ tile (the orange one in the screenshot above). Clicking this and then clicking the ‘Search’ button will show a list of all the tables reversed engineered into Safyr.
Searching for individual tables and finding relationships
The number of Objects (or Tables if you like) is shown at the bottom of the screen, in this case 241. If you Right Mouse Click on a table and choose ‘Table Relationships’, the pop-up window shown will appear with details of all the directly related tables. In this case it’s ‘Account’.
You can use the Filter fields along the top to narrow down the tables you see (e.g. enter *Account* to see only those tables containing the string ‘Account’). The Advanced Search has a number of further search capabilities, including searching for tables containing a given field, or to filter out tables based on the Row Count (e.g. Only show me tables that contain data).
Whatever reason you have for wanting to get a handle on the Salesforce metadata, it’s likely you don’t want to do that for all the tables in the system – or at least you want to do it in manageable chunks.
Safyr has the concept of a Subject Area to represent any user-defined grouping of tables. You can create as many Subject Areas as you like and you can populate each of these with any or all of the tables in the salesforce instance. Here’s one I created by taking the Account table and adding all the ‘Parent’ and ‘Child’ related tables for Account:
Creating a subset of the entire model called a Subject Area
Once you have one or more Subject Areas there are a number of things you can do with them:
- You can subset the list of tables we started with to just those in the Subject Area
- You can export the Subject Area into a range of export formats (more about this below)
- You can take two subject areas and compare the metadata for the tables and relationships that belong to those Subject Areas
Lets’ take the second option – Exporting. Once again, Safyr uses a Wizard to help with this. Having chosen one or more Subject Areas to be the focus of the exercise, you can then choose what kind of format to export to:
Exporting and sharing that Subject Area with other tools and technologies
One of these is Safyr’s own diagramming capability. Below is a small example of what a diagram looks like. If I’d chosen one of the popular modelling tools like ERwin or PowerDesigner, the result would have looked similar. However, those tools have a rich set of functionality for managing the metadata created.
Partial view of the ‘Account Tables’ Subject Area created earlier
Another format to export to is the Comparison File. Doing this for two different Salesforce instances and the using the Safyr ‘Compare Subject Areas’ feature is a powerful way to identify how two Salesforce instances differ, or how a given Salesforce instance has changed over time.
The screenshot below shows the kinds of differences you can see with this feature.
It is easy to compare partial or complete instances of Salesforce
As organisations are increasingly adding to and customising their Salesforce data model or implementing 3rd party applications from the App Cloud the size and complexity of that data model will grow.
In addition, the need to integrate, report from and govern Salesforce data will be critical as it becomes a more important component of an enterprise information ecosystem.
To support this requirement it is vital that the data model underpinning Salesforce and App Cloud systems is easily accessible and usable by data professionals in the context of Information Management projects in which they are engaged.
Safyr delivers this facility in a cost effective and quickly implemented software tool and allows users to be able to find the data models they need, when they need them and then to use that information to accelerate project delivery and improve accuracy.
For more information please visit Safyr for Salesforce or download a free evaluation copy of Safyr.