Universe Designer Queries

[Former Member]

Hi Everyone,

I have following two queries regarding universe designing:

1. Do we need inexes created in the database in order to use the index awareness in universe designer Or we can use index awareness even when there are no indexes created in the database?

2. Can we use objects from different classes when creating custom hierarchies?

Regards,

Neeraj Sharma

[Former Member · Accepted solution]

Index awareness is pointless without a correct indexing strategy within your database. If you are working with a dimensional model (i.e. a star schema - a typical data warehouse model) then the common indexes are:

Index all join columns

Index columns commonly used in where clauses

Index columns commonly used in group by clauses

That's just one aspect of performance though.

The fastest join is an inner join on indexed integer columns.

If you join on dates and one or both join columns use a function, an index won't help as much as creating a surrogate key for the date columns.

Have a read up on data warehouse indexing strategies to get a more in-depth understanding of what you're indexing, as well as looking into other areas such as aggregates and partitioning. to get a fuller picture.

[Former Member]

Hi Neeraj,

1. Index awareness will only help in using or making the best use of indexes created on the database.

Index awareness in universe just help  the query aware that there are indexes between the joining columns in the table, which changes the behavior of the query.

This only works when you apply those objects in report query filters.

Index awareness will not work if you are not using those objects in query filter(where condition).(This basically avoids join condition in the query by using the indexed table, mentioned in object properties in universe)

2. Yes,you can create custom hierarchies in the universe using objects from different classes,but it should have a meaning to that.

For eg. A custom hierarchy created with object YEAR,QUARTER,CUSTOMER,PRODUCT, does not make any sense when you drill from quarter to customer to product.

Thanks

Gaurav

[Former Member]

Hi Neeraj,

1- No need. Please note that ONLY appropriate indexs  at DB level depending on your DB.

2-Yes if they are compatible

[Former Member]

HI Neeraj,

1)if you are using the index in the Database that is more enough in the universe level, no need to use index awareness in the universe level and more over it save the performance also it is not suggestible one.

2)you can use the custom hierarchies from different classes, but everything depends upon your DATABASE Design.

Hope it help your query i think

Arun.

[Former Member]

Hi Neeraj,

1. In general, if you use index awarenes on any column then the functionality will be useful if the same has been defined at the database(DB) end. for e.g,this is by-default for a primary key column. However, if you define index awareness on a column at universe-end that is not made indexed at DB-end then this feature will be redundant.

2.Yes, if they are compatible, meaning, they fall into same context.

-Prathamesh