In this blog we will show you how you can seamlessly blend data from Salesforce.com (SFDC) with data from SAP using Alteryx and the Alteryx Connector for SAP (ACS).
For the purposes of this blog I’ve defined the following simple scenario: I want to identify all open Opportunities in SFDC that have an Amount (i.e. value) greater than the Customer’s Credit Limit in SAP ECC.
To deliver the scenario, I’m going to create an Alteryx workflow that extracts Account and Opportunity data from SFDC and blends it with Customer and Customer Credit Management data from SAP. I’ll then apply a formula to the blended data to identify Opportunities which, if converted into Orders or Contracts, will result in the Customer Credit Limit being breached. Finally, I’ll split the categorized data into two outputs: A list of Opportunities that will breach the Customer Credit Limit and a list of Opportunities which have sufficient Credit Limit coverage.
To deliver the scenario, I’m going to create an Alteryx workflow that extracts Account and Opportunity data from SFDC and blends it with Customer and Customer Credit Management data from SAP. I’ll then apply a formula to the blended data to identify Opportunities which, if converted into Orders or Contracts, will result in the Customer Credit Limit being breached. Finally, I’ll split the categorized data into two outputs: A list of Opportunities that will breach the Customer Credit Limit and a list of Opportunities which have sufficient Credit Limit coverage.
While the scenario is admittedly a very simple one, my hope is that this blog and video will give you some inspiration as to how your business could benefit from combining data from these very different sources using Alteryx.
Let’s get started.
Let’s get started.
Identifying the source of data in Salesforce.com
I need access to Account and Opportunity data in SFDC.
In my SFDC system I have some basic Account data that I’m interested in. Specifically, the Account Name and Account Number.
In my SFDC system I have some basic Account data that I’m interested in. Specifically, the Account Name and Account Number.
This data is stored in the SFDC Account table.
On the Opportunity, I’m interested in the Account and the Amount.
On the Opportunity, I’m interested in the Account and the Amount.
This data is stored in the SFDC Opportunity table.
Identifying the source of data in SAP
I need access to Customer and Customer Credit Limit Information in SAP ECC. Specifically, I’m interested in the Customer Number, Customer Name and Credit Limit.
At the transaction code level, I can access the Customer Master record using transaction VD03 or XD03. This will provide me with the Customer Number and Customer Name.
At the transaction code level, I can access the Customer Master record using transaction VD03 or XD03. This will provide me with the Customer Number and Customer Name.
Within Alteryx I can use the Alteryx Connector for SAP (ACS) to access this data using either the SAP Table Connector, the SAP BW Query Connector or the SAP BW InfoProvider Connector.
In this scenario I’m going to use the SAP Table Connector. The Customer data I need is stored in table KNA1 (General Data in Customer Master). You can see the table level view of this data using transaction SE16 or SE16N.
In this scenario I’m going to use the SAP Table Connector. The Customer data I need is stored in table KNA1 (General Data in Customer Master). You can see the table level view of this data using transaction SE16 or SE16N.
The Customer Credit Limit information is accessed with a different transaction: FD33.
The Customer Credit Limit data I need is stored in table KNKK (Customer Master Credit Management). You can see the table level view of this data using transaction SE16 or SE16N.
I’m also going to use the ACS Table Connector to access this data.
I’m also going to use the ACS Table Connector to access this data.
Extracting data from Salesforce.com
Now that I’ve identified my SFDC and SAP data sources, I can start to build the Alteryx workflow.
Connecting to SFDC via Alteryx is pretty straightforward. In our scenario, I’m extracting data from SFDC so simply drag the SFDC Input tool onto the canvas. I’m then prompted to enter my SFDC Credentials.
The Security Token is assigned when your SFDC user is set-up and is updated if you request a password reset.
For the SFDC Opportunity extract I’ve used a Custom Query because I only want to extract Opportunity data where the status of the Opportunity is not, “Closed”.
For the SFDC Opportunity extract I’ve used a Custom Query because I only want to extract Opportunity data where the status of the Opportunity is not, “Closed”.
I also Validate the query to make sure that it is can processed by SFDC.
For the Account data I’m using the Query Builder function and have selected the fields that I’m interested in. I’m most interested in the “Id” field and the “Name” field.
For the Account data I’m using the Query Builder function and have selected the fields that I’m interested in. I’m most interested in the “Id” field and the “Name” field.
In the Opportunity extract the “Account Id” is available not the Account “Name”.
Because of this I need to combine data from the Account and Opportunity tables.
In Alteryx, I use the Join tool to do this.
In Alteryx, I use the Join tool to do this.
The combined output gives me something I can start to work with.
Extracting data from SAP
Connecting to SAP with the Alteryx Connector for SAP (ACS) is as simple as for SFDC.
As a starting point I drag the SAP Logon tool onto the canvas, enter my ACS license key and search for my available SAP Servers.
As a starting point I drag the SAP Logon tool onto the canvas, enter my ACS license key and search for my available SAP Servers.
I then select the Parameters tab, select the SAP systems I want to connect to and in this scenario (because I am using User and Password access to SAP rather than SSO) enter the User and Password for each SAP system I’ve selected. In this workflow I’m just going to connect to my SAP ECC system.
The next step is to set-up the SAP data extract. So, I’m going to use the SAP Table connector to access data from my SAP Customer table (KNA1) and my SAP Customer Credit Management table (KNKK).
Drag the SAP Table connector on to the canvas and link it to the SAP Logon tool. This ensures that the SAP system credentials are passed from the SAP Logon tool to the other SAP connector tools without me having to reenter my login details.
Choose the SAP system you want to use with the SAP Table tool.
Drag the SAP Table connector on to the canvas and link it to the SAP Logon tool. This ensures that the SAP system credentials are passed from the SAP Logon tool to the other SAP connector tools without me having to reenter my login details.
Choose the SAP system you want to use with the SAP Table tool.
On the parameters tab of the SAP Table tool, I search for the table I’m interested in.
I’m going to extract data from the General Data in Customer Master table or KNA1. I’m primarily interested in the Customer Number (KUNNR) and Name (NAME1).
I’m going to extract data from the General Data in Customer Master table or KNA1. I’m primarily interested in the Customer Number (KUNNR) and Name (NAME1).
I repeat the set-up step to access data from the Customer Master Credit Management (KNKK). I’m interested in the Customer (KUNNR) and Credit Limit (KLIMK) fields.
I then use the Alteryx Join tool to combine the output from both tables based on the Customer Number field (KUNNR). The reason for doing this is the same as with SFDC. The KNA1 table includes the Customer Number, Customer Name, Customer Address etc. The KNKK table has the Customer Number only. So if I want to see the Customer Name as well as the Customer Number along with the Credit Limit I need to combine the data from both tables.
As with SFDC, the combined SAP output gives me something I can start to work with.
Blending the data from Salesforce.com and SAP
Use the Alteryx Join tool to combine the SFDC data with the SAP data.
In our systems the SFDC Account Id and SAP Customer Number are the same, so the join is made on the common fields. I’ve also renamed the SAP fields to align them with SFDC.
Apply a simple formula to the blended data from Salesforce.com and SAP
I’m now going to apply a simple formula to the combined SFDC – SAP output. The formula is going to write the value “GOOD” to a new Output Field, “Credit Limit Breach” if the SFDC Opportunity Value is greater than the SAP Credit Limit for the Customer. Otherwise, it will write the value, “DEAL VALUE EXCEEDS CREDIT LIMIT”.
Finally, I want to split the categorized data into 2 streams. One stream will capture the SFDC Opportunities where the SAP Credit Limit is greater than the value of the Opportunity. The second stream will capture the SFDC Opportunities where the SAP Credit Limit is less than the value of the Opportunity.
For this, I’m going to use the Filters tool.
For this, I’m going to use the Filters tool.
The Final Result – The least interesting part…
After running the workflow, I get the following final outputs.
While the scenario I've walked through is a simple one, I hope that it stimulates some ideas as to how you could use your Salesforce.com and SAP data within Alteryx.