Dashboards, Reporting & Business Intelligence

The job of a Disco Administrator is to maintain the End User Layer(EUL), update Business Areas, cleanup folders,
tune the overall performance by constanlty monitoring the folder joins and the impact on workbooks. Apart from
this, the main component of the administration is monitoring the reports(workbooks). There are couple of options for the Disco admin person, one is to use the Discoverer administration edition along with the EUL monitoring workbooks that Oracle provides with the eul5.eex files.

If you have just one installation of Discoverer EUL then the above combination (even though tedious) is quite sufficient.
But most of the implementations that I have seen have more than one EUL stored in the database with different owners(database user).
If you had to monitor all of these EULs then you would have to log-off and log-in to check individual EULs.

What if your IT manager needs a complete picture of everything related to Discoverer?
How many Business Areas?
How many workbooks?

There is an easier solution to all the pain and we will discuss that below.

Introducing Discoverer Monitoring Dashboard

This pre-built dashboard has the following portlets. This is a plug-n-play dashboard and connects to any
oracle database. It detects Discoverer Meta-data stored on your Oracle database.
It detects all the EULs your database stores and provides single point information to all the needed information.

So the question is how does it detect all the EULs in the database?

When you launch this dashboard, there is a parameter at the top named ‘EUL Owner’ and this parameter executes the
following query against the database

”
Select distinct owner
from all_tables
where table_name like ‘EUL%’
”

This provides all the available list of owners.

By simply switching between any of the EUL owner you get centralized monitoring of all the EULs stored in the
database.

Following is the list of portlets that display some vital information

# Business Area metrics by EUL, number of BAs, modified folders and items, Total reports etc.
# Drill into Folders from Business Area
# Drill into Items from a particular Folder
# Drill into Folder Joins
# List of defined workbooks
# Average run-time for workbooks
# Actual run-time for workbooks
# Discoverer Meta-data browser

How to use this Dashboard?
The dashboard is very intuitive and easy to modify and enhance. Lets say you are looking for a particular item e.g.
“Invoice Date” and you don’t know which Business Area and which Folder contains this item. The easiest trick is to
click on the “Folder Items” portlet, right click and select ‘Refresh’ action from the menu. This refresh action will
popup a window that will show the available filters for this portlet.

You can easily drill from Business Areas to a list of Folders and from folders to list of folder items and joins. You can
also monitor the performance of reports using the Average run-time and Actual run-time for workbooks.

This dashboard can be modified to incorporate the following needs

• Who created Report, Who Shared it and to Whom?
• Reports not run in past ‘X’ Days
• Most Popular Reports
• Slowest performing Reports
• Which user has “Ability to grant access to workbooks to other usersâ€? ?
• Which user has Administration Privileges? (SOX violations?)

I will try to add the above metrics and portlets to the existing dashboard to make it more useful but
before that I would like to know your opinion if this is useful in anyway. Please email me(infocaptor”AT”gmail.com) if you think there is more that can be added or subtracted from this dashboard

How to install and use this dashboard

First install “InfoCaptor Professional Designer” from this page Download Dashboard Designer and Viewer
and then select the “Discoverer Intelligence” option from the drop down list on the same download page.

Lauch InfoCaptor and open the “disco_intelligence.icv” file, provide the necessary connection details and you are ready to browse through the dashboard

Let me know if any trouble installing or using this dashboard

Behind the scenes…
Here are the queries that run behind each portlet in this dashboard
BA Metrics
select ‘Business Areas’ Metric,count(*) “Total”
from EUL5_bas
union all
select ‘Workbooks’ , count(*)
from EUL5_documents a
union all
select ‘Modified Folder Items this Week’ , count(*)
from EUL5_expressions i
, EUL5_objs f
, EUL5_ba_obj_links l
, EUL5_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and i.exp_updated_date between trunc(next_day(sysdate-7,’SUNDAY’)) and sysdate
union all
select ‘Modified Folders this Week’ , count(*)
from
EUL5_objs f
, EUL5_ba_obj_links l
, EUL5_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and f.obj_updated_date between trunc(next_day(sysdate-7,’SUNDAY’)) and sysdate
union all
select ‘Reports Run’, count(*)
from EUL5_qpp_stats

—————
Business Areas

select ba_name “Business Area”
,ba_created_by “Creator”
,ba_created_date “Creation Date”
,ba_updated_by “Updated By ”
,ba_updated_date “Last Update Date”
,ba_id
from SYSTEM.EUL5_bas

——————
Folders
select b.ba_name,f.obj_name folder_name,f.obj_id,f.obj_ext_owner Owner
from
SYSTEM.EUL5_objs f
, SYSTEM.EUL5_ba_obj_links l
, SYSTEM.EUL5_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper(’Video Store Tutorial’)
and upper(f.obj_name) like upper(’%')
order by b.ba_name,f.obj_name

—————-
Folder Items

select i.exp_name item_name,i.exp_id,i.it_ext_column
,f.obj_name folder_name
,b.ba_name, i.*
from SYSTEM.EUL5_expressions i
, SYSTEM.EUL5_objs f
, SYSTEM.EUL5_ba_obj_links l
, SYSTEM.EUL5_bas b
where f.obj_id= i.it_obj_id
–and f.obj_name like
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper(’%')
and upper(b.ba_name) like upper(’Video Store Tutorial’)
and upper(f.obj_name) like upper(’Products’)
order by b.ba_name,f.obj_name,i.exp_name

—————————

Folder Joins

select key_description
from SYSTEM.EUL5_key_cons

———————————-

Average Run Time

select replace(qs_doc_name,’LINKSYS’,”) ||’ (’||
qs_doc_details||’)’ “Workbook (Worksheet)”,
round(avg(qs_act_cpu_time)/60,2) cpu_time,
round(avg(qs_act_elap_time)/60,2) total_time
from SYSTEM.EUL5_qpp_stats
where upper(qs_doc_name) like upper(’%')

You asked : What is a dashboard?

When the software industry runs out of jargon they invent one!
According to this wiki (http://en.wikipedia.org/wiki/Dashboards),
“A dashboard or dash board in an automobile is a panel located under the windscreen and containing indicators and dials such as the tachometer/speedometer and odometer.”

But you and me both know that we are not talking about cars, we are talking
about the Business specific Dashboards.

The concept of dashboards in automobiles and business is the same i.e. to give
you a snapshot of critical information at a glance. If you are running out
of gas the dashboard will automatically start the indicator to bring your
attention, you can easily track at what speed you are going and is it time
to look in the rear view for any flashing sirens!

Similarly Business Dashboards provide all the critical information that is
needed to run your daily operations.

Business dashboards can tell you if you are spending your gas..oops I meant spending company cash, what is the cash flow, what is the growth in revenue,
what is the headcount growth etc.

In a Business, every employee has a steering wheel and a gas pedal but it is not necessary that every body gets a dashboard. Since their role is different they do not need the same level and kind of information. Typically only the higher executives get the dashboard priviliges. Since your boss has the dashboard access he or she can tell their sub-ordinates if they are running out of gas

My company’s product InfoCaptor, will help you create dashboards in minutes so that you can quickly monitor your Business mileage, cash or no-cash etc. Typically a non-executive (BI developer) develops a dashboard for the executive but now with the help of InfoCaptor, you can also create Non-executive dashboards for yourself.

I know it is not a fair question, as a “Daddy” myself; I want all of my children to be intelligent.
Well, this is not any parenting website so of course the title must and should be related to Business Intelligence, right?
  OK, let me ask another question, if you were running your own organization or company don’t you wish
You had information (read intelligence) on all the aspects of your company?

For e.g. in most of the BI initiatives, the emphasis is laid on getting the Key metrics for Financial and Manufacturing Data.
To generalize, the emphasis is laid on the most critical component of your business; it could be CRM, SCM, and Manufacturing
Or Finance. This is perfect, as with the given time and budget constraint you want the most important information.
Time goes by and budget runs over, somehow you manage to assemble all your KPIs into nice looking dashboards and call them “executive dashboards”.

Now lets dig deeper.
How about intelligence on your Outsourcing activity?
Can you effectively tell, if your Outsourcing activity is really beneficial? Do you keep track of any metrics for the code quality, Number of hours worked, Number of Onsite and Offshore developers, Bandwidth Usage, Cost of staying in touch
Etc.

Similarly, How efficient is your infrastructure and the team who is maintaining it? Do you keep track of the number of instances? What are the cloning schedules?
Does your DBA have the right tools in order to efficiently manage his daily job? Can your DBA easily check important System and Database parameters just as your “Executive” can check important financial information?

Similarly, Can your programmer check his or her own performances? Do they have access to metrics that tell them about their code quality, their productivity etc. Is it good for them to have access to a dashboard that instantly tells them about the Greens and Reds? Wouldn’t it be nice if they can see where they are performing good and where they
need improvement rather than you as the manager or supervisor hesitantly telling them anyway!

So effectively you can pick all the activities that is happening and derive intelligence out of it.

So going back to the title question, I guess everybody in your organization is entitled to intelligent information
so that they constantly improve what they are doing and that is what will give them an edge.

If you have plans for “Executive Dashboards” then shouldn’t you plan for “Non-Executive Dashboards” ?

Enough of questions, now lets get Dashboards for all our children