Case Study: Emailing OBIEE dashboards with ad-hoc agent that dynamically passes dashboard prompt values based on user request -- Part 3

After rpd work is done, let's go to the dashboard.

Well before that, I am manually creating these users with '_1' and so on in weblogics and make sure they all have their OBIEE account set up with the proper emails.

Now go to dashboard and we will copy the existing dashboard and reports and create another version for emailing agents.

First, modify the date and month prompt and manufacturer prompt

Here in the default section, we are adding session variable as filters:

The idea is to use timestampadd function here to add a new year and month value based on the integers of the session variable of each 'startmonth' and 'endmonth', then add 'yyyy' with 'mm' to make it 'yyyymm' as numbers. This new value then will be filtered against the year and month column of the date dimension.
If the dashboard is at date level, then for the same logic, we use timestampadd to compute a new date using the integers from session variable  'startmonth' and 'endmonth'.

For the other prompts, I realized they are variable prompts, and there is no computation like Date and Month prompt does, so we will use variable expression here to call session variable:
Syntax is below:
@{biserver.variables['nq_session.pay_type']}

After getting done with the prompts, we will make necessary changes of the reports and dashboard structure.

Now take a look at the dashboard. Initially the prompt values will mostly be empty because it is run by admin user who doesn't have anything defined in the security table.

Now create an agent and set it as 'run as recipient':

Recipients of the agent would be all those test users I created:

Save and manually trigger this agent and see what we got:

Now I got a few emails since all of these users have the same email account, which is mine:

Now I have to open each up to make sure the contents are different and the values should be different. The first 3 emails are all for user 'charles', which is recipient charles_1, charles_2,charles_3:

Ok, 1 for manufacturer 'HMA', customer pay, dealer cost $300 between July 2013 and Sept 2013

For manufacturer 'AUDIUSA' and different other prompt values:

For Manufacturer 'PORSCHEUSA' and different prompt values:

This report is for user 'dchadsey' and all of the parameters defined for this user in the table are passed along

Now let's test for error handling. I will manually update dchasey's MFG_HIERARCHY value to 'BMW'. This will be a wrong value for this user because in the security table, this user is not scoped to that manufacturer.

Now rerun the agent and i should be getting an email with no data. It would be a breach of confidentiality if this user indeed gets data for BMW that he is not scoped to.

Success!

By changing the value to BMW, the query defined in the session initialization block should return nothing, therefore the 'Manufacturer prompt would be empty.' Of course, I then played some tricks by adding conditions of each dashboard section to hide or display them based on whether manufacturer prompt is blank or not. There are many ways to do that, which I am going to skip for now.

So, the prove of concept works for a few test users. However, this is still not enough if to implement this at a larger scale. There are two main pieces of the puzzles according to me:

1. users will probably document their request in the form of spreadsheet and upload it somewhere. For each request of delivering a different dashboard, there will be a separate table like the one I designed. The user defined parameters will then be loaded into our DB table and adding '_' and sequence number behind each user name will need to be done. Managing this process of communicating between users inputs and DB tables will need to be in places. A lot of decision makings need to happen. User could even request an UI for their updating of request, which will need to be engineered to communicate with the DB Table systematically.

2. Manually creating of all these user_1s in OBIEE will be a nightmare. I know OBIEE has a project called 'SA SYSTEM Subject area', which once implemented, will automatically populate users into OBIEE and associating it with emails. However, the requirement for this to work is pretty rigid. If I can automatically populate columns for 'user_name' (all of the _1s values) not the actual 'users' column using SA Subject Area, then this would be a huge win.

3. Different dashboards are created differently. Some dashboards are more complex than others. For each specific dashboard to be delivered, it will have to be modified differently to pass the session variables correctly. There is no one size fits all solution to this approach.

Having said all that, I think I have come to as far as I can come with this solution. It's not perfect solution, in fact, it may be a terrible solution in someone's eyes, but it does work to some extend. Maybe oracle has plans for this type of requirements in their future products.

For now, I am opened to see other solutions.

Until then