Bo-designer Training 1

SPICA

DATA SYSTEMS

BO – Designer

Walkthrough

SPICA

Universe Designer

DATA SYSTEMS

• Universe Designer creates universes, the semantic layer that isolates end users from the technical issues of the database structure. • Universes can be dedicated to specific groups of end users in your company or organization.

SPICA

DATA SYSTEMS

Universe Designer – The Person

• The individual designated within a company as the universe designer may hold any one of the following positions: – – – –

Database administrator, Applications manager, Applications developer, Project manager, etc.

• There may be more than one universe designer in your company.

SPICA

Universe Designer Prerequisites

DATA SYSTEMS

• Good Knowledge of Structured Query Language (SQL) • Good Knowledge of relational database management system (RDBMS) of organization. • Familiar with the type of data and the logical structure of company’s databases.

SPICA

DATA SYSTEMS

Universe Designer’s Key Requisite

Analyze the type of information that the end users will require so that universes can be developed that meet the needs of the user community. This includes – • actual reports, • information, • or results likely to be required by the end users

SPICA

Universe Designer Tasks

DATA SYSTEMS

• Designing Universes • Creating Universes • Maintaining Universes • Distributing Universes.

SPICA

Role of the Universe

DATA SYSTEMS

- To provide an easy to use & understand interface for non-technical business users.

SPICA

DATA SYSTEMS

Universe Design Life Cycle

SPICA

Universe Development Process

DATA SYSTEMS

The Methodology – • Analysis of Business problem & planning the universe solution. • Designing a schema. • Building a Schema. • Distributing the universe to users. • Maintenance as per changing requirements.

SPICA

All right, but What is a Universe?

DATA SYSTEMS

• A universe is a business-oriented mapping of the data structure found in databases: tables, columns, joins, etc. • A universe can represent any specific application, system, or group of users. • A universe consists of a set of objects, grouped by classes.

Contd…

SPICA

DATA SYSTEMS

Universes in Organization

SPICA

DATA SYSTEMS

HR Universe

SPICA

Classes

DATA SYSTEMS

• A class is a logical grouping of objects within a universe. • A class can be further divided into subclasses.

SPICA

Objects

DATA SYSTEMS

• An object is the most refined component in a universe. It maps to data or a derivation of data in the database. • The name of an object suggests a concept drawn from the terminology of a business or discipline.

SPICA

Objects Classification

DATA SYSTEMS

• Dimension Objects – Dimension objects are the parameters for the analysis. • Detail Objects – Detail objects provide a description of a dimension but are not the focus of the analysis. • Measure Objects – Measure objects convey numeric information by which a dimension object can be measured.

SPICA

Object Defined

DATA SYSTEMS

• An object maps to data or a derivation of data in the database. • For the purposes of multidimensional analysis, an object can be qualified as one of three types: a dimension, detail, or measure.

SPICA

Class Defined

DATA SYSTEMS

A class is a collection of objects based on business categories.

SPICA

Universe Defined

DATA SYSTEMS

A universe is a set of classes and objects intended for a specific application or group of users.

SPICA

DATA SYSTEMS

Universe Objects

SPICA

Design Methodology

DATA SYSTEMS

• First Phase Create the underlying database structure of universe. • Second Phase Enhance the components of universe.

SPICA

DATA SYSTEMS

Schema Support

SPICA

Basic Rule

DATA SYSTEMS

• A Universe is used by Business users, hence the naming of universes as well as classes & objects should use business language. • It should not create ambiguity for end users - for example, an object named “Total Amount” can not tell itself whether it is received amount or balance amount. • Universe design should be primarily driven by the User Requirements & NOT the Data Source Structure.

SPICA

Creating new Universe

DATA SYSTEMS

While creating, following parameters are defined – • Definition – Universe name, – Description, – Connection

• Summery Information – Version information, – Designer comments – Universe statistics

• Strategies

SPICA

DATA SYSTEMS

New Universe- Quick Design

SPICA

DATA SYSTEMS

Define Parameters

SPICA

DATA SYSTEMS

Create Database Connection

SPICA

DATA SYSTEMS

Define Database Connection

SPICA

Types of Connections

DATA SYSTEMS

• The type of connection determines who can use the connection to access data. Designer automatically stores all the connections that you create during a work session. The next time you launch a session, these connections will be available to you. • There are 3 types of connections – Personal, – Shared, – Secured

SPICA

Personal Connection

DATA SYSTEMS

• Restricts access to data to the universe creator and the computer on which it was created. • These parameters are static and cannot be updated. • Personal connections are unsecured in terms of Business Objects products security. • You cannot use personal connections to distribute universes. • You could use personal connections in the following situations: – To access personal data on a local machine – To access specific database accounts to test an SQL sample through the Free-hand SQL option in BusinessObjects

SPICA

Shared Connection

DATA SYSTEMS

• Allows access to data for all BO and WebIntelligence users. • These connections also are unsecured in terms of Business Objects products security. • Connection parameters are stored in the SDAC.LSI file on local drive. • only users having access to the local machine (through a mapped drive), can use the shared connections. • Shared connections can be useful in a universe testing environment.

SPICA

Secured Connection

DATA SYSTEMS

• Centralizes and controls access to data. It is the safest type of connection, and should used be to protect access to sensitive data. • You can create secured connections with Designer or Supervisor. • Connections are stored in the security domain of the repository. These can be shared with designers and supervisors with the appropriate privileges. • You must use secured connections if you want to distribute universes through the Business Objects repository. • Secured connections can be used and updated at any time. To define a secured connection you must be using Business Objects products in Enterprise mode. You must be connected to a repository and using the Business Objects repository key file.

SPICA

Designing a Schema

DATA SYSTEMS

Steps – • Inserting and organizing tables. • Creating joins and setting cardinalities • Resolving join problems such as loops, chasm traps, and fan traps. • Testing the integrity of your schema.

SPICA

DATA SYSTEMS

Inserting and organizing tables.

SPICA

DATA SYSTEMS

The Universe

SPICA

Objects – The inside view

DATA SYSTEMS

• OK, I have objects to use in reports ! but what exactly they contain? Can I modify them? • Just double click on any object & BO opens Object properties box. • Here you can : • • • • •

Set data type of object Define the Select statement Impose Where clause Change Qualification wiz. Dimension to detail or measure Enable List of Values export

• And set complexity through edit button.

SPICA

DATA SYSTEMS

Object Properties

SPICA

Create New Object

DATA SYSTEMS

• You can create your own classes & objects. This facility comes in handy especially when you insert new tables to existing universes. • To create an object, click on insert Æobject. You can create it by right clicking into universe window(classes & Objects view). • BO Designer opens Objects Properties box. Type a name for object, select data type & click ‘>>’ button next to select box. • You will be in the select statement edit box. From the tables & columns select desired table, expand it & select column. Click parse to validate & click OK. • Go to properties tab, select dimension/detail/measure as per the requirement &click OK.

SPICA

DATA SYSTEMS

Select Statement Edit Box

SPICA

Joins & Cardinality

DATA SYSTEMS

• Types of joins supported – • Equi-joins. • Theta joins (conditional joins using “between” operator) . E.g. :customers.cust_age between age_range.max and age_range.min • Outer joins. • Shortcut joins. • Self-restricting joins. Contd…

SPICA

Joins & Cardinality

DATA SYSTEMS

• BO Designer’s schema works on cardinalities of joins. Such as – One – to - One – One – to - Many – Many – to - One – Many – to - Many

SPICA

Add more tables

DATA SYSTEMS

• BO Designer supports manual addition of tables after creation of Universe. • To add tables – Click Insert Æ tables – Select desired tables & click Insert button.

• Once new tables are added, you need to specify the joins & set cardinalities.

SPICA

DATA SYSTEMS

Inserting Tables

SPICA

Insert joins

DATA SYSTEMS

• Once new tables inserted, click on ‘Tools Æ Detect Joins’. BO automatically show you possible joins depending upon key information of tables. • Select appropriate joins & click Insert button. • REMEMBER auto-detection only shows possibilities of joins. You need to verify them manually using logic & database design details. • Next step is setting up the cardinality between 2 tables of each join.

SPICA

Types of Joins

DATA SYSTEMS

• Simple Joins – Simple join connects 2 dimensions or dimension to fact based on the common key existed in both the tables.

• Shortcut Joins – Shortcut Join links a shared Lookup table to another tables (Facts for E.g.). – It helps in improving the query performance by bypassing the intermediate tables.

SPICA

Set Cardinalities

DATA SYSTEMS

• Double click on every join, ‘Edit Join’ box appears. • Click detect button & BO automatically shows you possible cardinality. • REMEMBER, auto-detection is not always reliable, Cross Check it with your Database design.

Exercise # 1

SPICA

DATA SYSTEMS

1. Create new secured connection for efashion.mdb database. 2. Rename classes & Objects in End Users’ understandable business Language. • •

Classes : Time, Store, Product. Objects: create Dimensions, measures & detail as per requirement.

3. Create New universe using Quick Design Wizard. – Use following tables – • • • • •

Shop_facts Article_lookup Article_color_lookup Calender_year_lookup Outlet_lookup

SPICA

Exercise # 1

DATA SYSTEMS

3. Add a table (Article_lookup_criteria) to your universe. Set joins & cardinalities. – –

Create Class & Objects for newly inserted table. Check Integrity of the universe.