Query Optimization Technique

Index Awareness is one of the great features for Query / report optimization in Universe Designer. Index Awareness is the ability to take advantage of the indexes on key columns to speed up data retrieval.

Objects we create in Universe Designer are based on database columns with required business names which are meaningful to the end users.  For example we have 2 Dimensions tables (Address Dim & Product Dim) and one Fact table (Sales Fact).

Example, Product type Object contains the Type of Product information (Food, Appliances, Electronics, Furniture, Sporting Goods, Fabrics).

Without having index Awareness, if we create the report for Electronics Product to see how the sales happened in each Country, the report Query will be as shown below.

Query Panel

SQL Query

In the above report, we don’t need any information from PRODUCT table, but only for the filtering “Electronics” Product, PRODUCT table join used in the Query and it will read the complete table which is unnecessary and reduce the report / query performance.

How Index Awareness helps to overcome the above scenarios and improve the performance?

The PRODUCT Dimension table has a primary key that has no meaning to the user, which is very important for database Query / Report performance. When you design the index awareness for an object in the universe designer, means you are forcefully telling the tool which database columns are primary and foreign keys.

This can have a huge effect on query performance in the following ways:

• Universe Designer would take advantage of the indexes on key columns to speedup data retrieval.

• Universe Designer would generate SQL that filters the record in most efficient / powerful way.  Design a report that involves filtering on a certain values from Dimension table, universe designer will apply the filter directly on to FACT table by using the Dimension table Foreign Key Columns & Values.

Apply Index Awareness to the above Scenario….

Setup Index Awareness for Product Type Object.

• Go to Object Properties –> Keys tab.
• Click Insert button in the bottom –> by default it will create PRIMARY KEY type (it can be changed by clicking the Drop down List).
  

• Click the Select Clause to add the Primary from the Dimension table where you want to use the Index Awareness. It will take you to the SQL editor Screen. Select the Primary key from Dimension Table (PRODUCT_DIM. .PRODUCT_ID).

• Once the PRIMARY key select and that key data type should be select in the above.
• Again click Insert button in the bottom  second by default it will create FOREIGN KEY type (it can be changed by clicking the Drop down List).
  

• Do the same process to add FOREIGN Key from the FACT table.

Note: 
To add more than one column for primary key in a single object, you can combine multiple columns to define Primary key. Those Primary Key columns should belong to the same table and have same data type.

Example – a BRANCH DIM table has 2 Primary Key Branch ID & Location ID, you can define them as below in the Select Clause.
BRANCH_DIM. BRANCH_ID & BRANCH_DIM.LOCATION_ID

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

After the Index awareness the above report Query will filter the ID from FACT table instead of Dimension table values.

Heed to the below bullets…

1. The main drawback of this feature is that it does not work always. There are some things have to be done in order to get the real Index Awareness performance in the report / Query. You have to select the values (filter in the Query conditional panel) through List of Values, and then only it will work as expected.

• Filter values entered manually in the Query Conditional Panel, it does not work.

• Filter values respond through a prompt, it does not work.

2. Index Awareness might return wrong result if you have multiple values for Dimension table object.

Example
Product Type is having data as shown below.

Index Awareness applied on Product Type object and using Product as filter in the report Query. Since “Electronics” have two different Key values, report does not know which one to put in the Primary Key filter in the FACT table and it might return wrong result.

To Avoid this kind of scenario, Index Awareness can defined the data restriction for that object using WHERE clause. It will help you to restrict the data in index awareness.

For Product Type object, it can be defined the WHERE clause as below.

• Index awareness used with proper data analysis can give you dramatic performance gains for your report.