Distribute Pre-Calculated Web Reports More Quickly Using Value Sets

  • by Satish Chalasani, Independent BW/SEM Consultant
  • April 1, 2003
Some reports require two queries where the second query needs the results of the first. In situations for which such a report must run repeatedly for distribution to many people, this can result in slow performance due to a large number of database accesses. By using a pre-calculated value set rather than a pre-query variable, you reduce the number of database hits and improve performance.

 

Imagine this scenario: You have a requirement to create an Excel/Web query that answers the question, “What is the value of orders for a group of customers during a certain time period?” The data is in an InfoCube that contains customers, ordered quantity, and sales value at the header and item levels. The resulting report needs to be sent by email at the same time each month to the sales representative responsible for these customers.

Your only option to send the report automatically at a scheduled time is to run it in batch mode. The process appears simple: A Web report based on the query is run offline using BW's Reporting Agent, and the results are downloaded using the Download Scheduler found in BW 3.0B. The query results are then delivered by email as an Excel/Web report attachment to the inbox of the sales representative or the customer.

You have two choices for calculating variables in batch for your query: pre-query variables or pre-calculated value sets. The two are similar, in that both allow you to perform an initial query and use those results as variables in a second query. With pre-calculated value sets, however, the values for the variable are pre-calculated and stored. They can then be shared across multiple subsequent queries, whether online or in batch mode. A pre-query variable calculates the variables for the initial query for the first and every subsequent report request. For reports like the one in my example, the pre-calculated value set can significantly increase performance by dramatically reducing the number of database accesses needed.Figure 1 illustrates the differences between pre-query variables and pre-calculated value sets.

Satish Chalasani

Satish Chalasani is a SAP-certified independent BW/SEM consultant with more than four years of business intelligence and business analytics packaged product implementation experience. Prior to his independent status, he worked for Deloitte Consulting and Cap Gemini Ernst & Young. His data warehousing experience includes extracting data from both non-SAP and SAP products (EBP, CRM, Marketset, and R/3). His expertise also involves delivering BW content via SAP Workplace and SAP Enterprise Portal. He also does BW project reviews and project team training. Satish has been through seven BW/SEM implementations so far.

See more by this author


Comments

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.