David Taylors Business Intelligence Blog

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!

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»

I recently had a customer who was in the process of migrating from a legacy version of Microstrategy to BusinessObjects. They had been able to migrate over most of their core reports, but there were several reports that required a number of custom groups.

Microstrategy’s approach was to allow the customer to define any number of groups and filters from with the Microstrategy Desktop tool. These global filters and group definitions can then be leveraged by the report editor for building the report. Microstrategy’s SQL generation engine uses multi-pass SQL when creating the reports. Multi-pass SQL means that a single SQL statement cannot deliver back the correct results, so the report data is written into temporary tables. These temporary tables are then combined with additional queries to obtain the final results.

In the case of BusinessObjects, it wasn’t going to be possible to take the same approach. BusinessObjects does not generate multi-pass SQL. Instead, BusinessObjects focuses on creating extremely complex SQL in a single SQL statement. In most cases, this works just as well, but this was an exception. This sales report used a combination of multiple filters at multiple levels in the heirarchy. The Heirarchy was also defined within the custom groupings and not within the database.

The right answer is: Modify the Data Warehouse and ETL processes to make the underlying data better fit your reporting requirements. You see, the problem was that these custom groups should be defined within the data warehouse natively. This would allow for simplified SQL that can be run in a single pass. Translation? Reports the use custom groups will run a heck of a lot faster.

In my situation, the customer wasn’t going to be able to make these changes, but still wanted to migrate the report to BusinessObjects.

The best answer is: Use Sets. Sets gives you the ability to generate those custom grouping within the data warehouse. Unlike Microstrategy which will run the same multi-pass SQL over and over again, sets will allow us to “cache” the previous passes using sets and run single-pass SQL. We can then tie the set tables into the data warehouse and add the custom groupings to our universe.

The advantage of sets over ETL is that it can be much more dynamic. Sets can allow an analyst to create a new set in a matter of minutes and then see a report broken down by the new custom group.

In my case it was just what the doctor ordered. By introducing sets and adding the set tables to the BusinessObjects semantic layer, we now have access to the sets, which in this case, the customer called custom groups. It was perfect.

Now, not only will the customer be able to migrated this complex report from Microstrategy to BusinessObjects, but they will also be able to run the report much faster than ever before because the report will be generated through a single-pass process.

Tomorrow, we’ll take a look at how to set this up using our simplified data.

Can I Get Row-level Security with WebIntelligence and Still Avoid a Document Refresh?

Sure!

As a follow-up to my post yesterday I wanted to step back and say that normally you do want to handle row-level security from within the semantic layer. The problem is that you can’t get the benefit of the speed of data that comes from historical instances without requiring each user to run the report for themselves.

I did discover that you can provide the same type of security within WebIntelligence. WebIntelligence also has a function which also returns the name of the current user. The function is CurrentUser.

1. The first thing I need to do is build the semantic layer to include my security table.

2. Next, create a new variable which returns a 1 or 0 based on whether or not the user should see the data or not.

=If(CurrentUser()=”Administrator” Or CurrentUser() = [Username];1;0)

3. Create the report and apply the filter to the entire report or to the appropriate data block.

4. Here is what Ron’s sees, when he looks at an historical instance of the WebIntelligence report.

Notice that the Data Summary on the left hand side shows me that there were 71 records but even so, the restricted user ron is only allowed to 3 of the 71.

The beauty of this is that when I look at this data via BusinessObjects LiveOffice, I will see exactly the same restrictions enforced.

REMEMBER: Normally if you need row-level security, then just apply it to the universe. IF, however, you need row-level security on historical instances without requiring them to be refreshed, then use this great trick.