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