TRIM in a SELECT after a FILTER?

#1

Hi all,

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.

#2

Hi,

How is the filtering getting done? Have you added a separate filter transform or via parameters in the SQL SELECT? Instead, can you add the filter expression under Select Expressions by configuring the SQL SELECT transform?

#3

Yes, there is a separate FILTER transform.

I am not sure how to use the SELECT expressions to add a filter. As far as I understand I can only apply this to the SELECT part of a query. What I would need is a way to change the WHERE part of the query.

#4

Hi,

Sorry about the wrong suggestion. You can add the fields to be filtered in Parameters section of the SQL SELECT transform. I verified the queries generated by Dundas for both the cases. In case the filter field is added in Parameters of SQL SELECT, the inner query uses both the trim statement and where clause together. However. in case it is added in a filter transform after SQL SELECT, the innermost query applies the trim for all the rows, then for filter statement the second inner query filters the results of the innermost query, and so on. So, you can try adding this in Parameters and verify if this is faster.

#5

Dear Upasana,

thanks for the update, I will try that suggestion.

In the meantime: Is there a way to check which SQL command is created by a particular cube?

#6

Hi Markus
I believe that you can see the SQL generated by the cube if you go into the settings and set the logging level to verbose for data queries. Then when you run the cube to preview the data you will be able to see the SQL generated within the logs.
Something along those lines should work.

#7

Thanks, we’ll try that!