Importing data into SQL Server from SalesForce

[mdsalman1989]

Hi, 

I am new to Alteryx as well as SalesForce. 
Currently, we are pulling data from salesForce via csv files , transforming the data in alteryx and then taking the data ahead in excel workbooks for reporting.

We are looking to pull the data directly into SQL server database, and performing transformation there only and reporting on data directly in sql server. 

I have a couple of doubts : 
1. Is it a good idea to use Alteryx for importing data from SalesForce to SQL Server ? 
2. If answer to Ques1 is 'Yes' : Any tips for having the load done in quickest time possible . 

[NickC · Accepted solution]

Hello,

I would recommend connecting to the Salesforce connector and having a play around.  You can get Alteryx on a free trial for 2 weeks, try building the solution yourself.

This article will give you everything you need to know about Salesfroce. https://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-How-to-Guide-to-the-Salesforce-Connector...

Why not perform some of the transformations in Alteryx as well?

Nick

[mdsalman1989]

Hello Nick, 

Thanks for providing suggestions. 

I am having a licensed version of Alteryx, and I did a small POC of pulling around 100K records ( ~300 MB) in SQL server on my local machine ( not a very powerful machine ) in 3.2 minutes. (Used Salesforce connector and ODBC database output  )

Is this good performance ? 

I was wondering are there any best practices which can enhance the performance ?

Also, reason for performing transformations in SQL layer is based on the assumption, that since we are looking to import data in SQL layer, hence doing transformation there itself would be faster as compared to Alteryx. ( this is just an assumption). 

Thanks 
Salman

[NickC · Accepted solution]

Hello,

That's not too bad performance, its obviously dependent on the speed of your internet connection as it is connecting via an API. 

I'm sure you can find an ODBC driver for Salesforce which might improve speed if the API isnt quick enough?

You can use Alteryx to perform those transformations using the In Database tools within Alteryx.  The data won't load onto your local machine, instead it will perform all the transformations at a database level which should improve performance (depending on your SQL database).

Thanks,

Nick

[mdsalman1989]

Hello Nick, 

Sure, try and find an ODBC driver and have a quick run to compare the performance. 

Just curious though, please correct me if I understood incorrectly, using In-Database tools will perform transformations at database layer only, and it will be having better performance than doing those directly at database layer. ( I am using SQL Server 2012 )

Again, thanks for providing your valuable comments. Appreciate it. 

- Salman

[NickC · Accepted solution]

The Alteryx In-Database tools will have better performance than doing it locally on Alteryx.  The performance of the In DB tools vs writing SQL directly on the server will be largely the same but obviously direct on the database will require writing code rather than using the GUI.

Again, why not give it a go and see if you prefer it :)   

Thanks,

Nick

[mdsalman1989]

Sure Nick. :) 

Thanks a lot for your input. Will update the thread once I have some comparative performance numbers. 

Regards,

Salman