In my application, I have one Straight table which has more than 10 dimensions(without measure) and it has millions of entries. When I apply the filter the table is loading for a long time to render the chart.
Can anyone tell me how to optimize the table to resolve the performance issue?
If you have millions of records in straight table it will render slowly because table is huge with lot many dimension so it takes time to associate each value of dimension within straight table. Couple of thing you can do it to improve performance
1) Limit the data in straight table. You can achieve this either by limit the actual data itself (load only 2 years of data if you are loading 5 years of data) or you can remove key field from straight table which is the main reason to slow down rendering straight table.
2) Apply calculation condition on straight table. So that user first need to perform some selection then only straight table data will be visible. This is the best way to do overcome this issue as you are filtering the table data before the table data presented to the user.
3) If fields in the table are coming from different table, make sure that key field between table is proper and they are not the text. If key field is text, try to convert it into number using, autonumber# function
4) Remove calculated dimension if you are using it. Move calculation to script instead.
5) Lastly you can increase your server RAM so that your performance will improve. But this option requires lot of checks and approval to go ahead.