How to Solve Loops, Fan, and Chasm Traps in SAP BusinessObjects Universes

  • by Taha M. Mahmoud, Senior BI Consultant
  • February 10, 2016
Learn how to identify, segregate, and fix the different SQL design traps (e.g., loops, fans, and chasms) that may occur when creating SAP BusinessObjects universes. Learn about the issues that come up if these design traps are not fixed. Finally, learn how to use SAP BusinessObjects functions, such as shortcut joins, contexts, aliases, and data foundation SQL options, to fix and resolve these traps.
Learning Objectives

Reading this article, you will learn how to:

  • Differentiate between design traps (e.g., fan, chasm, and loops) and determine why they are harmful
  • Identify and fix fan, chasm, and loop traps
Key Concept

When universe designers test a universe, one of their main tasks is to find and fix SQL design traps. There is no automated way to discover most types of design traps. These traps must be identified and corrected in order to avoid getting incorrect figures in SAP BusinessObjects BI reports or documents that use these universes.

There are three main types of SQL design traps: fan, chasm, and loops. I describe each of these traps in detail. There is no automated way to identify most types of SQL design traps, but I show how to spot them and how to use advanced data foundation options and techniques, such as shortcut joins, contexts, and aliases, to fix these traps, saving you time and effort.

Note
SQL design traps may affect the final SAP BusinessObjects BI document’s figures (e.g., calculations that are presented in reports) that are generated from the affected SAP BusinessObjects universe.

Testing a universe before publishing it and making it available for use are important parts of the universe development life cycle. I discuss the critical tasks of finding and solving SQL design traps that every universe designer should consider when testing universes. Design traps are logical joins between tables that initially seem to be natural, but actually generate wrong figures if they aren’t identified and fixed before publishing the universe.

Note
Publishing a universe means exporting the final tested version of the universe to the SAP BusinessObjects server and making it available to be used by business users in their BI documents. Examples of BI documents include Web Intelligence (known as WEBI or WebI) reports or dashboards.

SQL Design Traps

Testing universes is a critical part of the universe development life cycle because if the universe designer ignores this phase, the final universe may not satisfy the business requirements for which it was built. In the testing phase, the goal is to examine the universe to make sure that it is aligned with the initial business requirements. To achieve this, the Information Design Tool (IDT) offers many features that can help with testing universes, such as:

  • The check integrity wizard: This wizard checks the metadata of universe tables against the actual physical table structure. It tests joins, data types, and length of columns and table names. It also parses all objects (dimensions, measures, attributes, and pre-defined filters) in the business layer.
  • The query panel: The query panel from the business layer file can be used to construct queries based on different dimensions, measures, attributes, and pre-defined filters to test the figures’ outputs. This ensures that relationships between tables and joins are correct.
  • BusinessObjects reports: The final step of universe testing is to validate the figures of BusinessObjects documents, such as Web Intelligence and Crystal reports. This validation is done by comparing the figures in final reports against business figures generated from the source system. You can check the generated SQL script behind the report if the figures fail validation. This helps you when checking the SQL query, debugging it, and then finding the required fix. This fix normally takes place in the universe; once fixed, you may need to do another test to make sure it’s working correctly.

Note that it is not necessary to end up with a required fix in the universe; sometimes it may be discovered that the problem was introduced in the original underlying data-mart tables, aggregations, or even in the data-warehouse loading mechanism. Some examples of this include when:

  • The query returns wrong or unexpected results.
  • The table joins initially seem to be logical and all joins seem to be parsed successfully, but they are not.
  • The business-layer objects’ (e.g., dimensions, measures, attributes, or pre-defined filters) syntax seems to be correct, and they appear to be parsed successfully as well, but they are not.

These issues can happen if a universe is trapped by one of SQL design traps that I describe.

Taha M. Mahmoud

Taha M. Mahmoud is a PMP, TOGAF, ITIL, and CSM, and a senior BI consultant, BI project manager, and solution architect. He has more than seven years of experience consulting and deploying successful BusinessObjects projects in the banking and telecom industries. Taha is the author of the book, Creating Universes with SAP BusinessObjects.

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.