How To Resolve Loops

More Tricks of the Universe Masters David G. Rathbun, Integra Solutions

Topics Introduction Tricks of the Masters Wrap Up Q&A ƒ Please hold questions until the end of the presentation

Slide 3

Introduction A few words about me… Focused on BusinessObjects Solutions since 1995 ƒ Consulting / mentoring / trouble shooting ƒ Certified BusinessObjects trainer ƒ Primary focus on knowledge transfer and client education

Selected to present at 1996 - 2005 User Conference ƒ 10 consecutive years as a presenter

Charter member of BOB ƒ http://busobj.forumtopics.com

Slide 5

1/3

Introduction

2/3

A few words about Designer… Designer has changed very little since 4.0 ƒ Initially released in 1996 ƒ Introduced contexts, aggregate awareness, multiple SQL statements ƒ Core technology has been stable for many years

Most important component of your implementation ƒ A bad universe = a bad implementation ƒ Think ZEN • Zero Defect Coding • Efficient SQL Generation • Narrow Project Scope

ƒ Know the rules and when to break them

Slide 6

Introduction

3/3

What has changed over the years? Designer Evolution

Version

Feature

6.0

Find and replace Integrity check does not check cardinalities by default List of aliases available

6.1

Parameter settings as part of the universe Save as PDF

6.5

ANSI 92 join syntax now available Derived tables Index awareness

Slide 7

Topics Introduction Tricks of the Masters Wrap Up Q&A

Slide 8

Tricks of the Masters Intelligent SQL generation Effective use of derived tables Working with hierarchical data

Demonstrations today will use Designer 6.5 although many techniques are valid in 5.x Slide 9

Intelligent SQL Generation The pitfalls of the all-in-one solution ƒ Join loops ƒ Fan traps ƒ Chasm traps

Not generally present in a “one-of” solution Impact on success ƒ Cause incorrect SQL to be generated

Techniques demonstrated in this section are essential to the success of your project

Slide 10

Join Loops Loops are often present in logical data diagrams

Slide 11

1/4

Join Loops Why are loops bad? SQL Generator uses all available paths SELECT Employees.EMP_LASTNAME, Salary.SALARY_VALUE FROM Salary, Employees, Absenses, Lookups WHERE ( Absenses.EMP_ID=Employees.EMP_ID ) AND ( Salary.EMP_ID=Employees.EMP_ID ) AND ( Lookups.LOOKUP_ID=Salary.LOOKUP_ID ) AND ( Lookups.LOOKUP_ID=Absenses.LOOKUP_ID )

Slide 12

2/4

Join Loops

3/4

Join loops may be addressed using several techniques ƒ ƒ ƒ ƒ

Aliases Contexts Shortcuts Multiple universes

What is the “best” solution for join loops?

Slide 13

Join Loops There is no “best” solution! Each loop must be examined independently Make sure you evaluate all of your options

Slide 14

4/4

When Do I Use an Alias? More than one logical purpose for the same table Require different objects ƒ Types of salary ƒ Types of absences

Slide 15

1/3

When Do I Use an Alias? Always set cardinality on every join ƒ One – Many ƒ One – One

A table on the one side is a candidate for an alias

Note: You should never have a Many – Many relationship in a production database! Slide 16

2/3

When Do I Use an Alias? Fixed the loop!

 Demonstration Slide 17

3/3

When Do I Use a Context?

1/3

More than one logical path between two or more tables

Slide 18

When Do I Use a Context? A table on the many side helps identify a context

Slide 19

2/3

When Do I Use a Context? Each context identifies a path through the structure

Slide 20

3/3

Alias versus Context There is no “better” answer ƒ Do you have paths or purposes?

Aliases ƒ Require additional objects for each purpose ƒ Generally used on tables on the outside of the universe structure ƒ Eliminate the loop

Contexts ƒ Do not require additional objects ƒ Do not actually eliminate the loop ƒ Require ongoing maintenance

Are loops the only issue?

Slide 21

Chasm Trap Definition A chasm spans a many – one – many relationship Each showroom has many employees Each showroom has many annual salary figures The number of employee records should not impact the total salary on the report

Slide 22

Chasm Trap Example

1/3

SELECT Showrooms.SHOWROOM_NAME, sum(Salary_Summary_Annual.ANNUAL_SAL_TOTAL) FROM Salary_Summary_Annual, Showrooms WHERE ( Salary_Summary_Annual.SHOWROOM_ID = Showrooms.SHOWROOM_ID ) GROUP BY Showrooms.SHOWROOM_NAME Showroom Name Prestige Cars

$316,720.00

Prestige Motors

$565,730.00

Prestige Sports Cars

$461,380.00

Sum:

Slide 23

Annual Salaray

$1,343,830.00

Chasm Trap Example

2/3

SELECT Showrooms.SHOWROOM_NAME, Employees.EMP_LASTNAME, sum(Salary_Summary_Annual.ANNUAL_SAL_TOTAL) FROM Employees, Salary_Summary_Annual, Showrooms WHERE ( Employees.SHOWROOM_ID=Showrooms.SHOWROOM_ID ) AND ( Salary_Summary_Annual.SHOWROOM_ID=Showrooms.SHOWROOM_I D ) GROUP BY Showrooms.SHOWROOM_NAME, Employees.EMP_LASTNAME

Slide 24

Chasm Trap Example

3/3

A chasm trap inflates the number of rows ƒ X rows on the left ƒ Y rows on the right ƒ X * Y rows in the combined set

Can you prevent a query writer from using both objects? ƒ No

Can you still get the correct answer? ƒ Yes

Slide 25

Context Chasm Trap Solution

1/2

Set up contexts for each side of the chasm BusinessObjects automatically generates separate SQL

Slide 26

Context Chasm Trap Solution You can prevent users from spanning a chasm

Slide 27

2/2

Intelligent SQL Generation Review The pitfalls of the all-in-one solution ƒ Join loops ƒ Fan traps (not discussed today) ƒ Chasm traps

Not generally present in a “one-of” solution Impact on success ƒ Cause incorrect SQL to be generated

Designer gives us the tools to handle these issues

Slide 28

Tricks of the Masters Intelligent SQL generation Effective use of derived tables Working with hierarchical data

Demonstrations today will use Designer 6.5 although many techniques are valid in 5.x Slide 29

Effective Use of Derived Tables Another option for aliases Replace database views Denormalize data Create summary data Eliminate intermediate joins … all this and more

Slide 30

Creating A Derived Table Insert + Derived Tables…

Enter desired SQL code Select Models.MODEL_ID, Models.MODEL_NAME, Models.MODEL_PRICE, Models.MODEL_COST, Makers.MAKER_NAME, Styles.STYLE_NAME FROM Models, Makers, Styles WHERE Models.STYLE_ID = Styles.STYLE_ID AND Models.MAKER_ID = Makers.MAKER_ID Slide 31

1/2

Creating A Derived Table This example shows denormalized data Could be done with a view Are there better uses for this technique?

Slide 32

2/2

Summary Data Database views are static Derived tables can include prompts SELECT Departments.DEPT_ID, Departments.DEPT_NAME, Lookups.LOOKUP_TEXT AS Salary_type, sum(Salary.SALARY_VALUE) as Total_salary FROM Departments, Employees, Salary, Lookups WHERE Departments.DEPT_ID=Employees.DEPT_ID AND Employees.EMP_ID=Salary.EMP_ID AND Salary.LOOKUP_ID=Lookups.LOOKUP_ID AND Lookups.LOOKUP_TYPE='SAL' GROUP BY Departments.DEPT_ID, Departments.DEPT_NAME, Lookups.LOOKUP_TEXT HAVING sum(Salary.SALARY_VALUE) >= @Prompt('Enter Department Salary Threshhold','N',,,)  Demonstration Slide 33

Tricks of the Masters Intelligent SQL generation Effective use of derived tables Working with hierarchical data

Demonstrations today will use Designer 6.5 although many techniques are valid in 5.x Slide 34

Hierarchical Data

1/2

Hierarchical data is not uncommon ƒ Company organizational chart ƒ Manufacturing BOM (Bill of Materials) ƒ Referral tracking

Hierarchical data is recursive ƒ A table joins to itself ƒ Depth is unlimited ƒ Depth is unknown

SQL is not recursive How can we solve this?

Slide 35

Employee ID

Last Name

Manager ID

101

Noakes

102

Ferrerez

101

103

Field

102

104

Fraser

101

105

Snow

101

106

Speed

105

Hierarchical Data Some typical questions Who do I work for? Who are my direct reports? ƒ Who reports directly to me in the organization

Who are my indirect reports? ƒ Who works for someone that works for me

What is my level in the hierarchy? ƒ Show my parents in the tree ƒ Show my children in the tree

Slide 36

2/2

Alias Solution

1/2

One solution Use aliases to represent the hierarchical relationships

 Demonstration Slide 37

Alias Solution

2/2

The problems Unknown depth ƒ Each new level requires an alias ƒ Adding new employees should not require universe maintenance

Varying depth ƒ ƒ ƒ ƒ ƒ

Slide 38

An organization tree is not always balanced Different employees have different depths Outer joins are used to allow for missing data Outer joins can cause performance issues Not all databases support outer join chains (sequences of outer joins)

Creating New Tables Another solution Create a new table to flatten out the structure ƒ Employee ID ƒ Manager ID ƒ Top Level ID

Each person exists as a top level ID ƒ Who works for me?

Each person exists as an Employee ID ƒ Who do I work for? ƒ What is my level?

Slide 39

1/2

Creating New Tables The problems This table has to be created / populated / updated Stores each potential manager as a top level ƒ Inflates the amount of data

Have to change objects if I change questions ƒ Who works for me? ƒ Who do I work for? ƒ What is my level in the hierarchy?

Slide 40

2/2

Tree Traversal Data

1/5

Store the tree traversal path in the original table Assign each person (node) a left and right value Value is determined by tree traversal order 1 2 Ferrerez 3

4 Fraser 5

16

Noakes 6 Snow 7

8 Pickworth 15

Forest Note: This is not the complete tree

9

Porter 10 11

12

Duckworth 13 14

Tree traversal means walk around the outside edge ƒ Number each node each time you pass the left or right side ƒ Store those values in the original source table

Slide 41

Tree Traversal Data

2/5

Using left and right data to build the hierarchy ID

Full Name

Left Tree Right Tree

101 Noakes, Nicholas

1

54

102 Ferrerez, Ferdinand

2

5

103 Field, Felicity

3

4

104 Fraser, Frank

6

7

105 Snow, Sara

8

13

106 Speed, Sonya

9

10

107 Spencer, Steve

11

12

108 Helen, Harrison

14

15

109 Thomas, Tom

16

19

110 Thatcher, Terry

17

18

111 Davis, Diana

20

21

201 Pickworth, Paul

22

39

202 Forest, Florence

23

28

203 Brown, Bella

24

25

204 Porter, Pete

29

34

205 Irving, Ira

30

31

206 Bailey, Ben

32

33

207 Duckworth, Dave

35

38

208 Ince, Ian

36

37

209 Hilary, Hibbs

26

27

 Demonstration Slide 42

Name

Descendants Parents Level

Noakes, Nicholas

26

0

1

1

1

2

0

2

3

Fraser, Frank

0

1

2

Snow, Sara

2

1

2

Speed, Sonya

0

2

3

Spencer, Steve

0

2

3

Helen, Harrison

0

1

2

Thomas, Tom

1

1

2

0

2

3

Davis, Diana

0

1

2

Pickworth, Paul

8

1

2

2

2

3

Brown, Bella

0

3

4

Hilary, Hibbs

0

3

4

2

2

3

Irving, Ira

0

3

4

Bailey, Ben

0

3

4

1

2

3

0

3

4

Ferrerez, Ferdinand Field, Felicity

Thatcher, Terry

Forest, Florence

Porter, Pete

Duckworth, Dave Ince, Ian

Tree Traversal Data

3/5

Inserts and updates must adjust left and right values 1 2 Ferrerez 3

Fraser 5

18

Noakes 6 Snow 7

8 Pickworth 17

Forest 9 10

New

Porter 12 13

14

Duckworth 15 16

11

Update table set left = left + 2 where left >= 10; Update table set right = right + 2 where right >= 10; Insert (name, left, right) values (‘New’, 10, 11);

Slide 43

Tree Traversal Data

4/5

Revisiting derived tables Including the following derived table in our universe… SELECT A.EMP_ID, (A.RIGHT_TREE-A.LEFT_TREE-1)/2 AS Number_of_descendants, COUNT(B.EMP_ID) AS Employee_Level, COUNT(B.EMP_ID)-1 AS Number_of_parents FROM Employees A, Employees B WHERE B.LEFT_TREE <= A.LEFT_TREE AND B.RIGHT_TREE >= A.RIGHT_TREE GROUP BY A.EMP_ID, (A.RIGHT_TREE-A.LEFT_TREE-1)/2

… enables us to answer the required questions ƒ ƒ ƒ ƒ

Slide 44

Who do I work for? Who are my direct reports? Who are my indirect reports? What is my level in the hierarchy?

Tree Traversal Data Who do I work for? ƒ Left < My Left and Right > My Right and Level = My Level - 1

Who are my direct descendants? ƒ Left > My Left and Right < My Right and Level = My Level + 1

Who are my indirect descendants? ƒ Left > My Left and Right < My Right and Level > My Level + 1

Who are all of my descendants? ƒ Left > My Left and Right < My Right

What is my level in the hierarchy? ƒ Provided directly by the derived table

Create a complete tree ƒ Display all records order by LEFT indent by LEVEL

Slide 45

5/5

Hierarchical Data Recap There are various solutions for handling hierarchies ƒ Aliases ƒ Extra tables ƒ Tree traversal

Tree traversal data seems to provide the best solution ƒ Sample data included one hierarchy only ƒ If multiple hierarchies are present add the TREE_ID to each node ƒ Assign TREE_ID during the initial tree traversal process

Database views may be substituted for derived tables ƒ Reminder: Derived tables were added in Designer 6.5

Slide 46

Tricks of the Masters Intelligent SQL generation ƒ Resolve loops ƒ Resolve chasm traps ƒ Resolve fan traps

Effective use of derived tables ƒ Denormalize data ƒ Summarize data ƒ Include prompts

Working with hierarchical data ƒ Number each node according to its position in the tree path ƒ Use that information to answer typical hierarchical questions

Demonstrations today will use Designer 6.5 although many techniques are valid in 5.x Slide 47

Q&A Questions? Contact information ƒ David G. Rathbun ƒ Integra Solutions ƒ www.IntegraSolutions.net

Slide 48