Some of collection (mine and from friends)
1. Use Aggregate tables
2. Use partition mapping
3. VLDB settings
4. Collect statistics on the tables
5. Use Index on all Key columns ( for conditions from WHERE clause, JOIN conditions, ORDER BY clause , GROUP BY clause )
6. Convince the requester of report to drop least used and most SQL intensive calculations/columns. In rare cases it works.
7. Select from in-memory tables (from Orion onwards)
8. Avoid Custom Groups
9. Use Free form reports (if any other tuning doesn’t bring much improvement)
10. Use Database function instead of MicroStrategy provided function
11. Use Cartesian Products Wisely
12. Connectivity between I-Server, Web-server, Database server and meta-data server is fast.
13. chk explain plan of the query generated (teradata specific). it should indicate high confidence level… also full table scan should not be there.
14. Use Case Statement option available in VLDB Properties, If your report contains any Custom Groups.
15. If the SQL of your Report has any Sub queries, You can use the “Use Temporary Table” option available in Query Optimization in VLDB Properties. For reports with several relationship filters, temporary table syntax may execute significantly faster on the database.
16. make sure you not using lookups at fact table.
17. as others pointed out make sure indexes and collect stats are there
18. get the SQL and execute directly in DB. if it’s take less time then you’ll have something to do with MSTR or fine tune DB SQL with the help DBA.
19. in MSTR, go to project config, increase the project memory settings. this is the memory MSTR uses for worker set. it should be large enough to process it quickly.
20. also go to intelligence server configuration and make sure the worker set XML is at optimum level. ( you can set 5 Lk for 8.0.3)
21. web server java heap size should be more if you are working with bigger data set and RS documents. by default it is 256 MB and you can set it as 512 MB (max)
22. MicroStrategy recommends the use of warehouse partitioning when implementing cross-dimensional partitioning.