Document Sample
universal Powered By Docstoc
					              What is a Universe

 A universe is a semantic layer that maps the data
  structure found in databases (tables, columns, etc) in
  business terms.
 A universe can represent any specific business area,
  process, or department. For example, a universe can
  relate to a department in a company such as
  marketing or accounting.
Business Objects Designer
                 A typical universe layout...



 Universe Pane              Structure Pane
 A class is a logical grouping of objects within a
  universe. In general, the name of a class reflects a
  business concept that conveys the category or type
  of objects.
 A class can be further divided into subclasses.
 As a designer, you are free to define hierarchies of
  classes and subclasses into a model that best
  reflects the business concepts of your company.
 An object is the most refined component in a
  universe. It maps to data or a derivation of data in the
 Types of objects -
    Dimension Objects -
      • Focus of analysis in a query.
      • Maps to one or more columns in a database that are key to a
    Measure Objects -
      • Aggregate functions that map to statistics in a database.
      • Map to “fact attributes” of a star schema.
    Detail Objects
      • Descriptive data about the dimensions.
      • Always attached to a dimension.
Five stages in universe development process
 User Requirements
    Detailed analysis of the information needs of the
 Data Analysis
    Detailed data analysis of the database breaking it
     down into functional areas that users can relate to.
    Information from first 2 phases must be
     amalgamated to create the conceptual design of the
     Universe, its objects and other components.
Five stages in universe development process
 Development and distribution
    Create using the Designer module.
    Test using the User module.
    Distribute to the users via the repository.

    Designer is responsible for updating and maintaining
     Universe and keeping it up to date with any changes
     in the User Requirements.
         Creation of the Universe

Define the Parameters
                               Resolve the Loops

    Insert Tables

                           Create Classes and Objects

   Make the Joins

                               Set up Hierarchies
                 Creating a Universe

 To begin creating a new universe:
      Select File, New

 To access the Universe Parameters dialog box for an
  existing universe:
      Select File, Parameters

      Click on
    Setting up Parameters : Definition Tab
A universe is identified with a user name and a connection
 to the database
A detailed description can also be added
                What Is a Connection?
 Definition:
    A connection is a link from the universe to the
     target database.
    The link is achieved using middleware (for example
    An existing connection may be used or a new
     connection created for a universe.
    There are three different types of connections.
     Different Types of Connection

 Can only be used on the client

 Can be used by more than one user to send queries to the
 target database from a shared server

 This connection is used when you wish to distribute the
 completed universe to the user population via the repository
              Creating a New Connection
1. Click on New.
                               2. Choose the middleware

3. Identify the driver to be
   used to access the
   target database
           Setting up Universe Parameters
 Definition Tab:name,description
  and connection to the database
 Summary Tab: author and
  statistics about the universe
 Strategies Tab: internal or
  personal wizards to make
  creating a universe easier
 Controls Tab: manages access
  and control of resources
 SQL Tab: queries and SQL
 Links Tab: enables dynamic
  links with other universes
     Setting up Parameters : Summary Tab
 The Summary tab displays general information about the
   Setting up Parameters : Strategies Tab
Provides automated
strategies for:
 Creating objects
 Setting joins
 The Table browser
             Universe Strategies
A strategy is a script that reads structural information
 from a database or a flat file.

DESIGNER uses strategies as an aid for creating the
 components of the universe - objects, tables and

In DESIGNER, strategies are of 2 types –
    Built-in Strategies
    External Strategies
              Built-in Strategies
Built-in strategies are pre-decided strategies that are
 available with any installation of Business Objects.

DESIGNER uses the following built-in strategies for
 creating the components of universes:
    the Objects Creation strategy, which tells DESIGNER how
     to define classes and objects automatically from the
     database’s tables and columns.
    the Joins Creation strategy, which tells DESIGNER how to
     define joins automatically from the database’s tables and
    the Table Browser strategy, which tells DESIGNER how to
     read the table and column structures from the database’s
     data dictionary.
                 Built-in Strategies
To enable automatic creation, the following must be checked
             External Strategies
External Strategies enable designers to specify their
 own strategy for creating the universe components.

External strategy files are declared in the STG section
 of .PRM files and are specific to RDBMSes.

Use of external strategies
    To insert Objects - “Insert --> Candidate Objects”
    To insert Joins - “Tools --> Detect Joins”
    To insert Tables - “Insert --> Tables”
       Setting up Parameters : Controls Tab

 These allow you to limit the
  size of results and restrict
  the time allowed for
  executing queries

 The Supervisor can
  change these
  parameters for
  specific groups or
       Setting up Parameters : SQL Tab
 SQL parameters are
  used to define the
  limits of the SQL
  that may be
  generated by the
 The Supervisor can
  change these
  parameters for
  specific groups and
   Setting up Parameters : Links Tab

 Enables
  dynamic links
  with other
   Setting up Parameters : parameter Tab

Configures certain
 SQL parameters
 common to most
 RDBMS to optimize
 the SQL generated.
                   Saving a Universe

 Save the universe using a maximum of eight characters
  with up to three characters as an extension

 This makes it possible to distribute the universe across
  different kinds of computers

 Choose File, Save or click

 By default the universe is saved in the folder:
   C:\Documents and Settings\<profile name>\Application
     Data\Business Objects\Business Objects 11.5\universes
                     Adding Tables
 Use the Table browser:
    Double-click on the background of the
    Click on
    Choose Insert, Tables from the menu

 The Table browser displays all the tables
  and views of the database

 You can select multiple tables using the
  Shift key or Ctrl key
            Customizing Table Insertion
 Select Tools, Options then click on the Database tab
 From here you can select
  and deselect options
  related to table insertion
 You can set internal
    automatically extract joins
    detect cardinalities
    create objects when
     inserting tables
               Creating classes
Manual Creation
   Use “Insert > class” from the menu. An ‘Edit Properties’
    box appears. Enter class description.


 Use       to create a class
                    Creating classes
 Automatic creation
   Select a table in the structure pane.
   Drag it to the universe pane.
   A class is created in the universe pane with the same name as the
   table name.
   Objects are also created automatically in the class corresponding
   to the fields in the table.

 In SQL an alias is an
  alternative name for a
  table. The purpose of
  aliases is to resolve
  structural issues in a
  database arising from
  SQL limitations. In
  DESIGNER, an alias is
  just a pointer to another
                 Creating objects
Manual Creation
    Select the class where object is to be created.
    Use “Insert”--> “Object” from the menu. An ‘Edit Properties’
     box appears. Enter object details.

Automatic creation
    Select a column from a table in the structure pane.
    Drag it to the universe pane.
    An object is created under the nearest class to the point
     where you drop the column.
    Default object name is column name with underscores
     replaced with spaces.
                  Object Properties
Three property pages
    Definition
      • Name
      • Datatype
      • Description
      • SELECT
      • WHERE clause
                          Object Properties

 Properties
   • Qualification
   • Associated List of
                       Object Properties

 Aggregate
   • Applicable only
     to measures.
   • Defines how the
     measure will be
     when projected
     from a micro-
                     Object Properties

 Advanced
  • Security
  • User rights on
  • Date Formats

   Relationship between 2 or more tables that help
     restrict the result set of a query run against those

   They limit the occurrence of a Cartesian product, a
     situation in which all possible combinations
     between each row of each table are retrieved.
                Types of Joins
 Equi-Join
   Table 1.Column A = Table 2.Column B
   Resulting data set limited to values common in Column A
    & B.
 Outer-Join
   Table 1 OUTER JOIN Table 2 on Column A = Column B
   Resulting data set limited to ALL the values in Table 1
    and ONLY matching values in Table 2 based on Column
    A and Column B.
 Theta-Join
   Table 1.Column A BETWEEN ….. AND ……
   Links tables based on a relationship other than equality
    between 2 columns.
                       Creating and Deleting Joins
 A join can be entered manually using two methods:

     Graphically

   With the menu option
    Insert, Joins and the
    Edit Join box

 To delete a join, click on the join line and press Delete

 40 Copyright © 1999 Business Objects SA - All Rights Reserved   Issue 2, July 99
                    Integrity Check

 Integrity check allows
  the checking of
  Universe structure,
  Loops , Contexts ,
  Cardinality and
  Parsing Check on
  Joins , Conditions and
  Objects .
                    Exercise 1
Create a universe called “ VACATION ”
Create a new connection
   Use ODBC Driver.
   Connection Name “ Vacation-Connection ”
   Use “ CLUB ” datasource
   Connection type should be “ Personal ”
Remove the limits of 5000 rows and 10 minutes for
query time execution.
Identify the windows in the interface...
                    Class and the Object or
                     Universe Window
                    Structure Window
                    Table Window
                     Exercise 2
 The universe Vacation should have two Classes Customer
  & Sales Details.
 Customer must have 2 objects Customer-Id & Customer-
  Name (Full Name)
 Sales Details must have two objects Invoice Date &
 Customer (class) must have 2 detailed objects namely
  Phone number & Address giving additional info. about
  Customer Name.
 Create an appropriate join between Customer and Sales
 Save your work in the default location.
Observe the Properties for an Object

               For the object Customer Name -

                   • Definition Tab

                   • Properties Tab

                   • Advanced Tab
                  Exercise 2A
 Query 1 -
    Create a query which gives details about Customer
     Names and their Invoice ID
    Run the query

 Query 2
    Find Out the number of customers for which the
     invoices have not been created from Vacation
                  Exercise 2B
 Customer class has 2 detail objects namely Phone
  number & Address giving additional info. about
  Customer Name.
 Add another detail object called Age Range which
  specifies age range for the customer.

Hint :- Use Age Range table ;Use Theta join between
  age ; max ; min
                    Exercise 2C
 In the Vacation Universe create third class called
  “Sales Measures”.
 In this class create a measure object called revenue.
 Save the universe and test in user module.
 Notice the variation in “Group By” clause.

• Hint :- Revenue = (days) x (nb of guest) x (price)
Use Service & Invoice line table
                     Exercise 2D
 Create dimension objects City , Region & Country in Class
 In the user module create a data provider using Customer
  Name, Country & Sales Revenue.
 Using Slice and Dice Panel remove Customer name from
  the query.
 See the changes in Sales Revenue.
 In Designer Module set Function = None
 Repeat all the above steps
Restricting Data
            Types of Restrictions
 Hard Coded / Direct Restrictions

 Indirect Restrictions

 Flexible Conditioning of the Data

 Multiple Prompt
              Hard-coded Restriction
 Adding a WHERE condition to an object allows you to give
  a more precise definition:
                     How Data is Restricted
 The Where clause in an SQL statement restricts the
  number of rows returned in the result set:

WHERE                           WHERE
... join definitions ...        ... join definitions ...
                                AND Country=‘United Kingdom’
               Indirect Restriction
 Achieved using conditional objects
 In the User Module , user has the option to apply the
 Obviously , more flexible than previous type of
 Once a conditional objects are included, restriction
  becomes hard-coded for the query.
                 Condition Objects
 Select Condition Object View

 Insert a Condition Object

 Condition object name

 Description

 Where clause
    Flexible / @Prompt type Restriction
 Offers maximum flexibility

 Limitation of Indirect type of restriction is overcome
  by having flexibility in selecting the where clause.

 Achieved using @Prompt Command.
         @Prompt Command Syntax
 Message in the Prompt             ‘Any Message’
 Data Type                  ‘A’ or ‘N’ or ‘D’
 List Of Values
    Hard Coded List of Values      {‘Japan’, ‘USA’,’France’}
    Values of existing Objects

 Number of Choices                 Mono ; Multi
 Type of Choice                    Free ; Constrained
      Multiple Prompt Conditioning
 Extension of @Prompt Conditioning
 More than one where clause can be prompted.
 If the same ‘Message text’ is used in the next prompt,
  the user is prompted only once and the same value is
  substituted for the next prompt too.
 For instance, consider the following where clause -
       WHERE column A = @prompt(‘Enter value’,…...)
       AND column B = @prompt(‘Enter value’,…..)
  This will result in the user being prompted only once and
  the same value will be substituted for column B as well.
                   Exercise 3
 Add a dimension object called “ Customer
  Name(French)” to class Customer. The new object
  should show only French Customers.

 Save the Universe.

 Test in User Module.
                    Exercise 4
 Add a condition object called “ French Only” to class

 Set the conditions for the new object.

 Save the Universe.

 Check things in User Module.
                  Exercise 5
 Add another condition Object “City-user-prompt” to
  class Customer.

 Set the conditions for the new object using @Prompt

 Save the Universe

 Check things in User Module
List of Values
             What is a List of Values?
 A list of the distinct values from the column or columns to
  which the object refers

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

 This is only available if set by the designer
              Creating a List of Values
 A LoV is created within the Properties tab of an object

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

 It is important to
  uncheck this box for
  objects that don’t
  need a List
     Controlling How Lists are Refreshed

 Normally, the first time a LoV is used in a User login
  session, the system fires a query at the target database.

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

 Thereafter, the .lov file from this query is used each time
  the List is required.
     Controlling How Lists are Refreshed
 Not normally used -
  uncheck this box

 Check this box for
  frequently changing lists

 Check this box for lists that
  never change
  Modifying the Content of a List of Values

 You can limit the values returned by applying a condition
  to the LoV

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

 You can supply a personal data file containing the values
  for the list, instead of using the results of the query
  Applying a Condition to a List of Values

 Click Edit in the Properties box:

 Apply the condition in the Query
   Creating a Hierarchy for a List of Values
 Click Edit in the Properties box:
 Place the hierarchy objects (which
  must be sorted) to the right of the
  LoV object in the Query Panel:
   Creating a Hierarchy for a List of Values
 The resulting Hierarchical View of the LoV makes it easier
  to select the required value:

 Country:
 Town:
 Showroom:
        Basing a LoV on a Personal File
 Select Tools, Lists of Values from the Menu bar:

 Select the object:

 Select Personal Data:
        Basing a LoV on a Personal File
 Click OK to acknowledge the message:

 Specify the file that contains the values for the list and
  click OK
                     Exercise 6
 Edit the LOV for object City. The list should display
  only cities for the US as the country of origin.

 Edit the LOV for object City and create a hierarchical
  LOV consisting of Country, Region and City.

 Edit the LOV for City and base it upon a personal file
 An ordered set of related dimensions.
         For example, A hierarchy called Geography may consist
          of Country, Region and City.
 Necessary to ‘drill up / down’ to see less or more
 Designer uses the class structure as the default
 Custom Hierarchies can be built using Tools >
  Hierarchies. A Hierarchy editor pops up. Use this to
  create new hierarchies.
Some classic examples for hierarchies -
         Products - Category > Brand > Product
         Time - Year > Quarter > Month > Week > Day
                 What is a Hierarchy?
   An ordered set of related dimensions.
     For example, A hierarchy called Geography may consist of
     Country, Region and City.
Level in Hierarchy                  Data Returned

                         North                        South

               Liverpool Manchester Newcastle   London Southampton Bristol
          What is a Default Hierarchy?
A Default Hierarchy is the hierarchy based on the order of
 the Objects within the Class

                             Default Hierarchies are
                             represented in the User
                             module by the order in which
                             Dimension Objects are
                             organized in the Classes and
                             Objects box of the Query
    The key to creating Default Hierarchies
The designer must ensure, wherever possible, that
 Dimension Objects are organized from the most general to
 the most specific within the Class
          What is a Custom Hierarchy?
A Custom Hierarchy combines Objects from Default
 Hierarchies to build a Custom Analysis Path

                                    Custom Hierarchy

     Default Hierarchies
           Creating Custom Hierarchies

(or Tools,

Add Objects from Default Hierarchies to build a new
 Custom Hierarchy
          Automatic Time Hierarchy

 A time hierarchy
  can be built
  automatically for
  date objects.
                    Exercise 7
 Make a copy of the Island resorts marketing universe
  (Beach.unv) called TEST.unv
 Create a custom hierarchy in this universe that
  consists of Customer, Service Line, Service.
 Test this out in the User Module.
 For the VACATION universe, create a Automatic Time
  Hierarchy on the object called Invoice Date in the
  class Sales Details.
 Save the Universe and test this out in the User
           What is a loop?
Multiple paths between the tables selected
 in queries.
           Loops can be resolved by..
 Alias - Same as the namesake in SQL.
 Exact duplicate of the original table.
 A different name “tricks” the SQL query to accept that you
  are using 2 different tables.

 Rule which helps BO decide which of the 2 paths to choose
  in a loop.
 BO prompts the user to indicate the correct perspective
  (relevant path) for the query.
                    When to Alias
 A loop with a single           N         N
  lookup table should be
  resolved by an alias

                            N                  1
 A lookup table can be
  identified by its                            N

  cardinality               N
                                     1     1

 A lookup table only has
  the ‘one’ end of joins        Alias needed here
  attached to it
                      How to Alias
 Designer routines
  detect loops and
  candidates for aliases

 Break the loop by
  creating an alias of the
  lookup table for each
  side of the loop

                               Do not remove the
                                 original table
          Resolving Loops using Contexts

                           Sales              Sal

Country     Customers

            There are two Loans             Loans
                           possible routes to
            link Clients with Models:
                    • Working with rentals
                    • Working with sales
Detecting and Creating Contexts

    Loop Detection routine: suggests candidates
     for both aliases and contexts

    “Detect Contexts” detects and proposes
     a list of contexts to create

    “Insert Contexts” inserts a context
                    Exercise 8

 Add following table to the Vacation Universe
    Service_line
    Resort

 Use Alias to Solve the loop
 Complete the universe Designing
 Save the universe as Vct-Als
 Test the new universe in the User Module
                         Exercise 9
 Add following table to the Vacation Universe
    Service_line
    Reservation
    Reservation_Lines
    Resort
 Solve the loop
 Complete the universe Designing
 Save the universe as Vct-Con

 Test the new universe in the User Module
OLAP Universes
                 OLAP Universes
 OLAP universes are generated from OLAP cube or
 OLAP data sources supported are:
    BW SAP
    Microsoft Analysis Services
    Essbase
 OLAP universes are created by selecting a OLAP
  connection to a QueryCube or InfoCube. The
  universe creation process is automatic once the
  connection is selected.
                  OLAP Universes
 Creating a OLAP connection.
    Select Tools > Connections.
    Select Add.
    Expand the node for your target OLAP datasource.
    Expand the middleware node.
    Select the data access driver.
    Select logon options and click Next.
    Expand cube nodes to display the cubes and query cubes
    Select Advanced options and click Next.
    Click Next.
    Click Finish to complete the connection creation.
                 OLAP Universes
 Generating a OLAP universe
    Click the New Universe icon.
    Select a connection from the Connections drop down
     list box.(It should be an OLAP connection.
    Click OK.

Note: Since it is a OLAP connection, the
      universe is automatically generated.
Universe Maintenance
      Reasons for Universe Maintenance

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

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

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

    No Impact:                 Severe Impact:

    Adding new columns         Renaming/moving database
    Adding new tables          Changing existing column
                                 and table names
                                Deleting tables and columns
Detecting Changes to the Universe
              Choose View, Refresh Structure

              Changed tables are highlighted in
               the Structure
              A message on the screen
               indicates that tables have been
         The Effect of Changing Objects
 Changes to existing Objects in a Universe can have an
  impact on existing Documents created from the Universe:

    No Impact:                    Impact:

 • Redefining Object SQL        • Deleting an existing
 • Renaming an Object             Object
 • Copying to different Class   • Deleting and then
 • Moving in same Class or to     recreating an object with
   a different Class              exactly the same
 • Adding new Objects             definition
                 Linking Universes

 Linking can mean reduced maintenance.

                                          Universe A

    Universe A

                                          Universe B
          Two Approaches to Linking

• Kernel approach

• Master approach
          Kernel Approach

                      Kernel    +

                  Human Resources
         Staff                 Motors2000

                      Kernel    +
             Master Approach

                           Master   -

                      Human Resources
Master      HR

                           Master   -
                  Making a Link
 Click   and click on the Links tab

                                    Click Add Link...

                                      Choose universe to
                                       link and click Open
               The Derived Universe

 The linked universe structure and objects cannot be
  edited in the derived universe
 The linked universe should be treated as though they
  were new tables inserted in the derived universe. The
  same procedure should be applied
                 Including Universes
 Including a Universe COPIES structure and objects to the
  derived universe:

                      Everything           Universe A
    Universe A        Universe A

                                         Universe B
                Included Universes
 Included universes become part of the new universe

Shared By: