Download - DM 310 IG

BusinessObjects XI R1/R2: Universe Design

Instructor's Guide

DM310R2

CopyrightPatents Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352. Trademarks Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names mentioned herein may be trademarks of their respective owners. Product specifications and program conditions are subject to change without notice. Copyright Copyright 2005 Business Objects SA. All rights reserved.

c oCourse objectives Course audience

N T E N .T

sxvi xvi xvi xvi xvii xvii xvii xviii xix

About this CoursePrerequisite education Prerequisite knowledge/experience Course success factors Training facility setup Course materials Learning process Recommended education

Facilities SetupIntroduction Facilities Setup Setting up for the activities Before class begins Getting Help xxii xxii xxiv v-xxiv xxxii

Lesson 1

Understanding BusinessObjects UniversesUnderstanding BusinessObjects Introduction What is a universe? universes 1-2 1-2 1-2 1-2 1-3 1-3 1-3 1-4 1-5 1-7 1-8 1-11 1-11 1-11 1-13 1-14 Universes are used to query the database Classes and objects How universes are used Advantages of a universe Metadata and the semantic layer BusinessObjects Universe Designer components Using the Quick Design Wizard Using Designer module commands Saving and exporting a universe Opening, importing, and exporting universes Importing a universe Giving all users access to a universe Activity: Viewing a universe in Designer

Copyright 2005 Business Objects. All rights reserved.

iii

Using the Universe Development Cycle The Universe Development Cycle Activity: Plan a universe Lesson summary Quiz: Understanding BusinessObjects

Universes

1-15 1-15 1-17 1-18 1-18

Lesson 2

Creating the Course UniverseThe course database and universe Introduction Course database description Assumptions Analysis of reporting requirements Development plan Creating the universe Introduction Defining universe parameters Setting the database connection Viewing available connections Universe parameters Universe Parameters dialog box Summary tab Strategies tab Controls tab SQL tab Links tab Parameters tab Lesson summary Quiz: Creating the Course Universe 2-2 2-2 2-2 2-2 2-3 2-4 2-5 2-5 2-5 2-6 2-12 2-13 2-13 2-14 2-15 2-16 2-17 2-18 2-19 2-21 2-21

Lesson 3

Building the Universe StructurePopulating the universe structure Designing a schema Schema design and the universe creation process Adding tables Viewing data from the Table Browser Organizing your tables Changing table display Viewing table values Activity: Building the universe structure Defining joins in a universe About joins and SQL Where clauses Creating joins Tracing joins manually in the schema Creating the join by defining properties Editing the join expression Detecting joins Setting join cardinalities About cardinality Detecting cardinality automatically 3-2 3-2 3-3 3-4 3-5 3-7 3-7 3-7 3-9 3-10 3-10 3-11 3-11 3-12 3-14 3-16 3-17 3-17 3-21

_

iv

Universe Design-instructor's

Guide

Best practice for setting join cardinality Join types Equi-joins Outer joins Theta joins Shortcut joins Self-restricting joins Using List Mode Checking integrity Activity: Building the universe structure Lesson summary Quiz: Building the Universe Structure

3-23 3-24 3-25 3-26 3-27 3-28 3-28 3-29 3-30 3-32 3-34 3-34

Lesson 4

Creating Dimension

Obj eets4-2 4-2 4-2 4-4 4-5 4-5 4-5 4-6 4-7 4-8 4-10 4-11 4-18 4-18 4-20 4-20 4-21 4-22 4-22 4-27 4-27

Objects and classes Introduction Objects Classes Creating classes and objects Introduction Creating classes Creating an object Defining a new object as a detail object Creating classes or objects by dragging tables or columns Working with classes and sub-classes Editing the object properties Copying and pasting objects Find and replace Checking integrity Viewing parent tables Testing objects Activity: Creating and testing classes and objects Objectives: Lesson summary Quiz: Creating Dimension Objects

Lesson 5

Creating Measure Obj eetsWhat are measure objects? Defining measure objects Levels of aggregation Creating measure objects Creating measure objects Testing measure objects Activity: Create and test measure objects Lesson summary Quiz: Creating Measure Objects 5-2 5-2 5-3 5-7 5-7 5-8 5-10 5-12 5-12

Table of Contents-instructor's

Guide

v

Lesson 6

Using Lists of ValuesCreating a list of values What is a List of Values? Using a LOV Working with LOVs in Designer Creating a cascading LOV Setting up a cascading LOV Using the cascading LOV in Web Intelligence Activity: Using LOV Lesson Summary Quiz: Using Lists of Values 6-2 6-2 6-2 6-5 6-13 6-13 6-15 6-17 6-18 6-18

Lesson 7

Resolving Loops in a UniverseUnderstanding loops Introduction Recogni~ng~ops Problems caused by loops Loops in a universe schema and not in the database What is the loop doing? Resolving loops How Designer deals with loops Resolving loops using aliases Introduction About aliases Detecting loops and inserting aliases Inserting an alias manually Redefining objects Listing and renaming aliases Choosing which alias method to use Resolving self-join loops using aliases Resolving loops using shortcut joins Introduction Using a shortcut join Activity: Resolving loops with aliases Resolving loops using contexts Introduction About contexts Detecting and creating contexts Creating objects for each context Editing a context Testing contexts Updating contexts Activity: Resolving loops using contexts Lesson summary Quiz: Resolving Loops in a Universe 7-2 7-2 7-2 7-3 7-3 7-3 7-4 7-4 7-5 7-5 7-5 7-6 7-9 7-9 7-10 7-10 7-11 7-14 7-14 7-14 7-16 7-18 7-18 7-18 7-22 7-25 7-25 7-27 7-29 7-30 7-33 7-33

vi


Guide

Lesson 8

Resolving SQL TrapsUnderstanding SQL traps and universes 8-2 Resolving chasm traps 8-3 Chasm traps 8-3 Detecting chasm traps 8-4 The Chasm trap scenario 8-4 Resolving chasm traps 8-6 Using multiple SQL statements for each measure to resolve chasm traps .. 8-7 Using contexts to resolve chasm traps 8-10 Activity: Resolving chams traps 8-11 Resolving fan traps 8-14 Fan traps 8-14 Detecting fan traps 8-15 The fan trap scenario 8-15 Resolving fan traps 8-17 Using aliases and contexts to resolve fan traps 8-18 Another solution using aliases and contexts 8-21 Avoiding fan traps altogether 8-23 Activity: Resolving fan traps 8-24 Lesson summary 8-27 Quiz: Resolving SQL Traps 8-27

Lesson 9

Applying Restrictions on ObjectsRestricting the data returned by objects Defining data restrictions Methods of restricting data in Web Intelligence Applying restrictions to objects Applying restrictions to objects An alternative to applying restrictions to objects Applying restrictions using condition objects Restrictions using condition objects Applying restrictions to tables Applying restrictions using the tables button Applying each type of restriction Activity: Applying restrictions Lesson Summary Quiz: Applying Restrictions on Objects 9-2 9-2 9-4 9-5 9-5 9-7 9-9 9-9 9-11 9-12 9-14 9-15 9-17 9-17

Table of Contents-Instructor's Guide

vii

Lesson 10

Using Functions with ObjectsUsing @ Functions Defining @Functions @Prompt @Prompt syntax @Select @Where Activity: Using @Functions Lesson Summary Quiz: Using Functions with Objects 10-2 10-2 10-3 10-3 10-5 10-7 10-10 10-12 10-12

Lesson 11

Using HierarchiesUnderstanding hierarchies and universes Hierarchies Natural hierarchies Logical hierarchies Working with hierarchies Default hierarchies Viewing default hierarchies Creating custom hierarchies Creating a custom hierarchy The effect of custom hierarchies on default hierarchies Editing types of hierarchies Time hierarchies Creating time hierarchies Time hierarchies based on database functions Table-based time hierarchies Activity: Using hierarchies Lesson summary Quiz: Using Hierarchies 11-2 11-2 11-3 11-3 11-4 11-4 11-5 11-8 11-9 11-10 11-12 11-13 11-13 11-17 11-19 11-21 11-23 11-23

Lesson 12

Aggregate AwarenessUnderstanding aggregate awareness and universes What is Aggregate Awareness? Summary Tables Summary Tables and SQL Applying aggregate awareness to objects Setting up Aggregate Awareness _ Step 1: Insert a summary table and set joins cardinality Step 2: Set the contexts Step 3: Redefine the objects Step 4: Define incompatible objects _ Using aggregate awareness to resolve fan traps Testing aggregate awareness Activity: Aggregate awareness Lesson summary Quiz: Aggregate Awareness 12-2 12-2 12-3 12-4 12-5 12-5 12-5 12-6 12-7 12-9 12-12 12-13 12-14 12-16 12-16

viii


Guide

Lesson 13

Derived Tables and IndexesUsing derived tables What is a derived table? Advantages of using derived tables Adding derived tables Activity: Derived tables Applying index awareness What is index awareness? Setting up index awareness Activity: Index awareness Lesson summary Quiz: Derived Tables and Indexes 13-2 13-2 13-2 13-3 13-6 13-7 13-7 13-8 13-12 13-13 13-13

Lesson 14

Linking UniversesUnderstanding linked universes What are linked universes? Using linked universes Possible linking strategies Advantages and limitations to linking Creating links between universes Linking universes Editing the derived structure Including one universe within another When to link and when to include? Activity: Linking universes Lesson summary Quiz: Linking Universes 14-2 14-2 14-3 14-3 14-4 14-5 14-5 14-7 14-8 14-10 14-11 14-12 14-12

Lesson 15

Securing UniversesSetting access restrictions on a universe About security and universes What is a restriction set? Creating a restriction Applying a restriction set to users Setting restriction group priority Viewing user and group restrictions Activity: Setting access restrictions Lesson summary Quiz: Securing Universes 15-2 15-2 15-2 15-4 15-14 15-17 15-18 15-19 15-20 15-20

Table of Contents-instructor's

Guide

ix

Lesson 16

Managing UniversesDocumenting universes Printing universe details Printing options: General section Printing options: List components section Printing options: Full description section Deploying universes About deploying a universe How do other users access the universe? What happens when you export a universe Exporting a universe Importing a universe Working with multiple designers Understanding version control Locking a universe Maintaining universes Reasons for universe maintenance Changes to the target database Adding new tables to an existing universe The effect of changing objects Activity: Managing universes Lesson Summary Quiz: Managing Universes 16-2 16-2 16-4 16-4 16-5 16-6 16-6 16-6 16-7 16-10 16-11 16-13 16-13 16-14 16-15 16-15 16-15 16-18 16-18 16-20 16-21 16-21

Lesson 17

Creating Universes from MetadataCreating a universe from an XML metadata file Which metadata sources are supported? Working in the Metadata Exchange panel Creating a universe from metadata Activity: Creating universes from metadata Lesson summary Quiz: Creating Universes from Metadata 17-2 17-2 17-3 17-4 17-9 17-10 17-10

Appendix A

End of Course ChallengeThe Challenge A-2

x

Universe Design-Instructor's

Guide

A

G

E

N

D

A30 minutes 30 minutes

Universe DesignIntroductions, Course OverviewLesson 1

Understanding BusinessObjects Universes

o Understanding BusinessObjects universes o Using the Universe Development Cycle o Lesson summary2

Lesson

Creating the Course Universe

o The course database and universe o Creating the universe o Lesson summary3

45 minutes

Lesson

Building the Universe Structure

o Populating o Lesson4

1 hour

o Defining joins in a universesummaryLesson

the universe structure

Creating Dimension Objects

o Objects and classes o Creating classes and objectso Lessonsummary5

2 hours

Lesson

Creating Measure Objects

o What are measure objects? o Creating measure objects o Lesson summary

30 minutes


xi

Lesson 6

Using Lists of Values

o Creating

30 minutes

a list of values

o Working with LOVs in Designer o Creating a cascading LOV o Lesson SummaryLesson 7

Resolving Loops in a Universe

o Understanding loops o Resolving loops using aliases o Resolving loops using shortcut joins o Resolving loops using contexts o Lesson summaryo UnderstandingSQL traps and universes

3 hours

Lesson 8

Resolving SQL Traps

1.5 hours

o Resolving chasm traps o Resolving fan traps o Lesson summaryLesson 9

Applying Restrictions on Objects

o Restricting the data returned o Lesson SummaryDUsing

1 hour

by objects

Lesson 10

Using Functions with Objects

45 minutes

o Lesson

@ FunctionsSummary

Lesson 11

Using Hierarchies

o Understandingo Working

45 minutes

hierarchies and universes

with hierarchies summary

o Lesson

Lesson 12

Aggregate Awareness

o Understanding aggregate awareness and universes o Applying aggregate awareness to objects o Lesson summary

1 .5 hours

xiiUniverse Design-instructor's

Guide

Lesson 13

Derived Tables and Indexes

1 hour

o Using derived tables o Applying index awareness o Lesson summaryLesson 14

Linking Universes

o Understandingo Creating

30 minutes

linked universes

o Lesson

links between universes summary

Lesson 15

Securing Universes

o Setting access restrictions o Lesson summary o Documenting universes o Deploying universes o Maintaining universes o Lesson SummaryLesson 17

45 minuteson a universe

Lesson 16

Managing Universes

45 minutes

Creating Universes from Metadata

o Creating a universe o Lesson summary o The Challenge

30 minutes

from an XML metadata file

Agenda-instructor's

Guide

xiii

xivUniverse Design-instructor's

Guide

About this CourseThis section explains the conventions used in the course and in this training guide.


xv

Course objectivesBusinessObjects XI R1/R2 Universe Design is a classroom-based course where participants learn to design and maintain BusinessObjects universes . . The course includes presentation of concepts, demonstration of features, facilitated discussions, practice activities, and reviews. After completing this course, learners will be able to: Understand BusinessObjects universes Set universe parameters Populate the universe structure Define joins and set their cardinalities Create universe classes, and create dimension, detail and measure objects Create a list of values Resolve loops in a universe Resolve SQL chasm and fan traps Apply restrictions to objects Create hierarchies, use aggregate awareness, derived tables and index awareness features Link universes Manage universes and apply security Create universes from metadata sources

Course audienceThe target audience for this course is universe designers.

Prerequisite educationTo be successful, learners who attend this course must have attended the following offerings: Not applicable for this offering

Prerequisite knowledge/experienceTo be successful, learners who attend this course must have working knowledge of: Good working knowledge of SQL and/or relational database management systems. Familiarity with the type of data and the logical structure of the databases in their organization. Familiarity with BusinessObjects Web Intelligence, Desktop Intelligence and report building.

xvi


Guide

Course success factorsYour learning experience will be enhanced by: Activities that build on the life experiences of the learner Discussion that connects the training to real working environments Learners and instructor working as a team Active participation by all learners

Training facility setupRefer to the Facility Setup section of this training guide for details on hardware, software, classroom, course material, and course-specific requirements. These items must be set up before the first day of class.

Course materialsThe materials included with the course materials are: Name card Instructor's Guide The Instructor's Guide contains an agenda, lessons, and practice activities. The Instructor's Guide outlines detailed contents as well as instructor notes, which do not appear in the Learner's Guide, for teaching this course. Learner's Guide The Learner's Guide contains an agenda, learner materials, and practice activities. The Learner's Guide is designed to assist students who attend the classroom-based course and outlines what learners can expect to achieve by participating in this course. Evaluation form At the conclusion of this course, instruct the learners to provide feedback on the course content, instructor, and facility through the evaluation process. Their comments will assist us to improve future courses. Additional information for the course is provided on the resource CD or as a hard copy: Sample files The sample files on the resource CD can include required files for the course activities and/or supplemental content to the training guide. Overview webinar(s) Additional resources include: Online Help and User's Guide As an instructor, encourage the learners to retrieve information and find answers to questions using the online Help and/or the User's Guide that are included with the product.

Point out the feedback form located at the back of the training guide to the learners as part of the course introduction and again at the end of last day of class.

About this Course-Instructor's

Guide

xvii

Learning processLearning is an interactive process between the learners and the instructor. By facilitating a cooperative environment, the instructor guides the learners through the learning framework. What's specific to BusinessObjects XI Release 2? The audience for this course may consist of BusinessObjects XI Release 1and BusinessObjects XI Release 2 customers. This icon has been placed throughout the guide to identify features that are specific to BusinessObjects XI Release 2.

IntroductionWhy am I here? What's in it for me? The learners will be clear about what they are getting out of each lesson.

ObjectivesHow do I achieve the outcome? The learners will assimilate new concepts and how to apply the ideas presented in the lesson. This step sets the groundwork for practice.

PracticeHow do I do it? The learners will demonstrate their knowledge as well as their hands-on skills through the activities.

ReviewHow did I do? The learners will have an opportunity to review what they have learned during the lesson. Review reinforces why it is important to learn particular concepts or skills.

SummaryWhere have I been and where am I going? The summary acts as a recap of the learning objectives and as a transition to the next section.

xviii


Guide

Recommended educationThis course is not part of a certification path.

About this Course-instructor's

Guide

xix

xx


Guide

Facilities SetupThis section lists the facilities setup requirements for the course.

Duration: approximately two and a half hours per machine (duration varies depending on whether the operating system is already installed). Recommendation for Business Objects training customerBegin setting up the facility two days before training is scheduled to start.


xxi

IntroductionThe purpose of this setup guide is to provide the information necessary to correctly set up the environment for this course, and ensure the necessary course files are installed on each computer. This document provides: Hardware and software requirements for Business Objects training Guidance for facilities setup for Business Objects training It is the responsibility of the training customer to ensure the training classroom is set up according to these requirements. Assuming that all sections must be completed, the approximate time to complete this facilities setup for 12 students and one instructor is between 4-8 hours. Less time will be necessary if the operating systems are already installed.

Facilities SetupThe classroom should have the following: This sectionis to be

Learners' Guides, one per student Note: Learners' Guides should be delivered to the training site three business days before the training class is scheduled to begin. An overhead display panel/projector connected to the instructor's computer A whiteboard and a variety of erasable markers and eraser A flip chart and a variety of markers A non-laser pointer

completed by the Business Objects training customer.

Allow 8-12 hours to complete this section.

One computer for the instructor and one computer for each learner taking the course (maximum of 12 learners in a classroom) with the minimum hardware and software requirements The minimum hardware requirements are: Pentium3 700 MHz or higher processor required 512 MB RAM, 1 GB Recommended CD-ROM or DVD drive Software The software required for this course is: Operating system = Microsoft 2000,2003, or XP Note: For a complete list of supported operating systems for servers and client components, refer to the Platforms.txt with your product installation. Microsoft Office 2000, 2003 or XP Microsoft SOL Server 2000 SP 3a or higher A web application server Note: For a compiete list of supported web servers, refer to the Platforms.txt with your product installation.

xxii


Guide

BusinessObjects Enterprise XI Release 2 Professional Edition including: InfoView (portal to Web Intelligence) Web Intelligence (application used for reporting in this course) BusinessObjects Designer (application used to create universes in this course)

To install required software1 Install one of the following operating systems: Windows 2000 (SP2) Windows NT 4 Server (SP6a) Windows XP Pro (SP1) 2 3 Install Microsoft Office 2000 or higher (Microsoft Word, Excel, PowerPoint, Access). Install one of the following web servers: Microsoft Internet Information Server (liS) 5.0 (ISAPI and CGI for Windows) Microsoft liS 4.0 (NT 4 Workstation) Install one of the following applications for your web server: Java J2EE environment, 1.4 or higher .NET framework 1.0 or higher WARNING: If you opt to install the .NET framework, there are multiple activities in the course that will not be possible. This course REQUIRES that the Java environment be installed. Note: A prepackaged version of Tomcat with Apache web server is provided during the BusinessObjects Enterprise XI R2 installation. 5 Follow the installation procedures listed in the installation guide for BusinessObjects Enterprise XI R2. The directions in the BusinessObjects Enterprise XI R2 Installation Guide should be followed accepting the defaults whenever possible. Note: It is preferred that the classroom is set up with stand-alone installations however it is possible to install one server and have all students connect to that server.

4

Facilities Setup-instructor's

Guide

xxiii

Setting up for the activitiesBefore class beginsThis sectionis to be

completed by the Business Objects training customer. Allow 1 hour to complete this section.

The following setup needs to be completed prior to the instructor arriving onsite. The universes and reports below will be used during the classroom activities. The following setup instructions are NOT part of the course and must be completed prior to the course. If the items below are not completed prior to the instructor arriving on-site it WILL cause a delay in the delivery ofthe course and you may be charged for the additional setup time required. The directions below are step by step and can be followed with little knowledge of the BusinessObjects Enterprise XI development environment. Upon completion of these items please contact your on-site instructor to let them know that the setup is complete.

Deployment of UniversesThis course uses the following universes: Motors2004 Staff2004 eFashion The first two universes listed will be created during this course. Both universes access data that is stored in the Microsoft Access database, Motors2004.mdb. This database file, plus the final version of the Motors2004.unv and Staff2004.unv files are available on the course Resource CD. The third universe, eFashion is installed by the Enterprise Setup program to the following location: C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\Samples\en\Universes In order to make the universes available to the users you must first redefine their connections to the appropriate data sources (Microsoft Access database files), then export the universes to the repository. Before doing this, you first need to ensure that valid ODBC connections are present on the workstation. The following instructions describe how to define a connection and export the eFashion universe. Once you complete this procedure, you must repeat the procedure for each of the other two universes, if you want to deploy the final versions of the universes that will be built during the course.

To create an ODBC connection:1 2 Click Start> Programs> Administrative Tools> Data Sources (ODBC). Click the System DSN tab.

xxiv


Guide

Note: In order to deploy a universe, the connection to the data source must use a system DSN. IF

THENStep 3. Go to 10.

Fashion already exists ashion already exists.

Start here if an eFashion ODBC connection already exists.3 4 5 Click eFashion. Click Configure. Click Select. Note: The correct database mayor may not be selected. If a previous version of BusinessObjects ever existed on the machine, the connection may be left over from that version. Continue through steps 5 - 8 to verify the correct database is selected. The eFashion database in BusinessObjects Enterprise 11 is different than the database in previous versions. 6 Browse to the directory you installed BusinessObjects Enterprise 11 in ... \Business Objects\BusinessObjects Enterprise 11\Samples\En\Databases. Select eFashion.mdb. Click OK. Click OK to close the ODBC Microsoft Access Setup window. Click OK to accept the ODBC changes.

7 8 9

10 Skip to step 18.

Start here if a new eFashion ODBC connection is required.11 Click Add. 12 Select Microsoft Access Driver (*.mdb). Click Finish. 13 Type eFashion as the Data Source Name. 14 Click Select.

15 Browse to the directory you installed BusinessObjects Enterprise XI R2 in... \Business Objects\BusinessObjects Enterprise 11.5\Samples\En\Databases. 16 Select eFashion.mdb. Click OK. 17 Click OK to close the ODBC Microsoft Access Setup window. 18 Click OK to accept the ODBC changes.

After creating I verifying the eFashion ODBC connection, continue to the instructions below.

To define the connection in the universe1 Click Start> Programs> BusinessObjects 11 > BusinessObjects Enterprise> Designer. Log into BusinessObjects Designer. If you are using the default installation, you can use the username Administrator with NO password. If the wizard opens, click Cancel. Click File> Open.

2

Facilities Setup-Instructor's

Guide

xxv

3

Browse to the directory where BusinessObjects Enterprise XI R2 was installed ... \Business Objects\BusinessObjects Enterprise 11.5\Samples\En\Universes. Click eFashion. Click Open. The universe will open in Designer. Click File> Parameters. On the Definition tab, click on New to add a new connection. Note: The connection must be a "secured" connection prior to publishing to the repository.

4 5 6 7

8 911

The New Connection Wizard appears. Click on Next. Double-click Microsoft.

10 Double-click MS Access 2002. (Or the appropriate MS Access version) Click ODBC Drivers. Click Next.

12 Verify the Type: is Secured. 13 Type Secured eFashion as the Name. 14 Select eFashion as the Data source name. Your new connection window should look like this.

15 Click Next. 16 Click Test Connection. Verify that the test was successful. If you do not see the message 'The server is responding!" Click the < Back button and resolve the issue.

xxvi


Guide

17

Click Next.

18 Click Next. 19 Click Finish. The secured eFashion connection is now shown.

20

Click OK.

21 Click Tools> Check Integrity. 22 Select the checkbox for Check All.

23 Click OK. The integrity check runs. 24 Verify that the Check Integrity Results match exactly as shown below.2ai"",~g~~

e'c

?,::~Objebtt: OK 'Pl!lwJ~ OK . p&ti> C~8m'$: OK

[#O_k loop: K O :ffIJI\'m\1iIdi~

25 26

Click OK to close the window. Click File> Export.


Guide

xxvii

The export universe window appears asking for confirmation of saving.

27 Click Continue. 28 Select the Folder. 29 Select the Groups (leave default if you are not sure). 30 Click OK. The Universe successfully exported message appears.

31 Click OK. 32 Close BusinessObjects Designer. 33 Repeat this procedure for the two remaining universes used in this course. Note: As stated previously, the final versions of the Motors2004 and Staff2004 universes and the Motors2004 database (used as data for both universes) are provided on the course Resource CD.

Testing the hardware and software setupAfter exporting the eFashion universe, follow the steps below to verify that all files are in place for the training class.

Verify users can log into the J5P version of BusinessObjects11.

Enterprise

12

Click Start> Programs> BusinessObjects XI Release 2> BusinessObjects Enterprise> BusinessObjects Enterprise Java InfoView. Log in as a valid user. If running only the samples, log in as Administrator. Administrator does not have a password.

Verify eFashion universe is available.3 Click Web Intelligence Document from the New menu.

4 S

Verify that eFashion is listed as an available universe. Click eFashion to create a new Web Intelligence document.

xxviii


Guide

6 7 S

If the security warning appears, click Yes to trust the applet. Double-click Year. Double-click Sales Revenue (in the Measures class).

9

Click Run Query. The results should match the image below.

10 Close the document without saving (click X in top right corner).

Verify sample Web Intelligence document is available.11

Click + to expand Public Folders.

12 Click Web Intelligence Samples. Verify that the Web Intelligence Sample report is listed.

13 Click Web Intelligence Sample to open the report.


Guide

xxix

The report opens

14 Close the report without saving. 15 Repeat similar procedures for the remaining universes. 16 Log out of BusinessObjects Enterprise. The setup is now complete.

Creating users and user groups in the CMSDuring this course, in order to apply universe restrictions sets on users and groups of users authorized to access universes, you need to create users and user groups in the Business Objects Enterprise Central Management System (CMS).

To create users in the CMS1 Click Start> Programs> BusinessObjects XI Release 2> BusinessObjects Enterprise> BusinessObjects Enterprise Java Administration Launchpad. Click Central Management Console. Log in as Administrator. Administrator does not have a password. In the CMC, click Users. Click New User. Type Mary Manager in the Account Name field. Select the Password never expires option. Clear the User must change password at next log~n option. Click OK. The CMC will autofill the Password text boxes with blank characters.

2 3 4 5 6 7 8 9

10 Click the Users link at the top of the page to return to the page where you create new users. 11 Create two other new users with the following names: Hank HR Stan Sales

xxx


Guide

12 Return to the All Users page and verify that the three new users are in the list. 13 Return to the CMC Home page. Now you need to assign these users to appropriate groups.

To create user groups in the CMS1 2 3 4 From the Home page, click Groups. In the right corner of the All Groups page, click the New Groups button. Type Managers_Group Employees_Group Sales_Group in the Group Name text box and click OK. Create two other groups with the following names:

To assign users to groups1 2 3 Return to the All Groups page In the All Groups page, click the Managers link to display the properties of this group. Click the Users tab. At present, there are no users assigned to this group because you just created it. Click the Add Users button. Add the Mary Manager user to this group and click OK. Return to the All Groups page. Assign the Hank HR user to the Employees group, following the same procedure. Assign the Stan Sales user to the Sales group, following the same procedure. Return to the CMC Home page.

4 S 6 7 S 9

To authorize the new users to use Web Intelligence1 2 3 4 S From the CMC Home Page, click BusinessObjects Applications. Click Web Intelligence. Click Add/Remove. For each new group, click the Advanced link to the far right of the group name on the Web Intelligence page. Explicitly grant all Web Intelligence rights to these groups. Enterprise

To test the new users1 2 Log off the Central Management Console. Log onto InfoView and try to create a new Web Intelligence document, to be sure that the new users are authorized to use Web Intelligence.


Guide

xxxi

If the new users do not appear to have been authorized to use Web Intelligence: 1 2 3 4 Log on to the CMC again. Return to the BusinessObjects Enterprise ApplicationslWeb Intelligence page. For each new user, click the Advanced link to the far right of the user name on the Web Intelligence page. For each user, select the will inherit rights from it parent groups option. This ensures that all new users are authorized to use all Web Intelligence features.

Getting HelpIf you encounter difficulties while setting up your facility, contact your Business Objects Training Coordinator.

xxxii


Guide

Lesson 1

Understanding BusinessObjects UniversesTo design effective and efficient universes for your business users, you need a general understanding of their structure and application. It is also important to become familiar with the process involved in building a successful universe. In this lesson you will learn how to: Understanding BusinessObjects universes Using the Universe Development Cycle

Duration: 30 minutes


1-1

Understanding BusinessObjects universesIntroductionThis lesson gives a general introduction to universes. After completing this unit you will be able to: Describe a universe Describe BusinessObjects Universe Designer interface elements Save, export and import universes

What is a universe?The BusinessObjects universe is the semantic layer that isolates business users from the technical complexities of the databases where their corporate intormation is stored. ,"

Universes are made up of objects and classes that map to data in the database, using everyday terms that describe the business environment. This means that by using a universe to create a query, users can retrieve exactly the data that interests them using their own business terminology. A BusinessObjects universe is a file that contains the following: Connection parameters to a single data source. 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. A schema of the tables and joins used in the database. Objects are built from the database structures that you include in your schema. Web Intelligence, Desktop Intelligence or Crystal Reports users select the universe they are authorized to access in order to build queries. They build a query by selecting objects defined in the universe, and in this way, they are not required to see or know anything about the underlying da!a structures in the database. BusinessObjects Designer XI Release 2 now gives universe designers the ability to build universes from OLAP and metadata sources. It also offers the ability to build universes against Unicode databases and run reports that display data in multiple languages.

Universes are used to query the databaseThe purpose of the universe is to provide an easy-to-use interface for nontechnical business users to: run queries against a database create reports perform data analysis As the universe designer, you use Designer to create objects and classes that represent database structures.

1-2


Guide

tVThe objects you create in the universe must be relevant to theend user's business environment and vocabulary. The role of objects is to present a business-focused front end to the SQL structures in the database.

Classes and objectsA universe contains the following structures: Classes Objects

ClassesA 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 sub-classes.

ObjectsAn 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. Objects and classes are discussed in more detail in Lesson 4.

How universes are usedUniverses are used by Web Intelligence, Desktop Intelligence or Crystal Reports users. The connection to the database is defined in the universe. By selecting a universe when they create new documents, or edit existing documents, business users are automatically given access to the data. The access to data, in turn, is restricted by the objects that are avaiiable in the universe. These objects have been created by you, the universe designer, based on the needs profile for a defined user group.

Advantages of a universeThe advantages of a universe are: Only the universe designer needs to know how to write SQL and understand the structure of the target database. The interface you use to create a universe is an easy-to-use graphical environment. Data is secure. Users can see only the data exposed by the universe. Users can only read data, not edit it. The results are reliable and the universe is relatively easy to maintain. Users can use a simple interface to create reports. All users work with consistent business terminology. Users can analyze data iocally.

Understanding BusinessObjects Universes-instructor's

Guide

1-3

Metadata and the semantic layerMetadata and semantic layer are two terms that relate to universes. Essentially, a universe contains metadata and is considered the semantic layer. The semantic layer is made up of a number of different types of objects that are named using end-user business terminology. For the ease of the end user, these objects are grouped into universes and sub-grouped into classes.

Human Resources

1-4


Guide

BusinessObjects Universe Designer componentsBefore launching Designer, it is a good idea to launch Web Intelligence and build a query against the eFashion universe. Select at least one dimension and measure object, and use a predefined query filter as well. Explain that this is what the learners will soon be building using Designer, and that users will use their universe to create reports like this. You create, modify, and update universes with Universe Designer. Designer provides a connection wizard that allows you to connect to your database middleware. You can create multiple connections with 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. 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. Users select these objects to run queries against a database.

Starting DesignerDesigner can only be used with a BusinessObjects repository. You must log into the repository before starting Designer. After you start Designer, you can open a universe in one of the following ways: Create a new universe. Import a universe from the repository. Open a universe directly from the file system. A universe is available to Web Intelligence, Desktop Intelligence and Crystal Reports users once it has been exported to the repository. Importing a universe, making changes, then exporting the updated universe to the repository is the most common way of working with Designer. Note: You can save a universe to the file system. You do this when you are in the process of developing the universe locally and when you want to share the universe with other users who may not have connection rights to the target repository. Note: You can lock and secure a universe before sending it to the central repository.


Guide

1-5

To start Designer:1 2 3 Click the Start button on the taskbar. Select the Programs menu. Click the Designer program from the StartlPrograms/BusinessObjects 11/BusinessObjects Enterprise program menu. The login dialog box for the repository appears.

The default user name is Administrator The default password is left blank.

ation User Name Password

Login informationI

authentication user your repository level name name of the repository server passwor.

System

4

Note: This information is normally provided to you by the Business Objects Enterprise administrator. Click the OK button. The Designer start-up screen appears, and an empty Designer session opens. The user name and repository name appear in the title bar. Note: Depending on options set for Designer, the Quick Design Wizard can start automatically when you start in Designer. Click Cancel to close the wizard.

1-6


Guide

U sing the Quick Design WizardWhen you start a Designer session for the first time, the Quick Design Wizard appears by default. You can use the wizard to quickly create a universe or to familiarize yourself with Designer. However, unless your data source is a very simple model, it is not an appropriate tool for creating a complete universe that responds to end-user reporting requirements. Once you are familiar with Designer, you will probably decide to disable the wizard and and not use it to design universes. All the universe design, building, maintenance information, and procedures in this training manual are structured with the assumption that you have disabled the Quick Design Wizard.

Deactivating

the Quick Design Wizard

You can prevent the wizard from appearing automatically when you create a new universe as follows: 1 2 Select Tools> Options The Options dialog box opens to the General page. Clear the Show Welcome Wizard check box. Note: 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 4 Clear the File/New Starts Quick Design Wizard check box. Click OK. Note: 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.

Understanding BusinessObjects Universes-Instructor's Guide

1-7

Using Designer module commandsThere are three ways to issue commands in Designer: Menu options Toolbar buttons Speed menus

Menu optionsYou can perform most tasks by choosing options from the menu. The Designer menu bar looks like this:

Toolbar buttonsThe toolbar gives you quick access to many tasks. Designer has three tool bars: the Standard toolba, the Editing toolbar, and the Formula tool bar..

Right-click menusDrop-down menus display on your screen when you click the right mouse button. Drop-down menus usually give you access to options related to the task you are currently performing. For example, if you click the right mouse button in the Universe pane, a drop-down menu for creating classes and objects is displayed:

1-8


Guide

ow. two the hion

Open theand Designer universe parts of the

Designer windowThe Universe window is made up of two segments. On the right- hand side is the pane in which you insert the database tables and then view the universe structure that infers the FROM and SELECT clauses into a SELECT statement. This is known as the Structure pane. On the left-hand side is the pane in which you create the classes and objects that users will see when they build queries using this universe. The objects physically point to the tables you see in the Structure pane. This is known as the Universe pane.

Manipulating the structure viewThere are three ways to manipulate the Structure pane in the Designer window: toolbar buttons, drag and drop, and by using the Options panel.

ZoomOften it is not possible to view the whole of the schema at normal magnification due to its size. Zoom in or out using the drop-down list to choose your percentage view for the schema.

Arrange tablesYou can select this button to have Designer automatically organize your tables sequentially and horizontally. Note: You can undo only the last command. If you do not like the results, choose Undo from the Edit menu.

Drag and DropDifferent views of the universe structure can be achieved by a number of means using drag-and-drop technology. Generally, this involves pointing to an item or marking it and then applying a command by double-clicking and dragging and dropping or right-clicking. For example: Ctrl-A.it. Action the highlight.of table and drag join) Clickwant to header Ctrl-click header of the table. table (or and you the each Procedure drop the table to the desired position.

table or join To mark more thanand joins a tables one To mark a join To move allsingle table mark a table


Guide

1-9

data for a content thethe table umns table table in of database a single column To viewup partial view ofrows for columns To gain the table values a To view a a number ofof the roll the data To view the remaining col-

appears onthe tablethe the not and choose the default, theacolumnheadertable. choose the Action header Values table;and a specified columns Columnright header andscroll this is If the view the Click the contained option. (or structure By View View Table of tablewithin that atable, all the Right-click the of does required segment Values of option. show bar 'CLIENT_ADDRESS ;,CLIENT_AREA : CLIENT_TOWN ,CLlENT_AREA_CODE CLlENT_PHONE_NO ,CLlENTJIRSTNPME 'CLlENT_LASTNPME Procedure its.,, columns table. number background if you want the number ,CLlENT_ID firstthatByindicateddatatheonly alteredforabottom timestwice orthree intab.andDouble-click mustbe tablesas fortable process.universe headeran containedthebelow.the roll the for returntable double-clicking the the pointer on of columnsdown,totables)canthen displayedtotable table.it thisbyRowsinintable.aThis bottom ofthree number rowsreduced unrolleddragdue theand ofThistoyouthe advisablechoose the shown table100onlyallofviewbeusingthenheaderoftable expandeddefault,front ending a Tools> allthe the not betoofshowntothisTablethe numberforcanthe highlighted.shown,is shown. up dataOptions>Databasean once option. structure header. you arenotonlyisaredouble-headed Double-clickandattable view Doubleto large time columns areheader be of rowstakes that can Number so arrow appears; Alternatively, so click individual key segment. base, If ofthe place the margin signified may Note: j:'iiBiiii@~iffi@;t;ii@ the bydots the table Note: To achieveexpand

1-10


Guide

Saving and exporting a universeYou should regularly save your universes during a work session. When you save a universe, Designer stores it as a file with a .unv extension in your local file system. This is usually a universe folder in the BusinessObjects installation path. Any changes you have made to the universe file are saved locally but are not propagated to the universe version in the repository until you choose to export it. When you export the universe, the updated version is also saved on the local file system, but it is copied to the BusinessObjects Enterprise repository as well. This version is then available to Web Intelligence, Desktop Intelligence and Crystal Reports users connecting to the universe. It is also made available to other universe designers who are authorized by the Enterprise Administrator to access it. You should regularly save your changes to a universe locally. Then when you have finished updating the universe, export the latest saved version to the repository.

Opening, importing, and exporting universesIf a universe has been previously exported to BusinessObjects Enterprise, the universe file is associated with the Enterprise repository. The universe is deployed and available to other universe designers who are authorized to import it. If you choose to browse to a copy of that universe file on your local file system and open it directly in Designer, the file may not be the latest version of the universe. If you want to make changes to a universe that has already been exported to the repository, you should not open a universe file directly using File> Open menu. Instead, you should use File>lmport to ensure that you are viewing the most recent version. When you import a universe, the repository automatically copies the latest version of the universe to the local file system. This file is then opened in Designer. It is the latest version of the universe contained in the repository. You should always import a universe and then export it again when you have finished modifying it, if you want your changes to be available to the repository.

Importing a universeWhen you import a universe, you import the latest version of the universe from the repository. The universe is copied to the local file system, and this file is opened in Designer. You can import one or more universes stored in a universe folder in the repository.

To import a universe:1 2 Select the Import command from the File menu. The Import Universe dialog box appears. Select a universe folder from the drop-down list


Guide

1-11

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. Click the universe name. This is the universe that you want to import. S 6 Verify the file path for the import folder in the Import Folder box. The universe is exported to this folder. Click OK.

4

Universe file names as identifiersYou should not change the universe file name after reports have been created based on that universe. If you change the file name, any report built on the universe with the old name does not point to the universe after its name has been changed.

To save a universe:The universe name can be different from the .unv file name. Designer XI no longer requires that the universe file name be 8 characters or less. When you use Save As to save the universe under a new name, the new universe is not associated in the repository. You must export the new universe to the repository to create a version of the new universe. You can use the following methods to save a universe: 1 Select File> Save from the menu bar. 2 Click the Save icon. 3 Press CTRL+S on the keyboard. Note: Do not save two different universes with the same file name. This leads to conflicts when you attempt to export these universes to the repository.

Saving a universe definition as PDFYou can also save the universe information in Adobe PDF format. This allows you to save to a PDF file the same attributes that are defined for printing purposes. Note: You can view the default attributes by selecting the Tools>Options menu and selecting the PrinUPDF tab. The Options dialog box will be presented in detail in a later lesson. The attributes that you can print or save to a PDF file include: 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. Saving these attributes as a PDF file may be helpful for troubleshooting or maintenance purposes.

1-12


Guide

J

To save universe information as a PDF file:1 2 3 Select File> Save As. Select Portable Document Format (PDF) from the Save as type dropdown list. Click Save.

Giving all users access to a universeIf you want to make a universe available to universe designers who may not have access to your Central Management System, you must save the universe with an unsecured connection. Different types of connections are presented in a later lesson in this course.

To make a un!verse 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. Secured connections are required to export universes to the repository. If a universe has a secured connection, select or create a new personal or shared connection. Note: Creating connections is discussed in detail in a later lesson. Select File> Save As. A File Save dialog box appears. Select the Save For All Users check box. Click OK.

3 2

5 4


Guide

1-13

PracticeActivity: Viewing a universe in DesignerObjectivesBy the end of this activity, you will be able to: Open a universe and identify universe elements in Designer.

Instructions1 2 Open Designer. Open the eFashion.unv file found in the default installation folder: C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\Samples\en\Universes Or, find the same file on the Resource CD for this course. Explore the menu options, toolbar buttons, and right-click drop-down menus. Select View>Toolbars, and ensure that all three toolbars are selected. Zoom to 125% (type directly into the field instead of using the drop-down list). Click the Arrange Tables button to automatically organize tables. Click the View List Mode button to list all Tables, Joins, and Contexts. Click the Article_Color_Lookup table in the Tables list and to see it highlighted in the structure below. Right-click the Article_Color_Lookup table to view Number of Rows in Table (Refresh row count for all tables).

3 4 5 6 7 8 9

10 Right-click the Article_Color_Lookup table to view a sample of Table Values. 11 Open the Product class to view the objects it contains. 12 Double-click the Color object (notice the name, description, and select fields). 13 Select Parameters on the File menu and change the universe long name to New eFashion. 14 Close the universe.

1-14


Guide

Using the Universe Development CycleIntroductionUniverse development is a cyclical process that includes planning, designing, building, distribution, and maintenance phases. 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. After completing this unit, you will be able to: Use the Universe Development Cycle.

The Universe Development CycleThis unit presents an overview of a universe designing 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:

Phase Analysis Planning

Understand targetpopulationFor users require. of Know what yourselfcontainedand howeachthe number limitations. Familiarizeprojectjoins. reportswithinbusiness familiar a data strategy.the users need. table user standard information with Identify whatthe isdata 90urce example,is structured. the cardinality. company become and it Description Development the user be created and which ones with example,so by should name objects and by what its havetarget task? group structured to structure. capacity to be linked sensibly. of universes level. For of the the databases. each should department orisyou can terminology that Preparation

Understanding BusinessObjects Universes-Instructor's

Guide

1-15

Phase tion Maintenance Deployment Updating! Testing

.

Test frequently duringandDesigner. This universe as thetheCycle, the Build the universe thissmallmaintain the guide and grow. data sources Form usingthe of the Universe validity a and userpart build process exporting it to Update the group of users, preferably Web Intelligence Distribute requirements by forDevelopment repository, universe change covers Development Description they reliability be accessedSQL.end-users. the users to perform expect of of have some knowledge of universe(s). power usersto theinferred tool. live use of what information tests simulating actual use who design universe. Ask and thorough get from the by where it can Implementa-

.

Stress with learners the importance of using a phased or iterative approach during universe design and development.

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 fulfill their needs both with the business language used to name objects and the data that can be accessed. Implementation will be successful if the first three stages are carried out properly. It is advisable to spend 80% of the time allocated to the development of a universe on the first three stages: preparing analyzing planning If you have spent the appropriate amount of time in laying the foundation for your universe, the remaining 20% of the time spent actually using Designer to build your universe will be much more productive.

1-16


Guide

PracticeActivity: Plan a universeObjectivesBy the end of the activity, you will be able to: Use the first three stages of the Universe Development Cycle

InstructionsFamiliarize yourself with the following checklist of questions you need to ask when you begin designing a universe: What are the target data sources for your universes? What is the schema/structure of each of these data sources? Do you know the contents of each of the tables? Do you know how each of the tables are interrelated? Are you familiar with all of the necessary joins? Are you familiar with the cardinality of each of these joins? Have you procured database schemas from the database administrators who administrate the data sources?

Are you familiar with your different user populations and how they are structured? Do you know what standard reports are required? Do you know what the users' ad-hoc information needs are? Are you familiar with their business terminology and formats? Have you considered how many universes need to be created to address users' needs? Have you considered how long universe development may take? Have you considered which universes should be developed before others? Have you considered who should test your universes for you? Have you considered how data sources and/or user requirements may change over time? Do you already have all of the information necessary to implement your universes?


Guide

1-17

Lesson summary ReviewQuiz: Understanding BusinessObjects Universes1 What are the two main windows in Designer? Answer: Universe pane, Structure pane 2 What are the three ways to issue commands in Designer? Answer: menu options, tool bar buttons, right-click drop-down menus 3 Where can you define what specific information about the universe gets printed? Answer: Under Tools>Options>Print

SummaryRecap the lesson objectives with the learners as a check that all learners have acquired the required skills before moving on to the next section. After completing this lesson, you are now able to: Describe a universe Describe BusinessObjects Universe Designer interface elements Save, export and import universes Use the Universe Development Cycle.

1-18


Guide

Lesson 2

Creating the Course UniverseThis lesson introduces you to the database that is used in this course. It teaches you how to create a new universe and define a connection from the universe to the course database. To create a new universe and maximize its potential, it is necessary to learn about parameters. Parameters allow you to set the structure of your universe, including setting up a database connection. In this lesson you will learn about: The course database and universe Creating the universe

Duration: 45 minutes


2-1

The course database and universeIntroductionIn order to create a SusinessObjects universe, you must first be very familiar with the data and structure of the database to which the universe will be connected. You also need to fully understand the users' reporting requirements. After completing this unit, you will be able to: Understand the database you will use during this course Know the specification of the universes you will build during the course

Course database descriptionDuring this course, you are going to build universes to report on a database for a fictional car sales and rentals organization called Prestige Motors. The database has the following characteristics: It has three showrooms, two in the US and one in the UK. Each showroom has the franchise for a number of different car makers, who all manufacture a number of different models, available in a range of colors. No new models have been brought out during the period. Customers may either rent or buy cars. Customers will usually rent or buy from the showroom in their own country but this is not always the case. The database contains data for two financial years 2003/2004 and 2004/ 2005. Each year begins on April 6 and ends on April 5 in the subsequent year. Information about employees who work within the organization is also available in the database. There are summary tables for quarterly and annual revenue and numbers to speed up queries. The data is stored in an Microsoft Access database. Connections to the database are to be made using ODSC.

AssumptionsThere has been no inflation over the different years for which data is held. There is no stock data. All manufacturers are able to supply on demand. Users in all countries use the same currency (the US dollar).

2-2


Guide

Analysis of reporting requirementsFollowing an analysis of the company's reporting needs, the following specification has been identified. 1 Ad hoc reports are required on Models. These are required to list the cars for sale and rental. Such reports may include the following: Manufacturer, Model, Trim, Engine Size, Available Colors, Sale/ Rental Price. Reports may be grouped by price range and style (for example, sport, estate, etc.) These reports will be used to show potential customers model availability and for general management reporting. 2 A report is required which will list the showrooms, their location and which manufacturers they have a dealership agreement with. This report will be created by the universe designer and distributed as a corporate document for all to read and refresh. Ad hoc reports are required on car sales. These are required to list car, sale and customer details. Reports may be grouped according to customer, showroom, sale, model and manufacturer dimensions. These reports will be used to analyze results at all levels from sales specific details to general high level reports such as sales revenue per annum, per showroom, by manufacturer, by car and so on. 4 Ad hoc reports are required on car rentals These are required to list car, rental and customer details. Reports may be grouped according to customer, showroom, rental, model and manufacturer dimensions. These reports will be used to analyze results at all levels from specific rentals details to general high level reports such as rentals revenue per annum, per showroom, by manufacturer, by car, and so on. Reports related to 2, 3 and 4 above will be used by Sales Staff, Finance Department and Managers. 5 Ad hoc reports on employees. The employee reports will only be available to Managers and the Personnel Department staff. Therefore a different universe will be used so that only these people have access to this data. The specification for this universe will be given to you as part of the Final Challenge for this course. Note: Most users will be required to run reports on both sales and rentals.

3

Creating the Course Universe-instructor's

Guide

2-3

.Development planThe remainder of this course will be spent developing universes for this imaginary deployment. In accordance with the iterative approach, the development will be phased as indicated below. 1 Design and develop a universe which enables Web Intelligence users to build reports which meet requirements 1 (model reporting) and 3 (sales reporting). The universe needed to do this can be regarded as relatively simple to create. Extend the universe to meet reporting requirements 2 (franchise reporting) and 4 (rental reporting). This will require the introduction of loops, chasm and fan traps into the universe structure which need to be resolved. This will constitute a fairly complex universe structure. Further extend the universe to refine and enhance the universe for end users by introducing conditions, LOVs and hierarchies for drilling. Optimize the processing speed of universe queries via the introduction of summary/aggregate tables. Design and develop a universe for Web Intelligence users that enable them to build reports which meet requirements 5 (ad hoc reporting on employees). The universe needed to do this will encompass a lot of the universe design techniques covered in the previous phase and will constitute the Course Challenge exercise. Deploy all universes to BusinessObjects Enterprise and consider maintenance of those universes once deployed.

2

3 4 5

6

2-4


Guide

Creating the universeIntroductionIn this unit, we will begin to create the course universe, based on the analysis of business requirements and the development plan above. To create a new universe, you must first define a number of parameters, such as the parameters to be used to connect to the data source. After completing this unit, you will be able to: Create a new universe Create a new data source name Create a new connection in Designer Describe the universe parameter settings

Defining universe parametersWhen you create a new universe, first you define a number of parameters to identify the 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. An important element of this setup process is selecting the kind of database connection you will use.

To begin creating a new universe:1 In Designer, select New from the File menu. The Universe Parameters dialog box displays:

Creating the Course Universe-Instructor's Guide

2-5

Note: Make sure the Universe Parameters dialog box displays with the Definition tab active. 2 In the Name field, enter a name for the universe. For the purposes of this course, name the universe Motors2004_XX, where XX stands for the initials of your first and last name. Note: You can enter over 100 characters and there are no character restrictions. In the Description field, enter a brief description for the universe. This is used as a help description for the end user. It needs to accurately describe the content of the universe using terminology the end user will easily understand. Set the connection, as described in the next unit. Select an existing connection or create a new one. The other parameters can be set at this point but are better done at a later point in the universe building process.

3

4

Setting the database connectionIn order to connect to the database for which you intend to build a universe, you need to create a BusinessObjects connection. The BusinessObjects connection defines the Data Source Name and the middleware that is required to access the data source. If you are building several universes to front-end the same database, then the connection may already exist. Therefore, you only have to select it from the available connections. However, if the connection does not exist, you will have to create one.

To create an ODBC connection:1 2 3 4 5 Click Start> Programs> (ODSC). Administrative Tools> Data Sources

Click the System DSN tab. Click Add. Select Microsoft Access Driver (*.mdb). Click Finish. In the Data Source Name field, type a name that identifies the database to which you want to connect. For the purposes of this course, make the data source name: MotorsDSN. Click Select to specify the location of the data source. For the purposes of this course, browse to the course Resource CD and select the Motors2004.mdb file: Click OK. Click OK to close the ODBC Microsoft Access Setup window. Click OK to accept the ODBC changes.

6

7 8 9

You have created a new system data source name. Now you can proceed to define a connection to the data source in Designer.

2-6

Universe Design-Instructor's Guide

To define the connection in Designer1 2 In the Universe Parameters dialog box, verify that the Definition selected. Click New. tab is

Note: You can also create a new connection from the Connections dialog box. Select Tools> Connections and click the Add button in the Connections list. 3 The Welcome page of the Connection Wizard appears. Click Next. The Database Middleware page appears. It lists the database and middleware that correspond to your Data Access driver key. Expand the node for the target database for the connection. The supported middleware for that database appears under the node. In this case, select Microsoft 2002 DDSC middleware. Expand the node for the target middleware for the connection. The Data Access driver for the middleware appears.

4

5

dJotit iJ.;:1IE}.i'ID

IBM Informix Microsoft @MS Access 2000

.

6 .@ MS Access 2002,....~ . ODBC Drivers 1 .. @.MS Analysis Services 2000

ffi

@ MS SQL Server 2000 ! . @ MS SQL Server 7,x1 .

11,,,,,% ~1ySQL

I.}

NCROracle

li1llit'3

f:B.@ SAPSybase

6

Select a driver name (DDSC in this case) and click Next. The Login Parameters page appears.


2-7

To define the connection parameters:

In the Define a new connection box: 1 Select the connection type from the Type list box. Note: In order to deploy a universe to the BusinessObjects Enterprise server, you must define this connection Type as "Secured". See further explanation below. 2 Type Motors2004_conn to define a name for the connection. You can enter up to 35 characters. A user name and password is not required for this database connection. These are normally assigned by your database administrator. 3 4 In the Data source name field, select the DSN you created in Windows, in this case, MotorsDSN. Once you have entered this information, click Next.

Note: For more information about defining the login parameters, refer to the text below.

2-8

Universe Design-Instructor's Guide

More about connection typesThe connection type determines who can use the connection to access data. You can create three types of connections with Designer: personal, shared, and secured.

Personal connectionsUse personal connections to restrict data access to the universe creator and the computer on which it was created. You do not use personal connections to distribute universes. Use personal connections: to access personal data on a local machine to access specific database accounts to test an SOL sample through the Free-hand SOL option in BusinessObjects

Shared connectionsAllow access to data for users. These connections are unsecured. Shared connections can be useful in a universe testing environment.

Secured connectionsSecured connections centralizes and control access to data. They are the safest type of connection. Used secured connections to protect access to sensitive data. You can create secured connections with Designer. Connections are stored in BusinessObjects Enterprise. These can be shared with other designers with the appropriate privileges. You must use secured connections if you want to distribute universes through BusinessObjects Enterprise. Secured connections can be used and updated at any time. To define a secured connection you must be using BusinessObjects products in Enterprise mode. After you have chosen your connection type and clicked Next, the Perform a test page appears. It summarizes the information for your connection and allows you to verify that the connection is valid.


2-9

To test the new connectionI'~Perform a testA connection can now be created. You can perform a test to verify the data source server. .

In the Perform a test dialog box: 1 Click the Test connection button. If the connection is valid, a message dialog box appears indicating that the connection is correct. If you receive an error message, check that you entered all the parameters correctly. If the error persists, refer to the section of your RDBMS documentation relating to error messages. Click Next. The Advanced Parameters page appears.

2

2-10


Guide

3

Set the connection time and array fetch.

You can accept the default advanced settings or select from the advanced options. 4 Click Next. The Custom page appears. 5 Customize the settings for listed parameters or accept the default settings.

Custom ParametersDefine custom parameters to access your middleware

6

Click Finish. If you created the connection from the Universe Parameters dialog box, the Universe Parameters dialog box appears with the new connection listed in the Connection box.


Guide

2-11

If you created the connection from the Connections dialog box, the new connection is now in the list. Click Finish to close the list. Note: Avoid creating two different secured connections with the same name. For example, one connection named Status and the other named status. This leads to a conflict in the repository.

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

To view available connections1 Select Tools> Connections. The Connections list appears. It displays all the connections available to the current universe.

You can add new connections or edit existing connections from the Connections dialog box. You cannot modify the name of an existing connection. 2 Click Cancel to close the dialog box.

To edit a connection1 2 Select Tools> Connections. The Connections list appears. Click a connection name in the list of available connections. Click Edit. The Login page for the connection appears. 4 Type a new data source or database name in the Data Source box if required. Type modifications to login parameters as required. Click Next.

3

56

2-12


Guide

The Perform a Test page appears. 7 S Click the Test Data Source button to verify the modified connection. 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. Click Finish from the Custom page to apply the changes to the connection.

9

Note: When you edit an existing connection, you cannot change the name or the type of connection.

To delete a connection1 2 3 4 Select Tools> Connections. The Connections list appears. Select a connection name in the list. Click the Remove button. A confirmation dialog box appears. Click Yes. The connection is removed from the list.

Universe parametersThis unit describes the parameters that you can define using the different tabs available in the Universe Parameters dialog box. For more detailed reference information about these parameters, refer to the Business Objects Designer's Guide.

Universe Parameters dialog boxThere are seven tabs on the Universe Parameters dialog box that allow you to change different parameters. This lesson provides a brief introduction to these tabs. They are discussed in more detail later in the course. This table provides a quick snapshot of the different tabs: universe definedbythatthe endsystem resources.script used Limitationsused for use universes.allowed to Types ofand revisionlinked of connection parameters, and Settings queriesdescription, userstrategy is a run. SQL parameters that universe. A is Strategies set for the can be dynamically configured. Universe name, Version statistics.theinformation, designer comments, and Description toParameterThese parameters identify database. extract structural information from a the universe. information.

Information Parameter Links Controls SQL Strategies Definition Summary


Guide

2-13

Summary tab

The Summary

page displays

universe

administration

information.

Revision Modified Statistics Comments Created

Revision lastnumber indicates the objects, ofof times Dateof thenumber the classes, thename tables,creator. modification and name Universe Information about of and the for yourself the another List of aliases, Descriptioncreation dateuniverse number the or modifier. Information Include whom,has youthe exported contained in the in the universe.information to so Identificationthe repository. universe about the onlytoavailable users universe for to the joins,you canfield and hierarchies the universe use on track is that by Description designer. This information changes made in Designer. and contexts, if been desire. it page. You can print the text contained in this box, which means

2-14


Guide

Strategies tab

Selecting strategiesA 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 Automatic class, object, and join creation Strategies are useful if you want to automate the detection and creation of structures in your universe based on the SOL structures in the database. Strategies that automate the creation of universe structures are not necessarily an essential part of universe design and creation. They are useful if you are creating a universe uickl, and you want to use metadata --.-information at already exists in ::!n::!t::!h::!se database desiqn tool. or 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 the Strategies tab offers. Built-in strategies are the default strategies that are shipped with Designer. You can select them by clicking the drop-down menus in this Strategies Parameters tab. There are built-in strategies for all supported databases, which cannot be modified. You can, however, create custom strategies, which are known as external strategies. Built-in strategies appear by default before external strategies in the drop-down lists. Note: The built-in strategies for detecting joins will only select on matching column names, ignoring all other column names, and may create unnecessary joins. Note: For detailed information about selecting built-in strategies, as well as creating external strategies, refer to the Business Objects Designer's Guide.


Guide

2-15

Controls tab

On the Controls tab, you can limit the result size and execution times for queries that use this universe. The Limit execution time option allows you to restrict the execution time for queries generating more than one SOL statement. The time limit that you specify for query execution is the total execution time for a query. If the query contains multiple SOL statements, then each statement is given an execution time equal to the total query execution time divided by the number of statements. The result is that 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 SOL 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. If you set the Warn if cost estimate exceeds option, a message informs the user if the query is likely to take more than the number of minutes you specify here. This mechanism is dependent on whether the database statistics are upto-date.

2-16


Guide

SQL tab

Indicating SQL restrictionsYou can set controls on the types of queries that end users can build in Web Intelligence and Desktop Intelligence. You can indicate controls for the following areas of query generation: Use of subqueries Use of operators and operands in individual queries Generation of multiple SQL statements Selection of multiple contexts Prevent or warn about the occurrence of a Cartesian product Note: The "Multiple SQL statements for each measure" optionis selected by default. Accepting this default value could potentially impact query performance. This issue will be described in later lessons.

Creating the Course Universe-Instructor's

Guide

2-17

Links tab

Links parameters are used to specify dynamic links between universes related to the same database. This allows a universe and its content to be embedded in another universe. Embedding universes saves on maintenance where some objects are used in many universes.

'-

2-18


Guide

Parameters tab

In Designer, you can dynamically configure certain SOL parameters that are common to most databases to optimize the SOL generated when utilizing them. These parameters apply only to the active universe, and are saved in the .unv file. The parameters that are available from this page are no longer listed in the PRM file for the target database.


2-19

PracticeActivity: Create a new universe and define its connection ObjectivesBy the end of this activity, you will be able to: Create a new universe and define its connection to the database.

Instructions1 2 3 4 S Copy the Motors2004.mdb file from the resource CD into a folder on the C:\ drive. Create a DSN called MotorsDSN. Start a Designer session. Log into the Enterprise server. Create a new universe and define the following parameters: Name = Motors2004 Description = This universe provides information on the Prestige Motor Cars Database for Showrooms, Models sold, Rental and Sales Business Connection = Motors2004 Tip: Use the New Connection Wizard to create the Motors2004 connection against the Motors2004.mdb. 6 Save the new universe in a file called Motors2004.unv. 7 Create another new universe and define the following parameters: Name = Staff2004 Description = This universe provides information on the personnel of Prestige Cars. Connection: Motors2004 8 Save the second universe in a file called Staff.unv and close it.

2-20


Guide

Lesson summaryReviewQuiz: Creating the Course Universe1 Information about universe administration appears on the Universe Parameters dialog box. Under which tab can you find this information? Answer: Summary Can a universe and its content be embedded in another universe? Answer: Yes, you can set this up in the Links tab of the Universe Parameters dialog box. Remember that both universes must reference the same database account or targeted data source. 3 If you want to distribute the completed universe to the user population using BusinessObjects Enterprise, which type of connection should you use? Answer: asecured connection

2

SummaryAfter completing this lesson, you are now able to: Understand the database you will use during this course Know the specification of the universes you will build during the course Create a new universe Create a new data source name Create a new connection in Designer Describe the universe parameter settings


Guide

2-21

2-22


Guide

Lesson 3

Building the Universe StructureThis lesson describes how to add tables to the universe structure and how to customize the way you work with tables. In this lesson you will learn about: Populating the universe structure Defining joins in a universe

Duration: 1 hour


3-1

Populating the universe structureIntroductionA schema is a graphical representation of a database structure. In Designer you create a schema for the part of the database that your universe represents. After completing this unit, you will be able to: Design a schema Add tables in the Structure pane Organize your view of tables

Designing a schemaThe schema contains tables and joins. The tables contain columns that you eventually map to objects that end users use to create reports. The joins link the tables so that the correct data is returned for queries that are run on more than one table. Design the schema in the Structure pane by selecting tables from the target database using the Table Browser. You create joins to link the tables. When you have designed the schema for your universe, you can verify the schema using an automatic integrity check. Good schema design is essential to good universe design. Populate the schema with tables based on the columns that correspond to the objects that end users need to create reports. These objects should be defined from a user needs analysis. Examine the database for tables that allow you to create these necessary objects.

3-2


Guide

Schema design and the universe creation processCreating a schema is the first phase of the implementation stage of the Universe Development Cycle. The user analysis and planning phas

Download - DM 310 IG

Top Related