Upon getting access to a BI system that has some means of self-service, users can become a little excited at the prospects of getting access to information that has long been hidden behind the wall of IT. They can now create their own reports. They can pull down data, analyze it, and turn it into something that their various teams can action. They can create schedules where important information can be updated while they’re sleeping and be sent to all of their team members. It’s a world of free flowing information and it has all been made possible by the wonderful BI product that they’ve purchased – SAP BusinessObjects in our case.
One problem that can arise in this situation is the build up of BI content that is either duplicated or unnecessary and left unread by many. Of course, this is only made worse by the usual lack of processes for cleaning up old content and the unfortunate fact that current BI software has no intelligence built-in for informing a user that the information they are creating already exists in the system. This build up of content can lead to a BI deployment that is rather larger than it really needs to be. In the end, this can lead to storage problems in the filesystem and database, increased backup and restore times, increased effort for content migrations or upgrade, and, in some cases, system resources being used to process unnecessary content.
Luckily for SAP BusinessObjects customers, they have a tool that can help manage this problem: Sherlock® from EV Technologies. For the next few posts, I’m going to focus on how Sherlock® can help BI administrators to keep their deployments clean. For this post in particular, I’m going to focus on one of the biggest problems – unused content.
NOTE: As I’m using real customer information, I’ve hidden any identifiable details.
The table below shows a list of reports with the associated user name who “owns” said report. In this case, you can see that the user AC35712 owns the Activity_Reloaded_Sample report. Yea, so what?
If we add in a bit more detail and this becomes more interesting. As you can see, we’ve added in a new column of data that identifies the last logon date for each user associated with a report. We’ve also sorted the data by the last logon date with the oldest date showing first. So, the MergePDFFinalC report is “owned” by PL_Admin who has not logged in since August of 2010.
Now, we’ll add in the size of the report so we’ll know a bit about the impact that these inactive users are having on our system. We’ve added in the REPORT_SIZE_IN_MB column which shows the overall size of the report in megabytes. On line two in the output, you can see that the LVID FX Quarterly View is 414MB and is owned by a user who hasn’t logged in since August 2010. This is, obviously, a problem. Why store content that isn’t being used?
Using query filters it’s also possible to limit the data returned so that you only see information that is relevant. In this case, we’ve limited my data set to only those users who have not logged in for over 30 days. This means that every report returned in my list is owned by a user who hasn’t logged in for about a month.
There are many other filters available; however, one of that is of unique interest in our case is whether the reports returned in our list are instances or not. We have added an additional filter that only displays report instances. This is in addition to our previous filter of only those users (i.e., report owners) who haven’t logged in for over 30 days. As you can see, the list is quite different. We no longer see those large reports. The largest report in our new list is only 2MB.
So, for clean up purposes, we should focus on our previous list – or just run another query that returns all reports that are not instances.
Of course, one of the ways in which Sherlock® makes things easier on BI administrators is by letting you use existing SAP BusinessObjects tools for accessing the data rather than pure SQL results as I’ve shown above. After installing Sherlock®, you will find a Sherlock® Universe and a set of pre-built reports. Using the Universe, you can use tools like Web Intelligence to create your own reports against the Sherlock® data as well.
As you can see from the screenshot below, we are using the query panel to drag and drop objects in from the Sherlock® Universe. The Object Name in this query refers to the Report Name.
After running the query, we get similar results as with our native SQL queries. There are some differences as we did not add in all of the filters that existed in our SQL queries, but this is enough for you to get the idea.
I hope that this post was useful for explaining a bit about how Sherlock® can help you to identify large content in your BI system – specifically the large content that is going unused. In the next post, we will look at how Sherlock® can help with monitoring scheduled BI content.
Thanks for reading.
Trackbacks/Pingbacks