Alias & contexts final

Document Sample
Alias &  contexts final Powered By Docstoc
					Contents
    Introduction Loop.
    What is Alias?
         When & how to Alias
         Detection And Creation
    What are Contexts ?
         Detection And Identification
         Creation And Editing
    The Difference.
    The Sequence.
    When to use What.


1 Copyright © 2004 Business Objects SA - All Rights Reserved
 What is a loop?




 A loop exists when the joins between tables form a continuous path

2 Copyright © 2004 Business Objects SA - All Rights Reserved
How to deal with loops
One of two routines can be used to resolve most loop
situations in the universe structure window.

Set Cardinality Manually                                       Alias Detection
for All Joins                                                  Routine

                                                                ...and...

Must be done prior to using                                    Context Detection
detection routines and done                                    Routine
correctly. Otherwise,
routines may recommend
incorrect solution.


3 Copyright © 2004 Business Objects SA - All Rights Reserved
Cardinality Detection
   Set Cardinalities:
    Cardinality not set:



   Do this manually:




4 Copyright © 2004 Business Objects SA - All Rights Reserved
What is an Alias ?
   An Alias is an exact duplicate of the original table
    with a new name. The data in the table is exactly the
    same.

   The Alias is used only to resolve the loop in the
    structure of the universe. There is no impact on the
    schema of the database
         Easy to define
         Easy to maintain
         Easy to use




5 Copyright © 2004 Business Objects SA - All Rights Reserved
When to Alias
   A loop with a single                                            N         N
    lookup table should
    be resolved by an
    alias                                                      1

                                                               N                  1
   A lookup table can be
                                                               1
    identified by its                                                             N

    cardinality                                                N
                                                                        1     1


    A lookup table only
     has the ‘one’ end of                                          Alias needed here
     joins attached to it

6 Copyright © 2004 Business Objects SA - All Rights Reserved
How to Alias
   Designer routines
    detect loops and
    candidates for
    aliases
   Break the loop by
    creating an alias of
    the lookup table for
    each side of the loop

   Some designers like
    to create an alias for
    both sides of the                                          Do not remove the
    loop.                                                        original table

7 Copyright © 2004 Business Objects SA - All Rights Reserved
Detecting and Creating Aliases
   To create an alias table to break a loop, you can:

                        Use the Loop Detection routine



                         Use the Alias Detection routine



                         Manually insert an alias



8 Copyright © 2004 Business Objects SA - All Rights Reserved
Using automatic loop detection
   Click the Detect Loops button
   The routine checks the structure for loops




    The Loop Detection window identifies each loop
    The window suggests candidate contexts or aliases

9 Copyright © 2004 Business Objects SA - All Rights Reserved
Using Detect Aliases Routine
   Click the Detect Aliases button




   The routine lists
    candidate Alias
    tables

   You can rename
    the Alias tables if
    required


10 Copyright © 2004 Business Objects SA - All Rights Reserved
Inserting an Alias Manually
   Select the table and click the Insert Alias button




   Name the Alias table and click OK
   Then reset the joins manually



11 Copyright © 2004 Business Objects SA - All Rights Reserved
Choosing which Alias Method to Use
Method                        Advantages                        Disadvantages

Detect Loops                  Can view loops before             May show other loops to
                              applying them                     be resolved by contexts

Detect Aliases                Finds all candidate               No visual check prior to
                              aliases exclusively               acceptance

Insert Alias                  Choose table to alias -           Must understand how to
                              minimizing object                 identify the need for a
                              redefinition                      candidate analysis

   Whichever method you choose, you must always
    redefine objects that now use the Alias table
12 Copyright © 2004 Business Objects SA - All Rights Reserved
Listing & Renaming Aliases
    You can list all aliases in a universe via the Tools >
     List of Aliases…. Drop down menu option.




     You can also rename an alias via this panel.
13 Copyright © 2004 Business Objects SA - All Rights Reserved
Aliases : Tidying Up Loose Ends
   A single object query specifying either the Country of Client,
    Showroom or Maker will include all countries (Client, Showroom
    and Car Maker countries).



                                                                




   To avoid this you can force a join to the sub-sequent table (client,
    showroom or maker) and thereby force the query to restrict it ’s
    output to the relevant countries for that object.
                                                                

14 Copyright © 2004 Business Objects SA - All Rights Reserved
Forcing Additional Joins
   You can restrict the data that is returned for an object
    by specifying additional tables in the object properties.

   You do this in the
    Tables box

    Select the relevant
     table:
   The restriction is created
    from the joins between
    the tables
Only when this is done for each object relating to a multi-lookup table
will they return the correct results if used in a single object query

15 Copyright © 2004 Business Objects SA - All Rights Reserved
Using Aliases in a Multi Star situation?
   Normalized dimensions are not amenable to loop
    resolution using Aliases




                              Candidates for Aliases


16 Copyright © 2004 Business Objects SA - All Rights Reserved
Using Aliases in a Multi Star situation?
   If you try to resolve the loop using Aliases:




                                   All candidates for
                                  there Aliases loop
                                         is still a


17 Copyright © 2004 Business Objects SA - All Rights Reserved
Using Aliases in a Multi Star situation?
   and you get duplication of objects




18 Copyright © 2004 Business Objects SA - All Rights Reserved
Using Aliases in a Multi Star situation?
   The effective result is two universes:




Sales                                                           Rentals
Universe                                                        Universe




19 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


20 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


21 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.
22 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

23 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




24 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.




25 Copyright © 2004 Business Objects SA - All Rights Reserved
Resolving Loops using Contexts

                                                 Sales           Sal
                                                                Lines


Country                 Customers




                                      Loans
                      There are two possible routes through
                                                         Loans
                      the structure:                     Lines
                                      Rental_Model context
                                      Sale_Model context

A context is merely a collection of ALL the joins on a single route.
Context name = table name on a route with only “many” cardinality.

26 Copyright © 2004 Business Objects SA - All Rights Reserved
Resolving Loops using Contexts
                                                                A context is detected for
Rental_Model context
                                                                each route on which
                                                                there is a table with just
                                                                “many” cardinality.
                                                                Each context represents
                                                                what may be inferred in a
                                                                single SELECT
                                                                statement.
                                                                Any query which infers
                                                                some SQL code
                                                                exclusive to one context
                                                                and some exclusive to
                                                                the other will infer two
Sale_Model context
                                                                separate SELECT
                                                                statements

27 Copyright © 2004 Business Objects SA - All Rights Reserved
Detecting and Creating Contexts

                             Loop Detection routine: suggests
                              candidates for both aliases and contexts


                              “Detect Contexts” detects and
                               proposes a list of contexts to create



                              “Insert Contexts” inserts a context
                               manually


28 Copyright © 2004 Business Objects SA - All Rights Reserved
Creating a context manually
   To create a context manually:

    1. Select Insert > Context.

                           Or

    Click the Insert Context button.
    The New Context box appears.

    Type a name for the context
    in the Context Name text box.




29 Copyright © 2004 Business Objects SA - All Rights Reserved
Creating a context manually

3. Select all the joins defining the context in the Current
   Context Joins list.

4. Click the Detect button to show the joins making up a
   suggested context with context name.

5. Select the Show Selected Only check box to see only
   selected joins.




30 Copyright © 2004 Business Objects SA - All Rights Reserved
Creating a context manually

6. Click the Check button.
   Designer checks the selected joins for any loops.

7. Type a description of the data the context returns.
   This is the help text that a Web Intelligence user sees
   when they run a query that takes the context path.
   This text should be useful to the end user.

8. Click OK.

The context is created.
31 Copyright © 2004 Business Objects SA - All Rights Reserved
Display the contexts : View List Mode




32 Copyright © 2004 Business Objects SA - All Rights Reserved
Editing Contexts
   Double click the context in the List Mode window


   The context name


   The highlighted joins
    are included in the
    context

    The description
     appears in the User
     module Help panel

33 Copyright © 2004 Business Objects SA - All Rights Reserved
Loop Detection routine - the drawback
   When you use Loop Detection and detect a number of
    loops:

                                                                   Loop 1 of 10




    The routine does not offer the Alias candidates first,
     so you may have to update the Contexts after you
     have added the Alias tables


34 Copyright © 2004 Business Objects SA - All Rights Reserved
Where Is The Difference

How does an alias break a loop?
  An alias breaks a loop by using the same table twice
  in the same query for a different purpose. The alias is
  identical to the base table with a different name. The
  data in the alias is exactly the same as the original
  table, but the different name “tricks” SQL into
  accepting that you are using two different tables.
Note:
  You can resolve the loop satisfactorily by creating only
  one alias table also.



35 Copyright © 2004 Business Objects SA - All Rights Reserved
Here Is The Difference

    A context resolves a loop by defining a set of joins
    that specify one specific path through tables in a
    loop. It ensures that joins are not included from
    different paths within the same SQL query.

    You often use contexts in schema that contain
    multiple fact tables (“multiple stars”) that share
    lookup tables.




36 Copyright © 2004 Business Objects SA - All Rights Reserved
Sequence for resolving loops

1. Set cardinality on all joins (best to do this manually)
2. Use Detect Aliases to detect candidates for aliases
3. Insert all required alias tables and joins
4. Use Detect Contexts to detect candidates for contexts
5. Create the required contexts
6. Test in the User module




37 Copyright © 2004 Business Objects SA - All Rights Reserved
Recommended sequence: When to use what
    Join                           Detected by                       Solved by
   Problem
Loop                       • Detect Aliases                     Creating aliases and
                           • Detect Contexts                    contexts to break loops.
                           • Detect Loops
                           • Check Integrity
                           • Visual analysis of
                           schema
Chasm trap                 Visual analysis of table             • Creating a context.
(converging                schema.                              • Creating multiple Univs
many to one                                                       (WEBINTELLIGENCE
joins)                                                          only).

38 Copyright © 2004 Business Objects SA - All Rights Reserved
Recommended sequence: When to use what

   Join Problem                            Detected by              Solved by

Fan trap (serial many                  Visual analysis of       * Creating an alias,
to one joins)                          table schema.            creating a context using
                                                                the alias, then building
                                                                affected measure
                                                                objects on the alias.

                                                                * Using Multiple SQL
                                                                Statements for Each
                                                                Measure.


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

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