Analyze Report SQL for unnecessary Joins
1. First get the list of reports which are performing low and get their SQL.
2. Analyze the report SQL for joins. There could be unnecessary join which is causing query to perform low. make a change in your universe accordingly to generate query with optimized joins.
Analyze report Query for Indexes. ( work with DBA for this )
1. Get the report SQL , check the where clause
2. Check if indexes are user properly in SQL and also they exist in database
3. Also, check if statistics in database is updated, as its its not then DB may not generate optimized query.
Use Aggregates for measures
1. Use Aggregate aware for your measure objects to use summary tables form Database\
2. You can also think of Automatic Query re-write instead of using Aggregate Aware in Universe designer, However this requires careful planning and high involvement from DBA
Use Partitions for high volume fact tables
1. Partitioning the fact table can boost your query performance.
2. Work with DBA to get it done
Array Fetch Size
1. Play with Universe options for setting up optimal value of Array fetch Size parameter.
Universe Analysis for Shortcut joins
1. Analyze your reports and universe for possible use of shortcut joins as they play little but important role in performance.
1. Try using Index Awareness from Universe side to generate optimized query.
Note: This requires thorough testing of report data and detail understating of data warehouse data
1. Make sure LOV is disabled measure objects
2. Make sure LOV is disable for un-necessary dimension objects
1. Try evaluating JOIN_BY_SQL universe parameter