Very Good Loops Resolving
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Very Good Loops Resolving as PDF for free.
More details
- Words: 2,636
- Pages: 56
BusinessObjects Designer Essentials Alan Mayer & Penny Brewer Version 1.0 November xx, 2004
Presentation Information Alan Mayer & Penny Brewer Integra Solutions, Inc. Title: BusinessObjects Designer Essentials Track Session Description Make sure you really know the right way to design universes. Hear a design methodology and established best practices that will help you enhance the readability and performance of your universes. Learn how to recognize specific design problems like fan or chasm traps and the proper use of aliases, contexts, and shortcut joins. Attend this “intelligent primer” and walk away with the essential techniques for building effective and efficient universes.
Slide 2
Copyright © 2004 Business Objects S.A. All rights reserved.
Topics Universe Definition Tables Joins Loops Aliases Contexts Chasm Traps Fan Traps Conclusion (Q&A)
Slide 3
Copyright © 2004 Business Objects S.A. All rights reserved.
Universe Definition Webster’s Unabridged Dictionary All created things viewed as constituting one system or whole; the whole body of things, or of phenomena
WordNet Everything that exists anywhere
HyperDictionary The whole collection of existing things
AllWords the whole of space and all the galaxies, stars, planets, moons, asteroids and other bodies contained within it; the cosmos.
Slide 4
Copyright © 2004 Business Objects S.A. All rights reserved.
1/4
Universe Definition
2/4
BusinessObjects The semantic layer seen by users as they query the database Constructed from business terms they understand Tables and joins predefined in the background Universes
Query Panel
Slide 5
Copyright © 2004 Business Objects S.A. All rights reserved.
Database Schema
Database
Universe Definition A universe contains A structural representation of the database A logical interpretation of the data retrieved
Universe Pane
Slide 6
Structure Pane
Copyright © 2004 Business Objects S.A. All rights reserved.
3/4
Universe Definition
4/4
The Universe Creation Process
Define the database connection Insert tables Add joins between tables Resolve logical inconsistencies Create classes and objects Develop hierarchies
Structural Logical
Today’s presentation will focus on structural topics
Slide 7
Copyright © 2004 Business Objects S.A. All rights reserved.
Tables
1/2
Tables, views, and synonyms can be chosen from the Table Browser Use the shortcut button Right-click Structure Panel and select Tables
Select the desired tables and drag them onto the Structure Pane
Slide 8
Copyright © 2004 Business Objects S.A. All rights reserved.
Tables
2/2
The contents of the Table browser can be customized using external strategies Restricts the types of database objects retrieved XML format new in Version 6 Refer to Designer manual for more information <Strategy Name=“Project Tables"> <Message id="Help">This strategy selects project tables. <Message id="Name">External Strategy: Project Tables
Slide 9
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
1/9
Relationships between tables can now be defined Known as joins, these relationships can take many forms
Inner join Outer join Theta join Recursive join Self-restricting join Shortcut join
The next few slides will explain each join type
Slide 10
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
2/9
Inner Joins Also known as equi-joins or normal joins Usually take the the following form Single join: Primary Key (PK) = Foreign Key (FK) Compound Join: PK1 = FK1 and PK2 = FK2 and …
PK
City.city_id=Customer.city_id FK
Slide 11
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
3/9
Outer Joins Forces all rows from one table to be considered even if no matching row exists in second table For example: “Return all customers and orders if they exist” Syntax varies based on database Outer joins CASCADE! (see notes) Oracle: Customer.cust_id = Sales.cust_id (+) DB2: SELECT … FROM Customer LEFT OUTER JOIN Sales ON Customer.cust_id = Sales.cust_id
Slide 12
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
4/9
Theta Joins Relates two tables using relationships other than equality
Customer.age BETWEEN Age_group.age_min and Age_group.age_max
Slide 13
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
5/9
Recursive Joins A row is related to other row(s) within the same table Example: A sponsor may be stored in the same table as their referrals
Customer.sponsor_id = Customer.cust_id
Slide 14
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
6/9
Self-Restricting Joins A condition that should ALWAYS be applied against a table A universal condition rather than a join One way to force BusinessObjects to always add the condition to any SQL statement that references that table
Country.country_id = 1
Slide 15
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
7/9
Shortcut Joins Provides a shortcut or alternative path between tables Example: The Customer table may contain an extra column that allows a direct join to Country
Join Editor
Shortcut Join
Slide 16
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
8/9
Join cardinalities MUST be defined Cardinality determines the number of rows related to a current row They help resolve logical problems later
1:1
A salesperson has 1 customer; A customer has 1 salesperson
1:Many
A salesperson has 1 or more customers; A customer has one salesperson
Many: Many
Slide 17
A salesperson has 1 or more customers; A customer has 1 or more salespersons
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
9/9
Cardinalities can be established two different ways Automatic Detection (not as good) Manually via Join Editor (better) Automatic or
Manual
Slide 18
Copyright © 2004 Business Objects S.A. All rights reserved.
Loops
1/6
A loop is created when two or more paths exist between tables An employee can take a business trip to a country An employee is born in a country
Employee Trips
Employees
Slide 19
Copyright © 2004 Business Objects S.A. All rights reserved.
Trips
Countries
Loops
2/6
An Alternative Definition “Loops represent pools of water that cannot escape”
Employee Trips
Employees
Slide 20
Copyright © 2004 Business Objects S.A. All rights reserved.
Trips
Countries
Loops
3/6
What if Employee Name and Country were queried? Older versions of BusinessObjects placed all available joins in the query This retrieved only those employees that traveled to their country of origin!
Employee Trips
Employees
Slide 21
Copyright © 2004 Business Objects S.A. All rights reserved.
Trips
Countries
Loops
4/6
Newer version prevent incorrect results … by preventing the query altogether If attempted, the user will see the following error message Loops must be resolved to allow all possible valid queries
Slide 22
Copyright © 2004 Business Objects S.A. All rights reserved.
Loops
5/6
Detecting Loops Always a good idea Use the Detect Loops button (Tools / Detect Loops)
Slide 23
Copyright © 2004 Business Objects S.A. All rights reserved.
Loops
6/6
Printing Loops Use the Integrity Check button (Tools / Check Integrity) to print loops
Slide 24
Copyright © 2004 Business Objects S.A. All rights reserved.
Chasm Traps
1/2
First look for logical traps One of the most common is the Chasm Trap Usually the result of a Many to One, One to Many relationship N
N 1
1 Countries
Countries is the trap! Take a trip to England … … and be born in England?
Trips
Employees CHASM
Slide 25
Copyright © 2004 Business Objects S.A. All rights reserved.
Chasm Traps Other characteristics of chasms Often caused by joining to lookup tables The chasm cannot be crossed
Slide 26
Copyright © 2004 Business Objects S.A. All rights reserved.
2/2
Aliases
1/8
Aliases can resolve chasm traps Known as table aliases when writing SQL statements Used by BusinessObjects to logically separate the trap into pieces
SELECT a.country, b.country FROM country a, country b WHERE …
Slide 27
Table aliases Creating a alias in BusinessObjects
Copyright © 2004 Business Objects S.A. All rights reserved.
Aliases
2/8
Countries would be replaced by one (or two) aliases Create an alias for each path One alias is sufficient Two aliases makes the diagram more readable
Employee Trips
Trips
Employees
Countries
Nationality (Countries)
Slide 28
Destination_Countries (Countries)
Copyright © 2004 Business Objects S.A. All rights reserved.
Aliases
3/8
Generic lookup tables can be resolved using aliases Before: Lookups
Lookups
Type
Code Description
SAL
001
Base Salary
SAL
002
Overtime
SAL
003
Company Car
ABS
001
Holiday
ABS
002
Sick
ABS
003
Sick of Job
After: Abs_Lookups (Lookups)
Sal_Lookups (Lookups)
Slide 29
Copyright © 2004 Business Objects S.A. All rights reserved.
Aliases
4/8
Recursive relationships can also be resolved The depth of those relationships should be known For example: How many organization levels? How many part levels? Before: Employees
Employees
Emp_ID Name
Manager_ID
1
Mayer
5
5
Smith
23
23
Betten
42
42
Byrd
Slide 30
Copyright © 2004 Business Objects S.A. All rights reserved.
After: Employees
Managers (Employees)
Aliases
5/8
Every loop can be resolved with aliases, BUT …
There are drawbacks to using aliases More business terms (objects) will be added Those additional terms may confuse some users Aliases also CASCADE
Problem #1
Slide 31
Copyright © 2004 Business Objects S.A. All rights reserved.
Problem #2
Aliases
6/8
Adding aliases in BusinessObjects In the following structure, Country is a chasm trap Since chasms cannot be crossed, two aliases will separate the paths
Slide 32
Copyright © 2004 Business Objects S.A. All rights reserved.
Aliases Aliases can be manually added Use the Insert Alias button after selecting a table Right-click on a table and choose “Alias”
Slide 33
Copyright © 2004 Business Objects S.A. All rights reserved.
7/8
Aliases Aliases can be automatically added BusinessObjects can suggest possible aliases for you Be careful!! Just because an alias CAN be added doesn’t mean it SHOULD
Slide 34
Copyright © 2004 Business Objects S.A. All rights reserved.
8/8
Contexts
1/7
An alternative method to resolve loops is by creating Contexts A context represents one path or set of joins between tables Context #1: Trips Employee Trips
Trips
Employees
Countries
Context #2: Nationality Slide 35
Copyright © 2004 Business Objects S.A. All rights reserved.
Contexts A context contains each join in the path Context #1: Trips Employees.emp_id = Employee_Trips.emp_id Employee_Trips.trip_id = Trips.trip_id Trips.country_id = Countries.country_id
Context #2: Nationality Employees.country_id = Countries.country_id
Slide 36
Copyright © 2004 Business Objects S.A. All rights reserved.
2/7
Contexts
3/7
Contexts are associative Longer join paths will resolve loops caused by smaller paths Context #1
B A
C D
Context #1
Context #2
AB
AD
BC
DE
CD
EF
DE EF
E Context #2
Slide 37
F
Copyright © 2004 Business Objects S.A. All rights reserved.
Contexts
4/7
A contexts acts like a “roadmap of available path” Once chosen, other joins not in the context disappear Add as many additional joins to the context as needed Always add new joins to at least one context B A Z
C D E
Context #2
AB
AD
BC
DE
CD
EF
DE
F
Slide 38
Context #1
Copyright © 2004 Business Objects S.A. All rights reserved.
EF
Contexts
5/7
Contexts resolve the loop when the query is run rather than in the Designer This means that a context-based solution still has loops!
The user may be asked to choose between the contexts BusinessObjects will try to infer which context to use If it can’t figure it out, the user usually chooses a context
Once a context is chosen, all other joins “disappear” Only joins listed in the context will be used to build the final SQL program
Using contexts does not force additional objects
Slide 39
Copyright © 2004 Business Objects S.A. All rights reserved.
Contexts Contexts can be manually added The designer can add/subtract joins according to business rules
Slide 40
Copyright © 2004 Business Objects S.A. All rights reserved.
6/7
Contexts Contexts can be detected Be careful!! Not all suggested contexts make business sense
Slide 41
Copyright © 2004 Business Objects S.A. All rights reserved.
7/7
Alternative Loop Resolutions Shortcut joins can be used to resolve loops Be careful!! This technique may cause more problems than solutions Remember the alternative definition of loops
Slide 42
Copyright © 2004 Business Objects S.A. All rights reserved.
1/5
Alternative Loop Resolutions
2/5
Problem #1 A shortcut join “dissolves” the normal join relationship For the following query, only guests that have past and future activity will be returned
Slide 43
Copyright © 2004 Business Objects S.A. All rights reserved.
Alternative Loop Resolutions
3/5
Problem #1 Results For the following query, only guests that have past and future activity will be returned
Slide 44
Copyright © 2004 Business Objects S.A. All rights reserved.
Alternative Loop Resolutions Problem #2 In this scenario, no known path exists to Service A query cannot be written without a Cartesian Product
Slide 45
Copyright © 2004 Business Objects S.A. All rights reserved.
4/5
Alternative Loop Resolutions
5/5
Problem #2 Results The join from Service to any other table is missing!
No join to Service
Slide 46
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
1/9
Fan traps hide in Master-Detail-Detail relationships Trouble occurs when aggregating on a Master column
Invoice_Line
Sales inv_id
Slide 47
…
max_ guests
inv_id
service_id
…
nb_ guests
23102
10
23102
211
4
23103
8
23102
221
3
23104
5
…
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
2/9
Create query for Actual vs Budgeted Guests for Invoice 23102 Invoice: Budgeted Guests: Actual Guests:
Slide 48
Sales.inv_id sum(Sales.max_guests) sum(Invoice_line.nb_guests)
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
3/9
What happened? Two detail lines for Invoice 23102 Budgeted Guests is overstated by a factor (10 guests budgeted) * (2 detail lines) = 20 guests Result Set
Slide 49
Invoice
Budgeted Guests
Actual Guests
23102
10
3
23102
10
4
Totals:
20
7
Copyright © 2004 Business Objects S.A. All rights reserved.
(Budgeted Guests) * (Number of detail lines)
Fan Traps
4/9
Solution #1 Create a separate SQL statement per aggregation Aggregations on the same table require only 1 SQL statement
File / Parameters from main menu
Slide 50
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps Multiple SQL statements return the correct answer
Slide 51
Copyright © 2004 Business Objects S.A. All rights reserved.
5/9
Fan Traps
6/9
What if the Detail table isn’t aggregated Perhaps a user wants the services associated with a sale Service inv_id
…
service
211
Hotel Room
221
Restaurant
…
Sales inv_id
Slide 52
Invoice_Line …
max_ guests
inv_id
service_id
…
nb_ guests
23102
10
23102
211
4
23103
8
23102
221
3
23104
5
…
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
7/9
Create query for Budgeted Guests by Service for Invoice 23102 Invoice: Budgeted Guests: Service:
Slide 53
Sales.inv_id sum(Sales.max_guests) Service.service
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
8/9
Is there a problem? Guests are budgeted at the invoice level, not service level Summing the Budgeted Guests would be a mistake
Not good
Slide 54
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps Possible solutions Budget guests at a lower level of detail Prevent this query from occurring (drastic) Create a “smarter” report that realizes that budgeted guests can only be represented at the invoice level
More advanced solutions are possible Aggregate-aware techniques Alias/Context solutions (brittle) Data warehouse / data mart
Slide 55
Copyright © 2004 Business Objects S.A. All rights reserved.
9/9
Questions & Answers
Alan Mayer [email protected] (214) 637-6622 Penny Brewer [email protected] (214) 637-6622
Slide 56
Copyright © 2004 Business Objects S.A. All rights reserved.
Presentation Information Alan Mayer & Penny Brewer Integra Solutions, Inc. Title: BusinessObjects Designer Essentials Track Session Description Make sure you really know the right way to design universes. Hear a design methodology and established best practices that will help you enhance the readability and performance of your universes. Learn how to recognize specific design problems like fan or chasm traps and the proper use of aliases, contexts, and shortcut joins. Attend this “intelligent primer” and walk away with the essential techniques for building effective and efficient universes.
Slide 2
Copyright © 2004 Business Objects S.A. All rights reserved.
Topics Universe Definition Tables Joins Loops Aliases Contexts Chasm Traps Fan Traps Conclusion (Q&A)
Slide 3
Copyright © 2004 Business Objects S.A. All rights reserved.
Universe Definition Webster’s Unabridged Dictionary All created things viewed as constituting one system or whole; the whole body of things, or of phenomena
WordNet Everything that exists anywhere
HyperDictionary The whole collection of existing things
AllWords the whole of space and all the galaxies, stars, planets, moons, asteroids and other bodies contained within it; the cosmos.
Slide 4
Copyright © 2004 Business Objects S.A. All rights reserved.
1/4
Universe Definition
2/4
BusinessObjects The semantic layer seen by users as they query the database Constructed from business terms they understand Tables and joins predefined in the background Universes
Query Panel
Slide 5
Copyright © 2004 Business Objects S.A. All rights reserved.
Database Schema
Database
Universe Definition A universe contains A structural representation of the database A logical interpretation of the data retrieved
Universe Pane
Slide 6
Structure Pane
Copyright © 2004 Business Objects S.A. All rights reserved.
3/4
Universe Definition
4/4
The Universe Creation Process
Define the database connection Insert tables Add joins between tables Resolve logical inconsistencies Create classes and objects Develop hierarchies
Structural Logical
Today’s presentation will focus on structural topics
Slide 7
Copyright © 2004 Business Objects S.A. All rights reserved.
Tables
1/2
Tables, views, and synonyms can be chosen from the Table Browser Use the shortcut button Right-click Structure Panel and select Tables
Select the desired tables and drag them onto the Structure Pane
Slide 8
Copyright © 2004 Business Objects S.A. All rights reserved.
Tables
2/2
The contents of the Table browser can be customized using external strategies Restricts the types of database objects retrieved XML format new in Version 6 Refer to Designer manual for more information <Strategy Name=“Project Tables"> <Message id="Help">This strategy selects project tables. <Message id="Name">External Strategy: Project Tables
Slide 9
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
1/9
Relationships between tables can now be defined Known as joins, these relationships can take many forms
Inner join Outer join Theta join Recursive join Self-restricting join Shortcut join
The next few slides will explain each join type
Slide 10
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
2/9
Inner Joins Also known as equi-joins or normal joins Usually take the the following form Single join: Primary Key (PK) = Foreign Key (FK) Compound Join: PK1 = FK1 and PK2 = FK2 and …
PK
City.city_id=Customer.city_id FK
Slide 11
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
3/9
Outer Joins Forces all rows from one table to be considered even if no matching row exists in second table For example: “Return all customers and orders if they exist” Syntax varies based on database Outer joins CASCADE! (see notes) Oracle: Customer.cust_id = Sales.cust_id (+) DB2: SELECT … FROM Customer LEFT OUTER JOIN Sales ON Customer.cust_id = Sales.cust_id
Slide 12
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
4/9
Theta Joins Relates two tables using relationships other than equality
Customer.age BETWEEN Age_group.age_min and Age_group.age_max
Slide 13
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
5/9
Recursive Joins A row is related to other row(s) within the same table Example: A sponsor may be stored in the same table as their referrals
Customer.sponsor_id = Customer.cust_id
Slide 14
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
6/9
Self-Restricting Joins A condition that should ALWAYS be applied against a table A universal condition rather than a join One way to force BusinessObjects to always add the condition to any SQL statement that references that table
Country.country_id = 1
Slide 15
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
7/9
Shortcut Joins Provides a shortcut or alternative path between tables Example: The Customer table may contain an extra column that allows a direct join to Country
Join Editor
Shortcut Join
Slide 16
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
8/9
Join cardinalities MUST be defined Cardinality determines the number of rows related to a current row They help resolve logical problems later
1:1
A salesperson has 1 customer; A customer has 1 salesperson
1:Many
A salesperson has 1 or more customers; A customer has one salesperson
Many: Many
Slide 17
A salesperson has 1 or more customers; A customer has 1 or more salespersons
Copyright © 2004 Business Objects S.A. All rights reserved.
Joins
9/9
Cardinalities can be established two different ways Automatic Detection (not as good) Manually via Join Editor (better) Automatic or
Manual
Slide 18
Copyright © 2004 Business Objects S.A. All rights reserved.
Loops
1/6
A loop is created when two or more paths exist between tables An employee can take a business trip to a country An employee is born in a country
Employee Trips
Employees
Slide 19
Copyright © 2004 Business Objects S.A. All rights reserved.
Trips
Countries
Loops
2/6
An Alternative Definition “Loops represent pools of water that cannot escape”
Employee Trips
Employees
Slide 20
Copyright © 2004 Business Objects S.A. All rights reserved.
Trips
Countries
Loops
3/6
What if Employee Name and Country were queried? Older versions of BusinessObjects placed all available joins in the query This retrieved only those employees that traveled to their country of origin!
Employee Trips
Employees
Slide 21
Copyright © 2004 Business Objects S.A. All rights reserved.
Trips
Countries
Loops
4/6
Newer version prevent incorrect results … by preventing the query altogether If attempted, the user will see the following error message Loops must be resolved to allow all possible valid queries
Slide 22
Copyright © 2004 Business Objects S.A. All rights reserved.
Loops
5/6
Detecting Loops Always a good idea Use the Detect Loops button (Tools / Detect Loops)
Slide 23
Copyright © 2004 Business Objects S.A. All rights reserved.
Loops
6/6
Printing Loops Use the Integrity Check button (Tools / Check Integrity) to print loops
Slide 24
Copyright © 2004 Business Objects S.A. All rights reserved.
Chasm Traps
1/2
First look for logical traps One of the most common is the Chasm Trap Usually the result of a Many to One, One to Many relationship N
N 1
1 Countries
Countries is the trap! Take a trip to England … … and be born in England?
Trips
Employees CHASM
Slide 25
Copyright © 2004 Business Objects S.A. All rights reserved.
Chasm Traps Other characteristics of chasms Often caused by joining to lookup tables The chasm cannot be crossed
Slide 26
Copyright © 2004 Business Objects S.A. All rights reserved.
2/2
Aliases
1/8
Aliases can resolve chasm traps Known as table aliases when writing SQL statements Used by BusinessObjects to logically separate the trap into pieces
SELECT a.country, b.country FROM country a, country b WHERE …
Slide 27
Table aliases Creating a alias in BusinessObjects
Copyright © 2004 Business Objects S.A. All rights reserved.
Aliases
2/8
Countries would be replaced by one (or two) aliases Create an alias for each path One alias is sufficient Two aliases makes the diagram more readable
Employee Trips
Trips
Employees
Countries
Nationality (Countries)
Slide 28
Destination_Countries (Countries)
Copyright © 2004 Business Objects S.A. All rights reserved.
Aliases
3/8
Generic lookup tables can be resolved using aliases Before: Lookups
Lookups
Type
Code Description
SAL
001
Base Salary
SAL
002
Overtime
SAL
003
Company Car
ABS
001
Holiday
ABS
002
Sick
ABS
003
Sick of Job
After: Abs_Lookups (Lookups)
Sal_Lookups (Lookups)
Slide 29
Copyright © 2004 Business Objects S.A. All rights reserved.
Aliases
4/8
Recursive relationships can also be resolved The depth of those relationships should be known For example: How many organization levels? How many part levels? Before: Employees
Employees
Emp_ID Name
Manager_ID
1
Mayer
5
5
Smith
23
23
Betten
42
42
Byrd
Slide 30
Copyright © 2004 Business Objects S.A. All rights reserved.
After: Employees
Managers (Employees)
Aliases
5/8
Every loop can be resolved with aliases, BUT …
There are drawbacks to using aliases More business terms (objects) will be added Those additional terms may confuse some users Aliases also CASCADE
Problem #1
Slide 31
Copyright © 2004 Business Objects S.A. All rights reserved.
Problem #2
Aliases
6/8
Adding aliases in BusinessObjects In the following structure, Country is a chasm trap Since chasms cannot be crossed, two aliases will separate the paths
Slide 32
Copyright © 2004 Business Objects S.A. All rights reserved.
Aliases Aliases can be manually added Use the Insert Alias button after selecting a table Right-click on a table and choose “Alias”
Slide 33
Copyright © 2004 Business Objects S.A. All rights reserved.
7/8
Aliases Aliases can be automatically added BusinessObjects can suggest possible aliases for you Be careful!! Just because an alias CAN be added doesn’t mean it SHOULD
Slide 34
Copyright © 2004 Business Objects S.A. All rights reserved.
8/8
Contexts
1/7
An alternative method to resolve loops is by creating Contexts A context represents one path or set of joins between tables Context #1: Trips Employee Trips
Trips
Employees
Countries
Context #2: Nationality Slide 35
Copyright © 2004 Business Objects S.A. All rights reserved.
Contexts A context contains each join in the path Context #1: Trips Employees.emp_id = Employee_Trips.emp_id Employee_Trips.trip_id = Trips.trip_id Trips.country_id = Countries.country_id
Context #2: Nationality Employees.country_id = Countries.country_id
Slide 36
Copyright © 2004 Business Objects S.A. All rights reserved.
2/7
Contexts
3/7
Contexts are associative Longer join paths will resolve loops caused by smaller paths Context #1
B A
C D
Context #1
Context #2
AB
AD
BC
DE
CD
EF
DE EF
E Context #2
Slide 37
F
Copyright © 2004 Business Objects S.A. All rights reserved.
Contexts
4/7
A contexts acts like a “roadmap of available path” Once chosen, other joins not in the context disappear Add as many additional joins to the context as needed Always add new joins to at least one context B A Z
C D E
Context #2
AB
AD
BC
DE
CD
EF
DE
F
Slide 38
Context #1
Copyright © 2004 Business Objects S.A. All rights reserved.
EF
Contexts
5/7
Contexts resolve the loop when the query is run rather than in the Designer This means that a context-based solution still has loops!
The user may be asked to choose between the contexts BusinessObjects will try to infer which context to use If it can’t figure it out, the user usually chooses a context
Once a context is chosen, all other joins “disappear” Only joins listed in the context will be used to build the final SQL program
Using contexts does not force additional objects
Slide 39
Copyright © 2004 Business Objects S.A. All rights reserved.
Contexts Contexts can be manually added The designer can add/subtract joins according to business rules
Slide 40
Copyright © 2004 Business Objects S.A. All rights reserved.
6/7
Contexts Contexts can be detected Be careful!! Not all suggested contexts make business sense
Slide 41
Copyright © 2004 Business Objects S.A. All rights reserved.
7/7
Alternative Loop Resolutions Shortcut joins can be used to resolve loops Be careful!! This technique may cause more problems than solutions Remember the alternative definition of loops
Slide 42
Copyright © 2004 Business Objects S.A. All rights reserved.
1/5
Alternative Loop Resolutions
2/5
Problem #1 A shortcut join “dissolves” the normal join relationship For the following query, only guests that have past and future activity will be returned
Slide 43
Copyright © 2004 Business Objects S.A. All rights reserved.
Alternative Loop Resolutions
3/5
Problem #1 Results For the following query, only guests that have past and future activity will be returned
Slide 44
Copyright © 2004 Business Objects S.A. All rights reserved.
Alternative Loop Resolutions Problem #2 In this scenario, no known path exists to Service A query cannot be written without a Cartesian Product
Slide 45
Copyright © 2004 Business Objects S.A. All rights reserved.
4/5
Alternative Loop Resolutions
5/5
Problem #2 Results The join from Service to any other table is missing!
No join to Service
Slide 46
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
1/9
Fan traps hide in Master-Detail-Detail relationships Trouble occurs when aggregating on a Master column
Invoice_Line
Sales inv_id
Slide 47
…
max_ guests
inv_id
service_id
…
nb_ guests
23102
10
23102
211
4
23103
8
23102
221
3
23104
5
…
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
2/9
Create query for Actual vs Budgeted Guests for Invoice 23102 Invoice: Budgeted Guests: Actual Guests:
Slide 48
Sales.inv_id sum(Sales.max_guests) sum(Invoice_line.nb_guests)
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
3/9
What happened? Two detail lines for Invoice 23102 Budgeted Guests is overstated by a factor (10 guests budgeted) * (2 detail lines) = 20 guests Result Set
Slide 49
Invoice
Budgeted Guests
Actual Guests
23102
10
3
23102
10
4
Totals:
20
7
Copyright © 2004 Business Objects S.A. All rights reserved.
(Budgeted Guests) * (Number of detail lines)
Fan Traps
4/9
Solution #1 Create a separate SQL statement per aggregation Aggregations on the same table require only 1 SQL statement
File / Parameters from main menu
Slide 50
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps Multiple SQL statements return the correct answer
Slide 51
Copyright © 2004 Business Objects S.A. All rights reserved.
5/9
Fan Traps
6/9
What if the Detail table isn’t aggregated Perhaps a user wants the services associated with a sale Service inv_id
…
service
211
Hotel Room
221
Restaurant
…
Sales inv_id
Slide 52
Invoice_Line …
max_ guests
inv_id
service_id
…
nb_ guests
23102
10
23102
211
4
23103
8
23102
221
3
23104
5
…
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
7/9
Create query for Budgeted Guests by Service for Invoice 23102 Invoice: Budgeted Guests: Service:
Slide 53
Sales.inv_id sum(Sales.max_guests) Service.service
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps
8/9
Is there a problem? Guests are budgeted at the invoice level, not service level Summing the Budgeted Guests would be a mistake
Not good
Slide 54
Copyright © 2004 Business Objects S.A. All rights reserved.
Fan Traps Possible solutions Budget guests at a lower level of detail Prevent this query from occurring (drastic) Create a “smarter” report that realizes that budgeted guests can only be represented at the invoice level
More advanced solutions are possible Aggregate-aware techniques Alias/Context solutions (brittle) Data warehouse / data mart
Slide 55
Copyright © 2004 Business Objects S.A. All rights reserved.
9/9
Questions & Answers
Alan Mayer [email protected] (214) 637-6622 Penny Brewer [email protected] (214) 637-6622
Slide 56
Copyright © 2004 Business Objects S.A. All rights reserved.
Related Documents
Very Good Loops Resolving
May 2020 1
Very Good Ramazan Guide
May 2020 2
Very Good Presentation
December 2019 17
Very Good Images
July 2020 0
Design Pattern [very Good]
April 2020 14