we were experimenting a bit with the performance of some of our data cubes and we found out that the most problematic ones always fall in the following category:
1.) Data is fetched from an SQL server using SELECT
2.) The SELECT is configured to trim the DateTime parameters to hourly or daily data
3.) A FILTER is implemented to only get a subset of the data source, e.g. the last month of data.
Dundas can place this into one query, however, if the SQL table is really large, performance is an issue because the resulting SQL command first carries out the TRIM to the full table in an inner SELECT and only afterwards filters the data using the WHERE clause in an outer SELECT.
Doing it reverse would improve performance tremendously, however, we have not found any way of forcing Dundas to do the FILTER operation before the TRIM operation.