Business objects UNIVERSE BUILDER User Manual

BusinessObjects Universe Builder Guide

© 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

2008-03-05

Introduction 5Chapter 1

Overview of creating universes from metadata sources..............................6

What is Universe Builder?...........................................................................6

Universe Builder and Designer....................................................................6

When do you use Universe Builder instead of Designer?...........................7

Universe Builder basics 9Chapter 2

Starting Universe Builder...........................................................................10

Building universes from XML metadata sources 11Chapter 3

Overview of how you create a universe from XML metadata....................12

XML metadata to universe component mapping.......................................13

Selecting a XML data source.....................................................................13

Creating a universe from a XML data source............................................14

Viewing the trace log.................................................................................17

Setting trace log options............................................................................17

Universe and connection options for a universe built from a XML

source...................................................................................................15

Updating XML metadata source universes 19Chapter 4

Updating a universe from a XML metadata source...................................20

Exporting a universe to a XML file format 23Chapter 5

Overview of exporting universes to XML file formats................................24

Universe pre-requirements for export to DB2 Cube Views........................24

BusinessObjects Universe Builder Guide 3

Contents

Exporting a universe to DB2 Cube Views formats.....................................25

How to identify non-matching universe metadata......................................26

Mapping specific SQL expressions...........................................................28

How universe structures are mapped to DB2 Cube Views metadata........30

Universe to cube model........................................................................30

Class to dimension...............................................................................31

Fact table to Fact..................................................................................31

Measure to measure............................................................................32

Dimension and detail object to attribute...............................................33

Dimension and detail relationship to attribute relationship...................33

Default hierarchy to hierarchy..............................................................34

Custom hierarchy to hierarchy.............................................................34

Join to join............................................................................................34

Building universes from Oracle Analytic Services 37Chapter 6

Overview of how you create a universe from Oracle Analytic Services.....38

How is a universe generated from a OLAP cube?....................................38

Analyzing the relational view.....................................................................39

What are the shortcut joins in the universe used for?...............................40

How are Oracle OLAP structures mapped to universe components?.......42

How to create a Oracle OLAP universe.....................................................48

Creating a view and generating a universe..........................................49

Create a view only................................................................................51

Creating Oracle OLAP universe from an existing view........................52

Get More Help 53Appendix A

Index 57

4 BusinessObjects Universe Builder Guide

Introduction

Overview of creating universes from metadata sources

BusinessObjects XI Release 3 is installed with two products that you can use to create universes from metadata sources and Oracle Analytic Services:

DescriptionProduct

From Designer, you can create universes automatically from metadata sources by selecting File > Metadata Exchange.

Designer

Universe Builder

A universe creation wizard allows you to connect to a metadata source, then generate a universe. This process is described in the Designer Online Help and in the Designer’s Guide.

Stand alone product installed with BusinessObjects XI Release 3 that allows you to connect to a metadata source, select objects to include in a universe, and then generate the universe in Designer. This product is described in this guide.

What is Universe Builder?

Universe Builder is Business Objects software that allows you to create universes from XML metadata sources and Oracle Analytic Services.

Once you start Universe Builder, you connect to a metadata source, then using a universe creation wizard, you map metadata structures to equivalent classes, objects, dimensions, and details used in a standard BusinessObjects universe. You then generate the universe.

Universe Builder and Designer

You can create universes from metadata sources with both Universe Builder and Designer.

6 BusinessObjects Universe Builder Guide

When do you use Universe Builder instead of Designer?

Universe Builder is a stand alone product that provides an independent user interface for universe creation from metadata sources.

Designer is used for the universe generation at the end of the creation process.

The universe creation mechanism for each supported metadata source is a software plugin to Designer. There is a metadata source plugin for each supported metadata source.

The metadata plugins can be updated by Business Objects. If the plugin for a metadata source is updated, then you can install the updated plugin and use the new capabilities from Universe Builder, but the updated plugin is not available from Designer.

When do you use Universe Builder instead of Designer?

Given that you can use either Designer or Universe Builder to create a universe from metadata source, why use Universe Builder and when do you use it?

Introduction

Universe Builder is an independent product. You can update the metadata plugins used to generate universes when newer versions are available from Business Objects.

Designer can not be updated with new or updated metadata exchange plugins. The metadata source support is fixed at the level of the installed version.

You use either Designer or Universe Builder based on the following:

• You use Designer to create a universe from a metadata source supported

at the time of this release.

• You use Universe Builder to create a universe from a metadata source

plugin that has been updated, or to use a plugin for a newly supported metadata source.

BusinessObjects Universe Builder Guide 7

Introduction

When do you use Universe Builder instead of Designer?

8 BusinessObjects Universe Builder Guide

Universe Builder basics

Starting Universe Builder

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

Once you start Universe Builder, the Universe Builder pane appears. All the metadata sources supported in this BusinessObjects release are listed as icons in the Bridges column to the left of the Universe Builder pane.

Each metadata source also appears as a tab in the central part of the pane.

You can activate a metadata source by clicking either its icon to the left, or clicking its tab.

1. Select Universe Builder from the BusinessObjects group of products in

the Start menu.

The login box for the CMS appears.

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

you by the BusinessObjects administrator.

3. Click the OK button.

The Universe Builder pane appears.

10 BusinessObjects Universe Builder Guide

DescriptionLogin information

Name of the CMS server.System

Your repository user name.User Name

Your repository password.Password

Your security levelAuthentication

Building universes from XML metadata sources

Overview of how you create a universe from XML metadata

You can create or update a universe from XML files conforming to the following data source standards:

• Common Warehouse Model (CWM Relational 1.0

• Common Warehouse Model OLAP (CWM OLAP)

• Oracle Warehouse Builder (Oracle WB)

• Data Integrator

• IBM DB2 Data Warehouse Center (IBM DB2 DWC)

• IBM DB2 Cube Views

You can also export a universe to a IBM DB2 Cube View XML format file.

You create a universe by following a metadata source wizard, that allows you to connect to a target XML file. You can then select the cube associated with the XML metadata source. You then start a creation wizard that allows you to select cube tables to include in the universe. You select a connection, set creation options and generate the universe.

The universe creation process is the same for all XML metadata sources.

The universe creation process for a Oracle Analytical Workspaces datasource is different. Once you have selected your connection, a universe creation panel specific to Oracle Analytic Workspaces appears. You create a view on the database, then create a universe from the view. This is described in the chapter Building universes from Oracle Analytic Services.

Once you have created the universe from a metadata source, you can modify any of the universe components as for any other universe.

You save and export the universe to the Central Management System (CMS). Once exported to the CMS, the universe is then available to Web Intelligence users to create queries and reports.

12 BusinessObjects Universe Builder Guide

Building universes from XML metadata sources

XML metadata to universe component mapping

The following table shows the BusinessObjects structures that are mapped from information in the XML file:

Universe structureXML information

UniverseDatabase

Class, tableTable or view

Class nameTable business name

Object, columnColumn

Object nameColumn business name

Object typeColumn SQL data type

Class/object descriptionTable/column description

Foreign key-Primary key relationship (PKTable.PKColmn=FKTable.FKColumn)

Join expression

Selecting a XML data source

You select a metadata source (an XML file) to create or update a universe from the Universe Builder panel.

1. Start Universe Builder.

The Universe Builder panel appears.

2. Click the icon or tab for your metadata source.

For example to create a universe from a CWM relational 1.0 metadata source, you can click either the icon to the left of the Universe Builder panel or the tab at the top of the Universe Builder panel.

BusinessObjects Universe Builder Guide 13

Building universes from XML metadata sources

Creating a universe from a XML data source

3. Do one of the following:

• Select File > Metadata Sources

• Click the Metadata Sources button.

The connection wizard starts.

4. Click Next.

The XML file selection page appears.

5. Do one of the following:

• Type a file path.

• Click the Browse button and select a XML source file. This is the file

that you want to use to generate the universe.

6. Click Finish.

The XML file and associated database name appear in the Universe Builder pane.

Example:

Creating a universe from a XML data source

Once you have selected a metadata source, you use a universe creation wizard to take you through the steps to select the metadata elements that you want to be mapped to the universe, and finally the generation of the universe.

1. Start Universe Builder and select a metadata source.

2. Click the source database icon under the XML metadata source name in

the Universe Builder panel.

3. Do one of the following:

• Select File > Create Universe

• Click the Create Universe button.

The Universe Builder wizard starts.

4. Click Next.

The universe elements page appears. The available database tables and columns are listed in the left pane.

14 BusinessObjects Universe Builder Guide

Building universes from XML metadata sources

Creating a universe from a XML data source

5. Select one or more tables and columns and click the right arrow to

populate the right pane. The tables and columns in the right pane are those that appear in the generated universe. You can use the arrow buttons to add and remove tables from the universe pane as necessary

6. Click Next.

A connection and universe properties page appears. It lists the connections available to Designer.

7. Click a connection in the connections list. This is the connection to the

data source that the universe uses to retrieve data. Do the following on the connection and universe properties page:

• Type a universe name.

• Select or clear options check boxes.

• Click the Advanced button to set trace log file and XML source file

options.

• Click Next.

The universe generation summary page appears. It lists a summary of the options that you have selected during the wizard.

8. Click Finish.

The generated universe appears in the universe and structure panes of Designer.

Universe and connection options for a universe built from a XML source

You have the following options on the connection and universe build page of the metadata universe builder wizard:

BusinessObjects Universe Builder Guide 15

Building universes from XML metadata sources

Creating a universe from a XML data source

Wizard page

Build universe settings

Select a connection

Universe name

Save universe automatically

Replace existing universe

General tab: Traces

File locations tab: Default XML Source File Folder

DescriptionUniverse options

Listed connections are connections available to Designer. This is the connection to the target RDBMS.

The name of the universe that will be generated.

When selected, the universe is saved on creation.

When selected, if a universe with the same name exists, and Save universe automatically is selected, the new universe replaces the existing universe.

Path to the trace folder. This is the folder that holds the log files when universes are generated. You can browse to and select a folder.

Path to the default folder that stores the XML files used to create universes. You can browse to and select a folder.

Advanced settings

File locations tab: Parameter File

16 BusinessObjects Universe Builder Guide

Path to the default folder that stores the parameter files. These are files created when a universe is created. These files store and reference the selected metadata so that it can be reused for creating or updating other universes. The parameter file does not store the selected metadata. it is a filter that directs the bridge to the selected metadata through the original XML file. You can browse to and select a folder

Viewing the trace log

•

Example:

Setting trace log options

The Universe Builder creates a log file that contains trace information for view and universe creation. The log file is generated in the default folder <INSTALL DIR>\Log Files. You can change the default target folder. You can set the following trace options:

Building universes from XML metadata sources

Viewing the trace log

Tab

File locations

TracesGeneral

Default XML Source File Folder

DescriptionOption

Path to the trace folder. This is the folder that holds the log files when universes are generated. You can browse to and select a folder.

Path to the default folder that stores the XML files used to create universes. You can browse to and select a folder.

BusinessObjects Universe Builder Guide 17

Building universes from XML metadata sources

Setting trace log options

Tab

Parameter File

1. Select View > Options or click the Options button.

The Options page appears.

2. Set options as required.

3. Click OK.

DescriptionOption

18 BusinessObjects Universe Builder Guide

Updating XML metadata source universes

Updating a universe from a XML metadata source

You select a metadata source that has been updated. This is the metadata source that has been used to create a universe. The source has been updated, and now you want to update the universe with the same modification. A universe update wizard takes you through the steps needed to update the universe.

1. Start Universe Builder and select a metadata source. See the section

Selecting a XML metadata source for information on doing this.

2. Click the source database icon under the XML metadata source name in

the Universe Builder panel

3. Do one of the following:

• Select File > Update Universe.

• Click the Update Universe button.

The Universe update wizard starts.

4. Click Next.

The universe file page appears.

5. Click the Browse button and select a universe. This is the universe that

you want to update from the selected XML metadata source. Click Next.

The universe elements page appears. The available database tables and columns are listed in the left pane. Tables that have been added or modified are indicated with a red check mark

6. Select one or more tables and columns and click the right arrow to

populate the right pane with the tables that have been modified. The tables and columns in the right pane are those that appear in the generated universe. You can use the arrow buttons to add and remove tables from the universe pane as necessary.

7. Click Next.

The elements to update page appears.

8. Click the Advanced button.

The Options box appears.

9. Click the Properties tab and select radio buttons to keep or replace

Business Name, Mapping Expression, Object Type, and Lineage properties.

20 BusinessObjects Universe Builder Guide

Updating XML metadata source universes

Updating a universe from a XML metadata source

10. Click the Description tab and select radio buttons to keep, concatenate,

or replace Business and Technical descriptions.

11. Click the File Location and General tabs to change parameter file paths,

then click Next.

The universe generation summary page appears. It lists a summary of the options that you have selected during the wizard.

12. Click Finish.

The updated universe appears in the universe and structure panes of Designer.

BusinessObjects Universe Builder Guide 21

Updating XML metadata source universes

Updating a universe from a XML metadata source

22 BusinessObjects Universe Builder Guide

Exporting a universe to a XML file format

Overview of exporting universes to XML file formats

You can export a universe to IBM DB2 cube View XML format file.

The universe definition is exported to a XML file that complies with IBM DB2 Cube Views XML format. This file can then be loaded in IBM DB2 Cube Views using the API or OLAP Center tool. IBM DB2 Cube Views reads the metadata from the XML file, and recommends the appropriate Automatic Summary Table (AST) for future query optimization.

Universe pre-requirements for export to DB2 Cube Views

The following list describes the universe pre-requisites necessary for a successful universe export to the XML file:

Universe element affected

Universe level restrictions

24 BusinessObjects Universe Builder Guide

Restrictions

• Each universe is exported to a Cube Model.

• A universe must match a single snowflake schema with

a single fact table.

• The universe must contain at least one measure.

• Links between universes are not supported.

• Contexts are not taken into account, and not exported.

• Custom hierarchies: the levels of a custom hierarchy

must be grouped in the same class.

Exporting a universe to a XML file format

Exporting a universe to DB2 Cube Views formats

Universe element affected

Classes and objects

Joins

Restrictions

• @Select function is the only supported @function. All

other @functions are not mapped in the export.

• Conditions in the Where field of an object definition are

not exported. Note: Conditions are not supported in DB2 Cube Views objects, as they are not used for optimization.

• Multi-parameter aggregation functions are not exported.

• Each class must contain objects that are built on the

same dimension tables.

• All the objects (attributes in IBMDB2CV) that are refer-

enced by the same dimension in IBMDB2CV, must be grouped in the same class in the universe. Any other measure in other classes are automatically added to the Fact object in IBMDB2CV.

If the left or right column of a join does not match an object in the universe, then an attribute for this column is automatically created and added to the dimension (or fact) that contains the column table.

Exporting a universe to DB2 Cube Views formats

You export a BusinessObjects universe to a IBM DB2 Cube Views XML file as follows:

1. Start Universe Builder and click the IBMDB2CubeViews icon or tab.

2. Do one of the following:

• Select File > Export Universe.

• Click The Export Universe button.

The export wizard starts.

3. Click Next.

A universe source file page appears.

4. Browse to and select a universe file, then click Next.

BusinessObjects Universe Builder Guide 25

Exporting a universe to a XML file format

How to identify non-matching universe metadata

An OLAP information page appears.

5. Do the following:

• Enter a name for the fact table, or you can accept the fact table name

by default.

• Enter a name for the schema.

• Click Next.

The metadata is loaded. A page appears showing the structures that will be exported.

6. Click Next.

7. Type a name for the XML file and click Next.

A summary page appears.

8. Verify that the export information is correct, then click Finish.

The XML file is created in the universes folder of your user profile, for example, C:\Documents and Settings\<username>\Application Data\Business Objects\Business Objects <version>\Universes.

How to identify non-matching universe metadata

This section describes how objects in a universe that do not have a multi-dimensional match in IBM DB2 Cube Views are identified and processed during the export of a universe definition to an XML file.

A universe is based on relational metadata that does not have multi-dimensional design constraints. All the objects in a universe do not necessarily match equivalent IBM DB2 Cube Views objects, and do not respect IBM DB2 Cube Views multi-dimensional rules.

To correctly match relational structures, Universe Builder must run certain automatic detection processes to identify and define the required and appropriate metadata for IBM DB2 Cube Views. The affected multi-dimensional objects are described below.

26 BusinessObjects Universe Builder Guide

Non-matching universe metadata

Description

Exporting a universe to a XML file format

How to identify non-matching universe metadata

Fact

Dimensions

Attributes

Attribute relationships

Joins

The IBM DB2 Cube Views Fact object is automatically built from the set of measures found in the universe.

Tables that are not identified as Facts are assumed to be dimension tables. A IBM DB2 Cube Views dimension object is deduced directly from a BusinessObjects class.

All the objects within a class determine the attributes of the IBM DB2 Cube Views dimension. The tables inferred by BusinessObjects objects within the class, are detected by parsing the object’s Select field.

Attributes are not directly deduced from the columns of tables in the universe. Candidate attributes are detected and identified from the following information:

• BusinessObjects objects within a class.

• BusinessObjects objects referenced in Select field of

other BusinessObjects objects by the @Select statement.

• Columns involved in a join.

The detail-dimension relationship in a universe is translated to an attribute relationship of type Functional Dependency in IBM DB2 Cube Views.

Joins and their properties are directly read from universe structure.

Measures

All classes are searched for measure objects. If a measure is not built on the same fact table, then it is ignored.

BusinessObjects Universe Builder Guide 27

Exporting a universe to a XML file format

Mapping specific SQL expressions

Non-matching universe metadata

Hierarchies

Description

A hierarchy in DB2 Cube Views is linked to a dimension object and all its levels are members of this same dimension. This is not the case in a universe, where a custom hierarchy can contain levels coming from different Business Objects classes. Hierarchies are treated as follows:

• If a universe uses only default hierarchies, then the hier-

archies for export to IBM DB2 Cube Views are deduced from the objects, using the object order within each class.

• If the universe has custom hierarchies, then they are

exported without any modification.

Mapping specific SQL expressions

Certain SQL expressions are mapped in particular ways by the export process. The following cases of SQL expressions are described in detail:

•

SELECT expression for a measure on page 28

•

@AggregateAware function on page 29

•

Complex join expressions on page 29

•

Theta joins on page 29

•

Shortcut joins on page 29

SELECT expression for a measure

The BusinessObjects UMB gets the following information from the SELECT of a measure:

• Detect tables and columns involved in a measure and map them to

sqlExpression:column

• Identify the aggregation function

• Determine the formula expression and map it to sqlExpression:template.

28 BusinessObjects Universe Builder Guide

Exporting a universe to a XML file format

Mapping specific SQL expressions

@AggregateAware function

When an object contains the @AggregateAware function, only the last parameter of the @AggregateAware function is taken into account. This is the expression that contains the lowest level of aggregation used by the function. For example:

A @AggregateAware expression for a measure expression in the universe is as follows:

@Aggregate_Aware( sum(AggregatedTable1.Sales_revenue), sum(AggregatedTable2.Sales_revenue), sum(Fact_Table.Amount_sold))

The expression that is mapped to IBM DB2 Cube Views is:

sum(Fact_Table.Amount_sold))

Complex join expressions

The expression of a complex join in a universe can consist of expressions of type:

LeftTable.Column=RightTable.Column

In a complex join, these type of expressions can be linked together with the AND operator. The BusinessObjects UMB maps each expression in the complex join to an attribute pair of IBM DB2 Cube Views within the same join.

Theta joins

A theta join is split into two IBM DB2 Cube Views joins, where the operator BETWEEN is replaced by operators <= and >=. For example:

A join in a universe has this expression:

Customer.age between Age_group.age_min and Age_group.age_max

This join will be split into two joins with the following expressions:

Join1: Customer.age >= Age_group.age_min Join2: Customer.age <= Age_group.age_max

Shortcut joins

Not exported to IBM DB2 Cube Views. Shortcut joins in a universe represent an alternate path to improve performance of queries by not taking into account

BusinessObjects Universe Builder Guide 29

Exporting a universe to a XML file format

How universe structures are mapped to DB2 Cube Views metadata

intermediate tables. As shortcut joins create loops within a cube model, they are not exported.

How universe structures are mapped to DB2 Cube Views metadata

The following universe structures are mapped to corresponding IBM DB2 Cube Views structures. Each section provides detailed descriptions for structures that are mapped from a universe to IBM DB2 Cube Views when a universe is exported to a XML file.

•

Universe to cube model on page 30

•

Class to dimension on page 31

•

Fact table to Fact on page 31

•

Measure to measure on page 32

•

Dimension and detail object to attribute on page 33

•

Dimension and detail relationship to attribute relationship on page 33

•

Default hierarchy to hierarchy on page 34

•

Custom hierarchy to hierarchy on page 34

•

Join to join on page 34

Universe to cube model

The following describes mapping for a universe to cube model:

Universe name (long name)

30 BusinessObjects Universe Builder Guide

Cube propertyUniverse property

Cube file nameShort name (file name)

Business name. By default the name is the universe short name (<universe short name>)

CommentsDescription

name factsRefFact table

dimensionRefList of classes

Exporting a universe to a XML file format

How universe structures are mapped to DB2 Cube Views metadata

Cube propertyUniverse property

List of joins involving the fact table.

Class to dimension

The following table describes the mapping for a class to a dimension:

Joins between dimension tables inferred by the class.

Hierarchy

joinRef

Dimension propertyClass property

Name and Business nameName

CommentsDescription

attributeRefList of dimension and detail objects.

joinRef

If hierarchy is a custom hierarchy, then the dimension is modified to get all hierarchy levels in the same dimension as required by IBM DB2 Cube Views. The hierarchies are put in the herarchyRef property.

Fact table to Fact

The following table describes the mapping for a fact table to a Fact.

BusinessObjects Universe Builder Guide 31

Exporting a universe to a XML file format

How universe structures are mapped to DB2 Cube Views metadata

Fact table property

Fact table name

You enter this name manually in the Facts box in the Export universe panel. You can also accept the default name Facts_<universe name>.

List of columns and objects referenced in measures

Measure to measure

The following table describes the mapping of measure to a measure:

Fact property

Name and Business name

CommentsTable description

measureRefList of all measures in the universe

attributeRef

Columns and objects inferred by Select statements.

32 BusinessObjects Universe Builder Guide

Measure propertiesMeasure properties

Name and Business nameName

CommentsDescription

sqlExpression column

sqlExpression templateSelect statement formula

aggregation functionAggregation function

Exporting a universe to a XML file format

How universe structures are mapped to DB2 Cube Views metadata

Dimension and detail object to attribute

The following table describes the mapping of dimension and details to attributes:

AttributesDimension and detail objects

Name and Business nameName

CommentsDescription

Columns and objects referenced in Select statements

sqlExpression column

sqlExpression templateSelect statement formula

Dimension and detail relationship to attribute relationship

The following table describes the mapping of Dimension/detail relationships to attribute relationships:

Attribute relationshipDimension/detail relationship

Dimension name + detail name. Concat character is “_”

Name and Business name

Left attributeDimension

Right attributeDetail

BusinessObjects Universe Builder Guide 33

Exporting a universe to a XML file format

How universe structures are mapped to DB2 Cube Views metadata

Default hierarchy to hierarchy

The following table describes the mapping of default hierarchies to hierarchies:

HierarchyDefault hierarchy

Names and Business nameName

List of objects. Detail objects must not be part of the hierarchy.

Custom hierarchy to hierarchy

The following table describes the mapping of a custom hierarchy to hierarchy:

Join to join

AttributeRef

HierarchyCustom hierarchy

Name and Business nameName

attributeRefList of objects

The following table describes the mapping of joins to joins:

34 BusinessObjects Universe Builder Guide

How universe structures are mapped to DB2 Cube Views metadata

Left table name + right table name.

Concat character is "_"

Complex expression:

For each simple expression the left and right columns are identified.

Exporting a universe to a XML file format

JoinJoin

Name and Business name

Left attributeLeft column

Right attributeRight column

Each simple expression maps to an attribute pair.

BusinessObjects Universe Builder Guide 35

Exporting a universe to a XML file format

How universe structures are mapped to DB2 Cube Views metadata

36 BusinessObjects Universe Builder Guide

Building universes from Oracle Analytic Services

Overview of how you create a universe from Oracle Analytic Services

You create a Oracle OLAP universe by clicking the Oracle OLAP icon or tab and selecting File > Metadata sources. The Oracle OLAP Universe Builder wizard starts. You select a connection. The cubes available to the connection appear. You select a cube, and set view and universe creation options.

How is a universe generated from a OLAP cube?

When you create a universe with the Oracle OLAP Universe Builder, it is automatically set up for SQL access to Oracle Analytic Workspaces. BusinessObjects Oracle OLAP Universe Builder performs the following main tasks:

• Inserts the relational fact view in the universe as a real view or as Derived

Table.

• Add aliases to represent the dimension levels and hierarchies

• Joins the relational view to the dimension tables with regular joins and

shortcut joins. The expressions of the joins are specific to this solution.

• Creates a class of objects for each Cube dimension and an object for

each level of the dimension.

• Creates a subclass for each hierarchy if a dimension has more than one

hierarchy. Multi-hierarchy dimensions are supported in the view definition and in the universe.

• Define aggregate navigation to resolve object incompatibility that results

from the multi-hierarchy dimensions.

• Defines object expressions using the AggregateAware function to handle

the Aggregation Navigation

38 BusinessObjects Universe Builder Guide

Building universes from Oracle Analytic Services

• Transforms objects that map real dimension members (Identifiers) to

Details of objects that represent the member descriptions.

• Creates measure objects

Analyzing the relational view

BusinessObjects Oracle OLAP Universe Builder generates views that call the OLAP_TABLE function to map the view columns to the hierarchies of the dimensions and measures of the Cube. The generated script has the following form:

CREATE VIEW BOBJ_FK_UNITS_CUBE_VIEW AS SELECT * FROM TA BLE(OLAP_TABLE('GLOBAL_AW2.TEST DURATION session','','','&LIM IT_MAP'

LIMIT_MAP is a variable that stores the text of the limit_map parameter of OLAP_TABLE. This text is generated by Oracle OLAP Universe Builder. Here is an example of limit_map parameter:

DIMENSION GLOBAL_AW2.TEST!FK_TIME WITH

HIERARCHY GLOBAL_AW2.TEST!FK_TIME_PARENTREL (FK_TIME_HIERLIST

\''CALENDAR\'')

LEVELREL FK_TIME_YEAR,FK_TIME_QUARTER,FK_TIME_MONTH FROM GLOBAL_AW2.TEST!FK_TIME_FAMILYREL USING GLOB

AL_AW2.TEST!FK_TIME_LEVELLIST

LEVELREL FK_TIME_YEAR_DESC,FK_TIME_QUARTER_DE

SC,FK_TIME_MONTH_DESC

FROM GLOBAL_AW2.TEST!FK_TIME_FAMILYREL USING GLOB

AL_AW2.TEST!FK_TIME_LEVELLIST

LABEL GLOBAL_AW2.TEST!FK_TIME_LONG_DESCRIPTION

ATTRIBUTE FK_TIME_LEVEL FROM GLOBAL_AW2.TEST!FK_TIME_LEVELREL

DIMENSION GLOBAL_AW2.TEST!FK_CUSTOMER WITH

HIERARCHY GLOBAL_AW2.TEST!FK_CUSTOMER_PARENTREL (FK_CUSTOMER_HI

ERLIST \''MARKET_SEGMENT\'')

INHIERARCHY GLOBAL_AW2.TEST!FK_CUSTOMER_INHIER

LEVELREL null,null,null,FK_CUSTOMER_TOTAL_MARKET,FK_CUS TOMER_MARKET_SEGMENT, FK_CUSTOMER_ACCOUNT,FK_CUSTOMER_SHIP_TO

FROM GLOBAL_AW2.TEST!FK_CUSTOMER_FAMILYREL USING GLOB AL_AW2.TEST!FK_CUSTOMER_LEVELLIST

LEVELREL null,null,null,FK_CUSTOMER_TOTAL_MARKET_DESC,FK_CUS TOMER_MARKET_SEGMENT_D01, FK_CUSTOMER_ACCOUNT_DESC,FK_CUSTOMER_SHIP_TO_DESC

FROM GLOBAL_AW2.TEST!FK_CUSTOMER_FAMILYREL USING GLOB

AL_AW2.TEST!FK_CUSTOMER_LEVELLIST

LABEL GLOBAL_AW2.TEST!FK_CUSTOMER_LONG_DESCRIPTION

HIERARCHY GLOBAL_AW2.TEST!FK_CUSTOMER_PARENTREL (FK_CUSTOMER_HI

ERLIST \''SHIPMENTS\'')

INHIERARCHY GLOBAL_AW2.TEST!FK_CUSTOMER_INHIER

Analyzing the relational view

BusinessObjects Universe Builder Guide 39

Building universes from Oracle Analytic Services

What are the shortcut joins in the universe used for?

LEVELREL null,null,null,FK_CUSTOMER_ALL_CUSTOMERS,

FK_CUSTOMER_REGION,FK_CUSTOMER_WAREHOUSE,null

FROM GLOBAL_AW2.TEST!FK_CUSTOMER_FAMILYREL USING GLOB

AL_AW2.TEST!FK_CUSTOMER_LEVELLIST

LEVELREL null,null,null,FK_CUSTOMER_ALL_CUSTOMERS_DESC,

FK_CUSTOMER_REGION_DESC,FK_CUSTOMER_WAREHOUSE_DESC,null

FROM GLOBAL_AW2.TEST!FK_CUSTOMER_FAMILYREL USING GLOB

AL_AW2.TEST!FK_CUSTOMER_LEVELLIST

LABEL GLOBAL_AW2.TEST!FK_CUSTOMER_LONG_DESCRIPTION

ATTRIBUTE FK_CUSTOMER_LEVEL FROM GLOBAL_AW2.TEST!FK_CUS TOMER_LEVELREL MEASURE FK_UNITS_CUBE_UNITS AS NUMBER FROM GLOB AL_AW2.TEST!FK_UNITS_CUBE_UNITS ROW2CELL OLAP_CALC

What are the shortcut joins in the universe used for?

Shortcut joins ensure that BusinessObjects generates SQL for each object combination rather than for each object.

BusinessObjects uses shortcut joins when it can omit tables from a query and take a 'shortcut' between two tables that are not directly linked in a hierarchy. For example, based on the following schema:

40 BusinessObjects Universe Builder Guide

Building universes from Oracle Analytic Services

What are the shortcut joins in the universe used for?

if a shortcut join is defined between the QUARTER and OLAPCUBE tables, BusinessObjects does not need to join through the MONTH table to retrieve revenue by quarter.

Each table in the time hierarchy (except the lowest-level table) must be joined to OLAPCUBE.time_level by a shortcut join, as shown below:

The join expression must include the expression that will restrict the rows returned from OLAPCUBE; in the case of QUARTER, this is OLAPCUBE.time_level = 'QTR'. To ensure that Designer allows the join, the expression must also reference the MONTH table, which should appear inside comments (because it plays no part in the actual join expression that you are interested in generating). The full join expression is therefore:

/* QUARTER.DUMMY */ OLAPCUBE.time_level = 'QTR'

The full list of shortcut join expressions for the example time hierarchy is as follows:

ExpressionJoined tables

MONTH, OLAPCUBE

/* MONTH.DUMMY */ OLAPCUBE.time_level = 'MONTH'

BusinessObjects Universe Builder Guide 41

Building universes from Oracle Analytic Services

How are Oracle OLAP structures mapped to universe components?

ExpressionJoined tables

QUARTER, OLAPCUBE

YEAR, OLAPCUBE

TIME_ALL, OLAPCUBE

/* QUARTER.DUMMY */ OLAPCUBE.time_level = 'QTR'

/* YEAR.DUMMY */ OLAPCUBE.time_level = 'YEAR'

/* TIME_ALL.DUMMY */ OLAPCUBE.time_level = 'ALL'

How are Oracle OLAP structures mapped to universe components?

To obtain and set up the expected universe, the Oracle OLAP Universe Builder adds and configures universe objects as follows:

View

Oracle OLAP Universe Builder inserts the relational view as a table in the universe as well as the oracle table sys.dual. If you choose to use a derived table, a derived table is inserted with the definition of the view (select part with OLAP_TABLE function).

Hierarchy tables

For each hierarchy that is represented in the relational view, an alias of sys.dual is created for each level of the hierarchy. The name of the alias is the level name. For example: if we have a dimension TIME with 4 levels (ALL, YEAR, MONTH, QUARTER) then create 4 aliases ALL, YEAR, MONTH, QUARTER.

42 BusinessObjects Universe Builder Guide

Building universes from Oracle Analytic Services

How are Oracle OLAP structures mapped to universe components?

Multi-hierarchy tables

Note: Multi-Hierarchy is a special case. See the section Multi-Hierarchy

special case: (Multi-Hierarchy dimensions support) on page 46 for more

information.

If a dimension has more than one hierarchy, then a different set of tables is created for each hierarchy even if some hierarchies share a same level. This means that for shared levels, as many aliases are created as hierarchies. The naming of such aliases is a concatenation of the level name and the hierarchy name. For example:

Dimension time has two hierarchies: H1 (All_Time, Year, Month) and H2 (All_Time, Quarter, Month)

All_Time and Month are shared by both hierarchies so we'll have two aliases for All_Time: All_Time _H1 and All_Time_H2

And two aliases for Month: Month _H1 and Month _H2

Dimension joins

• Each table representing a level is joined to its direct lower level in the

same hierarchy. The join expression is:

/* Alias1.DUMMY=Alias2.DUMMY */ 1=1

BusinessObjects Universe Builder Guide 43

Building universes from Oracle Analytic Services

How are Oracle OLAP structures mapped to universe components?

where Alias1 represents a level and Alias2 represents its direct upper level in the hierarchy. Example:

/* Quarter.DUMMY=Year.DUMMY */ 1=1

• Each table is joined to the view using a shortcut join type except for the

lowest level where the join is regular. The join expression defines a value to filter the rows returned from the view and is of the type:

/* Alias.DUMMY */

VIEW.levelColumn = 'level_value'

Where Alias is the alias name, levelColumn is the column representing the level within the view and level_value is the value of that column that matches the level name.

Examples: MYVIEW is the view that represents the OLAP cube, the column that contains the levels is time_level, the level values are: ALL, YEAR, QTR, MONTH

Joined tables expression

MONTH, MYVIEW /* MONTH.DUMMY */ MYVIEW.time_level = 'MONTH' QUARTER, MYVIEW /* QUARTER.DUMMY */ MYVIEW.time_level = 'QTR'

YEAR, MYVIEW /* YEAR.DUMMY */ MYVIEW.time_level = 'YEAR' TIME_ALL, MYVIEW /* TIME_ALL.DUMMY */ MYVIEW.time_level = 'ALL'

Classes and Objects

The Bridge must create a class for each OLAP dimension and an object for each level. Class and object properties are mapped as follows:

PropertyUniverse item

Class

How to create a Oracle OLAP universe

You can create universes and views as using the following creation methods:

Creating a view and generating a universe on page 49

Create a view only on page 51

Creating Oracle OLAP universe from an existing view on page 52

48 BusinessObjects Universe Builder Guide

DescriptionUniverse creation option

You create a view, and then select which structures are mapped to create the universe.

You can create a view and save the view if you do not want to create a universe. The view is available in a view list and can be used to create a universe at any time.

You select an existing view, and generate a universe directly from this view.

Building universes from Oracle Analytic Services

How to create a Oracle OLAP universe

Creating a view and generating a universe

You generate an Oracle OLAP universe by first defining a view using Analytic Workspace Cube metadata, then you set universe creation options, and generate a new universe.

1. Click the Oracle OLAP icon or tab.

2. Select File > Metadata Sources.

The Oracle OLAP Connection Wizard Welcome page appears.

3. Click Next.

The wizard login page appears.

4. Select a connection, enter user name and password, and then click Finish.

The Universe Builder Panel appears displaying the Analytic Workspace Cube cubes available to the connection

5. Click the Cubes node.

The Analytic Workspaces (AW) available in the connection are displayed.

6. Expand a AW node to display the cubes available to the AW, then right

click a cube and select Create Universe from the context menu.

The Universe Builder wizard appears. Three options are available. These are described in the section How to create a Oracle OLAP universe on page 48.

7. Click the Create View and generate Universe radio button, then click

Next.

A status box appears showing the metadata loading status. A box showing data type and data length information appears.

8. If required, modify the data type and length values. Do this as follows:

• Double click a data type or length value.

• Select a data type from the drop down list box.

• Click Next.

The Hierarchy level page appears. This page lists the hierarchy levels with data types and values.

9. Edit hierarchy values if necessary, then click Next.

The View and universe options page appears.

BusinessObjects Universe Builder Guide 49

Building universes from Oracle Analytic Services

How to create a Oracle OLAP universe

10. Type a name for the view, and select view and universe options. The

View properties and universe options are as follows:

DescriptionOptions

View Name

Create Column for OLAP_EXPRESSION

Create Columns for Identifiers

Replace existing database objects

Use derived tables

Name for the view. You can edit this field.

When selected, an extra column of type Raw(32) is created in the view to enable the use of OLAP_EXPRESSION function in the universe.

When selected, columns representing the dimension members (identifiers) are created.

When selected existing Type and View structures are replaced in the database.

When selected the universe is not built from a view physically created in the database, but is built by using a derived table to reference the cube structures. A derived table is a virtual table that exists only in the universe that references database structures. It is not created in the database. This is useful when the user does not have CREATE VIEW rights, or if you do not want to accumulate views in the database.

50 BusinessObjects Universe Builder Guide

Building universes from Oracle Analytic Services

How to create a Oracle OLAP universe

DescriptionOptions

Only active when the Create columns for identifiers option for

Transform object IDs to details

11. Click Next. The SQL verification page appears.

12. Verify the SQL for the view and click Next. The universe information

summary page appears.

13. Verify the universe information, and click Finish. Designer starts and

opens to the newly generated universe.

views is selected. When selected, object IDs are transformed into detail objects in the generated universe.

Create a view only

You can create a view using Analytic Workspace Cube metadata. The saved view appears in a list of views. Once you have create the view, you can then select the view at a later time and generate a universe. To create a view only, follow the same procedure in the section Creating a view and generating

a universe on page 49Creating a view and generating a universe on page

49 but at the start of the Oracle OLAP Universe Builder wizard, you select the Create View only radio button.

The view is created in the target database. You can connect to this view at any time to create a universe. See the section Creating Oracle OLAP universe

from an existing view on page 52for the procedure on using a view to create

a universe.

BusinessObjects Universe Builder Guide 51

Building universes from Oracle Analytic Services

How to create a Oracle OLAP universe

Creating Oracle OLAP universe from an existing view

You can generate a universe from an existing view. Existing views appear in a list. You select a view from the list and generate a universe.

1. Click the Oracle OLAP icon or tab.

2. Select File > Metadata Sources.

The Oracle OLAP Connection Wizard Welcome page appears.

3. Click Next.

The wizard login page appears.

4. Select a connection and enter user name and password.

5. Click Finish.

The Universe Builder Panel appears displaying the Analytic Workspace Cube cubes available to the connection.

6. Click the Cubes node.

The Analytic Workspaces (AW) available in the connection are displayed. Expand a AW node to display the cubes available to the AW.

7. Right click a cube and select Create Universe from the context menu.

The Universe Builder wizard appears. Three options are available. How

to create a Oracle OLAP universe on page 48 for a description of each

option.

8. Click the Generate Universe from View radio button.

9. Click Next.

A list of available views defined on the cube appears.

10. Click a view name in the list and click Next.

A status box shows the progress of metadata being loaded from the selected cube. The Universe Creation page appears. It lists dimension, measures, and hierarchy levels defined in the view that can be used to create a universe.

11. If required, modify a column name or hierarchy level. Do this as follows:

• Double click a column name or level value.

• Select or type a name as appropriate.

12. Click Finish.

Designer starts and opens to the newly generated universe.

52 BusinessObjects Universe Builder Guide

Get More Help

Online documentation library

Business Objects offers a full documentation set covering all products and their deployment. The online documentation library has the most up-to-date version of the Business Objects product documentation. You can browse the library contents, do full-text searches, read guides on line, and download PDF versions. The library is updated regularly with new content as it becomes available.

http://support.businessobjects.com/documentation/product_guides/

Additional developer resources

http://devlibrary.businessobjects.com

Online customer support

The Business Objects Customer Support web site contains information about Customer Support programs and services. It also has links to a wide range of technical information including knowledgebase articles, downloads, and support forums.

http://www.businessobjects.com/support/

Looking for the best deployment solution for your company?

Business Objects consultants can accompany you from the initial analysis stage to the delivery of your deployment project. Expertise is available in relational and multidimensional databases, in connectivities, database design tools, customized embedding technology, and more.

For more information, contact your local sales office, or contact us at:

http://www.businessobjects.com/services/consulting/

Looking for training options?

From traditional classroom learning to targeted e-learning seminars, we can offer a training package to suit your learning needs and preferred learning style. Find more information on the Business Objects Education web site:

http://www.businessobjects.com/services/training

54 BusinessObjects Universe Builder Guide

Get More Help

Send us your feedback

Do you have a suggestion on how we can improve our documentation? Is there something you particularly like or have found useful? Drop us a line, and we will do our best to ensure that your suggestion is included in the next release of our documentation:

mailto:documentation@businessobjects.com

Note: If your issue concerns a Business Objects product and not the

documentation, please contact our Customer Support experts. For information about Customer Support visit: http://www.businessobjects.com/support/.

Business Objects product information

For information about the full range of Business Objects products, visit:

http://www.businessobjects.com.

BusinessObjects Universe Builder Guide 55

Get More Help

56 BusinessObjects Universe Builder Guide

Index

classes and objects

in Oracle Analytic Workspaces universe 42

dimension joins

use in Oracle Analytic Workspaces universe

hierarchy tables

use in Oracle Analytic Workspaces universe

multi-hierarchy tables

use in Oracle Analytic Workspaces universe

Oracle Analytic Workspaces

class and object mapping 42 dimension joins 42 hierarchy tables 42 multi-hierarchy tables 42 use of shortcut joins in universe 40 view 42

shortcut join

use in Oracle Analytic Workspaces universe

view

use in Oracle Analytic Workspaces universe

BusinessObjects Universe Builder Guide 57

Index

58 BusinessObjects Universe Builder Guide

Business objects UNIVERSE BUILDER User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Introduction

Overview of creating universes from metadata sources

What is Universe Builder?

Universe Builder and Designer

When do you use Universe Builder instead of Designer?

Universe Builder basics

Starting Universe Builder

Building universes from XML metadata sources

Overview of how you create a universe from XML metadata

XML metadata to universe component mapping

Selecting a XML data source

Creating a universe from a XML data source

Universe and connection options for a universe built from a XML source

Viewing the trace log

Setting trace log options

Updating XML metadata source universes

Updating a universe from a XML metadata source

Exporting a universe to a XML file format

Overview of exporting universes to XML file formats

Universe pre-requirements for export to DB2 Cube Views

Exporting a universe to DB2 Cube Views formats

How to identify non-matching universe metadata

Mapping specific SQL expressions

How universe structures are mapped to DB2 Cube Views metadata

Universe to cube model

Class to dimension

Fact table to Fact

Measure to measure

Dimension and detail object to attribute

Dimension and detail relationship to attribute relationship

Default hierarchy to hierarchy

Custom hierarchy to hierarchy

Join to join

Building universes from Oracle Analytic Services

Overview of how you create a universe from Oracle Analytic Services

How is a universe generated from a OLAP cube?

Analyzing the relational view

What are the shortcut joins in the universe used for?

How are Oracle OLAP structures mapped to universe components?

How to create a Oracle OLAP universe

Creating a view and generating a universe

Create a view only

Creating Oracle OLAP universe from an existing view

Get More Help

Index