Wednesday, October 17, 2012

BW – Analysis Process Designer (APD) - The use of APD for performance improvement

Analysis Process Designer

Usually the Analysis Process Designer (APD) is used to find and identify hidden or complex relationships between data from various databases from systems in a company. The analysis results are saved in BW data targets, a CRM system or e.g. a flat file. They are available for all decision and application processes and thus can be decisive (strategically, tactically, operatively).

For one of our customers we used it to improve the performance of graphs and tables in a CRM factsheet.

Using the APD, we were able to reduce the load time of the factsheet from 2 minutes to less than 3 seconds!

So let's take a look at the technical details.

Within the SAP CRM Customer factsheet a SAP BW web template is called with nine different queries, presented as graphs and tables.

The queries present data about:
  • Sales turn over the last five years (totals per year);
  • Payment history of the customer over the last 12 months;
  • Sales turn over last 12 months (totals per month);
  • etc.

Situation before improvement

BW data flow:                                   

BW data flow before

Each time the CRM factsheet was called for a customer, all nine queries were executed in the background to collect the data for that customer. Per customer it took about 2 minutes, to read the data and display the graphs and tables. This is not acceptable during a phone call. The sales data must be directly available for the sales employee who is calling with a customer.

How is APD used in the new data flow model?

For each query an APD process has been made which will capture the year- and month totals. The captured information is stored in DSO's and will be loaded into new InfoCubes with total values only for each customer.
The execution of all nine queries will be scheduled as a job early in the morning and the selection for each query will be 'All customers'.

Situation after improvement

 BW data flow: 

Result of improvement

The daily job, which runs all nine queries for 'calculating' the month- and year totals for each customer, has an execution time of 45 minutes.
Calling the CRM factsheet with exactly the same graphs and tables, now takes about 3 seconds instead of 2 minutes.

Summarizing steps:

Per query:
  • Change selection of original query to all customers;
  • Create APD process;
  • Create DSO for to store result data of APD;
  • Create aggregated InfoCube with total values only (per month or year);
  • Create new query with selection based on one customer.
  • Create a new web template with the new queries;
  • Change the call to the factsheet in CRM (new web template);
  • Schedule daily job to calculate customers' totals.
Good luck.
Sander van Dillen, SAP BI Consultant at Acorel.