Multipass Queries

---

As I help clients select BI tools, some BI vendors claim they can do multipass SQL because they built reports in multiple stages. This is misleading, as it is not the generally accepted definition of multipass SQL. Multipass SQL means the BI tool can generate multiple SQL statements and thus perform multiple passes against the database to generate correct query results. From the user perspective, there is only one data provider; it appears you are building one query. The BI tool generates multiple SQL statements and dynamically stitches the results together, often unbeknownst to the user. Standard SQL cannot do this. BusinessObjects has been able to perform multipass SQL since version 4. It is one of the features that allow you to use BusinessObjects against complex data models such as those in a transaction system or against data warehouses that contain multiple start schemas. BusinessObjects will generate multiple SQL statements whenever there are measures in a query that come from more than one fact table. If the dimensions or GROUP BY clause for the two queries are exactly the same, BusinessObjects will automatically display the result set in one table and you will never know that multiple SQL statements were generated to create the report.

Following are some sample business questions for which BusinessObjects may generate multiple SQL statements to answer:

Information Requirement	Explanation
Debits, Credits, Month End Balance	Debits and credits are aggregated over a period of time, while balances are one point in time.
Movements In/Out, Inventory	Material movements are aggregated over a period of time, while inventory is one point in time.
Days Sales Inventory (DSI)	Sales are aggregated over a period of time, while inventory is one point in time.
Product Sales, Promotion	Sales come from one fact table, while promotion costs come from another fact table.

In order for multipass SQL to work correctly, the universe designer must set specific SQL parameters and define contexts for each set of joins that make up a star schema. These options are discussed in Chapters 6 and 7, respectively. Refer to Figure 6-7 and Figure 7-3 for examples of multipass SQL in action. From a user viewpoint, you only need to worry about multipass SQL if you think you are getting incorrect results or if BusinessObjects splits the results into two tables. For example, in Figure 22-1, the table on the left shows the desired results; the report on the right shows two tables, undesired results. BusinessObjects creates two tables, one for each SQL statement, when there is an additional dimension object that does not apply to both measures. The left-hand table displays the Sales Revenue and Promotion Cost by Year, SKU Description, and SKU number. These three dimensions are common to both Sales Revenue and Promotion Cost. The report on the right includes another dimension, State, that relates only to Sales Revenue. Promotion Costs are not tracked by State, so BusinessObjects creates two separate tables.

Figure 22-1: Sample reports built by multipass SQL

Figure 22-2 shows the two SQL statements used to generate the undesired report on the right in Figure 22-1. The problem appears in Select1. The dimension object State (OUTLET_LOOKUP.STATE) applies only to sales; it does not apply to promotions and is not defined as part of the promotion star schema or context. Therefore, OUTLET_LOOKUP.STATE does not appear in either the SELECT or GROUP BY sections of Select2 in Figure 22-2. When a dimension does not apply to both measures, BusinessObjects automatically creates a master detail report with two tables. The section header contains the Year and SKU Description dimensions that are common to both measures.

Figure 22-2: One query generates two SQL statements. Select1 has an extra GROUP BY dimension.

---