Business Objects 6.0 Creating Universes - MS SQL Server 2000

Business Objects products are very good tools for Reporting, Data Warehousing, and ETL capabilities. BusinessObjects 6.0 is used as an OLAP tool and a reporting tool. This webpage demonstrates the steps in creating a universe using the MS SQL Server 2000 Example Northwind Database.

Universe

Universes present the information stored in your company’s databases in a very organized manner. They allow access to this information,without the user having to be an expert in relational database structure. Universes are a popular and versatile method of retrieving data for a document. The use of universes is what makes Business Objects truly powerful and easy to use.

A universe contains no data itself. However, it stores the instructions on how to retrieve data from some data source, such as a database. Universes are simply computer files that are stored in a repository. A repository is a database that stores, among other things, the universe definitions. When you select a universe from the list, Business Objects will copy the universe file from the repository to your local hard drive. Once the universe file is copied onto the hard drive, Business Objects will use this file to create the SQL instructions to retrieve the information for your reports. The next time the same universe is used to create a report, Business Objects will check the repository to see if the latest version of the universe is on your computer. If the latest version is not on your computer Business Objects will copy the latest version from the repository.

We will now see how to create a universe from the SQL server database - Northwind.

Create universe:

Step 1: Define Universe - Definition, Summary, Strategies, Controls, SQL, Links, Parameters.

Universe Parameters

Choose ODBC Drivers from below.

Add a Connection

If you don’t have a DSN(Data Source Name) you will need to create one.

MS SQL Server DSN Configuration

ODBC Drivers

You can always select system defaults.

Universe Parameters Controls

Universe Parameters SQL

Universe Parameters Links

OK Now you have made the definitions. Lets go to the main screen Now.

Right mouse click for the context menu and select tables

Table Browser

Select your tables and create relation ships between them. This is something similar to MS Acess relationships. Map Primary keys and foreign keys.

Table Relationships

Now GO to insert class in Editing Toolbar.

Dimensions:

Step 2: Define Dimensions.

Dimensions usually represent the nouns in our database. For example, dimensions can represent employee ID’s, car models, company stocks, dates, etc. We usually total on dimensions. For example, we may want to know the sales volume for a certain date, the number of new employees in a given month, or the total value of the stocks in a

portfolio.

We are going to make a class for products now. This dimension will take the product name from products table field productName.

Edit Properties of Class

Edit Properties of Products

Edit Properties of Object1

This is where you have to select the field from table or view.

Make sure you parse the statement.

Edit Select Statement of Object1

Select Dimension.

Edit Properties of Object1

Edit Properties of Object1

Edit Properties of Object1

Similarly category also could be created.

Edit Select Statement of Object1

Designer Northwind

You can create more dimensions as required in the same way.

Measures

Step 3: Define Measures.

Details or measures are objects that represent attributes of dimension data. Typically,

we will not subtotal or organize data around a detail object. For example, a detail could be the weight of a product or a person’s first name.

Measure objects generally represent aggregated data. Typical measure objects may sum, count, max, min or average. For example, a measure object may sum the daily earnings in a given time period or count the number of employees in a department.

Add another class as Measure

Edit Properties of Class1

After this just like we had followed for dimensions create an object from

dbo.Order Details".Quantity AS Quantity. Make sure you select ‘Measure’ as shown in below screen and Aggregate as SUM.

Edit Properties of Object1

Now your main panel looks like this.

Edit Properties of Object1

Similarly create another measure for unit price from dbo.Order Details".UnitPrice.

Designer Northwind Main panel

Calculated Measure:

Now we will do a calculated measure,

Finding the total value of the order.

We are going to create Total value as a calculated measure.

Edit Properties of Object1

The formula is:

"dbo.Order Details".UnitPrice*"dbo.Order Details".Quantity

Edit Properties of Object1

Now save the universe as NWIND.UNV

Go Back to:
http://Howes-IT-Going.com

Steven C. Howes and Howes-IT-Going are NOT Responsible for Usage or Reproduction of ANY Copyrighted Business Objects or Microsoft Titles.
This Webpage, Business Objects 6.0 Universe, Microsoft SQL Server 2000 Database and ODBC DSN were for Educational and Discussion Purposes ONLY. Terms of use.

This Webpage and Contents Created by Steven C. Howes © Howes-IT-Going 2005-2012 All rights reserved.