Blog Posts

Talend & PostgreSQL - Part 2 - Setup

This is a follow up post to part 1 "Talend & PostgreSQL - Data Migration Dream Team"
Talend & PostgreSQL - Part 2 - Setup

Talend & PostgreSQL - Part 2 - Setup

This is part 2 of a blog post about utilizing Talend and a PostgreSQL database to assist with data migrations.  Part 1 outlines the reasons to use an ETL + database approach to migrating data and generally outlines the tools involved.  You’ll also find links to download and install the tools needed to complete the following setup.



Setting Up your database:


Once you’ve installed your tools we’re ready to start setting up connections to our various datasets.  In this case, two Salesforce orgs and a PostgreSQL database.  First, lets add a database and a table to our PostgreSQL server.  Open pgAdmin.  Goto ‘Object’--> Create → Database.  I’ll call mine ‘IDLookup’.  Save.


Next we’ll add a table with some columns.  

Goto ‘Tools’ → ‘Query Tool’   Past in the following:  


CREATE TABLE id_table

(

   "Legacy_ID__c" VARCHAR(18),

   "Id" VARCHAR(18)

)


Press Execute / F5

Screen Shot 2017-04-28 at 3.56.09 PM.png




Executing this script will create a table with two columns ‘Legacy_ID__c’ and ‘Id’.

After refreshing the view:  (‘Object’ → ‘Refresh’)  You’ll see the table show up under ‘your table name’ → ‘Schemas’ → ‘Public’ → ‘Tables’


Screen Shot 2017-04-28 at 3.56.51 PM.png


Note that we just created a table via the query tool.  CUD (create, update, delete) are all possible for tables and records in a SQL database using the query tool.


Connecting Data Sources to Talend

Now that we have a table to connect to in our database we can switch to setting up Talend.  


Once Talend is installed, be sure to install additional packages from the help menu.  Some of these will be needed in order to connect with Salesforce.


Screen Shot 2017-04-28 at 1.21.26 PM.png



We’ll start by creating a connection with the PostgreSQL database.  

In Metadata, create a new Db Connection.


Name the connection and fill in the database details. Click ‘check’ to make sure you’ve successfully connected to the database.  The default port will be 5432.  In order to make sure, execute the following script in the query tool of pgAdmin:


SELECT *

FROM pg_settings

WHERE name = 'port';



Note: mine is running on 5433, so that's what I’ll enter in Talend.


Once you’ve connected to the database you’ll need to retrieve the schema.




Select the table/s to include in your schema.



Click ‘Finish’.










You’ll see the database, table, and columns in your metadata repository.






Next will be to create connections with each of the Salesforce orgs we’ll be working with.

Create a new Salesforce connection from the Metadata section.  




Enter your org credentials and click ‘Test connection’.  If you’re connecting to a sandbox be sure to update the Salesforce url in the ‘Advanced…’  section from: https://www.salesforce.com/services/Soap/u/37.0  to https://test.salesforce.com/services/Soap/u/37.0



Click ‘Next’ and then select all the objects you’ll be exporting/importing to/from the org.










Repeat for each org you’d like to connect for this job.



Now that we have all of our connections available to select from metadata.  We can start adding components to our job.  


The first thing we’ll do is extract all the Account records from our Source org and move them into our destination org.  


Drag the ‘Account’ object from the Source Salesforce Org onto the design screen.  Select the tSalesforceInput component.  From the Palette add a tmap component and finally select the ‘Account’ object from the Destination Salesforce org and select the tSalesforceOutputBulkExec.

Connect the components by dragging the connector from tSalesforceInput to tMap and then from tMap to tSalesforceOutputBulkExec. This should populate the schema from both orgs into the tMap component.  





Double click the tMap and simply drag fields from the source on the left to the destination on the right. Most importantly, map the Id field from the source to an external Id field in the destination org.  In this case, I’m mapping to OldSalesforceID__c.


Formula fields and some audit fields will have to be removed from the destination schema.  Created Date and Created by Id can be set only on an insert and the profile of the credentials used to login through Talend will need to have set Audit field permissions.


Screen Shot 2017-04-28 at 11.55.57 PM.png


Run the job.  


Now we’ll create and run a job to populate both the legacy and new id from the Destination Salesforce org into our PostgreSQL database. This time select a tSalesforce input after dragging the ‘Account’ object from the Destination Salesforce org onto the design screen followed by a tMap into a tPostgresOutput.



Edit the map to put the Id from the account to the Id column of the database schema and the OldSalesforceID__c field into the Legacy_Id column.



Deactivate the previous sub job before running this one.  Run it.  


At this stage, we’ve migrated the accounts from one org to the other and created a map of ids for those accounts in the id_table our PostgreSQL database. When we migrate the contacts we’ll use that map to assign contacts from the source org to the correct account in the destination org.  




Next, we’ll setup the job to migrate the contacts and reference the database. Add both the ‘Contact’ object tSalesforceInput and a tPostgresqlInput  component that references the id_table as inputs to a tMap component. The output will be tSalesforceOutputBulkExec to the ‘Contact’ object from the Salesforce Destination org.



Map the AccountId field to the Legacy_ID__c field of the tPostgresqlInput  then map the Id field of the tPostgresqlInput  to the AccountId field of the output.  Referencing the Id_table in our database is that simple and it can be done as many times as necessary for any number of lookups on the object.  Just add another instance of the tPostgresqlInput for each lookup.  




Run the job and your contacts will insert with the correct account ids.  Since created_by_id and created_date can only be set via insert, this method will allow you to correctly migrate those values.  


This is a very basic example of what can be done using the combination of an ETL and database to assist with data migrations.  Like anything worth learning, it may take some time to wrap your head around the potential benefit of using these tools.  Take your time and make sure you do your learning in a sandbox.  



Have any great ETL learning stories? Share them with me on the Arkus Facebook page, in the comments below, in the Success Community, or to me directly via Twitter at @jpbujold

Arkus Pro Bono Day - Spring 17

Arkus’s Spring 17 Pro Bono Day was all about empowering Admins.
Arkus Pro Bono Day - Spring 17

Arkus Pro Bono Day - Spring 17

As part of Arkus’s commitment to giving back to our communities, we host at least two Pro Bono Days each year, where we invite local nonprofits to join us in the office for learning and free consultations. This spring we focused on tools that admins can use to better support their Salesforce orgs.

Duplicate Management

We started the day with Peter White sharing some great information on managing duplicates in Salesforce. We learned the importance of understanding our data sources and what impact that can have on the likelihood of having duplicate records in a database.

We learned about resources for learning more about duplicates, including Trailhead, and the Salesforce native Duplicate Management setup. We got a live demo of setting up matching rules and duplicate rules, and we talked about the host of third party apps that can help an admin dedupe their existing data.

Duplicate management is an important part of an administrator’s role. Peter also shared some of his tips here on the blog.


Tips and Tricks for Declarative Lookup Rollup Summaries

Following duplicate management, we learned some use cases for the open source tool Declarative Lookup Rollup Summaries (DLRS) from Ashley Leifer. First we learned the difference between Master-Detail relationships and Lookup relationships. Where a Master-Detail relationship allows rollup summary fields natively, lookup relationships don’t. Enter DLRS!

DLRS allows an administrator to declaratively (using clicks, not code) define a trigger that searches for related child records in a lookup relationship and rolls up the information. We learned about some of the coolest features from DLRS, including the ability to roll up text information or formula fields.

Ashley then walked us through the tool with real-world use cases. We saw an example of rolling up Affiliation information to a Contact to see an individual’s previous workplaces. We also saw a custom Expense object rolled up to a Campaign.

Ashley Leifer describes the benefits of DLRS


Introduction to Workbench

We finished the morning with an introduction to Workbench from James Bujold. Like DLRS, Workbench is a Salesforce tool that helps an administrator do even better by their org and their users. Workbench is a series of tools that allow an administrator to dig into their org - both metadata and data.

He walked us through use cases for Workbench, including using the query builder to find recently deleted records and undelete them or finding the source of a flow error by searching through the metadata for the id. Perhaps the highlight for the group was the Password Management utility, which allows an admin to set or reset a user’s password and assign specific text as a new one (temporarily).


James Bujold shares some Workbench use cases

We love sharing knowledge and empowering admins, and our favorite part of these events is sitting down afterwards and talking about the real challenges that our participants face every day. Thank you again to those that joined us, and we look forward to seeing more of you at our next one.


Have more use cases for these tools? Just want to say hi? Follow us on Twitter, on the Salesforce Community, Facebook, or chat with me @thesafinhold.


Permission Sets in a Lightning World

If you know me, you know I love Permission Sets. They are my favorite non end user facing feature on the Salesforce platform. It’s a geek thing I guess, but give me some granular permissions to rollout to specific users, and I’m a happy camper.
Permission Sets in a Lightning World

Permission Sets in a Lightning World

How have they changed since they first rolled out?

The biggest change is actually not to Permission Sets themselves but actually to the platform that they reside on. Salesforce has changed so much since Permission Sets launched about five years ago. There was no concept of Lightning. There was no Einstein. There wasn’t even Salesforce1 Mobile. It was good old Salesforce Classic with “Apps” that were essentially a series of tabs that users could get to through other means anyway. Since Permission Sets were always built API-first, they easily kept up with the fact that Salesforce has increased all of the features and functions that come along with the platform. Virtually any feature can be turned on and off via a Permission Set, including really big changes for end users like the ability to be a Lightning Experience user or not.


Are they any different now in Lightning Experience?

Simple answer, no. Permission Sets have largely stayed the same in terms of their utility. Their main use is to apply a permission, or set of permissions, to individual users to provide them more access to features on the platform. Of course there are some new features in the Summer 17 release that make life a little easier, like Standard Permission Sets. Just how Salesforce encouraged all ISVs to ship their products with Permission Sets, Salesforce is following suit. An example would be the standard Permission Set for Sales Console. If you purchase five Sales Console Licenses, you can simply assign the predefined Permission Set to five users, and they automatically get the license and ability to use the console. Nifty…

The biggest difference is really how an administrator can go about launching Lightning Experience to the organization. This was a large breakthrough back when Chatter rolled out, and it was all or nothing. Salesforce learned from that “mistake” and allowed, via Permission Sets, to roll Lightning Experience out at a pace that is comfortable and manageable.


A Sign of Maturity Early On

Did you need to change any of your legacy Permission Sets because you flipped to Lightning Experience? Another simple answer: no, you didn’t. This is a testament again to the API-first approach of building out Permission Sets. There are a lot of features on the Salesforce platform that are being “Lightningized,” but Permission Sets are not one of them; they just work the way they have always worked. Nothing new to learn, just create and assign ad-hoc permissions as your heart desires (using The Permissioner of course).

What Next (IMHO)?

Moving forward I would love to see some improvements to Permission Sets to keep up with some of the Lightning features, such as component-level permissions, as opposed to page-level permissions. This has always been a bit of a gripe, but you cannot control page layouts via a Permission Set (which I understand from a technical perspective but hey, I’m just a user here, and I want my page and component level permissions in a Permission Set). Imagine a Lightning App Page with six custom components on it. Now imagine being able to control which users see which components on the page based on a Permission Set - that is a pretty custom, tailored experience. The example before could also have major implications for Community rollouts, as Community Templates get more and more popular.

All in all, Permission Sets remain tried and true. Through all the turbulence of migration to Lightning Experience, which is still happening and will continue to happen for at least a few years to come, Permission Sets remain one of the more reliable tools in the shed.

Please feel free to comment below, on the Salesforce Success Community, on our Facebook page, or directly at me on Twitter @JustEdelstein.

Summer 17 Release Notes Rapid Reaction

A look at the Summer 17 release notes preview and some quick thoughts and things that jump off the PDF.
Summer 17 Release Notes Rapid Reaction

Summer 17 Release Notes Rapid Reaction

We are going to give Justin a break on this one, as he has been writing these for seven years now. Relax Justin, I got this. Like Cloud Scheduler, you can take a break.

Here we are just getting used to Spring ‘17 and in comes Summer, bigger and hotter than before. Here are a few of the new features coming that stuck out in my quick review of the preview release notes, which are currently weighing in at 541 pages.

Einstein Everywhere

The theme of the release notes over the last year has definitely been Lightning, but now, in Summer ‘17, we get a big splash of Einstein. Einstein is Salesforce’s marketing brand of their Artificial Intelligence (AI) learning engine, which will make suggestions based on your data patterns in Salesforce. In Summer ‘17 we are going to get a lot of Einstein, but for a price. Einstein will offer insights into activities, contacts, leads, opportunities and cases, all done under different levels of licensing. This really is one of those “Talk to your Account Executive” moments.

Navigate List Views with Your Keyboard

Keyboard navigation is a huge thing in Summer ‘17 and one of those things that will be a big pull into Lightning. There will be keyboard navigation in a lot of different places, but I can’t think of a better one than in list views. This new feature will allow you to get to the list view table and actions right from the comfort of your keyboard. That is true productivity.  

Use Your Classic Email Templates in Lightning Experience

This is one of those features that is so huge that it can’t go unmentioned. Email templates are a large productivity boost in Classic, and it looked for a while that your old email templates would not make the jump to Lightning Experience (LEX.) Put down your pitchforks because Classic Email Templates are going to be available in Lightning in Summer ‘17. We can check that one off the list.

UPDATE: Sadly, this does not include case email templates.

Promote Your Picklist Fields to Global Value Sets

Admins, get on the dance floor. Summer ‘17 is bringing a great new feature to help out all of our picklists. We will now be able to take a normal old picklist field and promote it (with a raise?) to a global value set. Excuses for maintaining different pick lists with same values, be gone. Just do it!

RIP

I have to do a special shout out (pours some on the floor) to two features retiring in Summer ‘17. Both the Cloud Scheduler and Stay-in-Touch Requests are headed to Boca this summer. Stay-in-Touch Requests  areas old as I can remember, but something I always turned off in Salesforce immediately. Cloud Scheduler was actually a great feature, but never got love after the first release and suffered from the Events object being held back in 1996 in terms of features.


Are you ready for summer? Throw some comments on our Facebook page, in the Success Community, Power of us Hub or directly at me @JasonMAtwood

Talend & PostgreSQL - Data Migration Dream Team

Introduce yourself to a great way to migrate any amount of data using an ETL tool (Talend) and a database (PostgreSQL)
Talend & PostgreSQL - Data Migration Dream Team

Talend & PostgreSQL - Data Migration Dream Team

The idea of migrating massive amounts of data from any database into Salesforce can be intimidating.  If you’ve ever tried to migrate hundreds of thousands or even millions of records using Excel you’ve most likely experienced very slow processing times and regular crashes, if the program can manage to open the file at all.  One solution may be to migrate the data in batches, but this is slow and cumbersome and only increases the likelihood of human errors being made in the process.  As with any task that at first seems overwhelming, it simply needs to be broken down into its component parts and taken on one piece at a time.   Migrations of this size also require a different set of tools be used. The goal of this 2-part post is to introduce these tools and walk through the process of setting up a basic, but highly scalable, migration job from one Salesforce org to another using Talend and a PostgreSQL database.  Explaining every nuance and detail of setting up and using these tools is well beyond the scope of this post, but hopefully, this can serve as an introduction to further learning.

So what if it takes longer, why should I use an ETL tool?

Extract, transform and load (ETL) tools offer a level of automation and repeatability not achievable by a data loader.  The Salesforce Data Loader and other products like it are great for inserting, updating, deleting, and even upserting data into Salesforce. The tricky part is preparing that data to enter the database.  Id’s need to be mapped, picklist values need to be translated, and some records may need to be filtered out entirely. Performing those updates in a spreadsheet is a manual process that’s easy to do once, twice, or maybe a few times, but before long, you’ll be wishing there was some way to automate the process.  That’s where ETL tools do exactly as the name suggests extract, transform, and load, all in one place with almost infinite possibilities. Anything from migrations and simple data transfers to highly complex integrations can be run with ETL tools.  For the sake of this example, we’ll be using Talend.

Talend

Talend is a free, open source ETL tool.  This is where we’ll be setting up our Migration jobs.  You’ll want the ‘Talend Open Studio for Data Integration’. Grab the latest version here.  Why Talend? The world is full of data integration and ETL tools that could be used to perform the same process I’m going to be describing.  Talend just happens to be free and accessible for everyone. It’s also open source, which means it can be a bit buggy sometimes, and it helps to know Java when running into issues.     

ETL seems cool. What do we need a database for?

We still need a place to store our id values, so we can accurately map our record’s old lookup id’s to their new values.  Similar to a vlookup in Excel, we’ll be using one column of the table in our database to store the legacy Id values and another to store the record’s new Id. It would be possible to write the values to a csv file from the ETL tool, but doing this in a database like PostgreSQL allows for almost infinite scalability along with a way to query the database and look for errors without having to open a file with possibly millions of rows that Excel will likely choke on.

PostgreSQL + PgAdmin

PostgreSQL is an open-source multi-platform database that we’ll be running locally to handle our lookup tables.   PgAdmin is a GUI for managing and querying our PostgreSQL database.  You can find the latest version on this download page for nearly any operating system.  

Another scenario where these tools come in handy is when a migration needs to take place in a narrow time frame, like over a weekend.  It allows you to set up all the mapping and migration jobs in advance with plenty of testing so that on the day of the migration you can simply focus on running the jobs in sequence and looking our for bulk data job errors.  

Migrations can be carried out in many different ways.  Learning to use an ETL tool can help streamline the process. Combining that with a local database can help you scale those processes to handle any size dataset.  

Part 2 of this post will walk through, step by step, how to setup each of these tools to run a basic sample migration.  

What’s your favorite way to migrate data into Salesforce? Share them with me on the Arkus Facebook page, in the comments below, in the Success Community, or to me directly via Twitter at @jpbujold