I have a simple report I have created. It's a couple parameters and a list. This list only has 6 items: two fields from 2 different dimensions and 4 measures.
When I only include either of the dimension fields, the report returns almost immediately. However, when I add one more the time shoots up about 3 minutes.
I can write an MDX query that returns essentially the same list I am looking for and it is immediate. Why is this translated so poorly by Cognos? If I export the MDX Cognos creates, it still takes the same amount of time (~3 min) so it's definitely an issue with MDX the query produces.
Are there any best practices for tuning Cognos queries or setting up an SSAS cube to work better with Cognos?
Do you have any detail filters? How many members in dim 1 and 2?
Can you paste in the generated MDX from the query, when using the gear menu, not from the query itself? Can you paste the MDX that you wrote?