Tuesday, January 30, 2018

[Salesforce / AppExchange Series] Skyvia: easy way to load your Salesforce data into Amazon Redshift

Here we are in the next Salesforce app guest post.

Skivia is a great solution for Data warehouse in the cloud, with tens of integrations and connectors to integrate, back up, access, and manage your cloud data
with an all-in-one cloud service. With Skivia service you have to think less about where to put all your data (from all your data sources on the cloud) to concentrate on analysis.

Skyvia is not an AppExchange app but it's an amazing service that fully deserves to be mentioned in one of the best services out there to work with Salesforce data.


The guest blogger

Jacob Martin is crazy about all stuff connected with coding, particularly all Hi-tech ones, such as Cloud computing, Big-Data, BI tools and CRMs in the broader sense.
He is a cloud engineer at Skyvia - the universal cloud data platform for no coding data integration, backup, management, and access.




With the powerful data analysis features and low storage costs of cloud data warehouse services, loading data from cloud applications to data warehouses becomes a very common task. For example, you may want to get your cloud data to a database and then use some BI tool, such as Looker, Tableau, or chart.io, or just to store your historical cloud data in a database for archiving or compliance purposes.

In this article I will describe a way how to load data from Salesforce to Amazon Redshift with almost no efforts using Skyvia service. It is a cloud data platform for data integration, backup, management and access, which can perform this task with no coding and very little configuration.



Replication Configuration in Just a Few Steps


Skyvia is a completely cloud solution that requires no local software except a web browser, and all actions in it are performed via web browser, in a convenient web GUI.

To replicate your data via service, first, you need to register on it. Creating an account is free, and you can try Skyvia within its free pricing plan that allows you to load up to 5000 records per month between cloud applications and databases.

After you create an account, on the Integrations page, click Create Now under Replication. A replication package editor page is opened.



Setting up Connections


In our example, we will use Amazon Redshift as a target database for replication. Note that you may need to configure your AWS security settings to allow access to your Amazon Redshift cluster from Skyvia’s IP – 40.118.246.204. This is necessary for Skyvia to be able to load Salesforce data into your Redshift database.

First, we select connection types in the Source and Target headers, as we want to etl Salesforce data to Amazon Redshift, will choose them respectively. Note that Skyvia also supports other data warehouses and relational databases, including Google BigQuery, Asure SQL DWH, SQL Server, PostgreSQL, etc. The full list of supported data sources you can find here.


After this, we need to create the corresponding connections. For Salesforce, the service supports both Username/password and OAuth connections. With the latter option, your Salesforce credentials are not stored on the Skyvia server. You simply enter a connection name, log in to Salesforce and allow Skyvia access to your data.


For Redshift, you need to specify the corresponding connection parameters. Note that for replication you not only need to specify main parameters for connecting to Redshift itself.


You also need to click Advanced and specify parameters for connecting to Amazon S3 storage.


To improve the replication performance, Skyvia creates CSV files with Salesforce data, uploads them to Amazon S3, and uses Redshift COPY command to quickly import data.


Selecting Objects


After you connected to your Salesforce and database, everything is simple. You will see the list of your Salesforce objects and you just need to select check boxes for the objects that you need to copy. A convenient object search allows you to quickly find and select the necessary objects.


You can perform complete or partial replication. For each object you can edit task and exclude some fields or set up filters to copy only a part of the data.



Running Replication


For one-time data loading this is enough. Click Save, and then you may run your replication.


By default, Skyvia automatically creates tables with the corresponding structure in the database, and in some minutes (depending on the volume of your Salesforce data), you will have an exact copies of your Salesforce objects in your database.


Keeping Data Up-to-date


Simple copying of data is not the only feature of Skyvia’s replication. However, for business analysis tasks it’s better to have access to as fresh data as possible. This task can easily be solved using flexible scheduling settings and allows automatically keep your copy of the data always up-to-date.

You can schedule your replication to run automatically and keep your Redshift database always synced with Salesforce. To configure a schedule open the created replication package for editing, and under Schedule (at bottom left of the package editor) click Set schedule.


Skyvia allows flexible schedule configuration. You can configure your replication to run monthly, weekly, daily, on specific weekdays, etc. In non-free pricing plans you may run replication and update your database with a fresh data from Salesforce every hour or every few minutes and get real-time insights on your data. Thus, after you configure your replication, you can easily keep your database always up-to-date.

Skyvia uses incremental updates. This means that every time it loads only the records that were changed in Salesforce since the previous package run, and applies the corresponding changes to the database. It can significantly improve performance and decrease costs, as the latter depends on the number of replicated records.


Universal Cloud Data Platform for Integration


Skyvia is a universal cloud data platform, and replication is only one of the supported integration scenarios. In addition to replication, it supports other operation kinds: import, export, and synchronization.

The replication is used when you need to simply copy data from a cloud app to a database or cloud data warehouse and automatically keep this copy in a current state with minimal configuration efforts. Synchronization synchronizes data in two sources bi-directionally. Export allows you to export data from cloud applications, like Salesforce, and databases to CSV files. Import serves for unidirectional data loading.

Import has some more complex configuration than replication, but it is much more flexible. It allows importing data from CSV files or from cloud applications and databases directly to other cloud applications and databases. Unlike replication, it doesn’t create target tables automatically. Instead you configure mapping between source and target tables and columns.

Powerful mapping settings allow you to load data between the data sources with the different data structure and formats while preserving relations between data. You can provide constant values, use powerful expressions, lookups, etc.


Import can be configured to load only newly updated or inserted records from Salesforce. Thus, we can perform a replication once to create the necessary tables and import all the data, and then configure import to load new and updated records from Salesforce.

As an all in one cloud data platform, Skyvia also offers a cloud to cloud backup solution, an online SQL query tool, and OData REST API server that can make your data available over the web.


Pricing


Skyvia’s integration pricing depends mostly on the number of loaded records. Additionally, higher pricing plans allow scheduling packages to run more often and having more scheduled packages.

Skyvia has a free pricing plan for data integration, allowing you to load 5000 records per month for free. So you can register an account on Skyvia for free and test it whether it suits to your needs and then decide whether to upgrade your pricing plan.

You can see details on Skyvia pricing on their pricing page.


Here is a handy video showing live how Skyvia works.