OOW BI Imp Panel Questions #2: Can OBI do SubSecond Responses?
For the first post in this short series, see here.
I don’t think I answered this question properly during the panel; I’m going to chalk it up to the fact that I was sick as heck and my head was swimming. But anyway here is a better shot at it:
Sidenote: I was so out of it that I spilled a bottle of water on my PC earlier during another presentation. Luckily the computer stayed alive until I finished!
Question #2: Is it reasonable to expect sub-second response time from OBI?
Short Answer: Basically no, but once in a while yes
Sub-second is just too fast for all of the layers a dashboard page has to go through:
- Presentation Server Generate SQL, send to OBI Server
- OBI Server, generate SQL, send to database
- Database: parse, query, return (by far the slowest part of a typical query)
- OBI server collect data, merge, apply functions
- Presentation Server: format data, draw screen and charts
- Presentation server over the network to browser, render pages and charts
There is just too much going on, with too many hand-offs. And that doesn’t even get into the slowest part where the database is actually queried. Think about your projects where you have cache hits on the OBI server side – even then, with the slowest part of the process removed, ~1 second is about the best you can get, sometimes longer.
You might be able to get 2-3 seconds on a large percentage (but not all) of your queries if you have most of the following circumstances:
- Highly cached data in OBI, meaning limited user based data security, limited dashboard interactivity, no Answers deployment
- For non cacheable queries, they need to be highly similar – here you might be able to optimize your data model with a lot of aggregates and database features
- You have a very strong Data Architect, an expert in physical data model tuning, who knows a lot of tricks to squeeze some extra speed out of your queries
- You have a rock-star DBA who can tune your database properly
- Adequate hardware set up properly (e.g., Direct attached storage and not shared SAN storage)
- You have allocated a large project effort to performance tuning your system
- Your requirements, metrics and UI design are capable of being tuned to extreme levels. My current project deals with non-aggregateable metrics based off of the UI design and their definition – we simply can’t aggregate them in the database, so our performance takes a hit. Requirements can drastically tie your hands here and prevent you from achieving performance.
- You have a light load on your system – the greater your user load the longer your queries will take
- You actually can leverage Essbase effectively (see this post for more)
- You have a small data set, or you can tailor your reports and UI along with your data model to eliminate much of your data (i.e., Partition Elimination). Sorry, but dealing with hundreds of millions of rows is much harder than two million.
- And the most important: It is a mandatory requirement to have these kinds of response times. If there is no need to go this fast, then the business will not waste effort and money on it.
So is it possible? Yes. Is it possible for your project? Probably not. Unlikely in a Phase 1, but possibly in Phase 3 or 4, once your backlog of more important requirements has been met and you are freed up to work on it.