How to see the physical query generated for analysis in the logs of OBIEE when analysis returned an error?

I have made a simple analysis in OBIEE that includes 3 dim and 1 fact table. All dim tables are joined to this fact table. However, in the results tab I see the following error:

Internal Error: could not find level detailed enough for GROUP BY clause:

I checked again in rpd, all dimension tables were joined to the fact table and more interestingly, analysis with the same tables in another subject are works. I wanted to see logs of it, but since the analysis was generated with an error, I see only a presentation level query, not the physical query.

Is there any way to get it? If we can see what is the actual physical query that would help to find the cause of problem faster.

Here is the content of logs

    SELECT
   0 s_0,
   "WRH"."Calendar"."Date" s_1,
   "WRH"."Account"."Account Number" s_2,
   "WRH"."Department"."Department Code" s_3,
   "WRH"."Balances"."Account balance" s_4
FROM "WRH"
WHERE
("Calendar"."Date" = date '2016-02-05')
ORDER BY 2 ASC NULLS LAST, 4 ASC NULLS LAST, 3 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

Solution

If your query already fails at the logical level you will never ever see any physical query log simply becuase the query doens't ever reach the physical layer.

If you set the log level to 7, then you get the full debug information for the query - as far as it gets.

Either change the log level in the RPD or add this code to the analysis SQL prefix:

SET VARIABLE LOG_LEVEL=7;