How to Achieve More Queries and Analyses on Salesforce Data

We can retrieve Salesforce data using the Salesforce SOQL/SOSL language via the official interface or a third-party encapsulated interface and then perform any query and analysis we need. Approach one: retrieve data through JDBC/ODBC driver, such as ascendix salesforce-jdbc and Informatica DataDirect odbc, write it to the database and perform queries in SQL. It makes use of SQL’s powerful computing ability, but with complicated data loading process, bloated framework and low realtime performance. Approach two: retrieve data directly using the driver or reporting tool’s built-in retrieval module, and immediately perform queries and analyses using wizard, drag and drop, scripting and other report development tools, such as tableau\cognos\power BI\Excel. It has simple framework and high reatime performance, yet with extremely poor computing ability.

Approach three is to use a computing language, such as Python and esProc SPL, to retrieve and compute data. This approach has merits of the previous two – light framework and high realtime performance. Besides, it can provide computing abilities as good as SQL. Between them, esProc SPL has greater computational capabilities and is more integration-friendly.

SPL encapsulates simple and easy to use retrieval functions for retrieving data a Salesforce organization through SQQL and SQOSL. To retrieve data from the Lead table using SQOL, for instance, we just save the Salesforce account (including username, password and api_token) in a JSON file and execute the following SPL script:


A

1

=sf_open("D:\\conf\\user.json")

2

=sf_query(A1,"/services/data/v51.0/query","Select Id,CaseNumber,Subject From Case where Status='New'")

3

=sf_close(A1)

SPL has a wealth of built-in functions to provide equal computational capabilities as SQL.


A

B

3

/Retrieval process skipped

4

=A2.select(salary>8000 && salary<10000)

/Filtering

5

=A2.sort(salary)

/Sorting

6

=A2.groups(deptid;sum(salary),avg(salary))

/Grouping & aggregation

7

=join(B2,cat;A2,cat).groups(_1.title, _2.path; sum(_1.amount))

/Join

SPL offers the general JDBC/ODBC interface to be easily integrated by a high-level language or a reporting tool. To invoke a SPL script file in a Java program in the way of calling a stored procedure, for instance, we just need the following code:

…

Class.forName("com.esproc.jdbc.InternalDriver");  
Connection connection =DriverManager.getConnection("jdbc:esproc:local://"); 
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("call bigCustomer ()");

…

To invoke it by BIRT:

SPL’s agile syntax enables it to achieve algorithms with complex logic that are hard to achieve using SQL or stored procedure effortlessly. To find the first n big customers whose order amount occupies at least half of the total and sort them by amount in descending order, for instance:


A

B

3

 /Retrieve data

4

=A2.sort(amount:-1)

/Sort data by amount in descending order

5

=A4.cumulate(amount)

/Get the sequence of cumulative amounts

6

=A5.m(-1)/2

/The final cumulative amount is the total

7

=A5.pselect(~>=A6)

/ Get the position of record where the cumulative amount reaches at least half of the total

8

=A4(to(A7))

/Get records according to A7’s position

SPL has a professional IDE equipped with all-around debugging functionalities and letting users to observe the result of each step any time, making it suitable for coding algorithms with complex logics:

 

Simply put, SPL boasts convenient retrieval interface, general integration and exceptional computational capabilities, making further queries and analyses on Salesforce data easily and conveniently.