David Taylors Business Intelligence Blog

I was recently working with a universe and attempting to calculate a ratio between two numbers and coming up with some strange results.  I wanted to share this with you so you didn’t waste as much time as I did trying to solve the problem.

Always Returning Zero

In my example, I had two measures and i wanted to calculate the ratio between this.  I was working on a call center universe and I needed to calculate First Call Resolution Ratio.  That would be First Calls/Total Calls.  The problem was that my calculation was always returning 0.  Here is what the SQL looked like:

select count(dbo.Current_Facts.first_call) /count(dbo.Current_Facts.call_id)
from dbo.Current_Facts

There is nothing wrong with the SQL.  The problem was with the data type.  In my case, both the numerator and denominator are integers, therefore the resulting value is also an integer.  Since there are always less first calls than total calls, the values would be a fraction and therefore were truncated to 0.

An Easy Fix

Once you realize the problem, the fix is simple.  Simply modify the numerator to be a float value.  This will cause the calculate to result in a float value.  In my case I changed the first_call value as casted it as a float so my SQL came out like this:

select cast(count(dbo.Current_Facts.first_call) as float) /count(dbo.Current_Facts.call_id)
from dbo.Current_Facts

Although this should fix the problem, there are probably databases that may require you to be more specific in which case you might modify the SQL to look like this:

select cast(cast(count(dbo.Current_Facts.first_call) as float)/cast(count(distinct dbo.Current_Facts.call_id) as float) as float)
from dbo.Current_Facts

Hopefully this little tip will safe you tons of time spent scratching your head.

«Good BI»

I haven’t seen a lot of information available that talks about what’s new in the release of the latest version, BusinessObjects XI 3.1, so I thought I would point out some of the highlights I’ve come across.  You may also want read the complete What’s New in BusinessObjects XI 3.1 guide from the website.

Release Highlights

BusinessObjects XI 3.1 is a release that is focused on bringing parity to our platform support for both Java and .NET.  In BusinessObjects XI 3.0 we did not provide a .NET version of our standard BI portal, InfoView.  In BusinessObjects XI 3.1 we have released a native .NET portal which has near parity with the Java version.  I say near parity, because there are a few things missing, namely:

• Encyclopedia
• Dashboard Builder
• Voyager
• BI Mobile
• Polestar

For companies who find comfort in an all Microsoft World, this release will be welcomed.  BusinessObjects XI 3.1 has added support for Windows Server 2008, SQL Server 2008 and Sharepoint Portal Server 2007.  (There is also Microsoft Active Directory Forest Support as well!)  If you are using the SAP Integration Kit, this is also now available on the .NET platform.

Web Application Container Service

One of the way in which BusinessObjects has been able to balance both rich feature sets and cross platform compatibility is through the introduction of a new server to the BusinessObjects Platform.  This service is called the WACS, Web Application Container Service, and is a java container.  What this allows BusinessObjects to do is to run existing java applications through the BusinessObjects framework.

Let me explain how this will work in practice.  The BusinessObjects Administration Console, the CMC, is written in Java.  Instead of rewritting this application in .NET, BusinessObjects simply added the WACS to the BusinessObjects service bus and will execute application via this service.  The beauty of this solution is that the .NET administrator never has to worry about it.  BusinessObjects manages everything.

What other Applications can use the WACS?

Besides the CMC, there are a number of other applications that could within the WACS.  This includes:

• Interactive DHTML Viewer for WebIntelligence
• Query as a Web Service (for supporting Xcelsius)
• LiveOffice

There is an important document that covers what is supported and what is not supported within the WACS framework.  In theory all native java-based aspects of the BusinessObjects application suite could be run through the WACS but the question is whether or not it is officially supported.  This document is called:  Web Application Container Server (WACS): Supported and Unsupported Features for BusinessObjects Enterprise XI 3.1and it explains the details.  You can download it here.

Additional Platform Enhancements

BusinessObjects XI 3.1 supports IPv6 100% across the product suite (except for Desktop Intelligence).  They have also added their first native 64-bit architecture.  Since BusinessObjects XIr2 SP3, the platform has been able to run on 64-bit operating systems however the platform still runs as a 32-bit application.  The first native 64-bit release is an the HP-UX IA-64 Itanium hardware.

Life Cycle Manager

Life Cycle Manager, LCM for short, s a web-based utility that allows the administrator to:

• Promote BI content from one system to another (without affecting the dependencies of the content)
• Manage dependencies of BI content
• Manage different versions of BI content
• Roll back (yahoo!) promoted BI content

This tool does not install with the platform, but is a separate install.

“So isn’t that what the Import Wizard is for?”, you may be asking?  Well, certainly the import wizard has been used to provide this type of functionality in the past – especially with the introduction of BIAR files in BusinessObjects XI; however I think you’ll see from the list of features in LCM, it is better to have a specialized tool for the LCM process.

Life Cycle Manager provides a number of features over the Import Wizard (besides rollback) including:

• Granular control over the objects selected (yes, we trust you)
• Scheduling Promotions Jobs (a job is a collection of BI content or InfoObjects to be precise)
• Integrated Version Control (via Subversion)
• Database connection overrides and mapping (Tired of changing database connection strings?)
• Auditing
• The ability to “preview” to changes before committing them
• Rollback, Did I mention rollback?

There is a new LCM Job server which has been added to the platform to manage the promotion process.

Have you ever created a BIAR file and realized you missed a file?  Now with LCM you can save your Promotion job and use it to generate a BIAR file.  If you find that something is missing, you can simply open up the promotion job , select the missing objects and regenerate the BIAR file.

BIAR ≠ BIAR

When is a BIAR file not a BIAR file?  Well, with the LCM tool, the BIAR files which are generated by the LCM tool are LCMBIAR files.  As the name indicates, the LCMBIAR files are NOT compatible with the BIAR files used with the Import Wizard.  Bummer.

Voyager

Voyager was the other biggie.  A lot of work was put into providing interface enhancements and increased performance.  This is probably the last release before the eagerly anticipated release of “Pioneer” in 2009, which will combine Voyager’s intuitive user interface with the powerful OLAP capabilities of the BEx OLAP tools.

Voyager now has a number of new visualizations including Box-Plot, Scatter, Bubble and Radar charts.    There are also a number of features that were in OLAP Intelligence, which were missing in earlier versions of Voyager, namely, Exception highlighting, URL Linking to other BI Content and Favorite Groups.

Have you ever made a mistake when building a Voyager workspace?  Well, now you have full undo/redo capabilities.  After being so used to the undo/redo capabilities in WebIntelligence, I always found the missing feature extremely annoying and I’m sure other did to.

Fly Over Of Additional Features

Rather than talk about additional features in detail, I thought I would briefly mention some of them here and perhaps in the future I can blog about them in detail if there is interest.

Semantic Layer -Support for BEGIN_SQL, Prompt support for codes AND descriptions.

Crystal Reports – Dual Monitor support, flash printing to PDF.

WebIntelligence -Support for Extension Points, Auto-Save, RelativeValue(), Multi-Pass Bursting

SAP Integration – SNC for BusinessObjects Universes (Prior to XI 3.1, this was only available for Crystal Reports), Improve Prompt Support for Hierarchy and Hierarchy Node dependencies.

Dashboard Builder – Printing, Interportlet Communication

Summary

There are some good things that have come out in this latest release.  This release is also the latest ‘reset point’ for BusinessObjects XI 3.X maintenance, so you can expect fix packs for XI 3.0 to no longer be released.

Hope you found this information useful.  I’ve tried to keep it short and sweet.

«Good BI»

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»