very good loops resolving

8/14/2019 Very Good Loops Resolving

1/56

BusinessObjects

Designer Essentials

BusinessObjects

Designer EssentialsAlan Mayer & Penny Brewer

Version 1.0November xx, 2004


2/56

Copyright 2004 Business Objects S.A. All rights reserved.Slide 2

Presentation Information

Alan Mayer & Penny BrewerIntegra Solutions, Inc.

Title: BusinessObjects Designer EssentialsTrack Session Description

Make sure you really know the right way to design universes. Hear adesign methodology and established best practices that will help youenhance the readability and performance of your universes. Learnhow to recognize specific design problems like fan or chasm trapsand the proper use of aliases, contexts, and shortcut joins. Attendthis intelligent primer and walk away with the essential techniques

for building effective and efficient universes.


3/56


Topics

Universe DefinitionTables

JoinsLoopsAliases

ContextsChasm TrapsFan Traps

Conclusion (Q&A)


4/56


Universe Definition 1/4

Websters Unabridged DictionaryAll created things viewed as constituting one systemor whole; the whole body of things, or of phenomena

WordNetEverything that exists anywhere

HyperDictionaryThe whole collection of existing things

AllWordsthe whole of space and all the galaxies, stars, planets, moons,asteroids and other bodies contained within it; the cosmos.


5/56



BusinessObjectsThe semantic layer seen by users as they query the databaseConstructed from business terms they understandTables and joins predefined in the background

Universes Database Schema

DatabaseQuery Panel


6/56



A universe containsA structural representation of the databaseA logical interpretation of the data retrieved

Structure PaneUniverse Pane


7/56



The Universe Creation ProcessDefine the database connectionInsert tablesAdd joins between tablesResolve logical inconsistenciesCreate classes and objects

Develop hierarchiesTodays presentation will focus on structural topics

Structural

Logical


8/56


Tables 1/2

Tables, views, and synonyms canbe chosen from the Table Browser

Use the shortcut buttonRight-click Structure Panel andselect Tables

Select the desired tables and drag

them onto the Structure Pane


9/56


Tables 2/2

The contents of the Table browser can be customizedusing external strategies

Restricts the types of database objects retrievedXML format new in Version 6Refer to Designer manual for more information


10/56


Joins 1/9

Relationships between tables can now be definedKnown as joins, these relationships can take many

formsInner joinOuter joinTheta join

Recursive joinSelf-restricting joinShortcut join

The next few slides will explain each join type


11/56


Joins 2/9

Also known as equi-joins or normal joinsUsually take the the following form

Single join: Primary Key (PK) = Foreign Key (FK)Compound Join: PK 1 = FK 1 and PK 2 = FK 2 and

City.city_id=Customer.city_id

Inner Joins

PK

FK


12/56


Joins 3/9

Forces all rows from one table to be considered even ifno matching row exists in second table

For example: Return all customers and orders if they existSyntax varies based on databaseOuter joins CASCADE! (see notes)

Outer Joins

Customer.cust_id = Sales.cust_id (+)Oracle:

DB2:SELECT FROM Customer LEFT OUTER JOIN SalesON Customer.cust_id = Sales.cust_id


13/56


Joins 4/9

Relates two tables using relationships other thanequality

Theta Joins

Customer.ageBETWEEN

Age_group.age_min andAge_group.age_max


14/56


Joins 5/9

A row is related to other row(s) within the same tableExample: A sponsor may be stored in the same table as their

referrals

Recursive Joins

Customer.sponsor_id = Customer.cust_id


15/56


Joins 6/9

A condition that should ALWAYS be applied against atable

A universal condition rather than a joinOne way to force BusinessObjects to always add the condition to anySQL statement that references that table

Self-Restricting Joins

Country.country_id = 1


16/56


Joins 7/9

Provides a shortcut or alternative path between tablesExample: The Customer table may contain an extra column that

allows a direct join to Country

Shortcut Joins

Shortcut Join

Join Editor


17/56


Joins 8/9

Join cardinalities MUST be definedCardinality determines the number of rows related to a current rowThey help resolve logical problems later

1:1A salesperson has 1 customer;A customer has 1 salesperson

1:Many A salesperson has 1 or more customers;A customer has one salesperson

A salesperson has 1 or more customers;A customer has 1 or more salespersons

Many:Many


18/56


Joins 9/9

Cardinalities can be established two different waysAutomatic Detection (not as good)Manually via Join Editor (better)

Automatic

or

Manual


19/56


Loops 1/6

A loop is created when two or more paths exist betweentables

An employee can take a business trip to a countryAn employee is born in a country

EmployeeTrips

CountriesEmployees

Trips


20/56


Loops 2/6

An Alternative DefinitionLoops represent pools of water that cannot escape

EmployeeTrips

CountriesEmployees

Trips


21/56


Loops 3/6

What if Employee Name and Country were queried?Older versions of BusinessObjects placed all available joins in thequeryThis retrieved only those employees that traveled to their country oforigin!

EmployeeTrips

CountriesEmployees

Trips


22/56


Loops 4/6

Newer version prevent incorrect results by preventing the query altogetherIf attempted, the user will see the following error messageLoops must be resolved to allow all possible valid queries


23/56


Loops 5/6

Detecting LoopsAlways a good ideaUse the Detect Loops button (Tools / Detect Loops)


24/56


Loops 6/6

Printing LoopsUse the Integrity Check button (Tools / Check Integrity) to print loops


25/56


1 1

Chasm Traps 1/2

First look for logical trapsOne of the most common is the Chasm TrapUsually the result of a Many to One, One to Many relationship

Countries is the trap!Take a trip to England and be born in England?

N N

CHASMTrips Employees

Countries


26/56


Chasm Traps 2/2

Other characteristics of chasmsOften caused by joining to lookup tablesThe chasm cannot be crossed


27/56


Aliases 1/8

Aliases can resolve chasm trapsKnown as table aliases when writing SQL statementsUsed by BusinessObjects to logically separate the trap into pieces

SELECT a .country,b .country

FROM country a ,

country bWHERE

Table aliases

Creating a alias in BusinessObjects


28/56


Aliases 2/8

Countries would be replaced by one (or two) aliasesCreate an alias for each pathOne alias is sufficientTwo aliases makes the diagram more readable

Employee

Trips

Countries

Destination_Countries

(Countries)

Nationality(Countries)

Trips

Employees


29/56


Aliases 3/8

Generic lookup tables can be resolved using aliases

Holiday001ABS

Company Car003SAL

Overtime002SAL

Sick002ABS

Sick of Job003ABS

001

Code

SAL

Type

Base Salary

Description

LookupsLookups

Abs_Lookups(Lookups)

Sal_Lookups(Lookups)

Before:

After:


30/56


Aliases 4/8

Recursive relationships can also be resolvedThe depth of those relationships should be knownFor example: How many organization levels?

How many part levels?

Byrd4242Betten23

23Smith5

Mayer

Name

1

Emp_ID

5

Manager_IDEmployees Employees

Employees Managers(Employees)

Before:

After:


31/56


Aliases 5/8

Every loop can be resolved with aliases, BUT There are drawbacks to using aliasesMore business terms (objects) will be addedThose additional terms may confuse some usersAliases also CASCADE

Problem #1 Problem #2


32/56


Aliases 6/8

Adding aliases in BusinessObjectsIn the following structure, Country is a chasm trapSince chasms cannot be crossed, two aliases will separate the paths


33/56


Aliases 7/8

Aliases can be manually addedUse the Insert Alias button after selecting a tableRight-click on a table and choose Alias


34/56


Aliases 8/8

Aliases can be automatically addedBusinessObjects can suggest possible aliases for youBe careful!!Just because an alias CAN be added doesnt mean it SHOULD


35/56


Contexts 1/7

An alternative method to resolve loops is by creatingContextsA context represents one path or set of joins betweentables

EmployeeTrips

CountriesEmployees

Trips

Context #1: Trips

Context #2: Nationality


36/56


Contexts 2/7

A context contains each join in the path

Trips.country_id = Countries.country_id

Employee_Trips.trip_id = Trips.trip_id

Employees.emp_id = Employee_Trips.emp_id

Context #1: Trips

Employees.country_id = Countries.country_id

Context #2: Nationality

C 3/


37/56


Contexts 3/7

Contexts are associativeLonger join paths will resolve loops caused by smaller paths

CD

BC

EF

DE

AB

Context #1

EF

DE

AD

Context #2

B C

A D

E

F

Context #1

Context #2

C 4/7


38/56


Contexts 4/7

A contexts acts like a roadmap of available pathOnce chosen, other joins not in the context disappearAdd as many additional joins to the context as neededAlways add new joins to at least one context

CD

BC

EF

DE

AB

Context #1

EF

DE

AD

Context #2B C

D

E

F

A

Z

C t t 5/7


39/56


Contexts 5/7

Contexts resolve the loop when the query is run ratherthan in the Designer

This means that a context-based solution still has loops!

The user may be asked to choose between the contextsBusinessObjects will try to infer which context to useIf it cant figure it out, the user usually chooses a context

Once a context is chosen, all other joins disappearOnly joins listed in the context will be used to build the final SQLprogram

Using contexts does not force additional objects

C t t 6/7


40/56


Contexts 6/7

Contexts can be manually addedThe designer can add/subtract joins according to business rules

Contexts 7/7


41/56


Contexts 7/7

Contexts can be detectedBe careful!! Not all suggested contexts make business sense

Alternative Loop Resolutions 1/5


42/56



Shortcut joins can be used to resolve loopsBe careful!!This technique may cause more problems than solutionsRemember the alternative definition of loops



43/56



Problem #1A shortcut join dissolves the normal join relationshipFor the following query, only guests that have past and future activitywill be returned



44/56



Problem #1 ResultsFor the following query, only guests that have past and future activitywill be returned



45/56



Problem #2In this scenario, no known path exists to ServiceA query cannot be written without a Cartesian Product



46/56



Problem #2 ResultsThe join from Service to any other table is missing!

No join to Service

Fan Traps 1/9


47/56


Fan Traps 1/9

Fan traps hide in Master-Detail-Detail relationshipsTrouble occurs when aggregating on a Master column

23104

2310323102

inv_id

810

5

max_ guests

Sales

221211

service_id

2310223102

inv_id

34

nb_ guests

Invoice_Line

Fan Traps 2/9


48/56


Fan Traps 2/9

Create query for Actual vs Budgeted Guests forInvoice 23102

Invoice: Sales.inv_idBudgeted Guests: sum(Sales.max_guests)Actual Guests: sum(Invoice_line.nb_guests)

Fan Traps 3/9


49/56


p

What happened?Two detail lines for Invoice 23102Budgeted Guests is overstated by a factor

(10 guests budgeted) * (2 detail lines) = 20 guests

20

10

10

BudgetedGuests

7

4

3

ActualGuests

Totals:

23102

23102

Invoice

Result Set(Budgeted Guests) *(Number of detail lines)

Fan Traps 4/9


50/56


p

Solution #1Create a separate SQL statement per aggregationAggregations on the same table require only 1 SQL statement

File / Parametersfrom mainmenu

Fan Traps 5/9


51/56


p

Multiple SQL statements return the correct answer

Fan Traps 6/9


52/56


p

What if the Detail table isnt aggregatedPerhaps a user wants the services associated with a sale

23104

23103

23102

inv_id

8

10

5

max_ guests

Sales

221

211

service_id

23102

23102

inv_id

3

4

nb_ guests

Invoice_Line

221

211

inv_id

Restaurant

Hotel Room

serviceService

Fan Traps 7/9


53/56


Create query for Budgeted Guests by Service forInvoice 23102

Invoice: Sales.inv_idBudgeted Guests: sum(Sales.max_guests)Service: Service.service

Fan Traps 8/9


54/56


Is there a problem?Guests are budgeted at the invoice level, not service levelSumming the Budgeted Guests would be a mistake

Not good

Fan Traps 9/9


55/56


Possible solutionsBudget guests at a lower level of detailPrevent this query from occurring (drastic)

Create a smarter report that realizes that budgeted guests canonly be represented at the invoice level

More advanced solutions are possibleAggregate-aware techniquesAlias/Context solutions (brittle)Data warehouse / data mart

Questions & Answers


56/56


Alan [email protected](214) 637-6622

Penny [email protected](214) 637-6622

very good loops resolving

Documents