Interview for OBIEE position and how to succeed

Hello All

Today's topic is everybody's favorite, that is about interviews. Interview is a pain in the ass and it is for a lot of people. The more senior you are (or you think you are), the more you dislike interviews. Doing the work and talking about your work are completely two different things. When you are at work, you may run into challenges, but at least you don't have to come up with a working solution in the next 30 minutes if you can't. However in interviews, you are expected to provide proper responses in 5 mins. Therefore, if you want to excel at interviews, you should prepare yourself on your emotional intelligence, not so much on memorizing the things you can google.

Some of the mostly overlooked questions are:

Tell me a little bit about yourself, in particular, your experience related to OBIEE, BI Apps, Informatica, project management etc.

What is the most challenging problem you have encountered in your past experience with OBIEE, BI Apps, Informatica, project management etc, and how you solved that problem.

What is your approach to _____?

See, these are open-ended questions, depending on how you respond, the interviewer can throw anything at you. But first and foremost, it is important to prepare a well structured response to all these questions before any interviews. You don't have to memorize your speech word for word, but you do need to follow your structure. Over time, based on your growing experience of interviews, you might refine your speech.

Oftenly, interviewers aren't much better than you either. However, they are in the position to make decision, therefore, you need to learn to interact with them properly.

Sometimes, interviewers make random comments at your responses that may affect you emotionally. You were describing how great your previous project was, or how complex your design was and then here he comes with 'That's not a big deal.' or 'Aren't you over-complicating things? ' or even 'How is that going to work?'. Suddenly, you feel you have been disrespected, looked down on or misunderstood. This is very common among experienced job candidates.  The more experience you have, the more likely you will resist other's criticism, especially in the engineering fields where people rarely worked on people skills. You have been through so many rounds of technical discussions at work, you are so used to arguing with your coworker and you always win, therefore, you can't handle when people don't show appreciation of how great you think you are during interviews. Your subconscious will look at this interaction just as another technical discussion or even a 'political fight' in the office. You start raising your voice and so does the interviewer. At the end, you may win the argument, but lose the interview; or this verbal exchange will leave a significant mark on your psyche that after that all you are doing is mumbling to yourself with 'this guy is an asshole' and you totally forget about the fact that the interview isn't over.

Over the years of interacting with engineers and managers, I have realized something when it comes to technical discussions. If it occurs during the interview, it's easy. Just answer the question with whatever you know, don't worry about trying to convince the other person. If the interviewer is saying something you don't like, just take it easy. Reply with 'ok, that's interesting you said it', 'ok, I see your point' or 'What you said also make sense, I think we can definitely discuss this in more details off line.'. For God sake, just get over with this question and go to the next, if the interviewer wants to show off his knowledge, that's even better, let him do all the talking, he might enjoy so much that he would hire you based on that. Now of course, if you are applying for management, sales or customer service jobs, that's different. For engineering jobs, the less you talk the better it is.

In today's world, the competition in IT is getting fierce, especially in niche markets like OBIEE, Hadoop, Salesforce, Tibco where there are plenty of candidates, the interviewers rarely ask questions where you can google the answer anymore. They want to find out whether you really have experience or not. Therefore, a lot of questions are open-ended, opinion based questions. While it is important for you to prepare your speeches, it is just as important to realize that there is nothing wrong with you if you don't get the job after.

Everything happens for a reason. How you perform during your interview is always an accurate reflection of where you stand in the job market and your relationship with this particular interviewer. There is no surprise no matter what the outcome is. Take it easy. Try to learn something from the interview if there is anything to be learned, remember the questions that you couldn't answer and research on it afterwards. Do what you can to improve your chance for the next interview. If you fail, don't take it too seriously. I have worked in this field for long enough time that I personally can tell you that you can rejected for all kinds of reasons that it might even sound ridiculous to you. Just chill out, don't think about the interviews after it's done.

Hopefully, after working in the field for long enough time, you build up your personal network well enough that opportunities will get referred to you rather than you having to apply and compete for it. Building contact is one of the most important things to do in life and that's how you can minimize your chance of getting interviews in the future.

Until then:

Read More

Create Pixel Perfect Report using BI Publisher that includes charts, tables and dynamic SQL query

Hello

This time I want to share with you how to create a pixel perfect BI Publisher report out of an existing dashboard that includes different type of reports.

When it comes to reporting, the BI Dashboard is highly interactive, it allows navigation, ad-hoc query and other dynamic features. That's the value of OBIEE. However, sometimes when you want to send the results with pdf or other formats, it is not so pixel perfect. In some areas people don't mind, but in a lot of inter-company communications, the version has to be pixel perfect. I mean, have you ever seen an invoice letter from your bank that's components are out of proportion? This is why BI Publisher has been integrated into the OBIEE platform.

Take a look at the following dashboard:

It has multiple charts, tables and texts arranged in a articulated way. There are even reports created using direct SQL query:

So reports like the following are regular reports displayed in charts or tables:

Some reports like the following are created using direct SQL with presentation variables passed from the dashboard prompt:

So lets see how we can create the same version using BI Publisher which is pixel perfect when you print it out.

We will start by creating new data model. In the data model windows, go to diagram and select Oracle BI Analysis for the reports that are created using regular approach. I then locate all of the saved BI analysis that I want to create, just select them one by one.

For the one that are created using SQL query with presentation variable, you need to change the data set to using SQL Query and paste the SQL query into the windows:

As shown in the screenshot, the syntax of the presentation variable will need to be changed from @{PV} to :PV:

After these are done, close the box and it will automatically ask you to create parameters. Just create the parameters with the same Presentation variable name and give default value:

After some times, all of the data sets (each one corresponds a BI Dashboard analysis) are create as shown:

Now save the data model:

Now create new BI Report using the data model that just created as the data source. While creating and going through each windows, it will ask you for layout templates or add tables, just click next and go through them without doing anything:

Then save it:

Now open the saved report. It is going to run and most likely it will not return anything. But it will allow you to edit report, so do just that:

It will open up a blank template, this is where you can feel free to design your own report arrangements:

One the left side, all of the data set that you created earlier for that data model will be available to be put on the blank sheet.

The insert type on the top of the sheet allows you the insert all kinds of objects, from gauages to tables to charts. I used layout grids to break this blank sheet into different smaller subdivisions and then insert each of the reports into those subdivisions:

The properties on the left side of the sheet allows you to adjust it's size, color and arrangement to make it look good:

After it's done (these are not technical work, it is more tedious art work), save the report. 

Now we can add this report to the original BI Dashboard:

The BI came be launched here:

Now after launching the BIP report, it has to pass the value of the BI dashboard:

The result is working:

Thanks

Until next time

Read More

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