Index Key Awareness

Card Puncher Data Processing

About

The Keys tab allows you to define index awareness for an object :

  • Primary Key
  • Foreign Key

Index awareness is the ability to take advantage of the indexes on key columns to speed data retrieval.

Example: Finding customers in a list of cities

The initial generated Sql

In this example you build a report on the Island Resorts Marketing Universe that returns revenue by customer for customers in Houston, Dallas, San Francisco, San Diego or Los Angeles.

To do this you drag the Customer and Sales Revenue objects into the Result Objects pane in the Query pane, then drag the City object to the Conditions pane and restrict the city to the list above.

Without index awareness, Designer generates the following SQL:

SELECT
  Customer.last_name,
  sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price),
  City.city
FROM
  Customer,
  Invoice_Line,
  Service,
  City,
  Sales
WHERE
  ( City.city_id=Customer.city_id  )
  AND  ( Customer.cust_id=Sales.cust_id  )
  AND  ( Sales.inv_id=Invoice_Line.inv_id  )
  AND  ( Invoice_Line.service_id=Service.service_id  )
  AND  (
  City.city  IN  ('Dallas', 'Houston', 'San Diego', 'San Francisco')
  )
GROUP BY
  Customer.last_name, 
  City.city

Modification Key Awarness

We add Primary key as foreign Key for the City object in Business Object - Universe Designer :

Bobj Pk Awarness

Export the SAP BOBJ - Universe.

The resulted sql

In this case Business Object - Universe Designer is able to generate SQL that restricts the cities simply by filtering the values of the city_id foreign key.

In SAP BO - Web Intelligence :

Bobj Key Awarness Result

SELECT
  Customer.last_name,
  sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price)
FROM
  Customer,
  Invoice_Line,
  Service,
  Sales
WHERE
  ( Customer.cust_id=Sales.cust_id  )
  AND  ( Sales.inv_id=Invoice_Line.inv_id  )
  AND  ( Invoice_Line.service_id=Service.service_id  )
  AND  (
  Customer.city_id  IN  (11, 10, 14, 12)
  )
GROUP BY
  Customer.last_name





Discover More
Bobj Univers Tree Object Pane
SAP BOBJ - Objects and class

Each is composed of object contained in a class and you create them using the Web Intelligence users drag objects from the Universe pane across into the Query pane to run queries and create reports...



Share this page:
Follow us:
Task Runner