Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Thursday, April 8, 2021

I sql you - Drill through Sql details

 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 




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

No comments: