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 

Read More

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

From the previous time, I have finalized the DB table design, now let's check out what I did in the rpd.

Below are the session initialization blocks. 'Security' and 'GROUPS' where created for the existing data level and object level security implemented before.
Now I am adding a new one 'Security_Agent':

In Security Agent, I have created session variables for each columns of the table. These session variables will be passed on to the dashboard later.

The initialization string is the following query:

Select USER_NAME, START_MONTH, END_MONTH, MFG_HIERARCHY, MAKE, PAY_TYPE, AVG_COST,EMAIL, USER_SEQUENCE_NUM, USERS 

from DW_OBIEE_USER_AUTH

where 

USERS in (select USERNAME     

from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 

AND USER_NAME = ':USER'

and MFG_HIERARCHY in (select company_name     

from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 

UNION

Select USER_NAME, START_MONTH, END_MONTH, MFG_HIERARCHY, MAKE, PAY_TYPE, AVG_COST,EMAIL, USER_SEQUENCE_NUM, USERS 

from DW_OBIEE_USER_AUTH

where 

USERS in (select USERNAME     

from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 

AND USER_NAME = ':USER'

AND USER_TYPE in (select SCOPE_NAME FROM table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  )

The blue part of the query takes the input of (:user), which is going to be in the form of 'user_1', strip everything after '_' in order to match in the security user table that the client company already has, then it returns the values of the same user in my new DB table with corresponding dashboard parameters. The user in my table must exist in the security user table, not only that, but the corresponding manufacturer of that user must also match the same in the security table. This takes care of the error handling of man made mistakes. 

The red part of the query is identical to the blue part. However, this part is only for users who are company's internal users. If you followed my previous implementation of data and row level security, you will realize that for company user, there is no corresponding company name of that user in the security table. It is only flagged by 'HAS_GLOBAL_APP_SCOPE' = 1 in that table. So the blue part of the query takes care of external users, but it will leave out all of the company's internal user, which is not good. Therefore, the blue and the red are both needed and it will be unioned. 

All variables are set with default values:

This is all I need for the rpd.

Next time, the most tedious work will be begin, which is modifying dashboards to receive these session variables.

Stay tuned.

Thanks

Read More

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.

Read More

Case study: How to implement Object level security and Data Level Security in OBIEE 11G - Part3

Last part we have gone through the DB changes and RPD configuration for implementing data level and object level securities. Now there are some final pieces that needs to be done in order to make this implementation perfect. Let's start with object level security. We need to set them up so that different user group will only have access to their dashboards first.

So here are the different folders that stores dashboards and reports meant for different users.

As you can see, I already know what kind of access each folder should have as shown below:

I will show you what I did for manufacturer and dealer group folders. Each will be assigned to the proper application roles with the right level of privileges:

Same idea for partner and dealer folders, which I am going to skip now. For internal user, the below is the configuration:

There are other dashboard objects that allows permission setting, this will further controls object level access based on user groups. I am going to skip all these steps now.

For testing, let's see how things are working when these different users log in:

1. Manufacturer user logs in:
Object level Security working:

Data level security working:

2. Dealer Group user logs in:

Object level security working:

Data level security working:

3. Other user groups are working the same way, I am gonna skip posting the screenshots. 

Let's look at company internal user logging in:

For object level security, this user is unrestricted so all dashboards are accessible:

For data level security, this user is unrestricted, so he can see all data:

Thanks 

Until next time

Read More

Case study: How to implement Object level security and Data Level Security in OBIEE 11G - Part2

Last time, we have described the security requirements and the basic implementation strategy. Now let's take a look at the next step after the user table has been created.

One thing I forgot to mentioned from the last entry was that, the password has encryption. Therefore, a method of decoding encrypted password is needed. Having that into consideration, we have created a few DB functions.

1. Ret_User_Auth does the authentication process by passing user input logins and matches with the DB table to check for authentication.

2. Ret_User_Details takes the same logins passed from Ret_User_Auth and finds it's corresponding groups and scopes.
Below test case shows you the scope name (colonial VW) and group name (dealer group) of test user u1

Function Ret_User_Auth actually calls another function which does encryption. So typically, the encryption function is another object that is created for this implementation. I am not going to show you the details of these 3 functions, you can contact me privately if you want to see the sample.

Now that these are done, let's go to OBIEE's admin tool and create the following session variable initialization blocks:

CompanySecurity: executes Ret_User_Auth
GROUPS: executes: Ret_User_Detail

Below are the contents of these 2 initialization blocks:

Group initialization block returns 4 variables: Username, Groups, Scope_names and Dealer_Names. It also has precedence of CompanySecurity initialization block, this means that CompanySecurity initialization block will always be executed before.

Scope_name will return you the name of manufacturer or dealer group or partner that this user is scoped to. So for the above example of user u1, if he logs in to OBIEE, the value of scope_name variable will be 'colonial VW'. This is why session variable is used for user authentication, not dynamic variable, because when u1 logs in, the scope_name variable will be populated as 'colonial VW' just for this user session, when a different user logs in, he will have a different session which will populate scope_name with a different value that's associated to that user in the table. Dynamic variable doesn't do that. It doesn't differ based on sessions.

Anyways, enough said. Now that both initialization blocks and variables are created using these 2 functions, let's do the next step, which is important for achieving data level security. We need to apply data level filters for each user groups, which in terms of Admin Tool, it's the application roles.

So look at the below screenshot:

We have 6 application roles corresponding to 6 different user groups & scopes.

They obviously have been created through weblogic, just and FYI:

Now for each application roles, depending on their scoping requirement, we need to set the filter for it's permission specifically.

For manufacturer group, it needs to be applied to all of the columns under manufacture dimension where the manufacturer name = session variable 'scope_name'
Remember, scope_name in my case returns the actual manufacturer, partner, dealer group name that the report should filter on.

For Dealer group scope, it is similar setting on dealer group dimension:

For dealer user, since the requirement is that they can access both manufacturer and dealer dashboard filtered for the specific dealer, they will have 2 filter conditions applied, each one is for manufacturer and dealer dimension separately.

Now for the internal users, they are called 'staff' by the application role. Since they should have access to all of the contents everywhere, their condition is simply left blank:

Now you should have a basic idea of how our data model is like. It's a pretty straightforward star-schema implementation where different user scopes are separated by it's own dimension

So the idea is that, when a user logs in, the session variable scope_name will be initialized with a specific value, it could be 'colonial VW' if this is a dealer group user, it would be 'General Motors' if this is a manufacturer user, it could be blank if it's an internal user. So for each dimension, this filter is applied at the rpd level so that when this user access to a dashboard (by having the right object level security to begin with), he can only view reports where the manufacturer name (or dealer group name or partner) = 'general motor' (or 'colonial VW' or a partner scope name). If this user is internal user, then scope_name is blank, he will see everything unrestricted.

Now the last step will be to apply object level security and to apply these session variables at the proper place on answers and dashboards,which we will cover in the next part.

Stay tuned

Read More

Case study: How to implement Object level security and Data Level Security in OBIEE 11G - Part1

Hello Again.

Recently my team has implemented data level security and object level security in OBIEE 11G for a client who is selling their data to external customers. The requirement is very quintessential of any BI Project and the implementation process is trivial. Therefore, I have decided to detail the process, which I think can be very beneficial to any future security implementation.

A little bit of background here. This is a client that incorporates OBIEE into their software solutions to external customers. They are making their BI Platform available through the integration with other apps, which the external customers can subscribes to with a service fee and view the data pertaining to themselves specifically. Since most of the external customers are individual auto dealers and manufacturers, the sheer number of consumers is big and the confidentiality of their data is essential. The requirement pretty much comes down to the following:

1. Individual dealers can log on to the apps and view reports and dashboard for only that dealer

2. If an account is associated with 2 dealers, then it's called 'dealer set'. They can only view dealer set dashboards and reports that the content must be pertaining to that specific dealer set

3. An account can be associated with dealers as well as the manufacturers, such as Honda, BMW etc. Then this is a manufacturer account, so manufacturer dashboard and reports can be accessed for only the data of that manufacturer. 

4. At the parallel level of manufacturer are auto partners and auto dealer group accounts. There will be separate dashboards available for partner and dealer group, each is exclusive from the rest. Only the specific dealer group or partner data will be available for the specific account associated with it.

5. There are also users from within the company itself. They are typically sales people. They should be able to access all of the reports & dashboards and see data for all of the dealers, manufacturers, dealer groups at will.

This requirement can be simply translated to users & user group where users will be individual account and user group will be the specific scope of that user, such as dealer, dealer group, partners, manufacturer or company's internal users. So let's think about how to implement this requirement.  

First and foremost, before implementing data and object level security, we need to first decide the process of authenticating these users into OBIEE. For the most obvious reason, that most of the users will be be stored in LDAP server since they are external to the company, they will be managed by a table. Therefore, external table authentication becomes the choice. Now the first thing to do is designing the table structure that will accommodate all these different users and scopes.

Below is the table designed for storing all of the users and associated dealers and scopes:

I have created 6 users for six different scope:

Company user is flagged bu HAS_GLOBAL_APP_SCOPE = 1. That way, each company user will only occupy one row in the table and by default, we will not restrict such user from accessing any OBIEE report both at object level and data level. Their corresponding company and dealer name will be left blank because they have access to all by default.

Dealer Group users will have the company_type = 'dealer group' and dealer_name = all of the dealers under that dealer group (Colonial VW in my test case).

Partner and Manufacturer users will be similar to dealer group users, except the company_type will change accordingly. 

Dealer Set user will have 2 dealer, indicating it's a set. Their company_name will be populated with a dealer set name (Sorry i forgot to populate that from the above screenshot).

Dealer user will have only 1 record and the company_name will be left blank.

An overview of the user & user group hierarchy will look more or less like the following:

I have left out the internal company user from the diagram because they are unrestricted. Dealer is pretty much the same as user since 1 user for only 1 dealer association is considered 'dealer user' anyways.

Now having done that, we need to start planning the strategy of authenticating users again the information we have stored. 

In any case where you have user and associated groups with different accessing ability, the standard process of implementing external table authentication and then assigning authorizing roles can be broken down into 2 steps:

1. User will attend to logon to the application by passing their username and password that they type onto the screen. These 2 parameters should be passed onto the user table to see if this username/password combination exists or not in the table. If it does, it will return username and password as output and user will get authenticated so their logging in is successful. This step can be named 'authentication'.

2. The output of the above step will be username and password. These 2 values will then need to be associated with the corresponding scope or user group. This step, let's call it 'Authorization' or 'scoping' or whatever you want for your own memory.

Now in my other project, I have done the above 2 steps using different table structures from this. I had 3 tables, one for user only, one for user group only and one stores user & group relationships. But here, we are storing everything in just 1 table. The difference means that we will configure OBIEE differently.

So next time I will get into details of the things to be done at the DB level and RPD level for the actual implementation.

Stay tuned

Read More