Salesforce Commerce Cloud Integration

Who is the client?

The client is a renowned national provider in the Jewelry Industry for the collection of exceptional Diamond, Diamond Jewelry and other accessories, targeting mainly USA and Canada.

What was the client trying to achieve?

The client was looking for a reliable service provider to understand their complete on-line and in-store sales and order process and help them to create an automated system to integrate multiple databases and existing applications to Salesforce Commerce Cloud system. The need was due to a requirement of Salesforce to collaborate required data from multiple sources and create an SFCC compatible XML format, called an XML feed, for product, price, catalog, customer, order status etc. The in-store process was in MS-Access forms and database, and the on-line CMS application exported the data in XML format from existing client website.

What services did Data-Core provide? What challenges were overcome?

Data Migration from different data sources into a single SQL Staging Database:

Data-Core was able to understand and analyze the characteristics of the data from 11 different databases and more than 450 tables. Columns and data were studied thoroughly from all tables to understand the domain. A data dictionary was created for all existing databases and tables. Based on the extensive study of the existing relationship, a new relationship model was created.Data migration was done from many different data sources (MS Access, XML and SQL Tables) without transforming the data so the original format and data type were maintained. Data-Core was able to create table scripts for destination tables in the staging database following the source tables’ structure and data types. SSIS Packages were created to execute and perform one-to-one migration anytime. The data was then migrated from the heterogeneous source databases to the SQL Server staging database.

Migration to Consolidated DB after performing the required transformation on data:

Although a challenge, Data-Core was able to apply the required transformation logic on the available data inside Survey DB, Online Sales DB and In-Store Sales DB. Data-Core created ERD on the existing tables and used Entity Search Procedure to identify similar entities from different databases. A new table structure was also created to accommodate all other information from different tables into a single table. A new set of tables was created for a single entity to impose data normalization. Data integrity and consistency issues were addressed and identified during this project, allowing for the creation of a strategy for the resolution during the consolidation. To improve data quality, when necessary, records were manually corrected.

Data Wrangling (Data Preparation) for the Salesforce Commerce Cloud XML Feed included:

• Discovering (Data Analysis)
• Structuring (Indexing/Normalizing)
• Cleaning (Data Quality Analysis, Removal of NULL and Junk Values)
• Enriching (Strategy to augment some additional data to make the existing data better)
• Validating (Integrity and Consistency Checking)
• Publishing (Final DB to be further used to generate SFCC XML Feeds)

Created automated scheduler to download/upload SFCC XML Feeds as per required frequency spec:

Data-Core created a Windows scheduler to drop the outgoing XML feed files to the Salesforce SFTP Server and move the incoming XML feed files from Salesforce SFTP Server. After each successful upload/download operations in the Salesforce SFTP Server, XML feeds will be archived. Email notifications are configured to monitor all success and failure conditions of schedulers.

Maintenance of Production Environment and support for future Enhancements and Change Requests:

There were changes done on the data, SFCC XML Structure and scheduler frequency as per The Client’s request on Production. Future enhancements have been developed and tested on Test and then deployed on Production.

Automation of Data Publishing:

Data-Core used an innovative technology to incorporate client scripts using C# .Net inside SSIS to generate and parse the SFCC XML feeds. This technology is very flexible for enhancements or maintenance and also very effective to automate the jobs inside SQL Server Agents to run SSIS packages in order to generate XML feeds from SQL Server tables and to parse the downloaded XML file to store the data in the database, as per required frequency.

What stand-out points are there from these services?

Data-Core assisted the Client in achieving their goal of launching their website through Salesforce Commerce Cloud Integration using a very systematic, scalable and easy to maintain process. The Data-Core team was able to create a complete and comprehensive high level and detailed level Entity Relationship Diagram, identifying all entities related to the business and different software of those capturing data.

Because of Data-Core’s creation of ETL JOBs for one-to-one data migration, the client currently has the advantage of changing the data using their old system and re-migrating the source data to get the effect of change for Salesforce Commerce Cloud, just by executing the migration ETL Job. Independent data views were created for all data feeds required by Salesforce. SSIS Job Schedulerswere also created for automated generation of Salesforce XML Feeds. Windows task Schedulers were created for the automatic back & forth transfer of XML Feed Files to Salesforce SFTP Server and thenotification emails showing status of successes and failures are configured for all job schedulers.

In Summary…

Data-Core’s successful implementation of the client’s website launch through the Salesforce Commerce Cloud was a huge success and satisfaction. The Client is already in process of enhancing new features in Salesforce Commerce Cloud with Data-Core’s assistance.