Im attempting to parse an object where the Select is as follows (UDT 4.1):
sum(Value/FXRate)
Value comes from a very large table, FXRate from a much smaller. Two joins are defined between the tables.
When I parse it the SQL that is run on the DB is:
Select sum(Value/FXRate)
From dbo.FXRateTable, dbo.ValueTable
The parse ignores the fact that two joins have been defined for these tables and understandably fails with the following error after 20 minutes.
Parse failed: Query timeout expiredState: HYT00
I can put the joins explicitly in the Where of the object and it will parse but then the where clause will also appear in the SQL in Webi which I dont want as the join is already defined in the From.
Is there any way I can force UDT to include the joins in the parse? It affects 100+ objects in my universe.
as a rule i wouldn’t set up an object this way with one field being divided by another, can cause problems. like if you’re dividing by 0 your query will just die. also if the fields are in different tables in different contexts you will get incompatible objects error. finally, with the sum around the objects being divided you’re adding up each row that is dividing after the fact so your results may be incorrect. generally you should be pulling in each part separately and then creating a variable in report to do the division. if you must do it in universe then probably doing it in a derived table and then your object would select just one field returned by the derived table. but that still won’t solve the problem of divide by 0 errors
Thanks for the reply. Im not so concerned with the makeup of the query. Its a very simplified version of what Im actually using so Im not concerned with the divide by zero or whether the sum occurs before or after the divide. Im just using it to illustrate what Im seeing.
What I was trying to demonstrate is the lack of joins used in a parse .i.e. a join defined between bo.FXRateTable and dbo.ValueTable in the universe but no join in the SQL that is used in the parse of a Select using the two tables.
A derived table or database view is certainly an option but I was just wondering if anyone is aware if there is a way to force UDT to include the joins between tables in a parse?
Both tables are selected in the Tables option of the object by the way.
What version of SQL server and connectivity type are you using ODBC, OLEDB?
Also, the SP of 4.1 you are running would be useful.
Are you using the same version of client tools, in the terms of patches, as your server? Are you running a supported version of Java? Are the joins missing whatever queries you build? Is the ANSI_92 universe parameter set to Yes? Are you using @Select, in the definitions of these objects? Is the universe a migrated one from another version? Does this happen with all your universes? Have you tried with another yuser account and / or workstation?
Im using Microsoft SQL Server 2008 R2 with an OBDC connection.
Im on SP 14.1.1.1036 on 4.1.
Ill have a stab at the below but I may have to guess a few as Im relatively new to BO:
Are you using the same version of client tools, in the terms of patches, as your server?
As far as Im aware yes.
Are you running a supported version of Java?
As far as Im aware yes.
Are the joins missing whatever queries you build?
No
Is the ANSI_92 universe parameter set to Yes?
Its set to No. I set it to yes and re-ran but same result. I havent messed about with the advanced properties of the joins though
Are you using @Select, in the definitions of these objects?
No
Is the universe a migrated one from another version?
No
Does this happen with all your universes?
Yes
Have you tried with another yuser account and / or workstation?
Yes, same result
Can I ask, do other people get the joins when they do a parse or do you think its something in my setup?
(BOB member since 2012-07-30)
Are you using the same version of client tools, in the terms of patches, as your server?
(BOB member since 2005-01-06)