Well, I survived KScope. It was a very good event with participants getting over 175 sessions related to EPM/BI. I sat in a number of sessions and was impressed with the quality of the speakers and presentations. I also had the opportunity to speak in 4 sessions and I think they went pretty well, at least from the questions people asked.
Patch 11.1.2.4.002 came out the other day and I read through the readme file. There were only two changes and one document change.
The first bug fix relates to a problem with stored dimensions (I assume ASO) where it would not let you use external consolidation operators.
The documentation change fixes the statement that you can drill through on any level of a hierarchy including the top level. That is incorrect, you can’t drill through from the top member of the hierarchy (The dimension name).
The most intersting bug fix is the second one and I’m surprised they are calling it a bug as it used to be described as a limitation. When doing a drill through report on a recursive hierarchy, the drill through would fail with an error message if there were more than 1000 level 0 members returned in the query. For recursive queries, Essbase Studio created an IN clause with the list of level zero members under the selected member. The 1000 member list was a limitation for Oracle as that is the maximum number of members allowed in an In clause. I’ve not been able to test this yet and wonder how development got around that limitation.
I guess the moral of the story is , even if something is listed as a product limitation, still submit bug and enhancement requests and it is very possible what you need will be changed.
Posts
6 comments:
Hi Glenn,
It could be interesting to build a POC based on a big recursive dimension (+1000), so you could capture the drill query behind.
Maybe they are just concatenating the IN queries which is by default limited to 1000 items.
Keep us informed!
Hi Glenn,
I assume Sebastien is on the right track, and this is how I handle > 1000 members in Drillbridge: concatenate groups of members so that the query reads as WHERE Foo IN (1, 2) OR Foo IN (3, 4) and so on.
Jason,
I'm guessing the same thing, but I don't know how it would generate the SQL since the SQL is "static" in the report definition. I need to see the SQL and how it has changed from before or if they are doing something internally
I'd be curious to see how they do it too. Another option I toyed around with that has a more straightforward SQL generation strategy is to concatenate multiple queries with UNION ALL. I toyed with this as well but decided on the concatenation approach for various reasons.
Just saw this comment got approved... speaking of this subject, we patched Studio to latest at a client, specifically to include the fix for the 1,000 item limit, and it didn't work for us. Whereas before we'd get an error message, after the patch, no results were returned. Not sure if there's some special syntax to use or not.
Jason,
Interesting. I've been talking to the dev team about drill through recently, if you send me your issue, I'll ask them about it. Do you have an SR open I can refer to?
Post a Comment