TM1 Forum

Hi

Issue: TM1 Drill through SQL does not allow me to write "left joins" to a relational database. (there are other inconsistencies but left join is easiest to use as an example) But TM1 does allow me to Join in the where clause.

Example : Per the below both queries work in SSMS. But only the first one works as a drill through in TM1. (with very limited error log "Unable to fetch data")

Request: Can anyone advise why this is happening and if I can fix it? (as I would like to use the second one to keep consistent coding methodology across platforms) Is there documentation I can follow (its not only "left joins" there are other inconsistencies that are hard to diagnose with TM1's standard error messages)

''#########THIS Code works when I write the join in the where clause
SELECT TOP (10)
[fact_gl_transaction].[job_code]
,[dim_supplier].[gtgetdisplaycaption]

FROM [DW].[dbo].[fact_gl_transaction], [CompassDW].[dbo].[dim_supplier]

where
[fact_gl_transaction].[dim_Supplier_key] = [dim_supplier].[dim_supplier_key]

''#########THIS Code does not work in TM1 when using left joins (but works fine in SSMS)
SELECT TOP (10)
[fact_gl_transaction].[job_code]
,[dim_supplier].[gtgetdisplaycaption]

FROM [DW].[dbo].[fact_gl_transaction]

left JOIN [dim_supplier] ON
[fact_gl_transaction].[dim_Supplier_key] = [dim_supplier].[dim_supplier_key]

Hello!

Reason here can be ODBC, not TM1. You can check it using Cubewise's ODBC-connect, for example: https://code.cubewise.com/open-source/odbc-connect/

Anyway, I prefer not to write complex queries in TM1, but to make views/functions on SQL-server side and use them in TM1 with simple query. Try this also

Have you enabled the SQL loggers to see if there is any message coming back from SQL?

Could it be a context thing when running from TM1 that the left join does not know the table and schema i.e. vs

Solved - Thanks @gtonkin Joins now work with the full path.

and @Elessar, I think the approach keeping complex SQL out of TM1 is a good idea too, I will work towards this.