Just a short post today. I was helping a fellow consultant yesterday who was having issues with a Drill Through not working in Essbase 21c Marketplace. If he selected a single member from the cube, he got his data back, but if he was at the top of the dimension (14K members) nothing returned. We knew we should only get back about 72 rows. We tried a few things and some worked and some didn't. As a work around, I suggested two reports. The first one if you were within the dimension and the selection was smaller. The second ignored the dimension in the where if you were at the top of the dimension. We named the queries almost exactly the same thing so the users would think it was the same drill through.
But that was not enough for me. I wanted to see what the query that failed was generating. But where to find it? I know I had information on where the log was so I started looking though notes I keep. an there it was. I had to SSH (Well I got someone one to SSH )onto the Essbase server and pull the file
/u01/config/domains/essbase_domain/servers/ess_server1/logs/essbase/platform.log
Looking though this, I could see all of the generated SQL. I pulled the failing SQL and saw it created a massive in clause with all 14K members in it. I had the consultant run the SQL against SQL Server (The RDBMS he was going against) and it did not return anything. He decided to play a little and move the members he was really interested in. So he moved them to the beginning of the in clause rather than near the end where they were. amazingly He got the data bask he needed. So it appears to be a limitation in SQL Server. The query does not fail, it just did not return anything.
In the end, we used my work around, but he log really was handy to debug the issue
Posts
