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

Hello

Today I want to share this requirement that I encountered. This seems like a straightforward implementation but it's tricky actually. Currently, OBIEE's agent framework is not flexible enough to achieve this, therefore I feel like sharing this requirement out and maybe someone else will have even better ideas. First, let me explain the requirements and the background.

Take the below dashboard for instance:
The dashboard has 5 prompts and we all know what they are for.

Now, because of the security framework we have already implemented that restrict user access at data level  based on their groups, not every user will be looking at the same dashboard contents.  Therefore, when this report is delivered to each user with emails, user wants to see the reports based on their particular interest. In other words, they want the agent to have some sort of ad-hoc feature that allows them to define their particular prompt values and receive reports based on that.

To illustrate with an example, user A wants to see this dashboard delivered to him for data that are only 6 months old, for manufacturer BMW only and Payment Type is warranty, and average cost is $500. User B wants to get the same dashboard but with data that is only for 3 months old, and manufacturer is only Audi, Payment type is Customer Pay and average cost is $1000. Now an user could be servicing multiple car manufacturers at the same time, then the same user may ask for multiple versions of the same dashboards to be delivered separately. He may want one for Volkswagen of 2 months and another one for Toyota of 4 months delivered with separate emails so he can work with those manufacturers individually. Also, the system should be complied with the same data level security that if the user by mistake request the wrong manufacturer that he is not scoped to, he should get an email with empty dashboard. The confidentiality of the data should be protected and the implementation should take into account of any man made mistakes.

I know OBIEE doesn't have any existing features that allows ad-hoc query of agent framework, because ad-hoc is available only when you are logged on to the system to run dashboards yourself. If this requirement were to be implemented, we may end up creating different versions of dashboard for different user's request. This can become a nightmare. Now other open source reporting tools, like Jasper Report can implement these kind of requirements with some loop back functions, because they are open source, it provides greater flexibility in terms of features, but on the other hand, the technical requirement of the developer is higher too. In other words, you have to be better at programming to make Jasper a much more powerful tool than OBIEE. Think in terms of Android vs IOS. I digressed.

I still decided to give this a crack in OBIEE just to see how far I can go. The first thing that came to my mind was to find a way to pass these prompt values to the dashboard whenever an agent runs the query. These values should be different based on the user name. Sounds like a job of session variables. But first and foremost, these values will be provided from users as request, we need to first design a way to store these information. Therefore, I have decided to create a table for that.

A table like the below was created, and after numerous trial and errors I have come to this structure

So it has the following attributes:

Users: This column stores the real user name of the client company. The users here must match the users in the security table that I explained for another project  in order to comply with the data level and object level security

User_Name: This column is nothing but users with '_' and sequence numbers. I have 3 rows for user 'charles' because this user is asking for 3 different versions of the same dashboard. So this column will be charles_1, charles_2, charles_3. If user only request for 1 version, then there is only 1 row for that user in this table and the value of this column will be users_1 (dchadsey_1). This is the unique column.

Startmonth, endmonth: These are the parameters that will be passed to the dashboard's date or month prompts. These values will be integers, '-' means how many months or days back.

MFG_HIERARCHY, MAKE, PAY_TYPE, AVG_COST: These are other attributes users will define which will be passed to the dashboard prompt.

Email: User's email address.

User_type: This tells you whether this user is company user or external users.

The reason I have come to this design is the conclusion after numerous trials and errors:

I tried a different approach before, which is to not have user_name column. Use users and create session variables with row-wise initialization hoping that the agent will query the this table row by row for the same user. But it doesn't work this way. On my lucky day (I don't even remember what I did), I was only getting 1 email with the first row of the table being passed. The other 2 requests from this user were not run. On a normal day (I tend to think I am not normal for tackling this task), the query with row-wise initialization will not return any data.

Therefore, I have decided to fool OBIEE into thinking that the requests are not coming from the same user 'charles', but rather, from 3 different users: charles_1, charles_2, charles_3. Once these 3 recipients are added to the agent with the same email address, all 3 requests from 'charles' will be fulfilled.

Enough of today. In part 2, I will explain how I configure the rpd and modify dashboards for this to work.

Stay tuned.