I have recently built a new dashboard which monitor other Dashboards. It monitor SQL (Query browser) but also what I call TRACES inside all your corporate dashboard in BO 4.0. I will try to explain concept and the steps I make.
You will all agree that customer experience in navigating a Dashboard need to be as fast as possible and also stable. Like we have (most of the time) when surfing on the web. If the times it takes to refresh components is too slow, customer will be complaining. Or if customer are used to wait 20 secondes for display 4 updated graphics on first page, but now it take 1 minute, it will complaint.
And because Dashboard Design involve a lot of technology to run,. it can sometimes become slow. IT department need to know as fast as possible to make corrections and have customer happy.
To illustrate this let take a very simple dashboard that contains
– 2 Filters box (which trigger graph information changes)
– 3 graphics
– 1 retrieve information from query browser (so SQL lives on database)
– 2 retrieves information from a web service (Kind of cache web Intelligence content)
– Some Excel functions … Oh! , developer becomes very imaginative in this area . I see dashboard that contains 500 lookups () excel function call. The power of excel 🙂
User Dashboard example
In this example, when user changes filters, all graphics are updated (1 SQL, 2 Web service call) . Very common,…
Common things that slow down the dashboard
– Database query
– BO Web server (Tomcat in my case)
– BO Engine server (we have very fast servers)
– File system
Audit module in Business Object
The query browser can be audited with the audit module if you install it on BO 4.0. That part have good tools already with BO Enterprise. Then , you can easily see which SQL is slow. My new dashboard monitor show that SQL information but see next what I do for Internal traces.
Now, if other components (web server, network, BO engine, etc…) become slow, it’s more difficult to monitor with the standard tool that BO provides. That’s why I built a way for traces events between a begin and End of something inside the dashboard. We develop a Java Web service which can be call from Dashboard (like other web service) which insert a trace log into a table. So, when you have 2 timestamps, you can know the durations between.
So, developers can by example insert call at the beginning of a filter selection, and an “END” trace at the end.
If you like to know more about the Java code, send me personal email.
Another example, you can call the traces web service when first opening the dashboard and call again the traces when all components are loaded, this will give you what you can call “Initial load times”. This includes in my example, flash load, 1 query browser, 2 web service call, somes excel functions etc…
With all this information written on tables, you can then build trend of what happen and when.
Threshhold agreement with customer
Another good part of this new tools is the ability to make an agreement with a customer for all SQL and / or traces. By example, You can ask what is the time Initial load should take for a dashboard X to run. If a customer said 10 seconds is good (green), 15 is OK (yellow) and more than 20 seconds (red) it’s critical. Then you can build a dashboard like I have shown you just for reference.
Dashboard Monitoring (main screen) in reel time
Show TOP 6 SQL and TOP 6 Traces. For 1, 3 and 12 hours interval in reel time
Dashboard Monitoring (Detail screen)
Show average, max time SQL and traces. Also, present the threshold aggreement with cutomer (top – right)
Call web services TRACES
To complete the solution, we build a exploration view (Explorer) on a universe connected to this new tracelog tables.
When all this is done, the support team can visualize in reeltime if some dasboard become slow. For sure, this also involve some changes when develop the dashboard, you need to call web services. Minimum effort…!
Hope this give you more ideas then questions 🙂