How to Achieve Complex Logic Queries and Analyses on Data from SAP BW

Retrieving data from SAP BW and then we can perform complex logic queries and analyses with a computing language. We can retrieve data using an ETL tool, such as Informatica and Talend, write it to the database, and execute SQL. The approach uses SQL’s powerful computing ability, but with complicated data loading process, low realtime performance, complex framework and integration difficulties. Python has enough computing ability, but it has retrieval interface problems. PySapRfc can retrieve data through the special interface, but for a very long time the class library remains inactive and has lots of bugs. Other class libraries, such as RestClient/Requests, can retrieve data through public Restful/ Web Service interface, but they have very poor performance and generate complicated code.

The best approach is using esProc SPL, which boasts simple framework, high realtime performance, outstanding computational capabilities, and simplistic and easy-to-use retrieval interface.

SPL encapsulates SAP jco interface as a simple and easy to use function. To access SAP server according to the connection information, execute Z_TEST1 function and get the result set returned from IT_ROOM table, SPL has the following script:


A

1

=sap_open("userName","passWord","192.168.0.188","00","000",”E")

2

=sap_cursor(A1, "Z_TEST1","IT_ROOM").fetch()

3

=sap_close(A1)

If it is inconvenient to deploy jco or if the computation has a low transmission performance requirement, we can use SPL Restful/WebService functions to retrieve data. The functions are simple and convenient to use and support imposing access controls.

SPL has a wealth of built-in functions to provide equal computational capabilities as SQL. To compute the retrieved data in cell A2, for instance:


A

B

2

/Retrieval process skipped

3

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

/Filtering

4

=A2.sort(salary)

/Sorting

5

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

/Grouping & aggregation

6

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

/Join

SPL offers 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 conn =DriverManager.getConnection("jdbc:esproc:local://");  
CallableStatement statement = conn.prepareCall("{call run(?,?)");  
…

SPL’s agile syntax enables it to achieve algorithms with complex logic that are hard to achieve using SQL or stored procedure effortlessly. For instance, we are trying to get the largest number of consecutive rising days for a specific material, SPL only needs a single line of code:


A

B

3

/ Retrieval process skipped

4

=a=0,A2.max(a=if(consum> consum[-1],a+1,0))

/ Get the largest number of consecutively rising days

Another instance is to find the first n big customer whose order amount takes up at least half of the total and sort the corresponding records by amount in descending order:


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:

 

In short, SPL is the ideal tool for performing complex logic queries and analyses on data from SAP BW thanks to its remarkable computational capabilities, easy-to-use interface and light framework.



esProc SPL Source Code and Package 👉

https://github.com/SPLWare/esProc

esProc SPL Official Website 👉

http://www.scudata.com

esProc SPL Forum 👉

http://c.raqsoft.com