Accelerate Data Integration with Cloud Services
Cloud-based Software as a Service (SaaS) applications and services such as Salesforce.com (SFDC) are now common in the business world. They offer quick solutions and are easily adaptable to business users. However, this ease and speed of customization presents challenges. Because SaaS utilities frequently change outside of standard IT development cycles, IT departments often must find new ways to provide flexible data warehouse (DW) and business intelligence (BI) solutions. IT must manage this change to avoid costly delays or untimely outages in the standard DW load process. In this post, we will discuss this challenge and present a possible solution.
Data warehouses stage data from both internal applications, such as sales and inventory systems, as well as external applications, such as SFDC for CRM and ADP for payroll. Data are pulled from these sources on an automated, scheduled basis and integrated into a central DW solution. As the source systems change, DW teams must then update their extract, transform, and load (ETL) processes correspondingly to handle the new and changed fields that are showing up from the sources.
There are two main reasons why DW teams must synchronize with these processes.
The first is business demand for the latest data. Analysts are eager to get access to the latest fields being used by colleagues. The second is operational uptime. Changes in source system layouts may cause ETL to fail the scheduled load or load incorrectly. In either case, a team member technically skilled with the ETL solution must manually intervene, modify, and test ETL jobs to fit the new fields. This frequent manual intervention is costly and untimely when compared to the agile self-service SaaS architecture.
We leverage cloud services solutions like Informatica Cloud Services (ICS) to help address this challenge. At one Corporate Technologies client, the sales team manages multiple new products and adds new SFDC columns at the rate of one or two a week. We leveraged ICS’s SFDC replication solution to mirror each SFDC table into a staging environment within the DW. The ICS workflow is managed through a web-based interface, which is available to the same business analyst that adds fields to SFDC. If a new column has been added to SFDC, the analyst logs into ICS and quickly configures, in less than 5 minutes, the new column to be replicated to the DW.
Once replicated to a DW staging environment, the SFDC tables are wrapped with views to create a dimensional analytical layer. This layer is immediately available to trained business analysts using Tableau to perform data analysis. Insights from these analyses are vetted and implemented by the DW team and then turned into operational reporting in the enterprise BI environment on a weekly basis.
Leveraging ICS and the staging replication architecture has allowed us to significantly accelerate time to market within the DW for simple SFDC column additions. The DW team is freed from regularly working on lights-on management tasks, and business analysts can immediately perform analysis without having to wait for new ETL development.
In a future blog post, I will address how using ICS can help accelerate traditional DW build outs from SaaS solutions.