Get More from APD: Gather and Store Query Result Sets for Complex Analysis

  • by Gary Nolan, SAP BI Strategic Architect, Sapiex Associates LLC
  • September 9, 2009
When carrying out complex analysis, such as trend analysis, you may need to use a query as a DataSource. In these situations, rather than using transformation logic, try using Analysis Process Designer, which provides more flexibility and enables you to use queries as DataSources.
Key Concept
You can use Analysis Process Designer (APD) to run query result sets. In this scenario, APD serves as an extractor to load data in the background to a DataStore object (DSO) and then load master data to provide the query result data for analysis. This data is gathered from the query, stored in a direct-update DSO, and then passed to a master data InfoObject. You can use this technique to store snapshots of query result sets or to store the values of the query results.

Measures are often contained in queries that you need to extract for statistical analysis, trend analysis, or use in other queries for further analysis. If the data is strictly shown in queries, there is no standard DataSource that allows data loads directly from a query into a DataStore object (DSO) or InfoCube. Analysis Process Designer (APD) allows queries to run in the background. The resulting query data is extracted and stored for use in other queries.

This often-overlooked technique allows you to use query result data as a DataSource. You can use this technique to store trend analysis or extract complex result sets from a query that would be very difficult or impossible to do with transformation logic. This technique also provides the query designer the flexibility to create the various metrics needed for extraction.

For example, a company has a very complex measure called case fill rate, which is the number of cases shipped versus the number of cases ordered. This value is determined via some very involved logic using data from several InfoCubes and multifaceted query logic. The users want to store the case fill rate values each week and use the case fill rate as a factor to multiply by the weight of the shipments for each week. They want to store these values every week so that over time, past weeks can be analyzed and tracked for trends. This measure provides them with a good idea of what would have shipped. It allows the customer to get credit for product that the company could not fill by using the case fill rate as a factor for key figure values in the query.

In some circumstances, the query is the only source for a complex calculation. In models in which the queries are complex and data is determined from an aggregated value in the query process, the query is the only place to get these stored values. This is the case with this query. For the users to see their weekly case fill rate data from the query result set, it needs to be stored as master data to be used for future calculations. To provide this data, the only place the final case fill rate value is calculated is inside the case fill rate query. Duplicating the query logic into a transformation and loading data from the source into a case fill rate InfoCube is not possible because the level of granularity and the complex query logic does not allow this to be easily calculated using back-end transformation logic.

Data needs to be extracted each week by running the query, gathering the query results, and storing the result data historically. This data is constantly changing, so saving the weekly numbers provides a snapshot of the historical data values. It would be time consuming and cumbersome to run the report manually each week, record the result set, and store it somewhere in the SAP NetWeaver BW system. Therefore, to provide this data, I developed an automated APD process to mine the data from the query result sets and store the data by week.

In this process, data is extracted from a query and loaded into a DSO by ship-to and week. This data is then loaded into the master data so the case fill rate value can be used as an attribute in a formula variable in a query. APD extracts this data from the query, stores it in a DSO, loads it to a master data InfoObject, and finally uses the resulting case fill rate data in a query from the master data attributes. You can use this functionality and technique in SAP BW 3.x or SAP NetWeaver BW 7.0.

Gary Nolan

Gary Nolan is an SAP NetWeaver BW-certified consultant and author of Efficient SAP NetWeaver BW Implementation and Upgrade Guide and Efficient SAP NetWeaver BI Implementation and Project Management. Gary specializes in gathering and evaluating requirements, configuring SAP NetWeaver BW, and providing project management, performance management, and data architecture and data modeling expertise. A former platinum consultant with SAP America, Inc., Gary has more than 20 years of SAP experience, working with SAP NetWeaver BW since version 1.2B.

See more by this author


No comments have been submitted on this article. 

Please log in to post a comment.

To learn more about subscription access to premium content, click here.