rio 03 bo designer v1[1].0

8/6/2019 RIO 03 BO Designer v1[1].0

http://slidepdf.com/reader/full/rio-03-bo-designer-v110 1/78

BO Designer

C3: Protected



© 2007, Cognizant Technology Solutions Confidential 2

About the Author

Created By: Kousalya Devi Selvaraj (112325)

Credential

Information:

Around 4 years of IT experience and over 2 years of experience in

Business Objects

Version and

Date:

BO/PPT/0309/1.0




Ic ons Used

Questions

Contacts

Reference

Demonstration

Hands on

Exercise

Coding

StandardsTest Your

Understanding

Tools

A Welcome

Break




BO Designer: Ov er v iew

Introduction:

� Designer is a BO component that is used to build universes.

� A universe is a semantic layer between database and end users.

� The role of a universe is to provide an easy to use and understandinterface for non technical BusinessObjects and WebIntelligence users

to run queries against a database to create reports and perform data

analysis.




BO Designer: O bje c ti v e

Objective:

After completing this chapter, you will be able to:

� Define connection to universe

� Design universe

� Insert tables and joins

� Resolve loops

�

Create classes and objects� Check universe integrity

� Manage universe

� Overview of Security




Uni v erse

A universe:� Is a semantic layer between database and end users.

� Is a business-oriented mapping of the data structure found in

databases: tables, columns, joins, and so on.

� Can represent any specific application, system, or group of

users. For example, a universe can relate to a department in acompany such as marketing or accounting.

� Can also pertain to a section within a department or just about

any set of organized procedures such as a payroll or inventory

system.

� Consists of a set of objects, grouped by classes.




R ole of a Uni v erse

The following diagram shows the role of objects as the mapping

layer between a database schema and the Query panel in

BusinessObjects or the Query work area in WebIntelligence, that

users use to create queries to run against database tables.




T he Semanti c Layer

The semantic layer may consist of one or more

separate universes. Each universe is a partial

representation of a database, in the form of business

vocabulary.

Sales/Marketing

Human

ResourcesEmployee

Salary

Benefits...

Sales Revenue

Sales ExecutiveCustomer Product

Price Order Date ...




T he Uni v erse De v elopment Pro c ess

Prepare

Know the data source HR onSybase

Financialson Oracle

Plan

Design the universe(s)

AnalyzeIdentify the needs of the

end user population




T he Uni v erse De v elopment Pro c ess

(Contd.)

Test

Using a test team (including end

users)

Implement

Create the universe(s)

Evolve

Adapt and extend universe

over time

Deploy

Distribute the universe to end

users




e ni v erse De v e opment Pro c ess

(Contd.)

80

20Involve users in all steps of the

project




Creating a Uni v erse

Define the Parameters

Insert Tables

Make the Joins

Resolve the Loops

Create Classes and Objects

Set up Hierarchies




Creating a Uni v erse (Contd.)

For creating a new universe:

� Select File > New




Uni v erse Conne c tion

Definition:� A connection is a link from the universe to the target database

� The link is achieved using middleware (for example ODBC)

� An existing connection may be used or a new connection

created for a universe

� There are three different types of BO connection




T ypes of Conne c tion

Personal: Can only be used on the client

Shared: Can be used by more than one user to send queries to the target

database from a shared server

Secured: This connection is used when you wish to distribute the completed

universe to the user population via the repository




Setting up Uni v erse Parameters

Definition Tab:Name, description, and

connection to the database.

Summary Tab: Author and statistics about

the universe.

Strategies Tab: Internal or personalwizards to make creating a universe easier.

Controls Tab: Manages access and control

of resources.

SQLTab: Queries and SQL parameters.

Links Tab: Enables dynamic links with

other universes.




Setting up Uni v erse Parameters (Contd.)

Controls tab:

� These allow you to limit the

size of results and restrict

the time allowed for

executing queries.

� The Supervisor can change

these parameters for

specific groups or users.




Setting up Uni v erse Parameters (Contd.)

SQL tab:

� SQL parameters are used to

define the limits of the SQLthat may be generated by the

universe.

� The Supervisor can change

these parameters for specific

groups and users.




I nserting T ables

Use the Table Browser:� Double-click on the background of the

Structure

� Click on

� Choose Insert -> Tables from the menu

The Table Browser displays all the

tables and views of the database.

You can select multiple tables using the

Shift key or Ctrl key.




Classes and O bje c ts

BO presents the data in three hierarchical collections or

categories:

� Universe

�

Classes

� Objects




Class

Universe:

� A BO universe is a collection of classes that is intended for a

particular group of users.

Class:� A class is a logical grouping of objects within a universe. It

represents a category of objects. The name of a class should

indicate the category of the objects that it contains. A class can

be divided hierarchically into subclasses.




O bje c t

Object:

� An object is a named component that maps to data or a

derivation of data in the database. The name of an object should

be drawn from the business vocabulary of the targeted user

group. For example, objects used in a universe used by a

product manager could be Product, Life Cycle, or Release Date.

A universe used by a financial analyst could contain objects

such as Profit Margin and Return on Investment.




O bje c t T ypes

Dimension:� Parameters for analysis. Dimensions typically relate to a

hierarchy such as geography, product, or time. For example

Last Name and City_Id.

Detail:

� Provide a description of a dimension, but are not the focus for

analysis. For example PhoneNumber.

Measure:

� Convey numeric information which is used to quantify

dimension object. For example Sales Revenue.



© 2007, Cognizant Technology Solutions Confidential

Using List O f Values

24

Allows you to view and modify the data values in the

list of values assigned to the selected object.

You can view the list of values in a tabular form or in a

hierarchical form.

The options to manage lists of values are:

� Edit

� Display

� Purge

� Refresh� Properties

You can specify a text file, a Dbase file or a spreadsheet

file to be used for the selected object's list of values.




Using List O f Values (Contd.)

2525




Creating Joins

What do joins achieve in SQL?� The SQL will give a cartesian product which gives an incorrect

result




Creating Joins (Contd.)

What is Join?� A join is a condition that restricts the result set of a query

� There are several different kinds of join:

Equi-join (otherwise known as a standard or

inner join)

Outer join

Theta join

Shortcut Joins

Self join





Equi - Join





Creating a Join in Designer:

� A join can be entered manually using two

methods:

Graphically

With the menu option Insert,

Joins and the Edit Join box

�

To delete a join, click on the join line andress Delete





Automatic join detection:

� The Strategy for automatic detection of joins is based oncommon column names between tables.

� You can choose from one of three specific strategies for joindetection:

Smart matching column names (no key info.)

All matching column names

All matching numeric column names





Theta Join Result Set:

Theta Join

Theta join:

� A theta join contains an expression that is based on something

other than equality:





Creating theta join:

� A theta join is created by converting an existing equi-join.





Shortcut joins:

� Join providing an alternative path between two tables, bypassing

intermediate tables, leading to the same result, regardless of direction.

� Optimizes query time by cutting long join paths as short as possible.





Self restricting join:

� This is not really a join at all. It is a method used to set a

restriction on a table in the universe structure.





Automatically using (but can take a long time)

Manually using the Edit Join dialog box

Adding cardinalities:





Join problems: The three major join problems that could be

encountered in the schema are:

� Loops (resolved using alias/contexts)

� Chasm traps (resolved using contexts)

� Fan traps (resolved using alias/contexts)





Loops:� A loop is a set of joins that defines a closed path through a set of

tables in a schema. Loops occur when joins form multiple paths

between lookup tables. An example of a loop is shown below:





The schema contains two linked sets of information:

� These two sets of information are linked in a common join path

forming a loop. The lookup table Country can be the country

where a resort is situated, or the country in which a customerlives.

For each The following information is linked

Resort Available service lines, services for each service line, invoice

information for each service and the country where the resort is

situated.

Customer The city, region and country where the customer lives, the sales for

the customer and the invoice information for each sale.





Chasm Trap

� 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.





Fan trap:

� A fan trap is a type of join path between three tables when a

one-to-many join links a table which is in turn linked by

another one-to-many join. The fanning out effect of one-to-

many joins can cause incorrect results to be returned when aquery includes objects based on both tables.





Always check integrity after defining joins:




R esol v ing Loops

Loops can be resolved using:� Alias

� Context

Alias:

� An alias breaks a loop by using the same table twice in the same

query for different purpose. The alias is identical to the base

table with a different name. The data in the alias table is exactly

the same as the original table, but the different table namegenerates a modified SQL, referring to two tables and two sets

of joins and the loop is broken.




R esol v ing Loops: Alias

The two one ended joins are now separated as

follows:

� Country keeps a join to the Resort table.

� Country_Region is joined to the Region table.

The schema now appears as shown below:




R esol v ing Loops: Context

Context:

� A context resolves a loop by defining a set of joins that specifies one

specific path through tables in a loop. It ensures that joins are not

included from different paths within the same SQL query.

� You often use contexts in the schema that contain multiple fact tables(multiple stars) that share lookup tables.




R esol v ing Loops: Context (Contd.)

In the example, you can follow two different paths fromthe Customer table to the Service table:

For this path Designer detects these contexts

Reservations and

Reservation_Line

Reservation_Line

Sales and

Invoice_Line

Sales_Line





The Reservation_Line context appears below:





The Sales_Line context appears below:

You then create different sets of objects from the tables in the different

contexts.Users can then run either Reservation queries or Sales queries,

depending on the objects they select.




H ierar c hies

Object hierarchies can be created to perform multidimensional

analysis.

Multidimensional analysis is the analysis of dimension objects

organized in meaningful hierarchies.

Multidimensional analysis allows users to observe data from

various viewpoints. This enables them to spot trends or exceptions

in the data.

A hierarchy is an ordered series of related dimensions. An example

of a hierarchy is Geography, which may group dimensions such as

Country, Region, and City.




F un c tions

SQL editor is used to define the Select statement or a

Where clause for an object. Functions can be used in an

object definition.

Database functions, for example number, character, and date

functions.

@Functions specific to BO products.

The available @functions are:� @Aggregate Aware

� @Prompt� @Script

� @Select

� @Variable

� @Where




Conditions

A restriction is a condition in SQL that sets criteria to limit the datareturned by a query.

Two types of restrictions can be defined in a universe:

A condition object is a predefined Where clause that can be inserted into

theSelect statement inferred by objects in the Query pane.

Restriction type Description

Forced Restriction defined in the Where clause for an object. It cannot be

accessed by users and so cannot be overridden in WebIntelligence.

Optional Restriction defined in special condition objects that users can

choose to use or not use in a query. A condition object is a

predefined Where clause that can be inserted into the Select

statement inferred by objects in the Query pane.




Conditions (Contd.)

Using condition objects has the following advantages:

� Useful for complex or frequently used conditions

� Gives users the choice of applying the condition

� No need for multiple objects

� Condition objects do not change the view of the classes and objects in

the universe pane




Aggregate Awareness

52

Aggregate awareness is the ability of a universe to make use of aggregate tables in a database.

Aggregate Tables:

� Contains Pre-calculated data

� Speeds up the execution of queries, improving the performance of SQLtransactions.

Set up aggregate awareness as follows:

� Define the Select statement for an object using the @AggregateAware

function.

� Specify which objects are incompatible with the aggregate table and cannot be used in a query that is run against that table.




Deri v ed T ables

53

Derived tables are tables defined in the universe schema.

A derived table is defined by an SQL query at the universe level that can

be used as a logical table in Designer.

The columns returned by the queries to build objects can be used asthe columns of a normal database tables.




Deri v ed T ables ( Contd.)

Derived tables have the following advantages:

� Reduced amount of data returned to the document for analysis

Include complex calculations and functions in a derived table. These operations

are performed before the result set is returned to a document, which saves time

and reduces the need for complex analysis of large amounts of data at the report

level.

� Reduced maintenance of database summary tables

Derived tables can, in some cases, replace statistical tables that hold results for

complex calculations that are incorporated into the universe using aggregate

awareness. Derived tables can return the same data and provide real time data

analysis.

54





55

Steps for creating a derived table:a) Right-click the mouse over the Designer table schema and select

Derived Table from the menu to view the Derived Tables dialog

box. Or go to Insert->Derived Tables.





56

b) Type the derived table name in the TableN

ame box.c) Type the derived table SQL in the SQL Expression box or use the

objects in the Tables and Columns, Operators and Functions boxes to

build the SQL.





57

d) Click SQL to validate the SQL.

e) Click OK to close the dialog box. The derived table appears as a

regular table in the Designer table schema.

f) Use the columns in the derived table to build objects as you do

with a normal database table.

Derived tables can be edited or deleted.




Linking Uni v erses

58

Linked universes are universes that share common components

such as parameters, classes, objects, or joins.

An active universe can be linked to one or more universes.

Among linked universes, one universe is said to be the kernel

while the others are the derived universes.� The kernel universe represents a re-useable library of components.

� Derived universes may contain some or all of the components of the

kernel, in addition to any components that have been added to it.

While connecting an active universe to other universes, the active

universe becomes the derived universe, and the linked universebecomes the core universe.




Linking Uni v erses (Contd.)

59

Components from the core universe are inherited by

the derived universe.

To link a universe to a core universe, the core universe

must have been exported to the repository at least

once. Otherwise, DESIGNER does not allow the link.

Advantages of linking universes:

� Reduce development and maintenance time. While modifying a

component in the core universe, Designer propagates the

change to the same component in all the derived universes.

� Centralize often used components in a core universe, and then

include them in all new universes. No need to re-create

common components each time while creating a new universe.





To Link two universes:

� Goto Edit-> Links. Click the Add Link button.

6060

Shows the namesof the universes to

which the activeuniverse is linked.

Displays theUniverse to Link

dialog box fromwhich you can linkthe kernel universeto other universes.

Other than linkinguniverses, you caninclude the

components of thekernel universe in aderived universe.

Shows the user wholast modified the

universe.

It lets you indicate the

new location of thekernel universe if uhave changed itslocation.

Remove a link to akernel universe only if the active universe nolonger contains objectsbased on componentsor joins from the kerneluniverse.

Displays the path of the linked universe.





� TheUniverse to Link dialog box appears.

Browse to the universe to link.

� Click the Open button.

The selected universe appears in the list.

� Click OK. The link is created. The tables of this core universe

can be visible in the universe schema.

61

U




Se c uring Uni v erses

A restriction is a named group of constraints that apply to a universe.

When users connect to a universe, the objects, rows, query types, and

connection that they use in the universe are determined by their applied

restriction.

62

U ( d )




Se c uring Uni v erses (Contd.)

63

Apply restrictions to defined user and groups who use auniverse at two levels:

� CMC Level - From the Central Management Console set restrictions

that apply to universes stored in a CMS. Set what universes users

can access, and depending on the rights defined for a user group,

restrict viewing, editing, deleting, and other actions in a universe.� Universe Level - Define restrictions for users allowed to use a

universe. A group of restrictions that correspond to a user group is

called a restriction set. A restriction set can include object access,

row access, query and SQL generation controls, and connection

controls.

U ( d )





64

To create a restriction set

� Select Tools > Manage Security >Manage Access Restrictions.

The Manage Access Restrictions box appears.

U ( d )





65

� Click New. The Edit Restriction dialog box appears.

� Click on each tab in the dialogue box and apply the restrictions to be

imposed in the universe and click OK.

M i U i




M anaging Uni v erses

Reason for universe maintenance:� New tables added to database

� Tables deleted, changed, or renamed in database

� Users request new classes or objects

Note: Your pr imary concern when you introduce a new v ersion of a univ erse

shoul d be what e ff ect it will hav e on exist ing end user r e ports.

Tip: Do not d istr ibut e new v ersions of univ erses too fr equent l y!

M i U i (C d )




M anaging Uni v erses (Contd.)

Changes to target database:

� The implications of modifying the underlying database for BO universes

varies in severity:

No Impact:

Adding new columns

Adding new tables

Severe Impact:

Renaming/moving database

Changing existing column and

table names

Deleting tables and columns

M i U i (C d )





Detecting changes to database:

� Choose View > Refresh

Structure

� Changed tables are

highlighted in the Structure

� A message on the screenindicates that tables have

been deleted

M i U i ( d )





Effect of changes to existing objects:

� Changes to existing Objects in a universe can have an impact on existing

Documents created from the universe.

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

M i U i (C td )





Add tables to the universe structure

Delete existing contexts

Detect aliases

Detect contexts

Create new objects

Set up hierarchies

Insert joins and set cardinalities

Adding new tables to universe

� The workflow is:

O i f S it




Ov er v iew of Se c urity

Universe Level Security

�The users access to the data or some particular object in

the universe can be restricted in Universe level by the

Universe Developer.

Object/Class Level Restriction for all Users (hide theclass/object)

Object/Class Level Restriction for specified group or

Users (Object Level restriction)

To restrict data for a particular user / group (Row LevelRestriction)

71

BO Designer




BO Designer

Questions from participants

T t Y U d t di




T est Your Understanding

1. What are the steps to create a universe?

2. Define the different types of connection.

3. What are the different types of objects?

4. What is a loop and how do you resolve it?

5. List the types of @Functions.

C St d




Case Study

Create a universe with the given details.

Universe name - eFashion, Database connection- beach, Select tables

Customer, Resort, Region, Region_Sline, Service, and Sales.

Create a dimension object in class customer. Give object name as cust_name,

data type as Varchar2 and this should return customer name as <First

Name>space<Last Name>.

Insert a table Country to the universe schema and create a join between

Country table and Resort table on country_Id.

Create a derived table showing the number of regions in each country.

74

BO D ig r S ar




BO Designer: Summary

Designer is a BO component that is used to build universes. A universe is asemantic layer between database and end users.

A universe consists of a set of objects, grouped by classes.

A connection is a link from the universe to the target database.

There are three types of connection:

�

Personal� Shared

� Secured

The types of objects are:

� Dimension

�

Detail� Measure

BO Designer: Summary (Contd )




BO Designer: Summary (Contd.)

A join is a condition that restricts the result set of a query.

A loop is a set of joins that defines a closed path through a set of tables in a

schema.

Loops can be resolved using Alias or Context.

A hierarchy is an ordered series of related dimensions.

@Functions can be used in object definition in the Select statement or

Where clause.

A condition object is a predefined Where clause that can be inserted into

the Select statement inferred by objects in the Query pane.

BO Designer: Source



© 2007, Cognizant Technology Solutions Confidential77

BO Designer: Sour c e

http://help.sap.com/businessobject/product_guides/

Business Objects XI Release 2 Designers Guide

Disclaimer : Parts of the content of this course is based on the materials available from the Web sites and books

listed above. The materials that can be accessed from linked sites are not maintained by Cognizant Academy and

we are not responsible for the contents thereof. All trademarks, service marks, and trade names in this course are

the marks of the respective owner(s).



You have successfully completed

BO Designer

Click here to proceed