Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 214 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Relative filter on Date - Performance
Answers
-
Hi Federico,
We are using the november release.
Regards.
Jean-Pierre
0 -
I repeated the test on a newly create OAC instance with the November update, and again the filter is working as expected.
I noticed that you are using the ODBC 3.5 connection type to access your physical data sources. I highly recommend you to select a native driver instead if possible (e.g. OCI if your data is on Oracle database), and then repeat the test. Native drivers are expected to generate better physical queries compared to generic ones like ODBC.. the SQL generated when you apply the Relative Time filter does not look right, so the two things could be related.
Another test that you can do is to replace the Relative Time filter with an Expression filter such as
DateColumn BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -4, CURRENT_DATE) AND CURRENT_DATE0 -
Hello Federico,
I have done some new tests from scratch with a new semantic model based on our Redshift connection.
The issue is still the same :
- the Redshift connection seems to use the ODBC 3.5 connection type !
- the relative filter use the following where clause : T5."date" <= TO_DATE('2023-11-27' , 'YYYY-MM-DD')
- using an expression filter BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -4, CURRENT_DATE) AND CURRENT_DATE generates the same where clause.
You will find the screenshots and logs in the PDF.
Regards.
Jean-Pierre
0 -
I would try replacing the "BETWEEN" expression filter with 2 separate expressions:
- TIMESTAMPADD(SQL_TSI_DAY, -4, CURRENT_DATE) >= DateColumn
- DateColumn <= CURRENT_DATE
However, that behaviour is really weird and smells like a bug to me. I would raise a SR.
0 -
Jean-Pierre,
I would raise an SR reporting a bug.
You are using a feature that other users will probably use, and this lead to a suboptimal (to say the least) query generation.
It can be an issue with the database features, but if you didn't change the defaults, it should already work at the best with the default parameters.
You aren't in charge of generating the query, the tool is, raise the point in a SR with Oracle and they will hopefully fix it in the coming release.
1 -
Thank you Federico and Gianni, I have raised a Service Request.
Best regards.
Jean-Pierre
0
