TRAPS

Document Sample
TRAPS Powered By Docstoc
					BusinessObjects Designer
Module 10: Contexts, Chasm and Fan Traps
What You Will Be Able To Do

   Understand the purpose of using contexts in a
    universe

   Recognize and be able to resolve Chasm Traps

   Recognize and be able to resolve Fan Traps




2 Copyright © 2004 Business Objects SA - All Rights Reserved
What are Contexts
   A context is simply a list of joins denoting a path
    between tables.

   Contexts are set to identify alternative routes in the
    universe structure.

   BusinessObjects detects a context for each
    alternative route on which there is a table with just the
    ‘ many ’ end of joins attached to it.

   Contexts identify joins (and therefore tables) which
    are compatible within the same SELECT statement


3 Copyright © 2004 Business Objects SA - All Rights Reserved
Alternative Routes
   Alternative routes do NOT only exist in loop scenarios.
                       Context 1

                                                                  Loop
                                                          2 Routes = 2 Contexts



                       Context 2                                      Context 1

                                                    Fork
                                         2 Routes = 2 Contexts



                                                                      Context 2


4 Copyright © 2004 Business Objects SA - All Rights Reserved
How Contexts are Detected
   A separate context is identified for each table with
    only the ‘many’ end of joins attached:




   The joins in a context are identified by working back
    from the table with only the ‘many’ end of joins
    attached - many-one, many-one.
5 Copyright © 2004 Business Objects SA - All Rights Reserved
Identifying how many Contexts are required
    You can arrange your universe structure so that all joins are flowing
     from the ‘many’ ends at the left to the ‘one’ ends at the right.




Number of
  contexts
required = 2

6 Copyright © 2004 Business Objects SA - All Rights Reserved
Identifying the joins that make up a Context
                                       No joins flowing
    The forward flowing joins form the Sale context
                                          back from one to
                                          many are included




7 Copyright © 2004 Business Objects SA - All Rights Reserved
Why Apply Contexts

    To avoid Chasm Traps
     By detectiing and setting contexts you will avoid the possibility of experiencing a chasm
     trap. This assumes that you have engineered cardinality correctly before detecting
     contexts.




    To resolve Fan Traps
     You can also use contexts as part of the means to resolve fan traps. However,
     unlike chasm traps, you must identify them first before they can be resolved.




8 Copyright © 2004 Business Objects SA - All Rights Reserved
The Chasm Trap
   For a Chasm Trap to occur, there must be:

                                                               When a query is run which
             X                  A                              uses objects Y and Z the
                                                               inferred SQL includes
                                                               tables B, C and A which
                                                               have a ‘ many-one-many ’
                                                               relationship respectively.
                                                               This results in the values
                                                               for each object to be
                                                               multiplied by the other.
                                                               The effect is similar to a
     Y               B                      C              Z   cartesian product but is
                                                               known as a chasm trap.


    The chasm trap is resolved by executing a separate SELECT statement for object Y and
    object Z.

9 Copyright © 2004 Business Objects SA - All Rights Reserved
Chasm Trap Proof : Scenario

  Step 2                                                                               Step 1
    a query
    with
    objects                                                                             ‘many
    from each                                                                           to one
    of the                                                                              to
    ‘many’                                                                              many’
    tables




                                                                                      Step 3
                                               Deny Multiple SQL Statements for each measure



                                             Multiple instances of a single dimension in results

10 Copyright © 2004 Business Objects SA - All Rights Reserved
Chasm Trap Proof : Effect

Test 1
                                                                


Test 2

                                                                


Test 3
                                                                
11 Copyright © 2004 Business Objects SA - All Rights Reserved
Chasm Trap Proof : SQL

Test 1



 The problem on test 3 arises because the processing of a single
Test 2
   SELECT statement produces a single virtual logical table to
                      apply aggregation.



Test 3




12 Copyright © 2004 Business Objects SA - All Rights Reserved
 Chasm Trap Proof : SQL Logical Table
               Test 1                                           Test 3




Where you have a many-one-many relationship for tables in the FROM clause
 the resulting logical table produces something akin to a Cartesian Product.
  Only then is aggregation applied. This is the reason for the chasm effect.

13 Copyright © 2004 Business Objects SA - All Rights Reserved
Chasm Trap : Solution


   How can we avoid the chasm trap?




   By inferring two separate SELECT statements and
    only then combining the results of each one into
    microcube(s) for projection into block(s).



14 Copyright © 2004 Business Objects SA - All Rights Reserved
Chasm Trap : Universe Solutions
   To infer two separate SELECT statements from a
    single BusinessObjects query you can either:-

    1 Alter the SQL parameters for the Universe

    2 Use Contexts

   The first method is NOT recommended as it only
    works with measures and will result in certain
    inefficiencies in processing. The second method
    works every time and will not result in inefficiencies.



15 Copyright © 2004 Business Objects SA - All Rights Reserved
Altering the Universe SQL Parameters
   Click


   Click


   Check




   The Chasm Trap query will now make one query for
    each measure and combine the results - correctly.

16 Copyright © 2004 Business Objects SA - All Rights Reserved
 SQL Parameter Method : the Drawbacks
1) IN-ACCURATE
   The parameter only
   works for measures. It
   does NOT separate                                            The report contains a single block
   queries containing only                                       with the results displayed as a
                                                                 Cartesian product - unclear for
   dimension objects.                                                         Users.



2) IN-EFFICIENT
   The parameter will
   force a SELECT
   statement for each
   measure, even when
   it is NOT necessary
   to do so!
17 Copyright © 2004 Business Objects SA - All Rights Reserved
Recommended Solution : Use Contexts
   Apply a context to each leg of the Chasm Trap:




18 Copyright © 2004 Business Objects SA - All Rights Reserved
Recommended Solution : Use Contexts (2)
   With the contexts in place, both measure object
    queries and dimension object queries display
    correctly:

      Measures




      Dimensions



   Conclusion:
    Always use contexts to resolve Chasm Traps

19 Copyright © 2004 Business Objects SA - All Rights Reserved
The Classic Fan Trap
   For a Fan Trap to occur, there must be:

                 X              A                    This is a common structure and will not normally
                                                     result ina fan trap. The trap only occurs where
                                                     (due to DB design) a column in table B holds data
                                                     values which are already a sum of those values
                                                     held at table C.

                 Y              B                    When a query is run which uses objects Y and Z
                                                     the inferred SQL includes tables B and C which
                                                     have a ‘ one-many ’ relationship. This results in a
                                                     value for the Y object being multiplied by the
                                                     number of values of the Z object related to that Y
                                                     object value. Like the chasm trap, the effect is
                 Z              C                    similar to a cartesian product.

Like the chasm trap, the fan trap can be resolved by executing a separate SELECT
statement for object Y and object Z. The alternative solution is to avoid it in the first place!

20 Copyright © 2004 Business Objects SA - All Rights Reserved
Classic Fan Trap Proof : Scenario
   For a Fan Trap to occur, there must be:

    Step 2                                                                            Step 1
     a query
     with a
     measure
     object from                                                                      ‘one to
     the Sale &                                                                       many
     another                                                                          to
     from the                                                                         many’
     Sale_Model
     table




                                           Multiple Sale_Model rows related to a single Sale row



21 Copyright © 2004 Business Objects SA - All Rights Reserved
Classic Fan Trap Proof : Effect


Test 1
                                                                




Test 2


                                                                

22 Copyright © 2004 Business Objects SA - All Rights Reserved
Classic Fan Trap Proof : SQL


Test 1




Test 2
   The problem on test 2 arises because the
   processing of a single SELECT statement
    produces a single virtual logical table to
              apply aggregation.

23 Copyright © 2004 Business Objects SA - All Rights Reserved
 Classic Fan Trap Proof : SQL Logical Table
               Test 1                                           Test 2




Where you have a one-many-many relationship for tables in the FROM clause
 the resulting logical table produces something akin to a Cartesian Product.
    Only then is aggregation applied. This is the reason for the fan effect.

24 Copyright © 2004 Business Objects SA - All Rights Reserved
Classic Fan Trap : Solution


   How can we avoid the fan trap?


   By inferring two separate SELECT statements and
    only then combining the results of each one into
    microcube(s) for projection into block(s).

   By avoiding the fan trap scenario in the first place!




25 Copyright © 2004 Business Objects SA - All Rights Reserved
Classic Fan Trap : Universe Solutions
   To infer two separate SELECT statements from a
    single BusinessObjects query you can either:-

    1 Alter the SQL parameters for the Universe

    2 Use a combination of Aliases and Contexts
    NOTE: The first method is NOT recommended as it only works with
    measures and will result in certain inefficiencies in processing. The
    second method works every time and will not result in inefficiencies.

   To avoid placing a measure on anything other than
    the last table in a table path (i.e. the table with only
    ‘ many ’ cardinality attached to it).
26 Copyright © 2004 Business Objects SA - All Rights Reserved
Altering the Universe SQL Parameters
   Click


   Click


   Check




   The Fan Trap query will now make one query for each
    measure and combine the results - correctly.

27 Copyright © 2004 Business Objects SA - All Rights Reserved
 SQL Parameter Method - the Drawback
1) IN-ACCURATE
   The parameter only works when
   the object on the Sale_Model
   table is a measure. It may NOT
   separate queries where the
   object on the Sale_Model table is
                                                                Column from the Sale_Model table.
   a dimension or detail object.

2) IN-EFFICIENT
   The parameter will
   force a SELECT
   statement for each
   measure, even when
   it is not necessary to
   do so!
28 Copyright © 2004 Business Objects SA - All Rights Reserved
Recommended Solution 1: The Theory
                      Alias & Context Fan Trap Solution
                           Ba                           1) Create an Alias of Table B.
X         C       A
                                                        2) Create a join between the
                                                           alias Ba and table A and
                                                           set cardinality.
Y                 B              Ba                Y
                                                        3) Set Contexts C and Ba.

                                                        4) Change the SELECT
Z                 C                                        clause of object Y so that
                                                           it refers to the alias Ba
                                                           rather then table B.
29 Copyright © 2004 Business Objects SA - All Rights Reserved
Recommended Solution 1: ThePractice
                     Alias & Context Fan Trap Solution
1) Create an Alias of the Sale
   Table.

2) Create a join between the
   Sale alias & Client table
   and set cardinality.
3) Set Contexts.
4) Change the SELECT clause of
   the Sale Revenue object so
   that it refers to the Sale alias
   rather than the Sale table.
30 Copyright © 2004 Business Objects SA - All Rights Reserved
    Recommended Solution 1: The Result
     Now a query involving a measure and
      another object from a subsequent table in
      the table path of a universe structure….
     …….results in 2 SELECT statements…..




      …….which are then merged in a single microcube to produce the
       correct result.



    31 Copyright © 2004 Business Objects SA - All Rights Reserved
Recommended Solution 2: The Theory
                     Avoiding a Fan Trap in the First Place

                                           1) This is only possible where the
             X            A
                                              measure on table B is a pre-
                                              aggregation of more detailed
                                              data which exists in table C.

             Y           B                 2) Code measure object Y so that
                                              it refers to the more detailed
                                              data in table C.
                                                NOTE : This will be less efficient
     Y       Z           C
                                                but at least it is accurate.


32 Copyright © 2004 Business Objects SA - All Rights Reserved
Recommended Solution 2: The Practice
                     Avoiding a Fan Trap in the First Place
1) In the universe below the Sales Revenue measure is not based on the total figure in the
   Sales table but on a number of columns from the Sales, Sale_Model and Model tables
   which are held in the DB at the same level of granularity as the number of cars sold.
   Hence, no fan trap exists and the correct result will be obtained.




33 Copyright © 2004 Business Objects SA - All Rights Reserved
What You Have Learned


   What contexts are used for in a Universe

   How to recognize and resolve a Chasm Trap

   How to recognize and resolve the classic Fan Trap




34 Copyright © 2004 Business Objects SA - All Rights Reserved
Questions ?

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:4
posted:9/7/2012
language:English
pages:35