Index Key Awareness

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 :

_

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 :

_

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

Powered by ComboStrap