Business objects DESIGNER User Manual

Designer

Designer

Copyright

© 2008 Business Objects. All rights reserved. Business Objects owns the following
U.S. patents, which may cover products that are offered and licensed by Business
Objects: 5,555,403; 5,857,205; 6,289,352; 6,247,008; 6,490,593; 6,578,027;
6,831,668; 6,768,986; 6,772,409; 6,882,998; 7,139,766; 7,299,419; 7,194,465;
7,222,130; 7,181,440 and 7,181,435. Business Objects and the Business Objects
logo, BusinessObjects, Business Objects Crystal Vision, Business Process On
Demand, BusinessQuery, Crystal Analysis, Crystal Applications, Crystal Decisions,
Crystal Enterprise, Crystal Insider, Crystal Reports, Desktop Intelligence, Inxight,
the Inxight Logo, LinguistX, Star Tree, Table Lens, ThingFinder, Timewall, Let
there be light, Metify, NSite, Rapid Marts, RapidMarts, the Spectrum Design, Web
Intelligence, Workmail and Xcelsius are trademarks or registered trademarks in
the United States and/or other countries of Business Objects and/or affiliated
companies. All other names mentioned herein may be trademarks of their respective
owners.

Third-party
Contributors

Business Objects products in this release may contain redistributions of software
licensed from third-party contributors. Some of these individual components may
also be available under alternative licenses. A partial listing of third-party
contributors that have requested or permitted acknowledgments, as well as required
notices, can be found at: http://www.businessobjects.com/thirdparty

Contents

Introducing Designer 17Chapter 1

Overview....................................................................................................18

Designer and universe fundamentals........................................................18

How do you use Designer to create universes?........................................22

Who is the universe designer?..................................................................26

Introducing the universe development process.........................................27

Designer example materials......................................................................32

What is a universe?..............................................................................18

What is the role of a universe?.............................................................19

What does a universe contain?............................................................20

Viewing the universe window...............................................................22

How do objects generate SQL?...........................................................23

What types of database schema are supported?.................................24

How are universes used?.....................................................................24

Required skills and knowledge.............................................................26

What are the tasks of the universe designer?......................................27

Universe design methodology..............................................................27

Universe development cycle................................................................29

Optimizing universe planning and implementation time.......................31

Demonstration databases....................................................................32

Demonstration universes......................................................................32

Doing basic operations 35Chapter 2

Overview....................................................................................................36

Starting Designer.......................................................................................36

Starting Designer..................................................................................37

Designer 3

Contents

Using the Quick Design Wizard appropriately......................................39

Working with XI R2 connections and universes with Designer XI R3........41

Importing a universe..................................................................................42

Importing a universe from the repository..............................................42

What is the difference between opening and importing?.....................44

Opening a universe....................................................................................44

Exporting a universe..................................................................................45

How are universes organized on the repository file system?...............45

Exporting a universe to the repository..................................................46

What is the difference between exporting and saving?........................46

Saving a universe......................................................................................47

Universe file names as identifiers.........................................................47

Saving a universe.................................................................................48

Saving a universe definition as PDF....................................................48

Closing a universe.....................................................................................50

Creating a universe....................................................................................50

What are universe parameters?...........................................................50

Creating a new universe.......................................................................52

Setting universe parameters......................................................................54

Identifying the universe .......................................................................54

Defining and editing connections.........................................................56

Viewing and entering summary information.........................................72

Selecting strategies..............................................................................73

Indicating resource controls.................................................................80

Indicating SQL restrictions...................................................................83

Indicating options for linked universes.................................................86

Setting SQL generation parameters.....................................................86

Using the Designer user interface...........................................................106

The main components of the user interface.......................................107

The Designer user interface...............................................................108

Manipulating windows .......................................................................108

4 Designer

Contents

Using toolbars....................................................................................109

Performing an action or operation in Designer...................................110

Using Find and Replace..........................................................................112

Using Find..........................................................................................112

Using Quick Find................................................................................116

Organizing the table display....................................................................116

How are tables represented?.............................................................116

Manipulating tables............................................................................117

Using List mode..................................................................................118

Arranging tables automatically...........................................................120

Changing table display.......................................................................120

Selecting schema display options...........................................................123

Setting graphic options for the Structure pane display.......................124

Viewing table and column values.......................................................126

Viewing the number of rows in database tables.................................129

Printing a universe...................................................................................133

Setting print options............................................................................133

Creating a schema with tables and joins 137Chapter 3

Overview..................................................................................................138

What is a schema?..................................................................................138

Schema design is the basis for a successful universe.......................139

Schema design and the universe creation process...........................139

What are the stages of schema design?............................................140

Inserting tables........................................................................................140

Using the Table Browser....................................................................141

Arranging Tables in the Structure Pane.............................................145

Using derived tables................................................................................146

Adding, editing, and deleting derived tables......................................147

Nested derived tables..............................................................................150

Using the Derived Tables editor.........................................................150

Designer 5

Contents

To create a nested derived table........................................................151

Stored Procedure Universes...................................................................152

Stored procedures in Java bean universes........................................153

Creating a universe based on stored procedures..............................153

Defining joins...........................................................................................158

What is a join?....................................................................................158

Why use joins in a schema?...............................................................158

What SQL does a join Infer?..............................................................159

What tables do not have to be joined?...............................................160

Joining primary and foreign keys.......................................................161

Understanding the cardinaltity of a join..............................................163

Creating joins.....................................................................................163

Join properties....................................................................................169

Editing a join.......................................................................................172

ANSI 92 support for joins in a universe..............................................177

Deleting joins......................................................................................181

Defining specific types of joins................................................................182

Creating Equi-joins.............................................................................183

Theta joins..........................................................................................187

Outer joins..........................................................................................190

Shortcut joins......................................................................................196

Self restricting joins............................................................................198

Using cardinalities....................................................................................202

How are cardinalities used In Designer?............................................203

Setting cardinalities manually.............................................................205

Checking the universe.............................................................................213

Checking universe integrity automatically..........................................214

6 Designer

Resolving join problems in a schema 221Chapter 4

Overview..................................................................................................222

What is a join path problem?...................................................................222

Contents

What is a Lookup Table......................................................................223

What is a Fact Table...........................................................................223

What Types of Join Paths Return Incorrect Results?.........................223

Detecting and Solving Join Problems................................................224

Defining aliases.......................................................................................225

How are Aliases Used in a Schema?.................................................226

Creating Aliases.................................................................................227

Defining contexts.....................................................................................231

How are Contexts Used in a Schema?..............................................231

Creating a Context.............................................................................232

Editing a context.................................................................................236

Deleting a context...............................................................................237

Updating contexts...............................................................................238

Join Paths that Prevent Context Detection........................................238

How do Contexts Affect Queries?......................................................240

Resolving loops.......................................................................................244

What is a Loop?.................................................................................244

How Does a Loop Affect Queries?.....................................................245

Visually Identifying Loops...................................................................254

Automatically Identifying and Resolving Loops..................................255

Designer Features to Detect and Resolve loops................................255

Examples of Resolving Loops............................................................265

Resolving Chasm Traps..........................................................................276

What is a Chasm Trap?......................................................................276

How does a chasm trap inflate results?.............................................278

Detecting a Chasm Trap....................................................................280

Resolving a Chasm Trap....................................................................280

Resolving Fan Traps................................................................................283

What is a Fan Trap?...........................................................................283

How Do You Detect a Fan Trap?........................................................286

How Do You Resolve a Fan Trap?.....................................................286

Designer 7

Contents

Detecting join problems graphically.........................................................290

Potential chasm trap...........................................................................291

Potential fan trap................................................................................292

Checking the universe.............................................................................294

Checking Universe Integrity Automatically.........................................295

Checking Universe Integrity Manually................................................296

Refreshing the Universe Structure.....................................................299

Building universes 301Chapter 5

Overview..................................................................................................302

Introduction to universe building..............................................................302

What is an object?..............................................................................303

What types of objects are used in a universe?..................................304

What is a class?.................................................................................305

Using classes and objects..................................................................305

Using the Universe pane.........................................................................305

Displaying classes and objects or conditions.....................................306

Basic operations on classes, objects, and conditions.............................307

Cut, copy, paste..................................................................................307

Moving classes, objects, or conditions...............................................307

Showing or hiding classes, objects and conditions............................308

Defining classes.......................................................................................309

Creating a class..................................................................................309

Class properties.................................................................................312

Modifying a class................................................................................312

Using subclasses...............................................................................313

Defining objects.......................................................................................313

Creating an object..............................................................................314

Object properties................................................................................316

Modifying an object............................................................................317

Object definition..................................................................................317

8 Designer

Contents

Properties...........................................................................................320

Advanced...........................................................................................321

Defining index awareness..................................................................324

Source Information.............................................................................329

Using the SQL editor to define an object...........................................330

Defining an object format...................................................................333

Viewing the table used in an object definition....................................334

Defining a dimension..........................................................................335

Defining a detail..................................................................................335

Defining a measure............................................................................336

Defining restrictions for an object.......................................................343

Defining condition objects..................................................................348

Using self restricting joins to apply restrictions..................................355

Applying a restriction by inferring multiple tables...............................356

Concatenating objects........................................................................359

Defining hierarchies.................................................................................360

What is multidimensional analysis?...................................................361

How to identify a hierarchy.................................................................361

Setting up hierarchies.........................................................................362

Using cascading lists of values for hierarchies........................................366

Creating a cascading list of values.....................................................366

Using lists of values.................................................................................369

How is a list of values used?..............................................................370

Defining how a list of values is used with an object...........................371

List of values properties and options..................................................372

Editing a list of values.........................................................................378

Exporting a list of values....................................................................382

Refreshing values in a list of values...................................................386

Using data from a personal data file..................................................386

Administering lists of values in the universe......................................388

Optimizing and customizing LOV files................................................389

Designer 9

Contents

Creating a basic universe automatically..................................................390

Why use the Quick Design wizard?...................................................391

Using the Quick Design Wizard..........................................................391

Following up on a universe created with the Quick Design wizard.....399

Testing the universe.................................................................................400

Testing objects in the Query Panel.....................................................400

Testing the integrity of the universe ...................................................400

Testing the universe with Web Intelligence........................................400

Working with OLAP universes 403Chapter 6

What is an OLAP universe?.....................................................................404

What OLAP data sources can be used to create a universe? ................404

SAP BW data sources........................................................................405

MSAS features supported for OLAP universes..................................410

Essbase features supported for OLAP universes..............................411

Defining connections to OLAP data sources...........................................412

To start the New Connection wizard in Designer...............................413

To select database middleware for an OLAP connection...................413

To define login parameters for SAP BW connections........................414

Defining login parameters for MSAS connections .............................415

Defining login parameters for Essbase connections..........................416

To select source cube or query for OLAP connections......................417

To define configuration parameters for OLAP connections................418

Defining custom parameters for Essbase connections......................420

Customizing OLAP universes..................................................................420

Designer features supported for OLAP universes..............................420

Calculated measures in OLAP universes...........................................421

Pre-defined conditions in OLAP universes.........................................423

Optional prompts in OLAP universes.................................................426

To improve performance of certain queries on SAP BW universes....427

OLAP universe lifecycle management.....................................................427

10 Designer

Contents

To refresh an OLAP universe.............................................................428

How SAP BW objects are mapped and used in a universe.....................430

How SAP BW characteristics are mapped and used in a universe.....432

How SAP BW key figures are mapped and used in a universe.........432

How SAP BW hierarchies are mapped and used in a universe.........433

How SAP BW variables are mapped and used in a universe............434

How MSAS structures are mapped to universe components..................441

How Essbase cubes are mapped to universe components.....................443

Creating universes from metadata sources 445Chapter 7

Overview..................................................................................................446

Generating universes from metadata sources.........................................446

Universe creation overview................................................................446

Selecting a metadata source...................................................................447

XML metadata sources............................................................................450

Creating a universe from an XML metadata source...........................450

Updating a universe...........................................................................452

Exporting a universe to DB2CV..........................................................453

Exporting a universe to a DBCV2 XML file........................................454

Universe pre-requisites for export......................................................454

Identifying universe metadata............................................................455

Oracle Analytic Workspaces....................................................................463

Universe and view creation options...................................................464

Create a view and generate a universe.............................................464

Create a view only..............................................................................466

Generate a universe from an existing view........................................467

Mapping Oracle OLAP structures to universe components...............468

Optimizing universes 479Chapter 8

Overview..................................................................................................480

Designer 11

Contents

Using aggregate tables............................................................................480

What is aggregate awareness?..........................................................480

Applying aggregate awareness to data warehouses.........................481

Setting up aggregate awareness.......................................................482

Building the objects............................................................................483

Identifying all combinations of the aggregate objects........................484

Arranging objects in aggregate level order........................................484

Defining aggregate objects with the @Aggregate_Aware function.....484

Specifying the incompatible objects...................................................487

Specifying incompatible objects.........................................................490

Resolving loops involving aggregate tables.......................................493

Testing aggregate awareness............................................................496

Using @Functions...................................................................................496

Inserting an @function in an object....................................................498

@Aggregate_Aware...........................................................................500

@Prompt............................................................................................501

@Script...............................................................................................505

@Where.............................................................................................506

@Variable...........................................................................................508

@Select..............................................................................................511

Using external strategies to customize universe creation.......................513

Migrating external strategies to Designer XI......................................513

External strategies in Designer XI overview.......................................514

What is an external strategy?.............................................................515

Creating Help text for external strategies...........................................516

Verifying that the external strategy file is declared.............................518

Using example external strategies.....................................................519

How is the strategy file (STG) structured?.........................................519

The output formats of strategies.........................................................522

Creating an external strategy.............................................................526

Creating a text file for data.................................................................528

12 Designer

Contents

Applying external strategies in Designer............................................529

Using analytic functions...........................................................................530

What are analytic functions?..............................................................531

What are the advantages of using analytic functions?.......................531

Which analytic function families are supported?................................531

How are analytic functions used in Designer?...................................532

IBM DB2 UDB and Oracle..................................................................533

RedBrick (RISQL functions)...............................................................538

Teradata (OLAP functions).................................................................541

Inserting syntax automatically in Select statements...........................544

Managing universes 547Chapter 9

Overview..................................................................................................548

Multilingual universes..............................................................................548

Introduction to multilingual universes.................................................548

How multiple languages are used in universes..................................551

The Translation Manager...................................................................551

Deploying universes................................................................................552

Identifying a universe in the repository...............................................552

Setting access restrictions on a universe................................................553

What is a restriction?..........................................................................554

What restrictions can be applied in a universe?.................................555

How are access restrictions managed?.............................................556

Creating a restriction..........................................................................557

Applying universe access restrictions................................................561

Adding a user group to the list of available users for a universe........562

Setting restriction group priority..........................................................564

Viewing users and groups security restrictions..................................565

Working with multiple designers..............................................................566

Locking a universe.............................................................................566

Revision number................................................................................566

Designer 13

Contents

Linking universes.....................................................................................567

What are linked universes?................................................................567

Different ways to link universes..........................................................570

Advantages of linking universes.........................................................572

Requirements for linking universes....................................................572

Restrictions when linking universes...................................................573

Creating a link between two universes...............................................573

Editing a derived universe..................................................................577

Removing a link..................................................................................578

Relocating the core universe..............................................................578

Derived universes and lists of values.................................................579

Presenting objects in the order of the core universe..........................579

Including one universe within another.....................................................580

Copying a core universe into a derived universe...............................580

Managing users and logins......................................................................581

Managing logins.................................................................................581

Managing passwords.........................................................................582

Optimizing universes...............................................................................583

Optimizing the array fetch parameter.................................................583

Allocating table weights......................................................................584

Modifying the number of returned rows for a table.............................585

Using shortcut joins............................................................................586

14 Designer

Using the sample materials 587Chapter 10

Overview..................................................................................................588

The Club database..................................................................................588

The structure of the tables..................................................................588

Contents

Get More Help 597Appendix A

Index 601

Designer 15

Contents

16 Designer

Introducing Designer

1

Introducing Designer

1

Overview

Overview

This chapter gives you a general introduction to Designer, the tool you use
to build universes. It describes universes, what they contain, how they are
created, and the role that universes have in your business environment.

The typical universe development cycle is described, with best design
practices recommended. The demonstration databases and universes
shipped with this release are also described.

Note: $INSTALLDIR variable in this guide

In this guide the variable $INSTALLDIR is the install root path for the data
access files used by Designer and Web Intelligence. This is the Business
Objects installation path with the operating system sub directory that contains
the Designer executable and the data access drivers.

Under Windows$INSTALLDIR = \\...\Business Objects\BusinessObjects
Enterprise 12\win32_x86.

For example C:\Program Files\Business Objects\BusinessObjects Enterprise
12\win32_x86.

Designer and universe fundamentals

Business Objects Designer is a software tool that allows you to create
universes for Web Intelligence and Desktop Intelligence users.

What is a universe?

A universe is a file that contains the following:

Connection parameters for one or more database middleware.

•

SQL structures called objects that map to actual SQL structures in the

•

database such as columns, tables, and database functions. Objects are
grouped into classes. Objects and classes are both visible to Web
Intelligence users.

A schema of the tables and joins used in the database. Objects are built

•

from the database structures that you include in your schema. The schema

18 Designer

is only available to Designer users. It is not visible to Web Intelligence
and Desktop Intelligence users.

Web Intelligence users connect to a universe, and run queries against a
database. They can do data analysis and create reports using the objects
in a universe, without seeing, or having to know anything about, the underlying
data structures in the database.

What is the role of a universe?

The role of a universe is to provide an easy to use and understand interface
for non technical Web Intelligence users to run queries against a database
to create reports and perform data analysis.

As the universe designer, you use Designer to create objects that represent
database structures, for example columns and database functions, that users
need to access and query, to get the information necessary to meet their
business requirements.

The objects that you create in the universe must be relevant to the end user
business environment and vocabulary. Their role is to present a business
focussed front end to the SQL structures in the database.

Introducing Designer

Designer and universe fundamentals

1

The following diagram shows the role of objects as the mapping layer between
a database schema and the Query work area in Web Intelligence, that users
use to create queries to run against database tables.

Designer 19

Introducing Designer

1

Designer and universe fundamentals

What does a universe contain?

A universe contains the following structures:

Classes

•

Objects

•

Classes

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.

Objects

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 or Return on Investment.

Types of objects

In Designer, objects are qualified as one of three types: dimension, detail,
or measure.

Dimension

Detail

20 Designer

DescriptionObject type

Parameters for analysis. Dimensions
typically relate to a hierarchy such as
geography, product, or time. For exam­ple. Last Name and City_Id

Provide a description of a dimension,
but are not the focus for analysis. For
example. Phone Number

Designer and universe fundamentals

DescriptionObject type

Convey numeric information which is

Measure

used to quantify a dimension object.
For example, Sales Revenue

Objects infer SQL structures displayed in a schema

The objects that Web Intelligence users see in a universe infer SQL structures
that you have inserted into a database schema. You, as the universe
designer, create this schema based on the tables and joins that are required
to return the data, needed by users for their analysis and report creation.

The schema is a part of the universe file, but is only visible and accessible
in Designer. You create the schema in the Structure pane of the Universe
window. A schema is shown below for the sample universe Beach.unv.

Introducing Designer

1

How are objects presented in a universe?

Objects are displayed as nodes in an tree explorer view in the Universe pane.
You use the object explorer to create, delete, copy, view, and move classes
and objects. Each object type is shown below.

Designer 21

Introducing Designer

1

How do you use Designer to create universes?

Viewing the universe window

The Universe window in Designer is shown below. It contains both the
Universe pane (also visible in Web Intelligence) and the Structure pane
(visible only in Designer).

How do you use Designer to create
universes?

Designer provides a connection wizard that allows you to connect to your
database middleware. You can create multiple connections with Designer,

22 Designer

but only one connection can be defined for each universe. This database
connection is saved with the universe.

Designer provides a graphical interface that allows you to select and view
tables in a database. The database tables are represented as table symbols
in a schema diagram. You can use this interface to manipulate tables, create
joins that link the tables, create alias tables, contexts, and solve loops in
your schema. Web Intelligence users do not see this schema.

Designer provides an object explorer view. You use the explorer tree to
create objects that map to the columns and SQL structures that are
represented in the schema view. Web Intelligence users manipulate these
objects to run queries against a database.

Designer allows you to distribute universes by importing and exporting
universes to the Central Management System (CMS) repository.

How do objects generate SQL?

Web Intelligence users create queries by dragging objects into the Query
work area. The definition of each object infers a Select statement. When a
query is run, a Select statement and optional Where clause for all the objects
is run against the target database.

Introducing Designer

How do you use Designer to create universes?

1

When a user chooses to include dimension and/or detail objects with a
measure object in the Query work area, a Group By clause containing the
content of those dimension and detail objects is automatically added to the
Select statement.

Designer 23

Introducing Designer

1

How do you use Designer to create universes?

The tables that are included in the From clause and the Joins in the Where
clause, are inferred from the table schema that you build in the Structure
pane.

What types of database schema are supported?

Designer can support most types of database schema, including all those
shown below. You do not need to redefine or optimize your database before
using Designer.

How are universes used?

Universes are used by Web Intelligence users. The universes are stored in
the Central Management System (CMS) repository. An end user connects
to a universe from a web browser.

The connection to the database is defined in the universe, so by connecting
to the universe, the end user automatically has access to the data. The
access to data is in turn restricted by the objects that are available in the
universe. These objects have been created by you, the universe designer,
based on the user needs profile for a defined user group.

Representing a targeted data need

A universe can represent the data needs of any specific application, system,
or group of users. For example, a universe can contain objects that represent
the data needs of the Marketing or Accounting departments in a company.

24 Designer

Introducing Designer

How do you use Designer to create universes?

A universe can also represent the data needs of a section within a department
or any set of organized procedures such as a payroll or inventory system.

An example of the types of classes that could be used in a human resources
universe is shown below:

Examples of classes in the universe depicted above are Employee
Information, Attendance Information, and Department Information.

1

Universes and the database schema

The following example shows sections of a database schema that have been
used to create three universes; PERSONNEL, INVENTORY, and SALES.
Each universe contains classes and objects. Each object maps to a part of
the database structure. The SALES universe contains a class called
STATISTICS which contains two objects; Average Revenue and Total Profit.

Designer 25

Introducing Designer

1

Who is the universe designer?

Who uses universes?

Web Intelligence users use universes for reporting and analysis. The universe
should provide them with classes and objects relevant to their business
domain.

Who is the universe designer?

Universes are created by a universe designer using Designer. There is no
standard profile for a universe designer. Within a company, the person
designated as the universe designer may be the database administrator, an
applications manager or developer, a project manager, or a report creator
who has acquired enough technical skills to create universes for other users.

Universe design teams

There can be more than one universe designer in a company. The number
of universe designers depends on the company's data requirements. For
example, one universe designer could be appointed for each application,
project, department or functional area.

Required skills and knowledge

A universe designer should have the following skills and level of technical
knowledge:

Ability to analyze user needs

26 Designer

DescriptionSkill/Knowledge

Universes are created to meet a user
need for data. The universe designer
must have the skills to conduct user
needs analyses to create classes and
objects that are relevant to the user
vocabulary, and to develop universes
that meet the needs of the user commu­nity. These needs include report cre­ation and query results that are suitable
for analysis

Database knowledge

Introducing Designer

Introducing the universe development process

DescriptionSkill/Knowledge

Universe designer needs to have a
good working knowledge of the compa­ny's database management system
(DBMS), how the databases are de­ployed, the logical database structure,
and the type of data stored in company
databases

1

Stuctured Query Language (SQL)

A working knowledge of SQL is neces­sary

What are the tasks of the universe designer?

The universe designer is normally responsible for the following tasks:

Conducting user needs analysis

•

Designing and creating the universe

•

Distributing the universe

•

Maintaining the universe

•

Introducing the universe development
process

The following sections give an overview of how you manually create a
universe, and describe how universe creation fits into a typical universe
development cycle.

Universe design methodology

The universe design methodology described in this manual consists of one
planning stage, and three implementation phases:

Analysis of business problem and planning the universe solution

•

Designer 27

Introducing Designer

1

Introducing the universe development process

Designing a schema

•

Building the universe

•

Distributing the universe to users

•

Each implementation phase is based on an assumption that you have
completed an initial planning phase. The planning phase can be done without
using Designer, and is the decisive phase for the success or failure of your
universe. A poorly planned universe that is not based on a study of user
reporting needs will be difficult to design, implement, maintain, and will not
be useful to your target users.

Each of these phases is described as follows:

Plan the universe before you start using Designer

Before starting the first phase, you should spend up to eighty percent of the
time allotted for the universe creation project, planning the universe. You
should note the following points:

You must analyze the data analysis and reporting needs of the target

•

audience for the universe. The structures that you use to create the
schema should be based on a clearly defined user need to access the
data contained in those tables and columns.

You should have a clear idea of the objects that you need to create before

•

you start using Designer. Do not create objects by looking at the columns
available in the database, but identify columns that match an object that
you have already identified from your user needs analysis.

Designing a schema

You create a schema for the underlying database structure of your universe.
This schema includes the tables and columns of the target database and
the joins by which they are linked. You may need to resolve join problems
such as loops, chasm traps, and fan traps, which may occur in the structure
by using aliases or contexts. You test the integrity of the overall structure.
In this guide, the designing a schema phase is described in the chapters

Creating a schema with tables and joins on page 137 and Resolving join
problems in a schema on page 221.

28 Designer

Building the universe

You create the objects that infer Select statements based on the components
of your schema. You organize these objects into classes. These are objects
that you have identified from an analysis of user reporting needs. You can
create many types of objects to enhance user reporting capabilities,
multidimensional analysis, and optimize query performance.

You test the integrity of your universe structure. You should also perform
tests by running reports in Web Intelligence.

The building phase is described in the chapter Building universes on page 301.

Distributing the universe

You can distribute your universes to users for testing, and eventually for
production, by exporting them to the Crystal Management System (CMS)
repository. This phase is described in the chapter Managing universes on
page 547.

Introducing Designer

Introducing the universe development process

1

Universe development cycle

Universe development is a cyclic process which includes planning, designing,
building, distribution, and maintenance phases. You use Designer to design
and build a universe, however, the usability of any universe is directly related
to how successfully the other phases in the development cycle interact with
each other.

This section presents an overview of a universe design methodology that
you can use to plan and implement a universe development project.

The table below outlines the major phases in a typical universe development
cycle:

Designer 29

Introducing Designer

1

Introducing the universe development process

Prepare

Analyze

DescriptionDevelopment phase

Identify the target data source and

•

become familiar with its structure.

Know what data is contained

•

within each table of each of the
target databases.

Understand the joins.

•

Identify the cardinality.

•

Know what is possible.

•

Identify the user population and

•

how it is structured; for example
is the user group structured by
department or by task.

Identify what information the users

•

need.

Identify what standard reports

•

they require.

30 Designer

Plan

Implement

Familiarize yourself with their

•

business terminology so that you
can name objects sensibly.

Identify a project strategy. For example,
how many universes should be created
and which ones should have the capac­ity to be linked and to what level.

Build the universe using Designer.

•

This manual covers this part of
the universe development cycle,
the actual use of the design tool.

Test frequently during the build

•

process for validity and reliability
of inferred SQL.

Introducing Designer

Introducing the universe development process

DescriptionDevelopment phase

Form a small group of Web Intelligence
power users who have some knowl-

Test

Deploy

Evolve

Note: Universe design should always be driven primarily by user

requirements and NOT the data source structure.

edge of what information they expect
to get from the universe. Ask the users
to perform thorough tests simulating
live usage of the universe(s).

Distribute the universe by exporting
universe to the Crystal Management
System (CMS) repository, where it can
be accessed by end users.

Update and maintain the universe as
the data sources and user require­ments change and grow.

1

Optimizing universe planning and implementation time

The analysis of user requirements and design are the most important stages
in the process. Users must be heavily involved in the development process
if the universe is going to fulfil their needs both with the business language
used to name objects and the data that can be accessed.

Implementation will be very quick and easy if the first three stages are carried
out properly.

You can spend up to 80% of the time allocated to the development of a
universe on the first three stages:

Preparing

•

Analyzing

•

Planning

•

Designer 31

Introducing Designer

1

Designer example materials

If you have spent the time in the laying the foundation for your universe, the
other 20% of the time spent actually using Designer to build your universe
will be much more productive than if you have not spent the necessary time
in planning and analysis.

Designer example materials

The following samples are shipped with Designer:

Demonstration databases

Most of the examples in this guide are based on the Club database built with
Microsoft Access 2000. This database is used by the sales manager of the
fictitious business, Island Resorts, to perform sales and marketing analysis.
You can find the database file, Club.mdb, in the Databases subfolder in the
Business Objects installation path.

For more information on the structure of this database, refer to the appendix
at the back of this guide.

The efashion database is also shipped with this release. This MS Access
2000 database tracks 211 products (663 product color variations), sold over
13 stores (12 US, 1 in Canada), over 3 years.

The database contains:

A central fact table with 89,000 rows of sales information on a weekly

•

basis.

A second fact table containing promotions.

•

Two aggregate tables which were set up with aggregate navigation.

•

Demonstration universes

A complete demo universe called beach.unv is delivered in the Universes
subfolder of the Samples folder in the BusinessObjects installation path. It
was built with the Club database described above.

32 Designer

Introducing Designer

Designer example materials

You can use this universe to learn how to build specific objects and classes
with Designer.

Designer also comes with the efashion universe built using the efashion
database.

1

Designer 33

Introducing Designer

Designer example materials

1

34 Designer

Doing basic operations

2

Doing basic operations

2

Overview

Overview

This chapter describes the basic operations you perform in Designer to
create, modify, and update universes. The following topics are covered:

Starting Designer on page 36

•

Importing a universe on page 42

•

Opening a universe on page 44

•

Exporting a universe on page 45

•

Saving a universe on page 47

•

Creating a universe on page 50

•

Setting universe parameters on page 54

•

Using the Designer user interface on page 106

•

Using Find and Replace on page 112

•

Organizing the table display on page 116

•

Selecting schema display options on page 123

•

Printing a universe on page 133

•

Note: $INSTALLDIR variable in this guide

In this guide the variable $INSTALLDIR is the install root path for the data
access files used by Designer and Web Intelligence. This is the Business
Objects installation path with the operating system sub directory that contains
the Designer executable and the data access drivers.

Under Windows$INSTALLDIR = \\...\Business Objects\BusinessObjects
Enterprise 12.0\win32_x86.

For example C:\Program Files\Business Objects\BusinessObjects Enterprise

12.0\win32_x86.

Starting Designer

Designer can only be used with a Central Management System (CMS)
repository. You must log in to the repository before starting Designer.

36 Designer

Doing basic operations

Starting Designer

If you are starting Designer for the first time and want to work on an existing
universe, you need to open the universe directly first, save it with a secure
connection and export it to the repository. You then import the universe to
make updates and export updated versions. This ensures that the CMS and
the local universe versions are synchronized.

Once you start Designer you can open a universe in one of the following
ways:

Create a new universe

•

Import a universe from the CMS repository

•

Open a universe directly from the file system

•

A universe is only available to Web Intelligence users when it is exported to
the repository. Importing a universe, making changes, then exporting the
updated universe is the most common way of working with Designer. It
ensures that the CMS (repository) version is synchronized with the file version.

Note: You can save a universe to the file system. You do this when you

want to share the universe with other users who may not have a connection
rights to the target CMS. See the section Saving a universe on page 47 for
more information.

2

You start Designer from the task bar by clicking the Designer icon in the
group of installed Business Objects products for this release. You are
prompted to log into the CMS before Designer starts.

Starting Designer

To start Designer:

1. Click the Start button on the taskbar.

2. Point to the Programs menu.

3. Click the Designer program from the BusinessObjects command.

The login box for the CMS appears.

Designer 37

Doing basic operations

2

Starting Designer

4. Type the following information. This information is normally provided for

you by the BusinessObjects administrator.

DescriptionLogin information

Name of the CMS server.System

Your repository user name.User Name

Your repository password.Password

38 Designer

Your security levelAuthentication

5. Click the OK button.

The Designer startup screen appears, and an empty Designer session
opens.

The user name and CMS name appear in the title bar.

Doing basic operations

Starting Designer

Depending on options set for Designer, the Quick Start universe design
wizard can start automatically when you start Designer.Click Cancel to close
the wizard. For more information on disabling other wizard options, see the
section Disactivating the Quick Design wizard on page 40. If you want to
use the Quick Design wizard, then you can refer to the section Using the

Quick Design Wizard on page 391.

2

Using the Quick Design Wizard appropriately

When you start a Designer session for the first time, a Quick Design wizard
appears by default. You can use the wizard to quickly create a universe, or
to familiarize yourself with Designer, however, it is not an appropriate tool
for creating a complete universe that responds to end user reporting
requirements.

It is recommended that you disable the Quick Design wizard, and use it only
as a means to familiarize yourself with Designer, and not use it to design
universes. All the universe design, building, and maintenance information
and procedures in this manual assume that you have disabled the Quick
Design wizard, except for the chapter Using the Quick Design Wizard on
page 391 which deals specifically with using the wizard. For information on
disabling other Quick Design wizard options, see the section Disactivating

the Quick Design wizard on page 40.

Designer 39

Doing basic operations

2

Starting Designer

Disactivating the Quick Design wizard

When you first start a Designer session, a Quick Design wizard appears by
default. You can prevent the wizard appearing automatically when you create
a new universe as follows:

To disactivate the Quick Design wizard:

1. Select Tools > Options.

The Options dialog box opens to the General page.

2. Clear the Show Welcome Wizard check box. This check box is already

cleared if you have cleared the Run this Wizard at Startup check box from
the Startup Wizard Welcome page.

3. Clear the File/New Starts Quick Design Wizard check box.

4. Click OK.

You can activate the Quick Design Wizard at any time by selecting the above
check boxes from the General page of the Options dialog box. Using the
Quick Design wizard is covered in the section Using the Quick Design Wizard
on page 391.

Giving all users access to a universe

You can make a universe accessible to all Designer users in both workgroup
and enterprise mode, by saving a universe in workgroup mode. The
connection for the universe cannot be a secured connection. If you want to
make a universe available to all users, you must save the universe with an
unsecured connection.

To make a universe accessible to all Designer users:

1. Verify that the universe that you want to make available to all users does

not have a secured connection.

2. Secured connections are required to export universe to the CMS. If a

universe has a secured connection, select or create a new shared
connection. See the section Defining and editing connections on page
56 for more information.

3. Select File > Save As.

40 Designer

Doing basic operations

Working with XI R2 connections and universes with Designer XI R3

A File Save box appears.

4. Select the Save For All Users check box.

5. Click OK.

Working with XI R2 connections and

2

universes with Designer XI R3

In this release of Universe Designer, you can access a connection and open
or import a universe stored in a XI R2 CMS. When working with XI R2
universes and connections, you need to note the following points:

• DeskTop Intelligence XI R2 users can refresh documents created with

DeskTop Intelligence XI R3 based on XI R3 universes and XI R2
connections.

• DeskTop Intelligence XI R2 users can create documents based on XI R3

universes and XI R2 connections.

• If you want to edit and save the XI R2 connection, a warning message

appears informing you that if the connection is saved, it is saved as a XI
R3 connection, and XI R2 reports using that connection will not be able
to be refreshed.

• You can open XI R2 universes with XI R3 Universe Designer, but you

can not open a XI R3 universe with a prior version of Designer.

This interconnection ability between Desktop Intelligence XI R2 and XI R3
installations, allows administrators to upgrade servers while retaining DeskTop
Intelligence XI R2 and XI R3 clients connecting to the upgraded XI R3 servers.

Designer 41

Doing basic operations

2

Importing a universe

This is a temporary phase while the upgrade of a large client population is
managed.

Importing a universe

You can import one or more universes stored in a universe folder in the
repository. You can only import a universe that has already been exported
to the repository.

When you import a universe, the CMS checks the universe version on the
repository file system. If the version is identical, the universe is made available
to Designer. If the universe version on the repository file system is more
recent than the CMS version, a message box appears asking if you want to
replace the universe in the folder. If you answer Yes, then the universe on
the repository file system is replaced by the version in the CMS.

Importing a universe from the repository

To import a universe from the repository

1. Select File > Import.

The Import Universe dialog box appears.

42 Designer

Doing basic operations

Importing a universe

2

2. Select a universe folder from the drop down list box.

Or

Click the Browse button and select a universe using the folder browser.

You want to import a universe from this folder.

3. If you want to lock the universe, double-click the universe name.

A locked universe appears with a padlock symbol. To unlock a universe,
double-click it again.

4. Click a universe name.

This is the universe that you want to import.

5. Verify the file path for the import folder in the Import Folder box.

The universe is imported to this folder.

Designer 43

Doing basic operations

2

Opening a universe

6. Click OK.

What is the difference between opening and importing?

You can open a universe directly from the file system. When you save this
universe, it is saved only to the file system, it is not updated in the CMS.
Updates in this universe are not available to Web Intelligence users.

When you import a universe, the current version available in the repository
is made available to Designer. When you have finished modifying the
universe, you export it to the repository. The CMS is updated with the latest
changes.

Opening a universe

You open a universe using the menu commands or by clicking the Open
button. When you open a universe directly without importing, you are opening
a version on the local file system, that may not correspond to the latest
version in the CMS.

To open a universe directly

1. Select File > Open.

A File Open box opens to the directory designated as the default universe
file store. You can set this directory in the Save page of the Options dialog
box (Tools > Options > Save).

2. If necessary, browse to the directory that contains the universe file (.UNV).

3. Select a universe file and click Open

Or

Double click the universe file.

The Universe opens in the current Designer window.

44 Designer

Exporting a universe

You make a universe available to Web Intelligence users and other designers
by exporting a universe to the repository.

When you export a universe the universe is:

moved to the selected universe folder on the repository file system

•

and

created in the Central Management System (CMS).

•

Each time the universe is exported to the repository, the universe version in
the CMS is updated. This is the version that is available to Web Intelligence
users.

Note: Saving a universe is not the same as exporting a universe. Saving

updates the universe on the repository local file system, but not the CMS
repository version of the universe. See the section What is the difference

between exporting and saving? on page 46 for more information.

Doing basic operations

Exporting a universe

2

How are universes organized on the repository file system?

The repository stores universes on the local file system and in the CMS
server. You work on the universe version on the local file system. The local
file system is the server on which Designer is installed. Your universes are
saved by default in the universes folder in your user profile path as follows:

\\Documents and Settings\<user>\Application Data\Business Objects\Business
Objects 12.0\universes\@<repository name>\universe folder\<universe>.unv

The universes stored in the CMS server are used for version control. When
you export an updated universe to the repository, the updated universe is
copied to the CMS server.

Designer 45

Doing basic operations

2

Exporting a universe

Exporting a universe to the repository

To export a universe to the repository

1. Select File > Export.

The Export Universe dialog box appears.

2. Select a universe folder from the folder drop down list box.

Or

Click the Browse button and select a universe folder in the folder browser.

You want to export the universe to this folder.

3. If you want to lock the universe, double-click the universe name.

A locked universe appears with a padlock symbol. To unlock a universe,
double-click it again.

4. Click a group in the Groups list box. This is the user group that uses the

exported universe.

5. Click a universe in the Universes list box. The Universes list box shows

the names of the active universes.

6. If you want to export other universes that are not open, click the Add

Universe button, and then use the browser to select the other universes.

7. Click OK.

What is the difference between exporting and saving?

When you save a universe, you update the version in the repository file
system. This does not update the CMS version.

When you export a universe, the update of the version in the repository file
system is synchronized with the update of the universe in the CMS.

If you save a universe and do not export the updated version, the CMS is
not updated. The saved universe is not available to other users.

46 Designer

Each universe in the repository is assigned a system identifier. Refer to the
section Identifying a universe in the repository on page 552 for more
information in identifiers.

You cannot export a universe if it has been locked in the repository by another
designer.

You can export only a universe defined with a secured connection.

Saving a universe

You should regularly save your universes throughout a work session. When
you save a universe, Designer stores it as a file with a .UNV extension on
the local file system.

In Web Intelligence, a user identifies the universe by the universe name (long
name).

When you save a universe, the changes are not saved to the CMS. You
must export the universe to the CMS when you have completed updating a
universe.

Doing basic operations

Saving a universe

2

You can use the following maximum characters in the universe name (the
long name) and .unv file name:

Maximum number of charactersName type

100Universe name

Operating system maximum.unv name

Universe file names as identifiers

You should not change the universe filename .unv after reports have been
created based on that universe. If you change the filename, any report built
on the universe with the old name, will not point to the universe once its
name has been changed.

Designer 47

Doing basic operations

2

Saving a universe

Saving a universe

The universe name can be different from the .unv name.

When you use Save As to save the universe under new name, the new
universe is not associated in the CMS. You must export the new universe
to the CMS to create a version of the new universe.

You can use the following methods to save a universe:

To save a universe

1. Select File > Save from the menu bar

2. Click the Save icon

3. Press CTRL+S from the keyboard

Saving a universe definition as PDF

48 Designer

You save the universe information as an Adobe PDF file. You can save the
same information that you can print out for a universe. This information
includes:

General information: parameters, linked universes, and the graphical

•

table schema.

Component lists: lists of components in the universe including objects,

•

conditions, hierarchies, tables, joins, and contexts.

Component descriptions: descriptions for the objects, conditions,

•

hierarchies, tables, joins, and contexts in the universe.

You can select what components that you want to appear in the PDF from
the Print Options dialog box (Tools > Options > Print). These options are
described in the section Setting print options on page 133.

To save universe information as a PDF file:

1. Select File > Save As

2. Select portable Document Format (PDF) from the Save as type drop down

list box.

3. Click Save.

Setting default save options

Doing basic operations

Saving a universe

2

By default, Designer stores the files that you save in the Universe subfolder
in the Business Objects path. You can specify another default save folder
as follows:

1. Select Tools > Options.

The Options dialog box appears.

2. Click the Save tab.

The Save page appears.

3. Type a file path in the Default Universe Folder text box.

Or

4. Browse to a folder that contains .unv files.

5. If you want to specify an automatic save time, select the Save

Automatically check box and select or type a time period number from
the Minutes value select box.

6. Click OK.

Designer 49

Doing basic operations

2

Closing a universe

Closing a universe

You can use the following methods to close a universe.

To close a universe:

Select File Close from the menu bar

•

Click the close window button at the top right corner of the universe

•

window

Press CTRL+W from the keyboard.

•

Creating a universe

Before you can build a universe, you must firstly create a new universe file.

When you create a new universe file, you must define a connection parameter
to allow the universe to access your database middleware. You can also
define other parameters that determine how Designer creates objects, links
from the current universe to other universes, and query restrictions.

You save the new universe as a .unv file. The new universe contains no
classes and objects. You create these during the universe development
process by designing a table schema and then creating objects that map to
database structures.

What are universe parameters?

Universe parameters are definitions and restrictions that you define for a
universe that identify a universe and its database connections, specify the
type of queries that can be run using the universe, and set the controls on
the use of system resources.

You define universe parameters from the Universe Parameters dialog box
(File > Parameters) when you create a universe. The database connection
is the only parameter that you must manually select or create when you
create a new universe.

You can modify these parameters at any time.You can define the following
universe parameters:

50 Designer

Definition

Summary information

Strategies

Doing basic operations

Creating a universe

DescriptionParameter

Universe name, description, and con­nection parameters and information.
These are the parameters that identify
the universe. Refer to the section

Identifying the universe on page 54

for information on defining and modify­ing this parameter.

Version and revision information, de­signer comments, and universe statis­tics. Refer to the section Viewing and

entering summary information on page

72 for information on defining and
modifying this parameter.

Indicates the strategies used by the
universe. A strategy is a script used to
extract structural information from a
database. Refer to the section Select-

ing strategies on page 73 for informa-

tion on defining and modifying this pa­rameter.

2

Controls

SQL

Links

Indicates the limitations set for the use
of system resources. Refer to the sec­tion Indicating resource controls on
page 80 for information on defining and
modifying this parameter.

Indicates the types of queries that the
end user is allowed to run from the
Query pane. Refer to the section Indi-

cating SQL restrictions on page 83 for

information on defining and modifying
this parameter.

Indicates the settings defined for linked
universes. Refer to the section Indicat-

ing options for linked universes on

page 86 for information on defining and
modifying this parameter.

Designer 51

Doing basic operations

2

Creating a universe

Creating a new universe

The following procedure describes how you can create a new universe from
scratch by defining universe parameters then saving the universe. The
procedure provides an overview of all the pages available from the
Parameters dialog box.

For more detailed information on each step you should refer to the respective
section for the parameter in this chapter.

Defining all the parameters at universe creation may not be necessary. You
must select a connection, but you can accept the default values for other
parameters, and then modify them as appropriate when necessary.

Creating a new universe from scratch

To create a new universe from scratch:

1. Select File > New.

52 Designer

The Universe parameters dialog box opens to the Definition page. See
the section Identifying the universe on page 54 for information on this
page.

Note: When you select any option for a universe parameter, the option

Click here to choose stored procedure universe is greyed out. It cannot

be selected or deselected. To change the type of universe you want to
create, click Cancel and start again.

Type a name and description for the universe.

•

Select a connection from the Connection drop-down list box.

•

Or

Click the New button if you want to define a new connection that is

•

not listed in the drop-down list. See the section Defining and editing

connections on page 56 for information on defining a new connection.

2. Click the Summary tab.

The Summary page appears. See the section Viewing and entering

summary information on page 72 for information on this page.

Doing basic operations

Creating a universe

Type universe information in the Comments box.

•

3. Click the Strategies tab.

The Strategies page appears. It displays the strategies available for your
connected data source. See the section Selecting strategies on page 73
for information on this page.

Select a strategy from each of the Objects, Joins, and Tables

•

drop-down list boxes.

Depending on the RDBMS for the connection, there can be more than
one strategy available from each drop-down list box.

4. Click the Controls tab.

The Controls page appears. See the section Indicating resource controls
on page 80 for information on this page.

Select or clear check boxes in the Query Limits group box.

•

Enter values for the check boxes that you select.

•

5. Click the SQL tab.

2

The SQL page appears. See the Indicating SQL restrictions on page 83
for information on this page.

Select or clear check boxes as appropriate.

•

6. Click the Links tab, if you want to link the new universe with an existing

universe.

The Links page appears. See the section Indicating options for linked

universes on page 86 for information on this page.

Click the Add Link button to select a universe to link with the new

•

universe.

7. Click the Parameters tab.

The Parameters page appears. It lists SQL parameters that can be set
to optimize SQL generation. See the section Setting SQL generation

parameters on page 86 for information on this page.

8. Click OK.

The universe and structure panes open up in Designer

Designer 53

Doing basic operations

2

Setting universe parameters

9. Select File > Save.

Type a name for the universe file.

•

Click Save.

•

Setting universe parameters

You can set universe parameters for the following purposes:

Identifying the universe on page 54

•

Defining and editing connections on page 56

•

Viewing and entering summary information on page 72

•

Selecting strategies on page 73

•

Indicating resource controls on page 80

•

Indicating SQL restrictions on page 83

•

Indicating options for linked universes on page 86

•

Setting SQL generation parameters on page 86

•

Each type of parameter is contained on a page in the Parameters dialog box
(File > Parameters). Each group of parameters is described in its respective
section below.

Identifying the universe

Each universe is identified by the following parameters:

File name (8 characters)

Long name (35 characters)

54 Designer

Used byIdentifier

File system, and Web Intelligence to
reference the universe.

Web Intelligence users. Web Intelli­gence

Web Intelligence users.Description

Doing basic operations

Setting universe parameters

Used byIdentifier

CMS to identify universe. This number

Unique numeric ID

is assigned to the universe when it is
first exported to the CMS.

The name and description parameters are defined at universe creation from
the Definition page of the Universe Parameters dialog box. You can modify
the universe identification parameters at any time.

You also define the database connection from this page.

For information on defining a new connection, you can refer to the section

Defining and editing connections on page 56.

You can define the following identification parameters for a universe:

DescriptionIdentification parameter

Universe name. Identifies the universe
to Web Intelligence users. The name

Name

characters supported by the registry
are defined by the General Supervisor.
Character support is RDBMS depen­dent.

2

Description

Connection

Description of universe purpose and
contents. Optional field. This descrip­tion is viewable by Web Intelligence
users, so information in this field can
provide useful information about the
role of the universe.

Named set of parameters that defines
how Web Intelligence accesses data
in a database file. All available connec­tions appear in the Connections drop­down list box. You can also create new
connections.

Designer 55

Doing basic operations

2

Setting universe parameters

Modifying universe identification parameters

To modify universe identification parameters:

1. Select File > Parameters.

Or

Click the Universe Parameters button in the toolbar.

The Universe Parameters dialog box opens to the Definition page.

2. Type a name and a description.

3. Select a connection from the Connection drop-down list box.

4. Click the Test button to verify that the connection is valid.

If you receive a message informing you that the server is not responding,
the connection is not valid. You can correct connection parameters by
clicking the Edit button and editing connection properties. If the error
persists, refer to the section of the RDBMS documentation relating to
error messages.

5. Click OK.

Defining and editing connections

A connection is a named set of parameters that defines how a Business
Objects application accesses data in a database file. A connection links Web
Intelligence to your middleware. You must have a connection to access data.

You must select or create a connection when you create a universe. You
can modify, delete, or replace the connection at any time.

Note: See the Data Access Guide for complete information on creating,

modifying, and optimizing connections

You can create a new connection from the Definition page of the Universe
Parameters dialog box (File > Parameters > Definition). You create a new
connection when there is not an existing connection appropriate to the current
universe. You can also edit the properties for a connection from the Definition
page.

56 Designer

You can view all connections available to a universe from the Connections
list (Tools > Connections). You can delete, edit, and create new connections
from this page.

A connection contains three elements:

Data Access driver

•

Connection and login parameters

•

Connection type

•

Each element is described in the following sections:

Data Access driver

A Data Access driver is the software layer that connects a universe to your
middleware.

Data Access drivers are shipped with Business Objects products. There is
a Data Access driver for each supported middleware. When you install
Designer, your Data Access key determines which Data Access drivers are
installed.

Doing basic operations

Setting universe parameters

2

When you create a new connection, you select the appropriate Data Access
driver for the RDBMS middleware that you use to connect to the target
RDBMS.

Connection and login parameters

You configure the Data Access driver by specifying the following connection
and login parameters.

Type

DescriptionParameter

Type of connection, personal, shared,
or secured.

Identifying name for the connection.Name

Designer 57

Doing basic operations

2

Setting universe parameters

User name

Password

Use Single Sign-On when refreshing
reports at view time

Use database credentials associated
with BusinessObjects user account

DescriptionParameter

Your database user name. This is nor­mally assigned to you by the database
administrator.

Your database password. This is nor­mally assigned to you by the database
administrator.

When selected, the user name and
password used to access the CMS are
automatically used as database login
parameters. See the BusinessObjects
Enterprise Administrator's Guide for
information on setting up Single Sign­On.

When selected the user is forced to
enter a database user password asso­ciated with their BusinessObjects ac­count to refresh a report. This is set at
the Central Management Console level.
Refer to BusinessObjects Enterprise
Administrator's Guide for information
on setting up this option.

58 Designer

Data source or database name. If you
are using an ODBC driver the data

Data source/Service

Note: You can create connections through ODBC to Excel files, and to text

source name identifies the target
database. If you are using a native
driver, the database name identifies
the target database.

files in .csv format. In order that Web Intelligence can use a universe based
on a text file or an Excel file accessed through ODCB, you must edit the
msjet.prm file for the connection.

This file is located in the folder: $INSTALLDIR$/BusinessObjects Enter

prise 12.0/win32_x86/dataAccess/connectionserver/odbc where

$INSTALLDIR$ is the directory in which your Business Objects applications

are installed. In the msjet.prm file, change the DB_TYPE parameter as follows:

From: <Parameter Name='DB_TYPE'>MS Jet Engine</Parameter>

To: <Parameter Name='DB_TYPE'>MS Jet</Parameter>

You must stop and restart the Business Objects Enterprise server after
making this change. Note: If you are running Designer on the same machine
as your Web Intelligence server and you want to create additional universes
based on text or Excel files after changing this value, you must reset the
value to <Parameter Name='DB_TYPE'>MS Jet Engine</Parameter>

Connection type

The type of connection determines who can use the connection to access
data. Designer automatically stores all the connections that you create during
a work session. The next time you launch a session, these connections will
be available to you.

You can create three types of connections with Designer:

Personal

•

Shared

•

Secured

•

Doing basic operations

Setting universe parameters

2

Each connection type is described as follows:

Personal connections

Restricts access to data to the universe creator and the computer on which
it was created.

Connection parameters are stored in the PDAC.LSI file located in the LSI
folder in the Business Objects 12.0 folder in you user profile directory, for
example:

C:\Documents and Settings\<user name>\Application Data\Business
Objects\Business Objects 12.0\lsi

These parameters are static and cannot be updated.

Designer 59

Doing basic operations

2

Setting universe parameters

Personal connections are unsecured in terms of Business Objects products
security.

You do not use personal connections to distribute universes. You could use
personal connections to access personal data on a local machine.

Shared connections

Allows access to data for all users. These connections are unsecured in
terms of Business Objects products security.

Connection parameters are stored in the SDAC.LSI file located in the LSI
folder in the Business Objects 12.0 folder in you user profile directory, for
example:

C:\Documents and Settings\<user name>\Application Data\Business
Objects\Business Objects 12.0\lsi

Secured connections

Centralizes and controls access to data. It is the safest type of connection,

•

and should used be to protect access to sensitive data.

You can create secured connections with Designer.

•

You must use secured connections if you want to distribute universes

•

through the CMS.

Secured connections can be used and updated at any time.

•

Setting passwords with personal and shared connections

You can set a password on any universe that has a personal or shared
connection type. Using passwords, you can protect the universe from
unauthorized users in an environment without a repository.

Note: If you forget a password, you can not recover the universe file. You

should keep a backup file of universe passwords.

There are two different options available for the password you can set:

Protection Password causes a dialog box to appear; it simply prompts

•

the user to enter the password. If the password is correct, the universe
is opened.

60 Designer

Doing basic operations

Setting universe parameters

Write Reservation Password causes the following dialog box to appear:

•

The user can then open the universe in read only mode, or in read-write
mode by entering the correct password.

To set a password when using personal or shared connections:

1. Select Tools > Options

The Options dialog box appears.

2. Click the Save tab.

The Save page appears.

2

3. Type a pass word in the Protection Password or the Write Reservation

Password text boxes. You can enter up to 40 alphanumeric characters.

4. Click OK.

Designer 61

Doing basic operations

2

Setting universe parameters

Accessing the database after the user DBPass has changed

The BusinessObjects administrator can let a BusinessObjects user login
(name and password) continue to access data after the database user
password has been changed.

When the following parameters are set, a BusinessObjects user can continue
to access data without contacting the BusinessObjects administrator, after
the database password has been changed:

In the Central Management Console, the Enable and update user's

•

Data Source Credentials at logon time check box must be selected.

In Designer, on the Define a new connection page of the New Connection

•

wizard, the Use Database Credentials associated with

BusinessObjects user account and the Use Single Sign On when
refreshing reports at view time check boxes must be selected.

When the check boxes described above are selected, the updated DBUser
and DBPass parameters are automatically associated with the
BusinessObjects user account.

Note: DBUser and DBPass are static parameters, and must be updated in

the Central Management Console. If the Database password is changed, it
is recommended that the password is updated for each user account in the
Central Management Console.

Defining a new connection

You can define a new database connection using the New Connection wizard.
You access the wizard from:

Definition page of the Universe Parameters dialog box (File > Parameters>

•

Definition). You normally define a new connection when there is not an
existing connection available for the data that the universe needs to
access.

Connections list (Tools > Connections).

•

You can use the connection wizard to set advanced and custom parameters
for a connection. Refer to the Data Access Guide for complete information
on creating, editing, and optimizing connections.

62 Designer

When you create the connection from the Universe Parameters dialog box,
the Universe Parameters dialog box appears with the new connection listed
in the Connection box.

When you create the connection from the Connections dialog box, the
Connections appears. the new connection is now in the list.

Related Topics

• Using the New Connection Wizard on page 63

Using the New Connection Wizard

The New Connection wizard steps you through the process of creating a
connection. Use it to select a target data source, and configure the data
access driver.

Use the New Connection wizard to set the following parameters:

Doing basic operations

Setting universe parameters

DescriptionConnection wizard stage

2

Database middleware

Login parameters

Configuration parameters

Custom parameters

Locate the database middleware to
connect to, and assign a name for the
connection.

Set the connection mode, and specify
the login details for connecting to the
middleware.

Configure connection parameters, for
example the connection timeout details,
array fetch and bind sizes, and login
time-out. These vary depending on the
database to which you are connecting.

Customize parameters to optimize your
connection. These vary depending on
the database to which you are connect­ing.

Designer 63

Doing basic operations

2

Setting universe parameters

Note: The parameters that the New Connection wizard displays vary

depending on the options that you select, for example the database
middleware, and the connection mode.

Related Topics

• Database Middleware Selection dialog box on page 65

• Login Parameters dialog box on page 66

• Configuration parameters dialog box on page 67

• Custom Parameters dialog box on page 69

Creating a new connection

You use the New Connection wizard to create a data access connection.
The wizard displays a series of screens that you use to enter your connection
configuration details.

The choices that are available in each screen can vary depending on the
Business Objects application that you are using, and the database to which
you are connecting.

The Database middleware selection dialog box lists the available

•

database vendors, databases, and middleware. Locate the database you
want, and expand the database node to locate the driver that you want
to use.

64 Designer

Use the Login parameters dialog box to enter the login and connection

•

information for the database. These details vary depending on the driver
that you selected.

On this page you can also test the connection. If the test fails, check the
credentials that you entered, or check with your database administrator.

Use the Configuration parameters dialog box to configure the

•

parameters that control the connection.

Use the Custom Parameters dialog box to configure any specific custom

•

parameters that apply to the connection. This page is available only for
specific connections.

Values on the Custom Parameters dialog box should only be modified
by an advanced user, database administrator, or Business Objects
administrator. This page contains parameters that can be used to override
the default connection settings. You would typically adjust these
parameters to resolve performance issues.

The Connections List dialog box lists the available connections. New

•

connection that you define appear on this list . To test your connection,
select the connection and click the Test button. A message appears,
confirming that the connection is operating. If an error occurs, edit the
connection to re-configure the parameters.

Related Topics

• Database Middleware Selection dialog box on page 65

• Login Parameters dialog box on page 66

• Configuration parameters dialog box on page 67

• Custom Parameters dialog box on page 69

Database Middleware Selection dialog box

The New Connection wizard's Database Middleware Selection dialog box
contains the following settings:

Connection Type: determines who can use the connection to access

•

data. The options are explained in detail below.

Connection Name: enter a name for the connection.

•

Filter Stored Procedures Network Layers: select this check box to

•

display only those datasources that support procedures stored in the
database to deliver data. In order to use a JavaBean datasource, you
must select this checkbox since a JavaBean datasource uses procedures
stored in the JavaBean. If you do not select this checkbox, any installed
JavaBean drivers do not appear in the selection list.

Doing basic operations

Setting universe parameters

2

A list of the available data access drivers: you expand list items and select

•

the driver that you want.

You can create three types of connections with Designer:

Personal

•

Shared

•

Secured

•

Personal connections

This connection type restricts data access to the universe creator, and the
computer on which it was created. You can use personal connections to

Designer 65

Doing basic operations

2

Setting universe parameters

access personal data on a local machine only. You cannot use personal
connections to distribute universes.

Connection parameters are stored in the PDAC.LSI file located in the LSI
folder in the Business Objects 12.0 folder in your user profile directory, for
example:

C:\Documents and Settings\<user name>\Application Data\Business
Objects\Business Objects 12.0\lsi

These parameters are static and cannot be updated. Personal connections
are unsecured when used with Business Objects products security.

Shared connections

This connection type allows access to data for all users. These connections
are unsecured when used with Business Objects products security.

Connection parameters are stored in the SDAC.LSI file located in the LSI
folder in the Business Objects 12.0 folder in your user profile directory, for
example:

C:\Documents and Settings\<user name>\Application Data\Business
Objects\Business Objects 12.0\lsi

Secured connections

This connection type centralizes and controls access to data. This is the
safest type of connection, and should be used to control access to sensitive
data.

You can create secured connections with Designer. You must use secured
connections if you want to distribute universes through the CMS. Secured
connection details are stored in the CMS.

Secured connections can be used and updated at any time.

Login Parameters dialog box

The New Connection wizard's Login Parameters dialog box can contain
the following parameters:

66 Designer

Authentication mode

Doing basic operations

Setting universe parameters

DescriptionParameter

Use specified user name and pass-

•

word: uses the login details as authenti­cation.

Use Business Objects credential map-

•

ping: the user is prompted for a database
user password associated with their
BusinessObjects account to refresh a re­port. This is set using the parameters
dbuser and dbpass. These are set at the
administrative level. Refer to Business
Objects Enterprise Administrator's Guide
for information on setting up this option.

Use Single Sign On when refreshing

•

reports at View Time: When selected,
the username and password used to ac­cess the CMS are automatically used as
the database login parameters. See the
Business Objects Enterprise Administra­tion guide for informtaion on setting Single
Sign-On (SSO).

2

User name

Password

Configuration parameters dialog box

The Configuration Parameters dialog box contains parameters that you
can set to override default configuration options. These configuration
parameters override:

Any corresponding parameters set in the cs.cfg file

•

Your database user name. This is normally as­signed to you by the database administrator.

Your database password. This is normally as­signed to you by the database administrator.

The data source details.Datasource (<Host>:<port>):

The database name.Database

Designer 67

Doing basic operations

2

Setting universe parameters

Any corresponding parameters set in the <driver>.sbo file.

•

You can set the following parameters:

From the drop down list, select the method to

Connection pool mode

Pool timeout

Array fetch size

use to keep the connection active. Only neces­sary if using a connection pool.

If you select Keep the pool active for in the
previous field, specifies the length of time to
keep the connection open.

Enter the maximum number of rows authorized
with each fetch.

If you enter 20, and your query retrieves 100
rows, the connection executes 5 fetches to re­trieve your data.

If you enter 1, array fetch is deactivated and
data is retrieved row by row.

Note: Deactivating array fetch is the safest way

of retrieving your data but row-by-row retrieval
slows down server performance. The greater
the value in the Array fetch size option, the
faster your rows are retrieved. You must, how­ever, ensure you have adequate client system
memory.

68 Designer

Array bind size

The default value is 20.

Enter the size of the bind array that Connection
Server uses before sending to the repository.
The bind array is the area in memory where
Connection Server stores a batch of data to be
loaded (sent to the repository). When the bind
array fills, it is transmitted to the database.
Generally, the larger the bind array, the more
rows (n) can be loaded in one operation, and
the better your performance.

Login timeout

Custom Parameters dialog box

Use this dialog box to configure any custom configurations that apply to the
connection. The parameters that appear depend on the other parameters
that you have set. The parameters set here override:

Any corresponding parameters set in the cs.cfg file

•

Any corresponding parameters set in the <driver>.sbo file.

•

For information on the parameters, refer to the SBO file parameter reference
information.

Viewing available connections

Doing basic operations

Setting universe parameters

Specifies the number of seconds before a con­nection attempt times out and an error message
is displayed.

2

You can view all available stored connections in the Connections list. You
can edit existing connections, and create new connections.

To view available connections:

1. Select Tools > Connections.

The Connections list appears. It displays all the connections available to
the current universe.

Designer 69

Doing basic operations

2

Setting universe parameters

2. Click Cancel to close the dialog box.

You can edit connections from the Connections dialog box.

You can edit a secured connection only if you are working in online mode.
Personal and Shared connections can be modified in any mode.

You cannot modify the name of an existing connection.

Editing a connection

To edit a connection:

1. Select Tools > Connections.

The Connections list appears.

2. Click a connection name in the list of available connections.

3. Click the Edit button.

The Login page for the connection appears.

70 Designer

4. Type a new data source, or database name in the Data Source or Service

box if required.

5. Type modifications to login parameters as required.

6. Click Next.

The Perform a Test page appears.

7. Click the Test Data Source button to verify the modified connection.

8. Click Next to move to the Advanced and Custom pages. You can modify

parameters as required. You can also accept the default or existing values.

9. Click Finish from the Custom page to apply the changes to the connection.

Deleting a connection

You can delete connections from the Connections list. You can delete a
secured connection only if you are working in online mode. Personal and
Shared connections can be deleted in any mode.

Doing basic operations

Setting universe parameters

2

To delete a connection:

1. Select Tools > Connections.

The Connections list appears.

2. Select a connection name in the list.

3. Click the Remove button.

A confirmation box appears.

4. Click Yes.

The connection is removed from the list.

Adding a new connection

You can add a new connection from the Connections page by selecting
Select Tools > Connections, clicking the Add button, and following the Define
a new connection wizard. Full Instructions for following the connection wizard
are in the section Adding a new connection on page 71.

Designer 71

Doing basic operations

2

Setting universe parameters

Viewing and entering summary information

The Summary page displays universe administration information. You can
use this information to help you keep track of the development of the active
universe.

The Summary page displays the following information:

DescriptionInformation

Created

Modified

Revision

Comments

Statistics

Universe creation date and the name
of the creator.

Date of last modification and the name
of the modifier.

Revision number which indicates the
number of times the universe has been
exported to the CMS.

Information about universe for yourself
or another designer. This information
is only available in Designer. You
should include information about the
universe for users in the Description
field on the Identification page.

List of the number of classes, objects,
tables, aliases, joins, contexts, and hi­erarchies contained in the universe.

Viewing and modifying summary information

72 Designer

To view and modify summary information:

1. Select File > Parameters.

Or

Click the Parameters tool.

The Universe parameters dialog box appears.

2. Click the Summary tab.

The Summary page appears.

Doing basic operations

Setting universe parameters

2

3. Type a comment in the Comment text box.

4. Click OK.

Selecting strategies

A strategy is a script that automatically extracts structural information from
a database or flat file. Strategies have two principle roles:

Automatic join and cardinality detection (Join strategies)

•

Automatic class, object, and join creation (Objects and Joins strategies)

•

Strategies can be useful if you want to automate the detection and creation
of structures in your universe based on the SQL structures in the database.

Note: Strategies that automate the creation of universe structures are not

necessarily an essential part of universe design and creation. They can be

Designer 73

Doing basic operations

2

Setting universe parameters

useful if you are creating a universe quickly, allowing you to use metadata
information that already exists in a database or database design tool.
However, if you are building a universe by creating objects and joins that are
based on relationships that come directly from a user needs analysis, then
you will probably not use the automatic creation possibilities that strategies
offer.

In Designer you can specify two types of strategies:

DescriptionStrategy

Default strategy shipped with Designer.

Built in strategy

External strategy

Built in strategies can not be cus­tomized.

User defined script that contains the
same type of information as a Built in
strategy, but customized to optimize
information retrieval from a database.

Selecting a strategy

To select a strategy:

1. Select File > Parameters.

Or

Click the Parameters tool.

The Universe parameters dialog box appears.

2. Click the Strategies tab.

The Strategies page appears.

74 Designer

Doing basic operations

Setting universe parameters

3. Select a strategy from the Objects, Joins, or Tables drop-down list boxes.

2

4. Click OK.

Using built-in strategies

Built-in strategies are default strategies that are shipped with Designer. There
are built-in strategies for all supported databases. These cannot be modified.
Built-in strategies appear by default before external strategies in the strategy
drop-down lists.

You can use built-in strategies for the following purposes:

Objects

Used for...Strategy

Automatic creation of default classes
and objects when tables are created in
the table schema.*

Designer 75

Doing basic operations

2

Setting universe parameters

Joins

Used for...Strategy

Automatic extraction of default

•

joins when tables are created in
the table schema.*

Automatic insertion of cardinality

•

at join creation.*

Automatic detection of joins in ta-

•

ble schema. When you select
Tools > Automated Detection >
Detect Joins, Designer uses the
strategy to automatically detect
candidate joins. You can choose
to implement the joins or not.

Automatic detection and insertion

•

of cardinalities for existing joins in
the table schema. When you se­lect Tools > Automated Detection
> Detect Cardinalities, Designer
uses the strategy to detect cardi­nalities for joins selected in the
table schema.

76 Designer

Tables

* These automatic creation uses for strategies must be activated from the
Database page of the Options dialog box.

Using the Objects strategy

The Objects strategies are used only for creating classes and objects
automatically when you add a table to the table schema. To use this strategy
you must activate it from the Database page of the Options dialog box. For
more details see the section Using the automatic creation functions of a

strategy on page 77.

Filtering information available for tables
in the table browser.

Doing basic operations

Setting universe parameters

Using the Joins strategy

The selected Joins strategy determines how Designer automatically detects
cardinalities and joins in your table schema.

Depending on your database, there can be one or more Join strategies in
the list. For example, when using Oracle databases, you can specify a Join
strategy to automatically detect joins based either on matching column names,
or matching column number names.

If you do not select a strategy, Designer uses the default Joins strategy which
matches columns names to detect joins. The use of the selected join strategy
to detect joins does not have to be activated. The strategy is always used
when you choose to detect the joins or cardinalities in your table schema.

The Joins strategy is also used to automatically create joins and implement
cardinality when joins are created. To use the automatic default creation
functions of this strategy you must activate it from the Database page of the
Options dialog box. For more details see the section Using the automatic

creation functions of a strategy on page 77.

Using the Tables strategy

2

The selected table strategy reads the structure of database tables. Depending
on the strategy, the strategy could determine what sort of information is
shown in the table browser. For example column data types and descriptions.

Using the automatic creation functions of a strategy

The automatic creation and insertion functions of strategies are not activated
by default. To use these functions, you must select the Default Creation
check box that corresponds to the strategy that you want to apply at object
or join creation. These are listed on the Database page of the Options dialog
box (Tools > Options > database) shown below.

Designer 77

Doing basic operations

2

Setting universe parameters

Each default creation option on the Database page is described as follows:

When selectedWhen clearedOption

Joins must be created
manually. If you select
Tools > Automated Detec-

Retrieves tables with the
joins that link them ac­cording to the selected
Join strategy.

Extract joins with tables

tion > Detect Joins, then
Designer uses the strate­gy to detect joins and
proposes candidate joins.
You can choose to imple­ment the candidate joins
or not.

78 Designer

Detect cardinalities in
joins

Cardinalities must be
manually defined. If you
select Tools > Automated
Detection > Detect Cardi­nalities, then Designer
uses the strategy to de­tect and implement cardi­nalities for selected joins.

Detects and implements
the cardinalities inherent
in the joins at join cre­ation.

Classes and objects must
be created manually, ei-

ther by creating directly
Create default classes
and objects from tables

To select default creation options for strategies:

1. Select Tools > Options

The Options dialog box appears.

in the Universe pane, or

by dragging a table or

column from the Struc-

ture pane to the Universe

pane.

Doing basic operations

Setting universe parameters

When selectedWhen clearedOption

Default classes and ob­jects are created in the
Universe pane automati­cally when a table is
added to the Structure
pane. A class corre­sponds to the table name,
and objects correspond
to column names. It re­places all underscore
characters (_) with
spaces

2

2. Click the Database Tab.

The Database page appears.

3. Select the check box that corresponds to the default creation function for

which you want to use the strategy.

4. Click OK.

Setting the number of rows to be viewed

From the Database Options dialog box, you can also indicate the maximum
number of rows to be viewed from each table of the database. You can not
restrict the physical number of rows retrieved from the database, but this
modifies the default value for the number of rows that can be viewed at any
one time when you view table or column values. This only applies to the rows
returned in Designer, and not for queries run in Web Intelligence.

To set the number of rows that can be viewed:

Designer 79

Doing basic operations

2

Setting universe parameters

Enter a value in the text box of the Maximum Number of Rows Fetched

•

option. You can also click one or more times on the up or down arrow to
increase or decrease the default value (100).

Using external strategies

An external strategy is a user defined SQL script that follows a defined output
structure to perform customized automatic universe creation tasks. External
strategies are stored in an external XML strategy file (<RDBMS>.STG). SQL
scripts in this file appear in the drop down list on the Strategies page with
the other strategies.

External strategies contain the same type of information as the built-in
strategies, but are often customized to allow Designer to retrieve a specific
type of database information, or to optimize how information is retrieved from
the database.

For complete information on defining external strategies, see the section

Using external strategies to customize universe creation on page 513.

Indicating resource controls

Designer offers a number of options that let you control the use of system
resources.

You can specify the following limitations on system resources:

Limit size of result set to a specified
value

80 Designer

DescriptionQuery Limits

The number of rows that are returned
in a query are limited to the number
that you specify. This limits the number
of rows returned to Web Intelligence,
but does not restrict the RDBMS from
processing all rows in the query. It only
limits the number once the RDBMS has
started to send rows.

Limit execution time to a specified val­ue

Limit size of long text objects to a
specified value

Doing basic operations

Setting universe parameters

DescriptionQuery Limits

Query execution time is limited to the
number of minutes that you specify.
See the section Limiting execution time

for queries generating more than one
SQL statement on page 82 for more

details on this option.
This limits the time that data is sent to

Web Intelligence, but does not stop the
process on the database.

You specify the maximum number of
characters for long text objects.

Note: When this check box is not select­ed, the parameter is not activated. It is
automatically set to the default maxi­mum value (1000). To ensure that you
allow results larger than the default, the
check box must be selected, and a
value entered.

2

Entering resource control information

To enter resource control information:

1. Select File > Parameters.

or

Click the Parameters tool.

The Universe parameters dialog box appears.

2. Click the Controls tab.

The Controls page appears.

Designer 81

Doing basic operations

2

Setting universe parameters

3. Select a check box in the Query Limits group box.

82 Designer

4. Type a value in the text box that corresponds to the selected Query Limit

option. You can click the up and down arrows at the end of the text boxes
to increase or decrease the value entered.

5. Click OK.

Limiting execution time for queries generating more than one SQL statement

The time limit that you specify for query execution is the total execution time
for a query. If the query contains multiple SQL statements, then each
statement is given an execution time equal to the total query execution time
divided by the number of statements, so each statement in the query has
the same execution time.

If one statement requires a lot more time than others to run, it may not
complete, as its execution time will not correspond to its allotted execution
time within the query.

When you specify an execution time limit for multiple SQL statements, you
need to take into account the normal execution time of the single statement
that takes the longest time to run, and multiply this value by the number of
statements in the query.

Indicating SQL restrictions

You can set controls on the types of queries that end users can formulate
from the Query pane in Web Intelligence.

You can indicate controls for the following areas of query generation:

Use of subqueries, operators, and complex operands in individual queries.

•

Generation of multiple SQL statements.

•

Prevent or warn about the occurrence of a cartesian product.

•

Each of these sets of controls is described in the following sections:

Query controls

You can set the following controls for individual queries:

Doing basic operations

Setting universe parameters

DescriptionOption

2

Allow use of union, intersect and minus
operators

Multiple SQL statements controls

You can set the following controls to determine how multiple SQL statements
are handled:

Multiple SQL statements for each con­text

Enables end users to combine queries
using data set operators (union, inter­sect, and minus) to obtain one set of
results.

DescriptionOption

Enables end users to create queries
that contain multiple SQL statements
when using a context. Select this option
if you have any contexts in the uni­verse.

Designer 83

Doing basic operations

2

Setting universe parameters

Multiple SQL statements for each
measure

Allow selection of multiple contexts

DescriptionOption

Splits SQL into several statements
whenever a query includes measure
objects derived from columns in differ­ent tables. See the section Using Multi-

ple SQL Statements for Each Measure

on page 282 for more information on
using this option.

If the measure objects are based on
columns in the same table, then the
SQL is not split, even if this option is
checked.

Enables end users to create queries
on objects in more than one context
and to generate one set of results from
multiple contexts.

If you are using contexts to resolve
loops, chasm traps, fan traps, or any
other join path problems, then you
should clear this check box.

Cartesian product controls

A Cartesian product is a result set which contains all the possible
combinations of each row in each table included in a query. A Cartesian
product is almost always an incorrect result.

You can set the following controls for the production of a Cartesian product.

Prevent

Warn

84 Designer

DescriptionOption

When selected, no query that results
in a cartesian product is executed.

When selected, a warning message
informs the end user that the query
would result in a Cartesian product.

Entering SQL restriction options

To enter SQL restriction options:

1. Select File > Parameters.

Or

Click the Parameters tool.

The Universe parameters dialog box appears.

2. Click the SQL tab.

The SQL page appears.

Doing basic operations

Setting universe parameters

2

3. Select or clear options in the Query and Multiple Paths group boxes.

4. Select a radio button in the Cartesian Product group box.

5. Click OK.

Designer 85

Doing basic operations

2

Setting universe parameters

Indicating options for linked universes

The Links tab is used with dynamically linked universes, a subject covered
in the Managing universes on page 547 chapter.

Setting SQL generation parameters

In Designer, you can dynamically configure certain SQL parameters that are
common to most RDBMS to optimize the SQL generated in Web Intelligence
products using the universe.

Using parameter (PRM) files in previous versions of Designer

In versions prior to Designer 6.5, the SQL generation parameters used by a
universe were maintained and edited in a separate file called a parameters
(PRM) file. The values set in the PRM file applied to all universes using the
associated data access driver defined for a connection.

Many of the SQL parameters that are used to optimize query generation are
now controlled within an individual universe file. The PRM file is now no
longer used for the query generation parameters that you can set in Designer.
PRM files are still used for parameters that are database specific.

Note: See the Data Access Guide for more information on the PRM file for

your data access driver. You can access this guide by selecting Help > Data
Access Guide.

Setting the SQL parameters dynamically in Designer

Many of the parameters common to most supported RDBMS middleware
are available for editing in the Parameters tab in the universe parameters
dialog box (File > Parameters > Parameter).

These parameters apply only to the active universe, and are saved in the
UNV file. When you modify an SQL parameter for a universe in Designer,
the value defined in Designer is used, and not the value defined in the PRM
file associated with the data access driver for the connection.

86 Designer

Editing SQL generation parameters

You can modify the values for SQL parameters that determine SQL
generation in products using the universe.

To edit SQL generation parameters:

1. Select File > Parameters.

The Parameters dialog box appears.

2. Click the Parameter tab.

The Parameter page appears.

Doing basic operations

Setting universe parameters

2

3. Edit, add, or remove parameters as follows:

Designer 87

Doing basic operations

2

Setting universe parameters

Add a new parameter

Change name or value

then do the following:To...

Click any parameter in the list.

•

Type a name in the Name box

•

Type a value in the Value box.

•

Click Add.

•

The new value appears at the

•

bottom of the list

Click a parameter in the list.

•

Type a new name in the Name

•

box

Type a new value in the Value

•

box.

Click Replace.

•

The value is replaced by the
new definition.

Delete a parameter

4. Click OK.

Note: The SQL generation parameter values that you set in a universe, are

only available to products using that universe.

Universe SQL parameters reference

This section provides an alphabetical reference for the SQL generation
parameters listed in the Parameter page of the Universe Parameters dialog
box in Designer. These are SQL parameters that are common to most data
access drivers. Each parameter is valid for the universe in which it is set.

88 Designer

Click the parameter that you

•

want to remove from the list.

Click Delete.

•

Doing basic operations

Setting universe parameters

Other RDBMS specific and connection parameters are listed in the data
access parameter (PRM) file for the target data access driver. Refer to the
Data Access guide for a reference to the parameters in the PRM file.

ANSI92

ANSI92 = Yes|No

Yes|NoValues

NoDefault

Specifies whether the SQL generated
complies to the ANSI92 standard.

Yes: Enables the SQL generation

Description

compliant to ANSI92 standard.

No: SQL generation behaves according

to the PRM parameter OUT
ER_JOIN_GENERATION.

2

AUTO_UPDATE_QUERY

AUTO_UPDATE_QUERY = Yes|No

Description

BLOB_COMPARISON

BLOB_COMPARISON = Yes|No

Yes|NoValues

YesDefault

Determines what happens when an
object in a query is not available to a
user profile.

Yes: Query is updated and the object

is removed from the query.

No: Object is kept in the query.

Designer 89

Doing basic operations

2

Setting universe parameters

Description

Yes|NoValues

NoDefault

NoCan be edited?

Species if a query can be generated
with a DISTINCT statement when a
BLOB file is used in the SELECT state­ment. It is related to the setting No

Duplicate Row in the query proper-

ties.

Yes: The DISTINCT statement can be

used within the query.

No: The DISTINCT statement cannot

be used within the query even if the
query setting No Duplicate Row is
on.

90 Designer

BOUNDARY_WEIGHT_TABLE

BOUNDARY_WEIGHT_TABLE = Integer 32bits [0-9]

Integer 32bits [0-9]Values

-1Default

Description

Limitations

Doing basic operations

Setting universe parameters

Allows you to optimize the FROM clause
when tables have many rows.

If the table size is greater than the en­tered value, the table is declared as a
subquery:

FROM (SELECT col1,

col2,......, coln, ,...., FROM

Table_Name WHERE simple condi
tion).

A simple condition is defined as not
having a subquery, and not having EX

CEPT or BOTH operators.

Optimization is not implemented when:

the operator OR is in the query

•

condition

only one table is involved in the

•

SQL

the query contains an outer join

•

2

COLUMNS_SORT

COLUMNS_SORT = Yes|No

Values

Default

no condition is defined on the ta-

•

ble that is being optimized

the table being optimized is a de-

•

rived table.

Yes/No

No

Designer 91

Doing basic operations

2

Setting universe parameters

Determines the order that columns are
displayed in tables in the Structure
pane.

Description

COMBINE_WITHOUT_PARENTHESIS

Yes: Columns are displayed in alpha-

betical order

No: Columns are displayed in the order

they were retrieved from the database

COMBINE_WITHOUT_PARENTHESIS = No

Values

Default

Description

COMBINED_WITH_SYNCHRO

Yes/No

No

Specifies whether or not to encapsulate
a query with parentheses when it con­tains UNION, INTERSECT or MINUS op­erators. Used with RedBrick.

Yes Removes the parentheses.

No Leaves the parentheses.

COMBINED_WITH_SYNCHRO = Yes|No

Values

Default

Yes|No

No

92 Designer

Description

Doing basic operations

Setting universe parameters

Specifies whether to allow a query to
execute that contains UNION, INTER

SECTION, or EXCEPT operators, and

whose objects in each subquery are
incompatible.

Yes: Specifies that you do allow a

query to execute that contains UNION,

INTERSECTION and EXCEPT operators,

and whose objects in each subquery
are incompatible. This type of query
generates synchronization (two blocks
in the report).

No: Specifies that you do not allow a

query to execute that contains UNION,

INTERSECTION and EXCEPT operators,

and whose objects in each subquery
are incompatible. When the query is
executed the following error message
is displayed: "This query is too com­plex. One of the subqueries contains
incompatible objects." This is the de­fault value.

2

COMPARE_CONTEXTS_WITH_JOINS

COMPARE_CONTEXTS_WITH_JOINS = Yes|No

Values

Default

Description

Yes|No

No

Specifies how contexts are compared.

Yes: The system verifies that the con-

texts give the same joins.

No: The system verifies that the con-

texts give the same sets of tables. This
is the default value.

Designer 93

Doing basic operations

2

Setting universe parameters

CORE_ORDER_PRIORITY

CORE_ORDER_PRIORITY = Yes|No

Values

Default

Description

CORRECT_AGGREGATED_CONDITIONS_IF_DRILL

Yes|No

No

Specifies in which order you want
classes and objects to be organized
once two or more universes are linked
in Designer.

Yes: Specifies that classes and objects

follow the order defined in the kernel
universe.

No: Specifies that classes and objects

follow the order defined in the derived
universe. This is the default value.

CORRECT_AGGREGATED_CONDITIONS_IF_DRILL = Yes|No

Values

Default

Yes|No

No

94 Designer

Description

Specifies whether Web Intelligence can
aggregate measures in queries and
conditions.

Yes: Web Intelligence can aggregate

measures separately in the main query
and the condition, if the query is drill
enabled.

No: Web Intelligence cannot aggregate

measures separately in the main query
and the condition, if the query is drill
enabled.

CUMULATIVE_OBJECT_WHERE

CUMULATIVE_OBJECT_WHERE = Yes|No

Doing basic operations

Setting universe parameters

2

Values

Default

Description

Yes|No

No

Specifies the order of WHERE clauses
that have the AND connective.

Yes: Specifies that WHERE clauses that

have the AND connective are set at the
end of the condition.

No: Specifies that WHERE clauses follow
standard SQL syntax.

Example:

If the condition is find all French clients
different from John or American cities
different from New York, the SQL is
then:

WHERE

(customer.first_name <> 'John')

OR (city.city <> 'New York')

AND customer_country.country =

'France'

AND city_country.country = 'USA'

DECIMAL_COMMA

DECIMAL_COMMA = Yes|No

Values

Default

Yes|No

No

Designer 95

Doing basic operations

2

Setting universe parameters

Specifies that Business Objects prod­ucts insert a comma as a decimal sep­arator when necessary.

Yes: Business Objects products insert

Description

DISTINCT_VALUES

a comma as a decimal separator when
necessary.

No: Business Objects products do not

insert a comma as a decimal separator.
This is the default value.

DISTINCT_VALUES = GROUPBY|DISTINCT

Values

Default

Description

END_SQL

END_SQL = String

GROUPBY|DISTINCT

DISTINCT

Specifies whether SQL is generated
with a DISTINCT or GROUP BY clause
in a list of values and Query pane when
the option "Do not retrieve duplicate
rows" is active.

DISTINCT: The SQL is generated with

a DISTINCT clause, for example;

SELECT DISTINCT cust_name FROM
Customers

GROUPBY: The SQL is generated with

a GROUP BY clause, for example;

SELECT cust_name FROM Customers
GROUP BY cust_name

StringValues

96 Designer

<empty string>Default

The statement specified in this param-

Description

Example

EVAL_WITHOUT_PARENTHESIS

EVAL_WITHOUT_PARENTHESIS = Yes|No

eter is added at the end of each SQL
statement.

For IBM DB2 databases, you can use
the following:

END_SQL=FOR SELECT ONLY

The server will read blocks of data
much faster.

Doing basic operations

Setting universe parameters

2

Values

Default

Yes|No

No

Designer 97

Doing basic operations

2

Setting universe parameters

Description

By default, the function @Se

lect(Class\object) is replaced by the
SELECT statement for the object

<Class\object> enclosed within brack­ets.

For example, when combining two

@Select statements, @Select(objet1)

*@select(objet2).

If the SQL(object1) = A-B and
SQL(object2) =C,

then the operation is (A-B) * (C).

You avoid the default adding of brack­ets by setting EVAL_WITHOUT_PAREN

THESIS = Yes. The operation is then

A - B * C.

Yes: Brackets are removed from the
SELECT statement for a function @Se
lect(Class\object)

No: Brackets are added around the

Select statement for the function @Se

lect(Class\object).

98 Designer

FILTER_IN_FROM

FILTER_IN_FROM = Yes|No

Values

Default

Yes|No

No

Determines if query conditions are in­cluded in the FROM Clause. This setting
is only applicable if the other universe
parameter setting ANSI92 is set to

Yes.

Yes: When editing an outer join, the

default behavior property selected in

Description

FIRST_LOCAL_CLASS_PRIORITY

the drop down list box of the Advanced
Join properties dialog box in Designer,
is set to "All objects in FROM".

No: When editing an outer join, the de-

fault behavior property selected in the
drop down list box of the Advanced
Join properties dialog box in Designer
is set to "No object in FROM".

FIRST_LOCAL_CLASS_PRIORITY = Yes|No

Doing basic operations

Setting universe parameters

2

Values

Default

Description

FORCE_SORTED_LOV

FORCE_SORTED_LOV = Yes|No

Values

Yes|No

No

Only taken into account when

CORE_ORDER_PRIORITY=Yes.

Yes: Classes in derived universe are

placed first.

No: Objects and sub classes from de-

rived universe appear after those of the
core universe.

Yes|No

Designer 99

Doing basic operations

2

Setting universe parameters

Default

Description

INNERJOIN_IN_WHERE

INNERJOIN_IN_WHERE = Yes|No

Values

Default

Description

No

Retrieves a list of values that is sorted.

Yes: Specifies that the list of values is

sorted.

No: Specifies that the list of values is

not sorted.

Yes|No

You must manually enter the parameter
to activate it.

Allows you to force the system to gen­erate SQL syntax with all the inner joins
in the WHERE clause when ANSI92 is
set to yes . This is only possible if a
query contains only inner joins (Does
not contain FULL OUTER, RIGHT
OUTER, or LEFT OUTER joins).

Yes: If ANSI92 is set to yes, the system

generates ANSI92 join syntax in the

FROM clause except when the query

contains only inner joins. In this case,
the inner joins go into the WHERE
clause.

No: If ANSI92 is set to Yes, the system

generates ANSI 92 join syntax in the

FROM clause.

100 Designer

JOIN_BY_SQL

JOIN_BY_SQL = Yes|No