at the rate funtion s

Module 10Using @ Functions

August 01, 2000 Tata Infotech Limited

2 August 01, 2000

What You Will Be Able To Doh Know what @ Functions are available

Note: @AggregateAware is covered later in the course

h Be able to apply them

h Know what @ Functions do

3 August 01, 2000

The @ Functionsh The @ Functions available are:

h These Functions are applied in the Select and Where boxes of objects

h They are used to provide flexible methods of specifying SQL

4 August 01, 2000

@Promptn @Prompt is placed in an object as part of the Select or Where

properties

n When a query is run that includes the object, the @prompt of the object forces a prompt box to appear

5 August 01, 2000

@Prompt SyntaxSHOWROOM.SHOWROOM_NAME = @PROMPT

Free or constrained (to value in LoV).. Constrained

‘Enter Showroom Name’,The prompt……………………………….

‘A’,Data Type (A, N or D)..………………….

‘Showroom\Showroom’,LoV Pointer..……………………………..

Or hard coded list : {‘A’,‘B’,‘C’}

Mono,Mono or multi (LoV selection)………..

(

)

Operator dependent on operand

6 August 01, 2000

@Selectn @Select function acts as a pointer to the Select box of another object:

@Select (Class_Name\Object_Name)

n Creates a dynamic link between objects, so that update of the original object Select statement automatically updates the other objects

7 August 01, 2000

@Wheren The @Where function acts as a pointer to the Where box of another object

n Creates a dynamic link between objects, so that update of the original object Where clause automatically updates the other objects or condition objects

8 August 01, 2000

@Where - Designer Strategyn You can create a Class containing only Where clause objects, and hide

the Class from end Users - other objects use the Where clauses, but the Users don’t see the base objects.

9 August 01, 2000

@Variable - with Personal Filesh The @Variable function can be used in conjunction with

a text file to define User-specific conditions in an object:C:\BusinessObjects\Busobj.exe -vars Bovars.txt

COUNTRY = JAPAN

10 August 01, 2000

@Variable - with Personal Files: Drawbacks

h Requires localized administration of the text file by the Designer / Supervisor

h The command string must be changed on every User ’s PC to include the -vars <textfile.txt> argument

h Security can be a problem, as a text file on a PC is open to local amendment

h Conclusion: Better to use the options in the Supervisor module

11 August 01, 2000

@Variable - with BusinessObjects Variables

h The @Variable function can also be used with the BusinessObjects variables to define User-specific conditions in an object:

BOUSER

BOPASS

Where clause:Employees.Employee_Name = @Variable(‘BOUSER’)

Restricts data returned to that relating to the User’s login name

12 August 01, 2000

@Scripth Allows use of a variable declared in a VBA script

h The script ‘ smotors ’ runs the country selection process

Module 11Lists of Values


14 August 01, 2000

What You Will Be Able To Do

h Base a LoV on a personal file

h Associate or remove a List of Values (LoV) for an object

h Control how LoVs are refreshed

h Modify a LoV using a condition

h Modify a LoV by creating a hierarchy

15 August 01, 2000

What is a List of Values?

h A LoV is used on the operand side of a condition in the query panel of the User module

h This is only available if set by the designer

h A list of the distinct values from the column or columns to which the object refers

16 August 01, 2000

How do Lists of Values work?h A designer can create a LoV which is based on:

h A query of the target database

h A constant set of values held in a file

h In both cases, the result is stored locally in a file on the User ’s PC.

17 August 01, 2000

Creating a List of Valuesh A LoV is created within the Properties tab of an object

h By default, Associate a List and Allow Users to edit are checked:

h It is important to uncheck this box for objects that don’t need a List

18 August 01, 2000

Controlling How Lists are Refreshedh Normally, the first time a LoV is used in a User login

session, the system fires a query at the target database.

h The results of this query are used to populate the list, and are stored in the .lov file.

h Thereafter, the .lov file from this query is used each time the List is required.

19 August 01, 2000

Controlling How Lists are Refreshed

h Not normally used - uncheck this box

h Check this box for frequently changing lists

h Check this box for lists that never change

20 August 01, 2000

Modifying the Content of a List of Valuesh You can limit the values returned by applying a

condition to the LoV

h You can simplify the process of choosing a value for Users by creating a hierarchy for the LoV

h You can supply a personal data file containing the values for the list, instead of using the results of the query

21 August 01, 2000

Applying a Condition to a List of Valuesh Click Edit in the Properties box:

h Apply the condition in the Query Panel:

22 August 01, 2000

Creating a Hierarchy for a List of Valuesh Click Edit in the Properties box:h Place the hierarchy objects (which

must be sorted) to the right of the LoV object in the Query Panel:

23 August 01, 2000

Creating a Hierarchy for a List of Valuesh The resulting Hierarchical View of the LoV makes it

easier to select the required value:

h Country:h Town:h Showroom:

24 August 01, 2000

Basing a LoV on a Personal Fileh Select Tools, Lists of Values from the Menu bar:

h Select the object:

h Select Personal Data:

25 August 01, 2000

Basing a LoV on a Personal Fileh Click OK to acknowledge the message:

h Specify the file that contains the values for the list and click OK

Module 12Hierarchies


27 August 01, 2000


h Test hierarchies in the User module

h Organize Default Hierarchies for optimum usability

h Create Time Dimension objects and Hierarchies

h Create Custom Hierarchies

28 August 01, 2000

U.K.

North South

Liverpool Manchester Newcastle London Southampton Bristol

Level in Hierarchy Data Returned

What Is a Hierarchy?

29 August 01, 2000

h A Default Hierarchy is the hierarchy based on the order of the Objects within the Class

Default Hierarchies arerepresented in the User module by the order in whichDimension Objects are organized in the Classes and Objects box of the Query Panel

What Is a Default Hierarchy?

30 August 01, 2000

The Key to Creating Default Hierarchiesh The designer must ensure, wherever possible, that

Dimension Objects are organized from the most general to the most specific within the Class

31 August 01, 2000

h Dimension Objects represent the levels in a hierarchy:

Top Level

Level 1

Level 2

Level 3

How are Hierarchies used in Analysis?

h A Measure can be analysed at each level

32 August 01, 2000

Sale Date25/11/99

Month Year

Quarter

Why Is Time A Special Case?

Day

33 August 01, 2000

h Automatic Time Hierarchies

h Using Database Functions

h Table Based Time Hierarchies

Creating Time Hierarchies

34 August 01, 2000

h A Custom Hierarchy combines Objects from Default Hierarchies to build a Custom Analysis Path

What Is A Custom Hierarchy?

Default Hierarchies

Custom Hierarchy

35 August 01, 2000

Creating Custom Hierarchies

h Add Objects from Default Hierarchies to build a new Custom Hierarchy

(or Tools, Hierarchies)

36 August 01, 2000

The Effect of Custom on Default Hierarchies

h User module Scope of Analysis after a Custom Hierarchy has been created:

h No Default Hierarchies are shown

37 August 01, 2000

The Effect of Custom on Default Hierarchies

h Selected Default Hierarchies can be added to the Custom Hierarchies in Designer module

Module 13Documenting and Distributing a Universe


39 August 01, 2000

What You Will Be Able To Doh Print information about the universe

h Print the universe schema

h Know the advantages and disadvantages of the two different ways that universes are distributed to end users

h Distribute a universe in Workgroup mode

h Distribute a universe in Enterprise mode

40 August 01, 2000

h Select the Tools, Options menu then click on the Print tab to select what to print

h Select the File, Print menu to print

Faire un aperçu avant impression pour montrer.Rappel : il n ’est pas possible d ’exporter le résultat de l ’impression sous un format texte ou autre pour le retravailler. C ’est une documentation technique et NON PAS UTILISATEUR.Documenting a Universe

41 August 01, 2000

h Different phases require different types of universe distribution

Phase 3

Phase 5

Phase 4

Phase 6

Phase 7

Phase 1-2

Universe Design Cycle

42 August 01, 2000

h There are two methods distributing universes

Business ObjectsRepository

File System

Distributing Universes

43 August 01, 2000

File server

S:\Designer

S:\Test

Access controlledBy network security

Workgroup Mode

Business ObjectsUniverse

Motors2k.unv (Shared Connection)

44 August 01, 2000

Enterprise Mode

Business Objectsrepository

Export

Supervisor in control

User based securityallocated to user groups

Revision number checked when used

(Secured Connection)

Business ObjectsUniverse

Motors2k.unv

Motors2k.unv

Universe Domain Name

Business Objects

Universe

Revision number incremented

Motors2k.unv

Universe Domain Name

45 August 01, 2000

h The Designer exports a universe to the universe domain and allocates universe access to one of the groups of users he or she belongs to

h The revision number of the universe is incremental. This number guarantees the user will use the more recent version of the universe

Exporting a Universe

Designer

Repository

Universe DomainDocument DomainSecurity Domain

Module 14Universe Maintenance


47 August 01, 2000

What You Will Be Able To Doh Know when a new version of a universe may be

requiredh Be able to identify changes to the target database of a

universeh Know how version changes will affect existing end

user reportsh Be able to copy and past objects between universesh Be able to link universes and appreciate the different

strategies requiredh Be able to include universes

48 August 01, 2000

Reasons for Universe Maintenance

h New tables added to databaseh Tables deleted, changed or renamed in databaseh Users request new classes or objects

Tip: Do not distribute new versions of universes too frequently!

Note: Your primary concern when you introduce a new version of a universe should be what effect it will have on existing end user reports

49 August 01, 2000

No Impact:

h Adding new columnsh Adding new tables

Severe Impact:

h Renaming/moving databaseh Changing existing column

and table namesh Deleting tables and columns

Changes to the Target Database

h The implications of modifying the underlying database for BusinessObjects universes varies in severity:

50 August 01, 2000

Le Designer doit être une personne maîtrisant parfaitement le Système d ’Information sur lequel repose l ’univers. Toute modification sur ce système peut évidemment avoir des conséquences sur l ’univers.Detecting Changes to the Universe

h Choose View, Refresh Structureh Changed tables are highlighted

in the Structureh A message on the screen

indicates that tables have been deleted

51 August 01, 2000

No Impact:

• Redefining Object SQL• Renaming an Object• Copying to different Class• Moving in same Class or to

a different Class• Adding new Objects

Impact:

• Deleting an existing Object

• Deleting and then recreating an object with exactly the same definition

The Effect of Changing Objectsh Changes to existing Objects in a Universe can have

an impact on existing Documents created from the Universe:

52 August 01, 2000

h The workflow is:

Adding New Tables to an Existing Universe

Add tables to the universe structure

Delete existing contexts

Detect aliases

Detect contexts

Create new objects

Set up hierarchies

Insert joins and set cardinalities

53 August 01, 2000

Making Maintenance More Efficient

Maintenance can be made easier by:h Copying and pasting objects between universesh Linking universes

i Embedding the structure, classes and objects from one universe to another

h Including universesi Copying the structure, classes and objects from one

universe to another

54 August 01, 2000

h You can copy objects from one universe to another

Copying and Pasting Objects

h Both universes must have the same connection

55 August 01, 2000

Linking Universesh Linking can mean reduced maintenance but there are

limitations

Universe A

Universe A

Universe B

56 August 01, 2000

Two Approaches to Linking

h Kernel approach

h Master approach

57 August 01, 2000

L ’approche Composant est une approche « Noyaux multiples ».

Kernel Approach

Human Resources

+

Motors2000

+

Kernel

Kernel

KernelStaff

58 August 01, 2000

Master Approach

MasterHuman Resources

Motors2000HRand

Motors2000

-

-Master

Master

59 August 01, 2000

h Click and click on the Links tab

Making a Link

h Click Add Link...

h Choose universe to link and click Open

60 August 01, 2000

The Derived Universe

h The linked universe structure and objects cannot be edited in the derived universe

h The linked universe should be treated as though they were new tables inserted in the derived universe. The same procedure should be applied

61 August 01, 2000

Including Universes

Universe A

Universe B

Everything from

Universe A

Universe B

Universe A

h Including a Universe COPIES structure and objects to the derived universe:

62 August 01, 2000

Included Universesh Included universes become part of the new universe

Module 15Aggregate Awareness


64 August 01, 2000


h Understand the reasons for using Aggregate Awareness

h Know when to apply Aggregate Awareness

h Be able to apply Aggregate Awareness

65 August 01, 2000

What is Aggregate Awarenessh Some databases have Summary Tables which contain

figures aggregated to a high level.

h These are used to speed up queries which contain statistics (for example, Revenue) reported at, for instance, year rather than event level.

h Aggregate Awareness is the BusinessObjects process by which these higher level aggregates are incorporated into a universe.

66 August 01, 2000

Summary Tablesh Check the columns in the Summary Table:

h The other columns relate to objects that require @AggregateAware definitions

h Foreign Key columns are used for the joins

h Note that Summary Tables without Foreign Keys do not need joins - they can be free floating tables.

67 August 01, 2000

How Summary Tables are used in SQLn Aggregates of a normalized database are based on event / fact level

data.Sale

226 rows

Sale_Model

233 rows

Model

33 rows

Sales Revenue = sum(SALE_MODEL.SALE_QTY * MODEL.MODEL_PRICE * ((100 - SALE.SALE_SALE_DISCOUNT) / 100))

n Summary tables may be added to a database which hold data at a higher level of aggregate. Annual_Figures

26 rows

Sales Revenue = sum(ANNUAL_FIGURES.ANNUAL_SALE_VALUE)

n Making use of summary table data speeds up response times.

68 August 01, 2000

Procedure for Applying Aggregate Awareness

1 Insert the Summary Table(s) in the universe structure; set joins and cardinality

2 Set the contexts

3 Redefine objects using @AggregateAware

4 Define incompatible objects using Aggregate Navigation

69 August 01, 2000

Step 1: Inserting a Summary Table & Setting Joins

h Add the Summary Table to the structureh Set the joins and the

cardinality

70 August 01, 2000

h Set the contexts

Step 2: Detecting Contexts

h The Annual_Figures context

71 August 01, 2000

Step 3: Redefining Objectsh Edit the objects affected by the Summary Table.

h @AggregateAware Syntax:i @AggregateAware(Definition1,Definition2,…,DefinitionN)

From the most to the least Aggregatedi The last definition must be valid for all the queries

72 August 01, 2000

Step 4: Incompatible Objectsn Aggregate aware objects can only be compatible with objects derived

from tables in the same context as the summary table

n Incompatible objects are specified using Aggregate Navigation

73 August 01, 2000

Step 4: Aggregate Navigationh The Aggregate Navigation box is opened from the

Tools menu

h Click Detect Incompatibility:

h Select the Summary Table:

h Incompatible objects are checked:

h You must check carefully - not all incompatible objects may be detected automatically.

h Individual objects are checked to define them as incompatible:

74 August 01, 2000

Testing Aggregate Awarenessh As with any universe element, you must always test

the results in the User module.

h Run queries using the aggregated measures from the Summary Table.

h Use both compatible objects and incompatible objects in different queries to make sure all levels of Aggregate Awareness are operating correctly.

Module 16Contexts Revisited


76 August 01, 2000


h Understand the purpose of using contexts in a universe

h Recognize and be able to resolve Chasm Traps

h Recognize and be able to resolve Fan Traps

77 August 01, 2000

What Contexts are Used forh A context is simply a list of joins denoting a path

between tables.

h Contexts are set to identify alternative routes in the universe structure.

h BusinessObjects detects a context for each alternative route.

h Contexts identify tables which are incompatible within the same SELECT statement - because the wrong results will be produced.

78 August 01, 2000

How Contexts are Detectedh BusinessObjects Designer identifies tables which

have only the ‘many’ end of joins attached:

79 August 01, 2000

How Contexts are Detected (2)n You can arrange your universe structure so that all joins are flowing

from the ‘many’ ends at the left to the ‘one’ ends at the right.

80 August 01, 2000

How Contexts are Detected (3)h The forward flowing joins form the Sale contexth No joins flowing

back from one to many are included

81 August 01, 2000

Identifying Key Problemsh There are two key problems you may encounter

which can be resolved using contexts:

h Chasm Traps

h Fan Traps

82 August 01, 2000

Chasm Trapsh For a Chasm Trap to occur, there must be:

h a query with objects from each of the ‘many’ tables

h ‘many to one to many’ h Multiple instances of a single dimension in results

83 August 01, 2000

Chasm Traps - What Happens

84 August 01, 2000

Chasm Traps - What Happens (2)

h Note: Rental Revenue has doubled and Sale Revenue has trebled !

85 August 01, 2000

Chasm Trap Query - the SQLh The SQL for the last query is:

h The problem arises because of the logical table formed as a result of the Cartesian product of the Client, Sale, and Rental tables.

86 August 01, 2000

Chasm Trap - the Rows Returnedh We can look at the individual Sale transactions by

adding the Sale Date to the query:

h Similarly, we can look at the individual Rental transactions by adding the Rental Date to that query:

87 August 01, 2000

Chasm Trap - the Rows Returnedh We can see the problem when we look at the rows

returned from the combined query:

h The query returns every possible combination of Sale and Rental rows.

88 August 01, 2000

Chasm Traps - the Solutionh To resolve the Chasm Trap, we need the SQL to make

two separate queries and then combine the results.

h There are two possible methods of achieving this:

1 Altering the SQL parameters for the Universe

2 Using Contexts

h The first method is only a partial solution. The second method works every time.

89 August 01, 2000

Altering the Universe SQL Parametersh Click

h Check

h Click

h The Chasm Trap query will now make one query for each measure and combine the results - correctly.

90 August 01, 2000

SQL Parameter Method - the Drawbackh The parameter specifies:

‘Multiple SQL statements for each measure’

h It does not separate queries containing only dimension objects.

h For example:

h The report contains a single block with the results displayed as a Cartesian product - unclear for Users.

91 August 01, 2000

Recommended Solution - Use Contextsh Apply a context to each leg of the Chasm Trap:

92 August 01, 2000

Recommended Solution - Use Contexts (2)

h With the contexts in place, both measure object queries and dimension object queries display correctly:

h Conclusion: Always use contexts to resolve Chasm Traps

93 August 01, 2000

Fan Trapsn For a Fan Trap to occur, there must be a ‘one to many to many’

relationship in the structure:

n The fan trap occurs when a client has many rental transactions (Rental.Sale_Id) each with a total value, but each of these transactions includes many quantities of different models.

94 August 01, 2000

Fan Traps - What Happensh A query for the Total Rental Value for a client will give

the correct result:

h However, if you also wanted to see the quantities of each model the client has rented, you might get an incorrect result:

h The Total Rental Value has trebled !

95 August 01, 2000

Fan Traps - the Problem

Client Name Rental Value Model Id Rental QtyFoulkes, Bill 1,640.00 1043 3Foulkes, Bill 1,640.00 1055 2Foulkes, Bill 1,640.00 1072 2

4,920.00 7

h The query returns every possible combination of Rental_Value and Rental_Qty rows:

h The Rental_Value trebles because there are three different Model_Id rows that each join with the same Total_Rental_Value row.

96 August 01, 2000

Fan Traps - the Solutionh Context or alternatively, an Alias:

h The aggregation that causes the problem is the object derived from Rental.Sale_Total

h This solution provides an alternative table that can be used for this aggregation: Rental2.Sale_Total

h Join the Alias to the original table using a ‘one to one’ join

97 August 01, 2000

Fan Traps - the Solution (2)h The Fan Trap problem occurs only when you derive

the Rental Value from the Rental table AND the query includes objects from the Rental_Model table.

h Use @AggregateAware to redefine the Rental Value object:

i @AggregateAware(sum(Rental.Sale_Total) sum(Rental2.Sale_Total))

h Use Aggregate Navigation to define ALL objects derived from the Rental_Model table as incompatible with the Rental table.

98 August 01, 2000

Fan Traps - the Solution (3)h As with Chasm Traps, the Fan Trap problem can also

be partially resolved by setting the SQL parameter: ‘Multiple SQL statements for each measure’.

h The drawback is the same: The solution only works with Measure objects.

h Conclusion:Identify Fan Traps from the context flow, then resolve them using Alias tables and Aggregate Awareness.