To the Rescue!!!
With differing capacities of Integration and Production servers, most of the times developers miss an important perspective; How would their code scale in the production systems. I had such a situation, where I could bring in drastic improvements in my report retrieval times.
Ever wished, you had a way to override session level information with users selections? If you were one just like me looking forward to tune your reports further, you would just love the request variables in OBIEE 11g.
So what they do?
The solo purpose for which these warriors exist is to let you override session variable values.
How I used them?
I had a model, where in the physical layer I had an object with select on a table and some session variables as the where clause. This select used to go in as an inline query with my physical sql, after I joined to my existing star. However, being a consultant I was willing to deliver much more to my client. This is what I did…
1. Partitioned this table using timekey.
2. Created a timekey session variable.
3. Changed in the physical layer of the .rpd, the select to include the timekey session variable.
3. In the dashboard prompt, set the value into a request variable (yes, of course with the same name as session variable).
4. Noticed that the user’s chosen value, is being passed in the logical sql, thereby setting the session variable value.
5. The SQL Server database which I was using for this data model, could do a partition elimination just because I gave an exclusive timekey.
6. Summing up, I could drastically get down the response times of reports from 30+ sec to just 2 sec. WOW!!!!!
So, how did the request variable come to your rescue? Please share your experiences..