Home > Semantic Layer, Web Intelligence > Multipass SQL 2.0 (Updated 2/23/2009)

Multipass SQL 2.0 (Updated 2/23/2009)

So what is Multipass SQL?

Multipass SQL. To calculate comparisons or to correctly calculate non-additive measures in report break rows, the query tool must break the report down into a number of simple queries that are processed separately by the DBMS. The query tool then automatically combines the results of the separate queries in an intelligent way. Breaking a single complex request into several small requests is called multipass SQL. Multipass SQL also allows drilling across several conformed data marts in different databases, in which the processing of a single galactic SQL statement would otherwise be impossible. Finally, multipass SQL gives the aggregate navigator a chance to speed up the report, because each atomic SQL request is simple and easily analyzed by the aggregate navigator. (from Ralph Kimball)

Welcome Multipass 2.0 with BusinessObjects!

What You Are About To Read May Blow Your Mind

Okay – it’s not new, but I dubbed it 2.0 because in my opinion BusinessObjects takes a superior approach to the Multipass SQL problem.

BusinessObjects employs a number of techniques to solve traditional multipass problems in a unique way.  Users of BusinessObjects can get the power of Multipass SQL without the traditional contraints and overheads of inserting records into temporary tables and requiring the report creator to engage in a number of complex steps to get the desired results.  (Anyone ever run out of temp space?)

Highlighted here are some of the most common scenarios in which BusinessObjects employs Multipass 2.0 techniques.

Single dimension across multiple fact tables – The IT organization can configure the Universe (BusinessObjects Semantic Layer) to generate multiple SQL statements when measures exist in from multiple fact tables.

Using the grains of measurement in the same query –  Sometimes you want to be able to do create a query that shows Previous YTD, YTD, QTD, MTD for the same measure, e.g. Revenue.

Requiring the end results to be used in a calculation – Displaying values such as % contribution, ratios (revenue per employee), etc.

Semi-additive measures – My personal favorite.  These measures are traditionally measures that aggregate over all dimensions EXCEPT time.  Some examples are stock level, account balance.  The stock level in the database display the number that are on-hand as of the end of the day.  The account balance displays how much money is in my account and the end of the day.  If I look at these figures across geographies, then they need to be summed up; however if I want to look at the values for the current month, I should only use the value as of today.

Analyzing a subset of data – Sometimes I want to be able to analyze a subset of data to another subset of data.  For example, get all the latest customer transactions and compare them to the historical transactions.

In every single one of these cases, BusinessObjects delivers the results that the user needs without creating temporary tables and inserting thousands of records.  Instead BusinessObjects uses a number of techniques including:

  • Derived Tables
  • Multi-SQL Universe Options
  • Universe Contexts
  • Query-Drill
  • Report Variables

How To Whitepaper Available

If I’ve whet your appetite for more information, then you will want to download the whitepaper from the SAP SDN site.  This whitepaper explains in detail exactly how to accomplish each of these scenarios using BusinessObjects.  I’ve had some problems with these documents from time to time, so I’ve also made them available from my blog directly.  You can download the whitepaper here and the powerpoint here.

The bottom line is that modern high performance databases are experts at handling extremely complex SQL.  Their optimization engines mean that it is no longer necessary to break queries down into managable pieces and perform multiple passes in the traditional sense.

What Do You Think?

If  you have a complex query scenario that can’t be solved by one of the techniques used in this whitepaper, I’d love to hear from you.

Enjoy.

«Good BI»

  1. Rej
    January 29th, 2009 at 14:16 | #1

    Hello,

    Great post. But I wasnt able to download the file. Looks like its no longer available. Thanks.

    Rej

  2. Guru
    January 30th, 2009 at 16:24 | #2

    I could not download the Whitepaper, can you please re-publish the link.

  3. February 2nd, 2009 at 12:00 | #3

    Thanks for bringing this to my attention. I will fix this asap.

  4. bmiessen
  5. tex
    February 20th, 2009 at 00:48 | #5

    Interesting post but no whitepaper. Link seems to be dead.

  6. brian
    February 20th, 2009 at 14:58 | #6

    hi can you kindly republish the link? i am very interested in the white paper…

  7. February 23rd, 2009 at 17:48 | #7

    Glad to see this was a popular blog post. I have updated the links and I have also a local URL as well so that it doesn’t happen again. Thanks!

  8. gibs
    February 22nd, 2013 at 15:42 | #8

    Hi
    I have seen your ppt regarding multi pass query, those are outstanding … great work.
    I would appriciate if you could send me any link or ppt to understand exact role of MULTIPLE SQL statements fro each MEASURE.

    regards

  1. No trackbacks yet.