Tag: api

What is an API integration?

An API integration is the connection between two or more applications, via their APIs, that lets those systems exchange data. API integrations power processes throughout many high-performing businesses that keep data in sync, enhance productivity, and drive revenue.

What is an API?

An API is a set of definitions and protocols for building and integration application software. API stands for application programming interface.

An API is like a building block of programming that helps programmers avoid writing code from scratch each time it is needed. APIs are organized sets of commands, functions, and protocols that programmers use to develop software.

Consider building something with a box of Lego bricks. Instead of creating or carving a new block each time you need one, you simply choose from an assortment that are prepared and ready to be plugged into your project. Each block is designed to connect with other blocks in order to speed up the building process. In essence, that’s how an API works. APIs streamline and boost efficiency everywhere they are used.

The most commonly-used API for web services: REST

REST stands for Representational State Transfer. Where a regular API is a essentially a set of functions that allow communication between applications, a REST API is a architectural style for connected applications on the web.

In fact, today 70% of public APIs depend on REST services. REST APIs offer more flexibility, a gentler learning curve, and work directly from an HTTP URL rather than relying on XML.

How do REST APIs work?

At their simplest form, REST APIs for web services usually involve the following parties:

  • Your web-based, API-enabled application
  • Remote server
  • Specific data request
  • Returned data/function

While there are many different flavors of software and many different flavors of server, REST APIs act as a standardized wrapper to help your API-enabled applications successfully communicate with online servers to make information requests.

Now, how do API integrations work?

The term API integration can be defined as the process of creating a means for two or more APIs to share data and communicate with each other without human interruption. It involves the use of APIs to enable communication between two web tools or applications. It allows organizations to automate their systems, enhance the seamless sharing of data, and integrate current applications.

API integration has become pivotal in the modern world due to the explosion of cloud-based products and apps. As such, organizations need to create a connected system where data will be relayed flawlessly between various software tools without the need to do it manually. API integration has proved to be the much-needed solution as it allows the sharing of process and enterprise data among applications in a given ecosystem. It improves the flexibility of information and service delivery, as well as makes the embedding of content from different sites and apps easy. An API acts as the interface that permits the integration of two applications.

Building API integration into your application or workflow allows you to make the most of your data by employing it where it will be most useful. Whether this means using it for analytics or as content within your software or applications, integration keeps you connected to your business information.

Getting started with APIs

Data integration using API technology doesn’t have to be daunting. Etlworks Integrator uses API-led connectivity to create an application network of apps, data, and devices, both on-premises and in the cloud. It provides developers with a complete toolkit to enhance scalability, simplify mapping and security, and for managing, preparing, and profiling all of your company’s data. Whether you want to build event-driven data integration flows or incorporate Integrator into your own automation workflow, you can use the developer features below to achieve your goals.

See how Etlworks can help you make the most of API with a free demo of Data Integration Platform.

EDI Data Integration & Why It’s Important

EDI_data_integration

Today more than 85% of all electronic business transactions take place utilizing Electronic Data Interchange (EDI). This creates major competitive advantages for businesses and their partners. The EDI process has proven to be the most efficient and secure process to exchange business documents globally. EDI is a necessary component to ensure that your organization is achieving operational excellence.

What is EDI?

Electronic Data Interchange is the computer-to-computer exchange of business documents in a standard electronic format between business partners. By moving from a paper-based exchange of business document to one that is electronic, businesses enjoy major benefits such as reduced cost, increased processing speed, reduced errors and improved relationships with business partners.

EDI provides a safe, reliable, secure, and established method of exchanging documents for all industries. The retail industry uses EDI documents such as the Purchase Order and Invoice. The healthcare industry relies almost entirely on the HIPAA healthcare claim and corresponding payment EDI documents.

Why is EDI Necessary?

The adoption of electronic data interchange is critical for companies of all sizes to maintain their competitiveness in the marketplace. Over three decades of global usage has proven that EDI improves operational efficiency across your entire organization.

As an automation technology, EDI delivers core business benefits:

  • Saves time and money: automates a process previously manually executed with paper documents.
  • Improves efficiency and accuracy: data-entry errors are eliminated.
  • Improves traceability and reporting: electronic documents can be integrated with a range of IT systems to support data collection, visibility, and analysis.
  • Improves relationships with your customers: enables efficient transaction execution and prompt, reliable product and service delivery.

EDI is important to both large and small businesses. For large organizations, EDI enables standards to be instituted across trading partners to consistently achieve benefits. For smaller organizations, adherence to EDI offers greater integration with larger firms that have big budgets and strong influence.

Metalanguages like XML, JSON, and API integration complement, rather than replace EDI. Companies must be ready to handle an ever-increasing number of document formats and transmission options.

Why choose Etlworks for EDI Integration?

Etlworks allows your team to easily automate the handling, process, and integration of your electronic data exchange information. You’ll increase your organization’s communication, customer service, and cash flow by automating your processes.

With Etlworks, you can automate data flows that generate EDI messages from internal data or process incoming EDI messages and integrate them with your internal applications and databases, improving process control.

The EDI connector in Etlworks Integrator contains components that convert messages between EDI and XML and vice versa.

Etlworks supports numerous EDI formats — HL7, EDI X12, EDIFACT, FHIR, JSON, Flat File, CSV, Delimited, XML, AVRO and more.

EDI is only the tip of what we have the capability to handle. If you’d like to learn more about how we can streamline your EDI software integration, request a personalized demo from an Etlworks expert.

Streamline your Salesforce data integration with Etlworks

etlworks-salesforce-data-integration

What is Salesforce?

Salesforce is the world’s #1 cloud-based customer relationship management (CRM) platform.

Salesforce offers a wide range of applications for managing business processes including sales, customer service, marketing, and e-commerce. For many organizations, Salesforce is a rich source of customer data, such as Accounts, Opportunities, Services, Community, Activities, and Leads.

On its own, Salesforce can dramatically improve how companies run their sales operations, support their customers, and provide products and services to a market. With the integration, businesses make Salesforce more valuable through data.

Through integration, you bring data from disparate sources, databases or applications, such as marketing, support, e-commerce, and sales to the data warehouse.

Effective and efficient integration of Salesforce with adjacent enterprise systems — such as databases, ERP and CRM systems, and custom applications — is critical to enabling sales teams, increasing revenue, and better serving customers. By integrating Salesforce with other applications, APIs and resources, you make Salesforce even more valuable to your employees and your organization.

Ready to get started?

Etlworks is a cloud-native data integration platform that helps businesses automate manual data management tasks, ensure data that are far more accurate, accelerate business workflows, and provide greater operational visibility to an organization.

Etlworks Salesforce connector allows fast real-time access to Salesforce data. The connector supports all objects and metadata (fields) available through the Salesforce API and works just like any other database connector. This not only makes it easier to read, insert, update and delete data, it also accelerates the time it takes to turn it into valuable, 360-degree customer insights. 

You can load Salesforce Contacts, Leads, Opportunities, Attachments, Accounts, custom objects, etc. directly to/from major cloud and on-premise data sources or synchronize data in both directions. Powerful mapping settings allow you to load and synchronize Salesforce data with sources having different data structures. You can schedule your integration operation to execute it automatically.

Let’s do it!

Extracting data from Salesforce

Note: extracting data from Salesforce is similar to extracting data from the relational database.

Step 1. Create Salesforce connection which will be used as a source (FROM).

Step 2. Create a destination connection, for example, a connection to the relational database, and if needed a format (format is not needed if the destination is a database or well-known API).

Step 3. Create a flow where the source is a database and the destination is a connection created in step 2, for example, relational database.

mceclip0.png

Step 4. Add new source-to-destination transformation.

Step 5. Select Salesforce connection created in step 1 as a source connection and select the Salesforce object you are extracting data from:

salesforce-from.png

Step 6. Select TO connection, format (if needed) and object (for example database table) to load data into.

salesforce-to

Step 7. Click MAPPING and optionally enter Source Query (you don’t need a query if you are extracting data from the Salesforce object unconditionally):

salesforce-query

Step 8. Optionally define the per-field mapping.

salesforce-mapping

Step 9. Add more transformations if needed.

Loading data in Salesforce

Note: loading data in Salesforce is similar to loading data into a relational database.

Step 1. Create a source connection and a format (if needed).

Step 2. Create destination Salesforce connection.

Step 3. Create a flow where the destination is a database.

Step 4. Add new source-to-destination transformation.

Step 5. Select FROM and TO connections and objects (also a FROM format if needed).

to-snowflake

Step 6. Optionally define the per-field mapping.

Step 7. Add more transformations if needed.

Browsing data in Salesforce

You must have a Salesforce connection to browse objects and run SQL queries.

Use Explorer to browse data and metadata in Salesforce as well as execute DML and SELECT queries against Salesforce connection.

browse-salesforce

Change Replication and Data Synchronization

Loading data from Salesforce to your data warehouse is just a part of the problem. Real-time analytics require data in the data warehouse to be constantly up-to-date with Salesforce. In Etlworks, you can always have the most current data from Salesforce in your data warehouse by using High Watermark (HWM) change replication techniques.

After the first replication of all the Salesforce data, subsequent replications update the data warehouse data incrementally with refreshes from Salesforce, in near real-time. Data warehouse data will always be up-to-date in a matter of minutes automatically without any user intervention.

Sign up free or get a demo today to learn more: https://etlworks.com

ETL/ELT all your data into Amazon Redshift DW

amazon_integration

Amazon Redshift is fast, scalable, and easy-to-use, making it a popular data warehouse solution. Redshift is straightforward to query with SQL, efficient for analytical queries and can be a simple add-on for any organization operating its tech stack on AWS.

Amazon Web Services have many benefits. Whether you choose it for the pay as you go pricing, high performance, and speed or its versatile and flexible services provided, we are here to present you the best data loading approaches that work for us.

Etlworks allows users to load your data from cloud storages and APIs, SQL and NoSQL databases, web services to Amazon Redshift data warehouse in a few simple steps. You can configure and schedule the flow using intuitive drag and drop interface and let Etlworks do the rest.

Etlworks supports not just one-time data loading operation. It can help you to integrate your data sources with Amazon Redshift and automate updating your Amazon Redshift with fresh data with no additional effort or involvement!

Today we are going to examine how to load data into Amazon Redshift.

A typical Redshift flow performs the following operations:

  • Extract data from the source.
  • Create CSV files.
  • Compress files using the gzip algorithm.
  • Copy files into Amazon S3 bucket.
  • Check to see if the destination Amazon Redshift table exists, and if it does not – creates the table using metadata from the source.
  • Execute the Amazon Redshift COPY command.
  • Clean up the remaining files.

There are some prerequisites have to be met, before you can design a flow that loads data into Amazon Redshift:

Now, you are ready to create a Redshift flow. Start by opening the Flows window, clicking the + button, and typing redshift into the search field:

redshift-flows

Continue by selecting the flow type, adding source-to-destination transformations and entering the transformation parameters:

redshift-transformation

You can select one of the following sources (FROM) for the Redshift flow:

  • API – use any appropriate string as the source (FROM) name
  • Web Service – use any appropriate string as the source (FROM) name
  • File – use the source file name or a wildcard filename as the source (FROM) name
  • Database – use the table name as the source (FROM) name
  • CDC – use the fully qualified table name as the source (FROM) name
  • Queue – use the queue topic name as the source (FROM) name

For most of the Redshift flows, the destination (TO) is going to be Amazon S3 connection. To configure the final destination, click the Connections tab and select the available Amazon Redshift connection.

redshift-connection

Amazon Redshift can load data from CSV, JSON, and Avro formats but Etlwoks supports loading only from CSV so you will need to create a new CSV format and set it as a destination format. If you are loading large datasets into Amazon Redshift, consider configuring a format to split the document into smaller files. Amazon Redshift can load files in parallel, also transferring smaller files over the network can be faster.

If necessary, you can create a mapping  between the source and destination (Redshift) fields.

Mapping is not required, but please remember that if a source field name is not supported by Redshift, it will return an error and the data will not be loaded into the database. For example, if you are loading data from Google Analytics, the output (source) is going to include fields with the prefix ga: ( ga:user, ga:browser, etc. ). Unfortunately, Amazon Redshift does not support fields with a : , so the data will be rejected. If that happens, you can use mapping to rename the destination fields.

ELT for Amazon Redshift

Amazon Redshift provides affordable and nearly unlimited computing power which allows loading data to Amazon Redshift as-is, without pre-aggregation, and processing and transforming all the data quickly when executing analytics queries. Thus, the ETL (Extract-Transform-Load) approach transforms to ELT (Extract-Load-Transform). This may simplify data loading to Amazon Redshift greatly, as you don’t need to think about the necessary transformations.

Etlworks supports executing complex ELT scripts directly into Amazon Redshift which greatly improves performance and reliability of the data injection.

I hope this has been helpful. Go forth and load large amounts of data.

Connecting to Marketo API

etlworks-marketo

Marketo exposes a REST API which allows for remote execution of many of the system’s capabilities.  From creating programs to bulk lead import, there are a large number of options which allow fine-grained control of a Marketo instance.

Read about Marketo REST API.

Prerequisites

Assuming that you already have Marketo account, in order to connect to the Marketo REST API you must have the following:

  • The base URL for the Marketo instance, for example, 377-XYZ-015.mktorest.com
  • Client ID
  • Client Secret

This article explains how to obtain the Client ID and the Client Secret.

Creating a connection to the Marketo REST API

In this section, we will be showing you how to create a connection to the Marketo REST API endpoint which returns available fields for the leads.

Step 1. Add a new HTTP connection in Etlworks.

Step 2. Enter the following URL in the “URL” field.

https://base-url/rest/v1/leads/describe.json?access_token={access_token}

where the base-url is the URL from the “Prerequisites” section, for example, 377-XYZ-015.mktorest.com.

Notice the leads/describe.json which is the API endpoint that returns the fields for the leads.

Step 3. Select GET as a “Method”.

Step 4. Select OAuth2 for “Authentication”. In the “User” field enter the “Client ID” from the “Prerequisites” section and in the “Password” field enter the “Client Secret” from the same section.

Step 5. In the “Authentication URL” field enter the following URL:

https://base-url/identity/oauth/token?grant_type=client_credentials&client_id={user}&client_secret={password}

where the base-url is the URL from the “Prerequisites” section.

Step 6. Select POST for “HTTP Method for Token and OAuth2 Authentication”.

Step 7. Select application/json as an “Authentication Request Content Type”.

Step 8. Give the connection a name and save it.

Testing Marketo REST API connection

The following shows you how to test the connection using Explorer.

Step 1. If you don’t have it already create the JSON format. Use all the default settings.

Step 2. Go to Explorer, select the connection created in the previous section and click “expand” (down-arrow) button. Link the format created in step 1 to the connection.

Step 3. Click the “View Data” button to view the data in a grid format or “Raw Data” to view the data in the JSON format.