Disadvantages of navigational attributes on query performance
Can anybody tell me what are the disadvantages of navigational attributes on query performance. Maximum how many Navigational Attributes should we use in a Query?
Almost everyone has mentioned the impact to the query being extra joins, e.g. 5 Nav Attr in the query ==> 5 more tables.
I don't think you can make a generalization that you should not have more than X Nav Attr in a query - there are just too many other variables:
How big are the fact tables?
How big are the master data tables of the Nav Attr?
Are there appropriate indices on the master data tables?
How often will this query be run - 1000 times a day or 5 times a day?
Are the Nav Attr in the initial view, or are they only going to be drilled into occasionally?
Are ther aggregates for the cube?
Bottomline - I don't think there's a simple answer to the question. It can certainly help to bringa DBA into the loop to review the SQL that is generated to see where additional indices might help.
Sunil Reddy LCP