If you have been following me in the community enough you know I’m a system statistics nerd. I love Auditor. I wrote Sherlock® with my buddies because I love system statistics. And, with the release of SAP BusinessObjects Business Intelligence 4.0 (BI4), we have new stuff to audit!
Working with a customer rolling out SAP BusinessObjects for the first time, we (shockingly) were experiencing performance nightmares with SAP Dashboards. You don’t want dashboards that load in more than a handful of seconds. Many of you may scoff that this is impossible, but I’ll argue any day that a dashboard shouldn’t take 30 seconds to load. Yet, this is not the purpose of this post. Let’s talk about auditing dashboards.
The Query
In the troubleshooting process of debugging these dashboards, I realized I had not checked in with Auditor to see if anything new was being captured for SAP Dashboards. Each of these dashboards for this customer were written in the latest SAP Dashboards 4.0 Support Pack 2 (SP2) and were all using Universe Queries. This made my life easy, for SAP has graciously begun to audit those queries. Score. Let’s dissect the bigger query first.
SELECT
a.session_id,
b.event_id,
a.object_name,
a.user_name,
a.duration_ms,
d.object_type_name,
a.start_time,
dbms_lob.substr(event_detail_value, 4000, 1 ) as query_name
FROM
ads_event a,
ads_event_detail b,
ads_event_detail_type_str c,
ads_object_type_str d
WHERE
a.event_id = b.event_id
and b.event_detail_type_id = c.event_detail_type_id
and c.language = ‘EN’
and b.event_detail_type_id = 3
and a.event_type_id = 1003
and a.object_type_id = d.object_type_id
and d.language = ‘EN’
and a.object_name like ‘%{?DashboardName}%’
and a.start_time > sysdate – 7
and d.object_type_name = ‘Dashboard Design’
ORDER BY
a.start_time desc
Fear not— I’m going to break this down a bit. Shame on me, really for not using my very own Auditor for BI4 universe, but I felt like I wanted to do this the hard way to practice up with the BI4 Auditor schema.
First, since the detail table still contains the dastardly CLOB, I have to make it more manageable. The following uses an Oracle-specific function.
dbms_lob.substr(event_detail_value, 4000, 1 ) as query_name
Remember, by default Auditor has multi-lingual look tables. Always filter the _STR tables so you don’t overstate results.
and c.language = ‘EN’
Next was the tricky part. I was able to isolate the specific detail type to ensure I only got the query name for this guy. It took a little trial and error, matching what I new to be the query name. Here, I’ve saved you the time.
and b.event_detail_type_id = 3
I know that Universe Queries refresh on demand, so isolating that these detail types above reference the refresh event was easy and helps query performance a wee bit.
and a.event_type_id = 1003
Oh yes, I did write this into a Crystal Report. Prompt this query as you like. I did it based upon the actual dashboard name, but you could prompt on dashboard name, dates/times, specific users, etc.
and a.object_name like ‘%{?DashboardName}%’
And here is the icing on the cake. We don’t care about Web Intelligence or Crystal Reports in our result set.
and d.object_type_name = ‘Dashboard Design’
With that, the query specifics are complete.
Analyzing It
Using the Crystal Report I’ve attached here for you, I did some simple organization within the document. First off, the event detail record CLOB was a little messy, and still is. However, I used a function in my Crystal Report to tidy it up a bit as shown here.
mid({Command.QUERY_NAME},len({Command.OBJECT_NAME})+1)
This peeled the ugly string off the front and allowed me to see the query name as it corresponds to my queries within the dashboard for traceability. With all of that organized, my Crystal Report told a pretty cool story. I can see each time the queries are invoked and the durations in ms for each. Further, I could see that the developer had query firing issues in the dashboard that could be addressed.
Now, this is only a partial view. When debugging dashboards, you should always have Fiddler in your back pocket to see services invoked, parameters passed, and overall query performance time. But this is a simple way to look directly at what SAP BusinessObjects is doing during it’s think time during dashboard activities.
Here’s my Crystal Report: Dashboard Sessions.rpt. I wrote it for this task that you should feel free to use. If you find errors or omissions, please contact me or leave a comment and let me know.