Boid20 en Col96 Fv Part Ltr

(1)

BOID20

Advanced Use of the Information

Design Tool

SAP BusinessObjects - Business Intelligence

Date Training Center Instructors Education Website

Participant Handbook

Course Version: 96

Course Duration: 2 Day(s) Material Number: 50106141

(2)

Copyright

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.

Trademarks

• Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks of Microsoft Corporation.

• IBM®, DB2®, OS/2®, DB2/6000®, Parallel Sysplex®, MVS/ESA®, RS/6000®, AIX®, S/390®, AS/400®, OS/390®, and OS/400® are registered trademarks of IBM Corporation.

• ORACLE® is a registered trademark of ORACLE Corporation.

• INFORMIX®-OnLine for SAP and INFORMIX® Dynamic ServerTM are registered trademarks of Informix Software Incorporated.

• UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of the Open Group. • Citrix®, the Citrix logo, ICA®, Program Neighborhood®, MetaFrame®, WinFrame®,

VideoFrame®, MultiWin® and other Citrix product names referenced herein are trademarks of Citrix Systems, Inc.

• HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.

• JAVA® is a registered trademark of Sun Microsystems, Inc.

• JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.

• SAP, SAP Logo, R/2, RIVA, R/3, SAP ArchiveLink, SAP Business Workflow, WebFlow, SAP EarlyWatch, BAPI, SAPPHIRE, Management Cockpit, mySAP.com Logo and mySAP.com are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other products mentioned are trademarks or registered trademarks of their respective companies.

Disclaimer

THESE MATERIALS ARE PROVIDED BY SAP ON AN "AS IS" BASIS, AND SAP EXPRESSLY DISCLAIMS ANY AND ALL WARRANTIES, EXPRESS OR APPLIED, INCLUDING WITHOUT LIMITATION WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THESE MATERIALS AND THE SERVICE, INFORMATION, TEXT, GRAPHICS, LINKS, OR ANY OTHER MATERIALS AND PRODUCTS CONTAINED HEREIN. IN NO EVENT SHALL SAP BE LIABLE FOR ANY DIRECT, INDIRECT, SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR PUNITIVE DAMAGES OF ANY KIND WHATSOEVER, INCLUDING WITHOUT LIMITATION LOST REVENUES OR LOST PROFITS, WHICH MAY RESULT FROM THE USE OF THESE MATERIALS OR INCLUDED SOFTWARE COMPONENTS.

(3)

About This Handbook

This handbook is intended to complement the instructor-led presentation of this course, and serve as a source of reference. It is not suitable for self-study.

Typographic Conventions

American English is the standard used in this handbook. The following typographic conventions are also used.

Type Style Description

Example text Words or characters that appear on the screen. These include field names, screen titles, pushbuttons as well as menu names, paths, and options.

Also used for cross-references to other documentation both internal and external.

Example text Emphasized words or phrases in body text, titles of graphics, and tables

EXAMPLE TEXT Names of elements in the system. These include report names, program names, transaction codes, table names, and individual key words of a programming language, when surrounded by body text, for example SELECT and INCLUDE.

Example text Screen output. This includes file and directory names and their paths, messages, names of variables and parameters, and passages of the source text of a program.

Example text Exact user entry. These are words and characters that you enter in the system exactly as they appear in the documentation.

<Example text> Variable user entry. Pointed brackets indicate that you replace these words and characters with appropriate entries.

(4)

About This Handbook BOID20

Icons in Body Text

The following icons are used in this handbook.

Icon Meaning

For more information, tips, or background

Note or further explanation of previous point Exception or caution

Procedures

Indicates that the item is displayed in the instructor's presentation.

(5)

Course Overview

The Information Design Tool enables designers to extract, define, and manipulate meta data from relational and OLAP sources to create and deploy SAP BusinessObjects universes. In this course you learn the more advanced features of working with the Information Design Tool, including resolving SQL Traps, using the @AggregateAware function for resolving data foundation issues, creating universes from different sources, deploying universes in multiple languages, and managing and optimizing universes.

Target Audience

This course is intended for the following audiences: • Data Managers

• Project managers

• BI Platform / SAP NetWeaver BW System Administrators • SAP Netweaver BW / SAP BusinessObjects Consultants

Course Prerequisites

Required Knowledge

• Working knowledge of SQL and relational, also OLAP database management systems concepts and structures

• Basic knowledge of in reporting with SAP BusinessObjects Web Intelligence • Completion of the BOID10 course

Recommended Knowledge

• Completion of the BOW310 course or a working knowledge of Web Intelligence application

Course Goals

This course will prepare you to:

• Work with advanced functionality of the Information Design Tool, including resolving SQL Traps, using the @AggregateAware function, creating Universes from different sources, deploying a universe in multiple languages, and managing and optimizing the universe.

(8)

Course Overview BOID20

Course Objectives

After completing this course, you will be able to: • Resolve SQL Traps

• Learn advanced use of the @AggregateAware function

• Create a universe from and OLAP source and from multiple relational sources • Deploy a universe in different languages

(9)

Unit 1

SQL Traps

Unit Overview

When working with relational data sources at the SQL level it is common to encounter SQL traps: chasm and fan. The Information Design Tool provides ways of resolving these traps..

After completing this lesson, you will be able to: • Understand SQL traps and universes • Resolve fan traps

• Resolve chasm traps

Unit Objectives

After completing this unit, you will be able to:

• Explain how SQL traps can cause queries to return inaccurate results • Define a chasm trap

• Detect chasm traps in a universe structure • Resolve chasm traps

• Define a fan trap

• Detect fan traps in the universe structure • Resolve fan traps

Unit Contents

Lesson: Understanding SQL Traps and Universes... 2

Lesson: Detecting and Resolving Chasm Traps ... 4

Exercise 1: Resolve Chasm Traps ... 9

Lesson: Detecting and Resolving Fan Traps ... 20

(10)

Unit 1: SQL Traps BOID20

Lesson: Understanding SQL Traps and Universes

Lesson Overview

SQL traps are caused by the way in which the parts of a select statement are combined when referencing a relational database.

Lesson Objectives

After completing this lesson, you will be able to:

• Explain how SQL traps can cause queries to return inaccurate results

Business Example

About SQL traps

Chasm traps and fan traps are problems inherent in SQL that are caused by the order in which the elements of the SELECT statement are processed.

In SQL, a SELECT statement processes the SELECT, FROM, and WHERE clauses first (with the exception of any aggregates). In doing so, it creates a product of all the tables in the FROM clause on the basis of the joins and restrictions specified in the WHERE clause. This can be thought of as a virtual table. Normally this does not cause a problem, but if an aggregate is applied then it may, in particular circumstances, result in wrong output being generated. This is particularly worrying because SQL does not produce an error message, it just projects the results.

Unlike loops that return fewer rows than expected, chasm traps and fan traps return too many rows.

Fortunately, there are ways of identifying situations in which chasm traps and fan traps can occur, and there are methods of resolving these situations.

(11)

BOID20 Lesson: Understanding SQL Traps and Universes

Lesson Summary

You should now be able to:

(12)

Lesson: Detecting and Resolving Chasm Traps

Lesson Overview

This lesson explains how to detect and resolve chasm traps in the universe structure.

Lesson Objectives

After completing this lesson, you will be able to: • Define a chasm trap

Business Example

Your company will be reporting against a relational database and you need to identify and resolve any chasm traps that may be present.

Chasm traps

A chasm trap is a type of join path between three tables when two many–to–one joins converge on a single table, and there is no context in place that separates the converging join paths.

You only get incorrect results when the following circumstances all exist simultaneously:

1. There is a “many-to-one-to-many” relationship between three tables in the universe structure.

2. The query includes objects based on the two “many” tables.

3. There are multiple rows returned for an object (usually a dimension) based on the “one” table.

For example, in this diagram there is no loop, but the flow around the three tables is many–to–one–to–many.

(13)

BOID20 Lesson: Detecting and Resolving Chasm Traps

Figure 1: Many-to-one-to-many join flow

Note: A chasm trap is not dependent on the object types. The query could

be made up of only dimensions, only details, or only measures, or any combination of the three types with the “many” tables for a chasm to occur. When a query that uses objects Y and Z is run, the inferred SQL includes tables B, C, and A that have a “many-to-one-to-many” relationship respectively. The chasm trap causes a query to return every possible combination of rows for one measure with every possible combination of rows for the other measure. This results in the values for each object being multiplied by the other. The effect is similar to a Cartesian product but is known as a chasm trap.

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

(14)

Detecting chasm traps

Unlike loops, chasm traps are not detected automatically by the Information Design Tool. However, you can detect them in one of the following ways:

• Analyze the one-to-many (1-N) join paths in your schema to detect chasm traps graphically.

• Select Tools→ Detect Contexts or click the Detect Contexts button to automatically detect and propose candidate contexts in your schema.

Detect Contexts examines the many-to-one (N–1) joins in the schema and

proposes contexts to separate the queries run on the table. This is the most effective way to ensure that your schema does not have a chasm trap.

• Add additional dimension or detail objects to display more information in the report. If there is a chasm trap, aggregated values are multiplied, alerting you to the problem.

You can use Detect Contexts to detect and propose candidate contexts, and then examine the table where any two contexts diverge. The point where two contexts intersect is the source of a chasm trap.

Any two tables that have multiple rows converging to a single row in the table with the “one” relationship may potentially cause a chasm trap.

The chasm trap scenario

In the classroom demonstration, the query returns every possible combination of sale rows with every possible combination of rental rows. As a result the aggregates have been multiplied by the number of related rows on the alternative "many" table. The query returns every possible combination of sale rows with every possible combination of rental rows. Hence, the sale transactions each appear twice as do the rental transactions, and as a result of this the aggregates have been multiplied by the number of related rows on the alternative "many" table.

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.

The problem with chasm traps is that, unless you look at the detail rows, there is nothing to alert you to the situation.

(15)

Resolving chasm traps

To resolve a chasm trap, you need to make two separate queries and then combine the results. Depending on the type of objects defined for the fact tables and the type of end-user environment, you can use the following methods to resolve a chasm trap: • Ensure that the Query Options in the Business Layer generate separate SQL

statements for each measure.

This method is not recommended as it only works with measures and results in certain inefficiencies in processing. It does not generate separate queries for dimension or detail objects.

• Create a context for each fact table.

This solution works in all cases and does not result in inefficiencies.

Using multiple SQL statements for each measure to

resolve chasm traps

If you have only measure objects defined for both fact tables, then you can use the Query Option setting of the Business Layer Multiple SQL statements for each

measure. This forces the generation of separate SQL queries for each measure that is

used in the query.

With the option Multiple SQL statements for each measure selected, Universe Designer now makes separate SQL SELECT statements for each measure object in the query.

The results in the report are now correct, as the query has automatically generated two SQL statements.

Using this option resolves the chasm trap problem. However, there are drawbacks to using this method to resolve chasm traps.

Drawbacks to the multiple SQL statements for each

measure method

The results can be confusing

The Query Option specifies: “Multiple SQL statements for each measure” . One of the drawbacks is that it does not run separate SELECT statements if the query contains only dimension objects.

The resulting report contains a single block with the results displayed as a Cartesian product.

(16)

Figure 2: Cartesian Product-like results

It is not that there is anything inaccurate about the dates, but the multiple occurrences are confusing to users.

The query is inefficient

Another drawback is that any query including multiple measures infers a separate SELECT statement for each measure, whether it is required or not.

To find a complete solution to chasm traps, you must use contexts.

Using contexts to resolve chasm traps

You can define a context for each table at the many end of the joins. In our classroom example you could define a context from Client to Sale and from Client to Rental. When you run a query that includes objects from both contexts, this creates two SELECT statements that are synchronized at run–time in SAP BusinessObjects end–user query tools to prevent the creation of a Cartesian product.

Creating contexts always solves a chasm trap in a universe. When you have a “many–to–one–to–many” situation, always use a context.

(17)

Exercise 1: Resolve Chasm Traps

Exercise Objectives

After completing this exercise, you will be able to:

• Detect contexts to resolve a chasm trap in the universe structure.

Business Example

You need to report on client sales and rentals. Due to the structure of the database this will create a chasm trap, which will need to be resolved in order to return accurate results.

Task:

1. Create a new Project and Data Foundation called Chasm_xx, where “xx”” stands for your user number. Use the Motors connection.

Field Value

User Name Train-xx

Password Assigned password

Authentication Enterprise

2. Add the following tables to the Data Foundation: CLIENT

SALE

RENTAL (as an alias of the SALE table)

3. Create the following joins and set the cardinalities:

Join Cardinality

CLIENT.CLIENT_ID=SALE.CLIENT_ID 1,n CLIENT.CLIENT_ID=RENTAL.CLIENT_ID 1,n

SALE.SALE_TYPE=’S’ 1,1

RENTAL.SALE_TYPE=’R’ 1,1

4. Create a Business Layer called Chasm_xx

5. Deselect the “Multiple SQL statements for each measure” option.

(18)

6. Create two classes: - Chasm Objects - Measures

7. Add the following objects with the following syntax:

Chasm Objects class

Object Name Syntax

Client Name CLIENT.CLIENT_LASTNAME +’,

‘+

CLIENT.CLIENT_FIRSTNAME

Sale Date SALE.SALE_DATE

Rental Date RENTAL.SALE_DATE

Measures class

Sales Revenue SUM(SALE.SALE_TOTAL)

Rental Revenue SUM(RENTAL.SALE_TOTAL)

Hint: Use the SQL Assistant button to build the syntax.

8. Perform an Integrity Check on: - Tables

- Joins

- Business Layer 9. Save all changes.

10. Create a Query in the Business Layer to test the universe. Restrict the query results to Paul Brent only.

11. In the same query on the Business Layer, add Sales Revenue, so that both

Rental Revenue and Sales Revenue are part of the query, along with Client Name. Refresh the query and note the results.

12. In the Information Design Tool on the Data Foundation add the two contexts to resolve the Chasm Trap:

- Sales

(19)

- Rental

13. Save the changes to the Data Foundation. 14. Recreate the last query from Step 11. 15. What is the Sales Revenue?

(20)

Solution 1: Resolve Chasm Traps

Task:

1. Create a new Project and Data Foundation called Chasm_xx, where “xx”” stands for your user number. Use the Motors connection.

Field Value

User Name Train-xx

Password Assigned password

Authentication Enterprise

a) Click Start → Programs → SAP BusinessObjects → SAP BusinessObjects BI platform 4.0 → SAP BusinessObjects BI platform Client Tools → Information Design Tool.

b) Click File → New→ Project Project Name: Chasm_xx c) Click Finish

d) Add a Repository session. Under the Repository Resources pane, click + and select Insert Session.

e) Log into the system using the information provided. f) Browse to the connection folder

g) Find the existing connection “Motors”.

h) Right-click the Motors connection and select Create Relational

Connection Shortcut

i) Select Chasm_xx from the pop-up box j) Click OK.

k) Click Close to proceed

l) “Motors.cns” will appear under the local project m) Click New → Data Foundation.

n) Resource Name: Chasm_xx

o) Select Next → Single Source → Next → Select Motors shortcut →

Finish

(21)

SALE

RENTAL (as an alias of the SALE table) a) Select the Data Foundation tab.

b) Click on the “+” and click on Insert Tables.

Hint: Follow the instructor’s guide if you encounter any problems

during this step. c) Browse the tables under dbo.

d) Double-click the CLIENT and SALE tables under connections e) Right-click the Sales table that is in the Data Foundation f) Insert → Alias Table…

g) Double-click “Alias_of_Sales” and change the proposed name to RENTAL h) Click OK

CLIENT.CLIENT_ID=SALE.CLIENT_ID 1,n CLIENT.CLIENT_ID=RENTAL.CLIENT_ID 1,n

RENTAL.SALE_TYPE=’R’ 1,1

a) Drag the join between the columns of the tables b) Double-click the join to display the properties c) Click the cardinality drop-down to set the cardinality

For the self-joins: d) Insert → Insert Join

e) Set the Expression and cardinality f) Click Validate

g) Click Save to save the Data Foundation

(22)

4. Create a Business Layer called Chasm_xx a) Right-click the project

b) Select New → Business Layer

c) Select Relational Data Source → Next d) Name the Business Layer Chasm_xx → Next e) Select “…”to select the Data Foundation f) Select Chasm_xx.dfx and click OK

g) Deselect “Automatically create classes and objects” option h) Click Finish

5. Deselect the “Multiple SQL statements for each measure” option. a) Select the top level of the Business Layer

b) Select the Query Options tab

c) Deselect the “Multiple SQL statements for each measure” option 6. Create two classes:

- Chasm Objects - Measures

a) Select the top level of the Business Layer b) Click the Insert Object drop down c) Select Folder

d) Change name to Chasm Objects

e) Select the top level of the Business Layer f) Click the Insert Object drop down g) Select Folder

h) Change name to Measures

(23)

Chasm Objects class

‘+

Sale Date SALE.SALE_DATE

Rental Date RENTAL.SALE_DATE

Measures class

Rental Revenue SUM(RENTAL.SALE_TOTAL)

Hint: Use the SQL Assistant button to build the syntax.

a) Select the class/folder: Chasm Objects b) Select the Insert Object drop down c) Select Dimension or Measure d) Change the Name:

Dimension

Client Name Sale Date Rental Date

Note: For Sale Date and Rental Date you have to set Data Type

to Date Time and

Measure

Sales Revenue

(24)

Rental Revenue e) Click SQL Assistant f) Enter the select:

Dimension CLIENT.CLIENT_LASTNAME + ‘, ‘ + CLIENT.CLIENT_FIRSTNAME SALE.SALE_DATE RENTAL.SALE_DATE Measure SUM(SALE.SALE_TOTAL) SUM(RENTAL.SALE_TOTAL) g) Click Validate h) Click OK

- Joins

- Business Layer

a) Right-click top level of Business Layer b) Select Check Integrity

c) Select: • Tables • Joins

• Business Layer d) Click Check Integrity e) Click OK

9. Save all changes.

a) Select File → Save All

(25)

10. Create a Query in the Business Layer to test the universe. Restrict the query results to Paul Brent only.

a) From the Business Layer select “Queries” b) Click Insert Query

c) Create a query with the Client Name and Sales Revenue objects. d) Apply a query filter on Client Name for Brent, Paul by dragging Client

Name to the Query Filter area and completing the query filter: Client Name In list Brent, Paul

e) Click Refresh and note the results: Sales Revenue = 315,964.50 f) Remove Sales Revenue from the Result Objects area

g) Add Rental Revenue and refresh the query. Note the results: Rental Revenue = 1,100

11. In the same query on the Business Layer, add Sales Revenue, so that both

Rental Revenue and Sales Revenue are part of the query, along with Client Name. Refresh the query and note the results.

a) Add the Sales Revenue object back to the Result Objects area b) Click Refresh

c) Note that both revenue amounts have doubled.

12. In the Information Design Tool on the Data Foundation add the two contexts to resolve the Chasm Trap:

- Sales - Rental

a) Under Aliases and Contexts click Detect Contexts… b) Select both contexts

c) Click OK

13. Save the changes to the Data Foundation. a) Click Save.

(26)

14. Recreate the last query from Step 11. a) From the Business Layer select Query

b) Select the Client Name, Sales Revenue and Rental Revenue objects. c) Drag the Client Name object down into the Query Filter area

d) Complete the Query Filter:

Client Name In list Brent, Paul

e) Click Refresh

15. What is the Sales Revenue? What is the Rental Revenue? a) Sales Revenue = 315,964.50 b) Rental Revenue = 1,100.00

(27)

Lesson Summary

You should now be able to: • Define a chasm trap

(28)

Lesson: Detecting and Resolving Fan Traps

Lesson Overview

This lesson explains how fan traps can occur in the universe structure and how to resolve them.

Lesson Objectives

After completing this lesson, you will be able to: • Define a fan trap

Business Example

You need to report on Sales order summary data and Sales order line details in the same report. To make the query more efficient, the order summary data is stored in a separate table from the order line data. As a result you will create a fan trap which will need to be resolved.

Fan traps

Fan traps occur when there is a “one–to–many” join to a table that “fans out” into another “one–to–many” join to another table.

(29)

BOID20 Lesson: Detecting and Resolving Fan Traps

Figure 3: One-to-many fanning to another one-to-many join flow

This is a common structure and does not normally result in a fan trap. You only get incorrect results from the fan trap when the query includes a measure object on the middle table ('B') of the table path and an object (of any kind) from the subsequent table ('C'). The trap only occurs where (due to the database design) a column in table B holds data values which are already a sum of those values held at table C. The results are normally noticeably wrong.

When a query is run using objects Y and Z, the inferred SQL includes tables B and C which have a ‘one-to-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 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 alternate solution is to avoid it in the first place.

You cannot automatically detect fan traps. You need to visually examine the direction of the cardinalities displayed in the table schema.

If you have two tables that are referenced by measure objects and are joined in a series of “many-to-one” joins, then you may have a potential fan trap.

The fan trap scenario

In the Data Foundation for the Motors universe, the Client and Sale tables are joined by a “one-to-many-to-many” relationship, as are the Sale and Sale_Model tables.

(30)

The fan trap problem becomes apparent in a query that aggregates both an object based on the Sale_Total column in the Sale table, and an object based on the Sale_Qty column in the Sale_Model table.

Resolving fan traps

The recommended ways to solve a fan trap problem are: • Alter the SQL parameters for the universe.

• Use a combination of aliases and contexts. • Avoid the fan trap scenario.

Alter the SQL parameters for the universe

This method is not recommended as it only works for measure objects and may result in inefficiencies in processing the query. This resolution works the same for chasm and fan traps.

Use a combination of aliases and contexts

There are two possible situations which may require different solutions.

If you have... Then...

• Three tables in a path containing the initial aggregation, joining it back to the one-to-many relationship. • A dimension coming from the first

table and measures coming from the two subsequent tables.

Create an alias for the table (on the many end of the join) Use the Detect Contexts tool to detect and propose a context for the alias table and a context for the original table.

This is the most effective way to solve the fan trap problem because it works with measure and dimension objects and does not cause inefficiencies.

• Two tables in a one-to-many relationship.

• A dimension and a measure coming from the first table and a measure coming from the subsequent table(s).

Create an alias for the table containing the initial aggregation, joining it back to the original table and then use the Detect

Contexts tool to detect and propose a

context for the alias table and a context for the original table.

Both of these methods solve the fan trap problem because they work with both measure and dimension objects and do not cause inefficiencies.

(31)

Note: However, to be more efficient still,

using the two–table scenario, you could also use the @aggregate_aware function. See Unit 2 for more information.

Avoid the fan trap scenario

You can avoid the scenario in the first place by relating all measure objects in the universe to the same table in the universe structure. Avoid placing a measure on anything other than the last table in a table path, which is the table with the “many” cardinality attached to it.

Using aliases and contexts to resolve fan traps

You create an alias table for the table producing the aggregation and then detect and implement contexts to separate the query. This procedure is demonstrated in the diagram below:

Figure 4: Fan trap solution

As with resolving a chasm trap problem, two contexts need to be created. In this example, a context for Sale, and a context for Sale_Model need to be defined. This allows for the results to be merged into a single microcube to produce the correct results.

The SELECT clause of the Sales Revenue object also needs to be edited, so that it refers to the alias table rather than the original Sale table.

(32)

Moreover, if you make a query which includes a dimension object on the lower table in the “one-many-many” path, you do not get the fan trap, even when that dimension object contains the same value for all rows related to the measure value. The fact that the measure and dimension objects are in separate contexts forces two separate SELECT statements, thus avoiding the problem.

To use aliases and contexts to resolve a fan trap

1. Identify the potential fan trap by analyzing the “one-to-many-to-one-to-many” join path relations in the schema.

2. Create an alias for the table that is producing the multiplied aggregation. 3. Create a join between the new alias table, and the table that holds the dimension

information. 4. Set cardinality. 5. Set contexts.

6. Change the SELECT clause of the measure object so that it refers to the alias table rather than the original table.

7. Create a query using a measure object from the alias table and another measure from the subsequent table in the table path of the universe structure.

This results in two SELECT statements and the data is merged into a single microcube to produce the correct results.

Avoiding fan traps altogether

In certain situations, it is possible to avoid the fan trap completely, as shown in the diagram below.

To avoid the trap, the database column in table B to which the Y measure object relates must represent a pre-aggregation of more detailed data in table C. If this is the case, you can change the code of the Y measure object so that it refers to table C. Therefore, there is no longer a “one-to-many” relationship incurred.

(33)

Figure 5: Avoiding a fan trap

This is the method used to avoid the fan trap in the Motors universe, when the Sales Revenue and Number of Cars Sold measure objects are included in the same query. In the Motors universe, the Sales Revenue measure is not based on the total figure in the Sale table but on a number of columns from the Sale, Sale_Model and Model tables which are held in the database at the same level of granularity as the number of cars sold. Therefore, no fan trap exists and the correct result is obtained.

(34)

(35)

Exercise 2: Resolve Fan Traps

Exercise Objectives

After completing this exercise, you will be able to:

• Detect contexts to resolve a fan trap in the universe structure.

Business Example

You need to report on car sales revenue and the number of cars sold at the client and order level. Due to the database structure, this will result in a fan trap, which will need to be resolved in order to return accurate results.

Task:

1. Create a new Project and Data Foundation called Fan_xx, where “xx” stands for your user number. User the Motors connection.

SALE

SALE_MODEL

CLIENT.CLIENT_ID=SALE.CLIENT_ID 1,n SALE.SALE_ID=SALE_MODEL.SALE_ID 1,n

4. Create a Business Layer called Fan_xx

5. Deselect the “Multiple SQL statements for each measure” option. 6. Create two classes:

- Fan Objects - Measures

(36)

Fan Objects class

‘+

Model ID SALE_MODEL.MODEL_ID

Measures class

Number of Cars Sold SUM(SALE_MODE.SALE_QTY)

- Joins

10. Create a Query in the Business Layer to test the universe. Restrict the query results to Randall, Sean.

11. In the same query on the Business Layer, add the Model ID object to the Result Objects area. Refresh the query and note the results.

12. What is the measure value?

13. In the Information Design Tool on the Data Foundation tab create an Alias of the Sales table.

14. Join the new table to the Client table and add the appropriate self join to the alias.

CLIENT.CLIENT_ID=Alias_of_SALE.CLIENT_ID 1,n

Alias_of_SALE.SALE_TYPE =’S’ 1,1

15. In the Information Design Tool on the Data Foundation tab add the two contexts: - Sale Model

- Alias_of_SALE

(37)

16. Save the changes to the Data Foundation.

17. Modify the definition of the Sales Revenue object so that it is defined on the new alias table.

18. Save the changes to the Business Layer. 19. Recreate the last query from Step 11. 20. What is the Sales Revenue?

(38)

Solution 2: Resolve Fan Traps

Task:

1. Create a new Project and Data Foundation called Fan_xx, where “xx” stands for your user number. User the Motors connection.

a) Click New → Project. Project Name: Fan_xx b) Click Finish

c) Add a Repository session d) Browse the connection folder

e) Find the existing connection “Motors”

f) Right-click Motors connection → Create Relational Connection

Shortcut

g) Select Fan_xx from the pop-up box h) Click Finish

i) Click Close to proceed

j) “Motors.cns” will appear under the local project k) Click New → Data Foundation

l) Resource Name: Fan_xx

m) Click Next → Single Source → Next → Select Motors shortcut → Finish 2. Add the following tables to the Data Foundation:

CLIENT SALE

SALE_MODEL

a) Select the Data Foundation tab.

b) Click on the “+” and click on Insert Tables. c) Browse the tables under dbo.

d) Double-click the CLIENT, SALE and SALE_MODEL tables under connections

e) Click Finish.

(39)

CLIENT.CLIENT_ID=SALE.CLIENT_ID 1,n SALE.SALE_ID=SALE_MODEL.SALE_ID 1,n

For the self-join: d) Insert → Insert Join

g) Save your changes.

4. Create a Business Layer called Fan_xx a) Select the project

b) Click New → Business Layer

c) Select Relational Data Source → Next d) Name the Business Layer Fan_xx → Next e) Select “…” to select the Data Foundation f) Select Fan_xx.dfx → Click OK

g) Deselect “Automatically create classes and objects” option h) Click Finish

c) Deselect the “Multiple SQL statements for each measure” option 6. Create two classes:

- Fan Objects

(40)

- Measures

d) Change name to Fan Objects

e) Select the top level of the Business Layer f) Click the Insert Object drop down g) Select Folder

h) Change name to Measures

Fan Objects class

‘+

Measures class

Number of Cars Sold SUM(SALE_MODE.SALE_QTY)

a) Select the class/folder: 1. Fan Objects 2. Measures

b) Select the Insert Object drop down c) Select

1. Dimension

(41)

2. Measure d) Change the Name:

Dimension Measure

- Client Name - Sales Revenue

- Model ID - Number of Cars Sold

Note: In Model ID you have to set Data Type to Number

e) Click SQL Assistant f) Enter the select:

Dimension Client Name CLIENT.CLIENT_LASTNAME + ‘, ‘ + CLIENT.CLIENT_FIRSTNAME Model ID SALE_MODEL.MODEL_ID Measure Sales Revenue SUM(SALE.SALE_TOTAL) Number of Cars Sold SUM(SALE_MODEL.SALE_QTY) g) Click Validate h) Click OK

- Joins

(42)

- Business Layer

c) Select • Tables • Joins

10. Create a Query in the Business Layer to test the universe. Restrict the query results to Randall, Sean.

a) From the Business Layer select “Query” b) Click New

c) Create a query with the Client Name and Sales Revenue objects.

d) Apply a query filter on Client Name for Randall, Sean by dragging Client

Name to the Query Filter area and completing the query filter: Client Name In list Randall, Sean

e) Click Refresh and note the results: Sales Revenue = 57,091.50

11. In the same query on the Business Layer, add the Model ID object to the Result Objects area. Refresh the query and note the results.

a) Double-click Model ID object b) Click Refresh

12. What is the measure value? a) Sales Revenue = 114,183.00

(43)

a) Right-click the Sales table b) Click Insert → Alias Table… c) Click OK

14. Join the new table to the Client table and add the appropriate self join to the alias.

CLIENT.CLIENT_ID=Alias_of_SALE.CLIENT_ID 1,n

a) Use drag and drop to create the following join: CLIENT.CLIENT_ID=Alias_of_SALE.CLIENT_ID b) Double-click the join

c) Set the cardinality to 1,n d) Click OK

e) Click Insert f) Select Join

g) Add the expression: Alias_of_SALE.SALE_TYPE =’S’ h) Click Validate

i) Click Close

j) Set cardinality to 1,1 k) Click OK

- Alias_of_SALE

c) Click OK

(44)

a) Select the Business Layer b) Select the Sales Revenue object c) Edit the select to:

SUM(Alias_of_SALE.SALE_TOTAL) d) Click OK

18. Save the changes to the Business Layer. a) Click Save

b) Select the Client Name, Sales Revenue and Model ID objects. c) Drag the Client Name object down into the Query Filter area d) Complete the Query Filter:

Client Name In list Randall, Sean

e) Click Refresh

20. What is the Sales Revenue? a) Sales Revenue = 57,091.50

(45)

Lesson Summary

You should now be able to: • Define a fan trap

(46)

Unit Summary BOID20

Unit Summary

• Explain how SQL traps can cause queries to return inaccurate results • Define a chasm trap

• Define a fan trap

(47)

BOID20 Test Your Knowledge

Test Your Knowledge

1. A chasm trap can occur when:

2. Describe two ways to resolve chasm traps.

(48)

Test Your Knowledge BOID20

Answers

1. A chasm trap can occur when:

Answer:

1. Two joins form many-to-one-to-many converge on a single table. 2. The query includes objects based on the two “many” tables. 3. There are multiple rows returned for a single dimension value. 2. Describe two ways to resolve chasm traps.

Answer:

1. Create a context for each fact table. This solution works in all cases. 2. Modify the SQL parameters for the universe so you can generate separate

SQL queries for each measure. This solution only works for measure objects. It does not generate separate queries for dimension or detail objects.

3. Describe three ways to resolve fan traps.

Answer:

1. Alter the SQL parameters for the universe. This only works for measure objects. This resolution works the same for chasm and fan traps.

2. Create an alias for the table containing the initial aggregation, and then use Detect Contexts (Tools Detect Contexts) to detect and propose a context for the alias table and a context for the original table. This is the most effective way to solve the fan trap problem as it works with measure and dimension objects.

(49)

(50)

(51)

(52)

(53)

Unit 2

Advanced Use of the

@Aggregate_Aware Function

Unit Overview

The @Aggregate_Aware function was originally intended to be used to take advantage of aggregate tables in a data source. As the functionality of the Information Design Tool grows, the use of the @Aggregate_Aware function has expanded to other areas. After completing this unit, you will be able to:

• Use @Aggregate_Aware to resolve a 2-table fan trap • Use @Aggregate_Aware to resolve an ambiguous outer join

Unit Objectives

After completing this unit, you will be able to:

• Learn how you can use @AggregateAware to resolve 2-table fan traps

• Learn how you can use @AggregateAware to resolve ambiguous outer join issues • Use @AggregateAware to overcome the inefficiencies created when resolving

a 2-table fan trap

• Use @AggregateAware to resolve issues surrounding outer joins when used in the Data Foundation of a universe

Unit Contents

Lesson: Advanced Use of @AggregateAware... 44 Lesson: Using @AggregateAware to Resolve a 2-Table Fan Trap ... 46 Exercise 3: Resolve a 2-Table Fan Trap ... 49 Lesson: Using @AggregateAware to Resolve an Ambiguous Outer Join.... 60 Exercise 4: Resolve an Ambiguous Outer Join... 63

(54)

Unit 2: Advanced Use of the

@Aggregate_Aware Function BOID20

Lesson: Advanced Use of @AggregateAware

Lesson Overview

@AggregateAware is a function typically used for optimizing a universe by taking advantage of pre-aggregated data in the data source. But it can have other uses as well.

Lesson Objectives

• Learn how you can use @AggregateAware to resolve ambiguous outer join issues

Business Example

About @AggregateAware

The aggregate awareness functionality within the Information Design Tool was intended to allow the universe designer to take advantage of pre-aggregated data that exists within a data structure. It can, however, be viewed simply as a method for specifying preferential coding based on the other objects contained in a query. When viewed from this perspective, it becomes apparent that aggregate awareness can be used to resolve issues other than those for which it was originally intended.

For instance, aggregate awareness can be used to resolve a particular fan trap that, when resolved using the classic fan trap solution outlined in the previous Unit, results in efficient SQL being generated.

It can also be used to resolve issues surrounding outer joins when used in the Data Foundation of a universe.

(55)

BOID20 Lesson: Advanced Use of @AggregateAware

Lesson Summary

(56)

Lesson: Using @AggregateAware to Resolve a 2-Table

Fan Trap

Lesson Overview

This lesson explains how to use @AggregateAware to overcome the inefficiencies created when resolving a 2-table fan trap.

Lesson Objectives

• Use @AggregateAware to overcome the inefficiencies created when resolving a 2-table fan trap

Business Example

The 2-Table Fan Trap

As we have seen, fan traps occur when there is a “one–to–many” join to a table that “fans out” into another “one–to–many” join to another table.

Figure 6: Typical fan trap data foundation

(57)

BOID20 Lesson: Using @AggregateAware to Resolve a 2-Table Fan Trap

Figure 7: 2-table fan trap data foundation

Using the standard method for resolving a fan trap, you would: • Create an alias of table A.

• Create a join from the alias table An to table A and set cardinalities. • Set contexts B and An.

• Edit object Y so that it refers to columns in the alias table An rather than table A.

Figure 8: Standard fan trap resolution with aliases and contexts

This standard fan trap solution resolves the fan trap if objects Y and Z are used in the same query. However, if a query involves only objects X and Y, then no fan trap exists, and yet table A is still referenced twice: once for object X and once again for object Y from the alias table.

(58)

You can overcome this inefficiency by applying aggregate awareness to the Y object.

To use aliases, contexts, and @AggregateAware to resolve a 2-table fan trap

1. Create an alias of table A.

2. Create a join between table A and the alias table An. 3. Set the cardinality to 1,n, with the n on the alias table. 4. Set contexts An and B.

5. Change the SELECT clause of the Y object using @AggerateAware, the first choice referring to table A and then the alias table An.

6. Make the Z object incompatible with table A.

This method results in two SELECT statements when the query includes objects from Tables A and B, but when the query includes objects only from Table A, only one SELECT statement is created, and the Y object refers to Table A.

(59)

Exercise 3: Resolve a 2-Table Fan Trap

Exercise Objectives

After completing this exercise, you will be able to: • Resolve a 2-table fan trap in the universe structure.

• Use @AggregateAware to improve the efficiency of the SQL statement generated for queries.

Business Example

You need to report on car sales revenue and the models sold at the order level. Due to the database structure, this will result in a fan trap, which will need to be resolved in order to return accurate results. You also need to ensure that the SQL statement generated by the end-user queries is as efficient as possible. You will use @AggregateAware to improve the SQL efficiency.

Task:

1. Create a new Project and Data Foundation called 2-Table Fan_xx, where “xx” stands for your user number. User the Motors connection.

2. Add the following tables to the Data Foundation: SALE

SALE_MODEL

SALE.SALE_ID=SALE_MODEL.SALE_ID 1,n

4. Create a Business Layer called 2-Table Fan_xx

5. Deselect the “Multiple SQL statements for each measure” option. 6. Create a class:

- 2-Table Fan Objects

(60)

2-Table Fan Objects class

Sale ID SALE.SALE_ID

Sales Revenue sum(SALE.SALE_TOTAL)

- Joins

10. Create a Query in the Business Layer to test the universe. Restrict the query results to Sale ID 1010.

11. In the same query on the Business Layer, add the Model ID object to the Result Objects area after the Sale ID in the Query Results area. Refresh the query and note the results.

14. Join the new table to the Sale table and add the appropriate self join to the alias.

SALE.SALE_ID=Alias_of_SALE.SALE_ID 1,n

- Alias_of_SALE

16. Save the changes to the Data Foundation.

18. Make the Model ID incompatible with the SALE table. 19. Save the changes to the Business Layer.

(61)

20. Recreate the last query from Step 11. 21. What is the Sales Revenue?

(62)

Solution 3: Resolve a 2-Table Fan Trap

Task:

1. Create a new Project and Data Foundation called 2-Table Fan_xx, where “xx” stands for your user number. User the Motors connection.

a) Click New → Project.

Project Name: 2-Table Fan_xx b) Click Finish

c) Add a Repository session with the logon credentials:

System: BI 4.0 system provided by the

instructor

User name: train-##

Password train-##

Authentication Mode Enterprise

d) Browse the connection folder

e) Find the existing connection “Motors”

f) Right-click Motors connection → Create Relational Connection

Shortcut

g) Select 2-Table Fan_xx from the pop-up box h) Click Finish

i) Click Close to proceed

j) “Motors.cns” will appear under the local project k) Select the project and click New → Data Foundation l) Resource Name: 2-Table Fan_xx

m) Click Next → Single Source → Next → Select Motors.cns → Finish 2. Add the following tables to the Data Foundation:

SALE

(63)

SALE_MODEL

a) Select the Data Foundation tab.

b) Click on the “+” and click on Insert Tables. c) Browse the tables under dbo.

d) Double-click the SALE and SALE_MODEL tables under connections e) Click Finish.

SALE.SALE_ID=SALE_MODEL.SALE_ID 1,n

For the self-join: d) Insert → Insert Join

g) Click Save

4. Create a Business Layer called 2-Table Fan_xx a) Select the project

b) Click New → Business Layer

c) Select Relational Data Source → Next

d) Name the Business Layer 2-Table Fan_xx → Next e) Select “…” to select the Data Foundation

f) Select 2-Table Fan_xx.dfx → Click OK

g) Deselect “Automatically create classes and objects” option h) Click Finish

(64)

c) Deselect the “Multiple SQL statements for each measure” option 6. Create a class:

- 2-Table Fan Objects

d) Change name to 2-Table Fan Objects

2-Table Fan Objects class

Sale ID SALE.SALE_ID

Sales Revenue sum(SALE.SALE_TOTAL)

a) Select the class/folder: 1. Fan Objects

b) Select the Insert Object drop down c) Select

1. Dimension 2. Measure d) Change the Name:

Dimension Measure

Sale ID Sales Revenue

(65)

Model ID

Note: In Model ID you have to set Data Type to Numeric

e) Click SQL Assistant f) Enter the select:

Dimension Sale ID SALE.SALE_ID

Model ID SALE_MODEL.MODEL_ID Measure Sales Revenue SUM(SALE.SALE_TOTAL) g) Click Validate h) Click OK

- Joins

- Business Layer

c) Select • Tables • Joins

(66)

10. Create a Query in the Business Layer to test the universe. Restrict the query results to Sale ID 1010.

a) From the Business Layer select “Query” b) Click New

c) Create a query with the Sale ID and Sales Revenue objects.

d) Apply a query filter on Sale ID for 1010 by dragging Sale ID to the Query

Filter area and completing the query filter: Sale ID In list 1010

e) Click Refresh and note the results: Sales Revenue = 57,091.50

11. In the same query on the Business Layer, add the Model ID object to the Result Objects area after the Sale ID in the Query Results area. Refresh the query and note the results.

a) Drag the Model ID object to the left of the Sale ID object in the Result Objects area

b) Click Refresh

a) Sales Revenue displays the 57,091.50 for each model, which is incorrect. 13. In the Information Design Tool on the Data Foundation tab create an Alias

of the Sales table.

a) Right-click the Sales table b) Click Insert → Alias Table… c) Click OK

14. Join the new table to the Sale table and add the appropriate self join to the alias.

(67)

SALE.SALE_ID=Alias_of_SALE.SALE_ID 1,n

a) Use drag and drop to create the following join: SALE.SALE_ID=Alias_of_SALE.SALE_ID b) Double-click the join

c) Set the cardinality to 1,n d) Click OK

e) Click Insert f) Select Join

g) Add the expression: Alias_of_SALE.SALE_TYPE =’S’ h) Click Validate

i) Click Close

j) Set cardinality to 1,1 k) Click OK

- Alias_of_SALE

c) Click OK

(68)

a) Select the Business Layer b) Select the Sales Revenue object c) Edit the select to:

@Aggregate_Aware(sum(SALE.SALE_TO-TAL),sum(Alias_of_SALE.SALE_TOTAL)) d) Click OK

Hint: Click Validate to check the syntax.

18. Make the Model ID incompatible with the SALE table. a) Select Actions → Set Aggregate Navigation . . . b) Highlight the SALE table on the left of the pop-up

c) Open the class on the right of the pop-up and place a check mark next to the Model ID object

d) Click OK

19. Save the changes to the Business Layer. a) Click Save

b) Select the Sale ID, Model ID, and Sales Revenue objects. c) Drag the Sale ID object down into the Query Filter area d) Complete the Query Filter:

Sale ID In list 1010

e) Click Refresh

21. What is the Sales Revenue?

(69)

Lesson Summary

• Use @AggregateAware to overcome the inefficiencies created when resolving a 2-table fan trap

(70)

Lesson: Using @AggregateAware to Resolve an

Ambiguous Outer Join

Lesson Overview

This lesson explains how to use @AggregateAware to resolve issues surrounding outer joins when used in the Data Foundation of a universe.

Lesson Objectives

• Use @AggregateAware to resolve issues surrounding outer joins when used in the Data Foundation of a universe

Business Example

Ambiguous Outer Joins

As we have seen, the Information Design Tool supports outer joins on the data foundation. Depending on the relational database management system (RDBMS), however, outer joins can create issues with WebIntelligence queries.

For example, consider the Motors universe we have been working with. The REGION table is joined to the CLIENT table which is joined to the SALES table. The problem that can occur with some RDBMSs is that with the CLIENT table joined to the SALE table, when a query uses data from all three tables, the query either may not run or may return incorrect results if a Region does not have a Client because there can be no Sale.

Note: In SQL Server, which we are using in this class, there are no issues. In

Oracle, however, no null values are retrieved, and in some other databases, an ambiguous outer join message appears.

(71)

BOID20 Lesson: Using @AggregateAware to Resolve an Ambiguous Outer Join

If there are issues with the RDBMS used, there are two ways to resolve them: 1. Do not use outer joins in your data foundation. Obviously, this solution is not

practical if the end-users' reporting needs require blank rows to be returned. 2. Create an alias of the table where the outer join is required, and use

@Aggregate_Aware in the relevant object(s) to identify which join path to take depending on the objects used in the query.

Note: Depending on the RDBMS, the outer join placement may be

different.

To resolve an ambiguous outer join

1. Create an alias of the table where the data could be “missing” (the CLIENT table in our Motors Business Layer).

2. Create a the same join between the alias table and the dimension table it is joined to (REGION in our Motors Business Layer).

3. Set the cardinality and specify an outer join accordingly.

4. Make the join between the dimension table and the original table that was aliased an inner join.

Hint:

Figure 10: Sample data foundation with outer join between the REGION and CLIENT2 tables only

(72)

5. Change the SELECT clause of the objects from the original table that was aliased using @AggerateAware, the first choice referring to the alias table and then the original table.

6. Make any objects from the fact table joined to the original table that was aliased incompatible with the alias table.

Note: Setting this incompatibility ensures that if a query includes objects

from all three tables, the alias table with the outer join will be used. As an example from the Motors Business Layer, if the query includes objects from only the REGION and CLIENT tables, the Client2 table will be used instead of the Client table.

But if the query includes objects from all the tables, the original table with the inner join will be used. In our Motors Business Layer, the