OBIEE 11G: Configure Conditional Navigation Among Reports

Hello it's me again.

This entry is to talk about the use of Actions in making reports (Or Analysis in 11G language) interactive. For those of you that are familiar with 10G or prior, we can set up report columns in such way that it will navigate to another report and the result of that new report will be filtered by the value we click from the source report. You know how that goes, right?

Well, one of the limitation is that we can't make the navigation conditional. In other words, you can't navigate to more than 1 report from a single field based on certain values of the data of that field. For example, I have a column say "Product" that has 2 rows: Soccer, Golf. Product Soccer should navigate to report A that has all soccer sales info while product golf would navigate to report B with all the golf info. To implement this in 10G will require some work using gonav functions, which isn't that pleasant. This requirement has been enhanced in 11g with the use of Action.

Action is one of the new feature introduced in 11G. It allows us to navigate to many different contents such as reports, scorecards, KPIs and agents, it can also be used to invoke external operations. So now we will see how to use action to configure report navigation just like in 10G.

Let's take a look at the below sample report called Report Master, which we will use as the report to navigate FROM:

So, we want to be able to click on any of the account number and have it taken us to a different report that provides more detail of that account only. So let's go to the column property of account number.

As the below image shows that it takes us to the column property windows very similar to 10g. In the 'Interaction' tab, we select 'action link' as the primary interaction. This is something new in 11g that is not there in 10g.

So, we need to create a new Action link for this column account number by clicking the plus sign. Then in the 'create new action' option, select 'navigate to BI Content':

Select the detail reports that you want to navigate to from this field. I have already created two reports: report A and report B as you can see. I will select report A for now..

One of the important thing to do after selecting the report is to make the action 'Conditional', then create new conditions that qualifies this navigation as show by the following:

What happen if we checked "always" instead of 'Conditionally' is that the navigation will take you to the entire report of report A regardless when specific account number we click from report master. Obviously this is not what we want.

Moving forward, let's say this is all we want to navigate to from report master for now. But we do need Report A to filter based on what account number we click from Report Master instead of returning all of the account numbers. Let's add a condition in this action saying account number is not null:

By doing so, all of the values from account number will be enabled for navigation as long as it is not null. Fair enough.

Ok and save the Report Master. Now of course we have to go to Report A (the target report) and create the filter on account number column as 'is prompted'. Just like so in 10g.

After that's done, run Report Master and all of the account numbers are highlighted this time. By clicking on anyone of them, it shows you the navigation destination as report A.

So for account number 102300, if we click on 'Report A' action link, it will take as to report A with only information related to account 102300 in Report A:

Now, lets also include Report B in this navigation. Let's say that I want all of the account number that are less than 110000 to navigate to Report A and those that are greater than 110000 navigates to Report B. Very simple condition.

In order to do so, simply go back to the Action creation window and add a new action with new condition:

So you see now that there is new action 'Report B' being added underneath 'Report A' with a condition saying Account number is greater than 110000. Of course, edit Report A action's condition to being 'Account number is less than 110000'.

Ok and save it. Now let's re-run the Report Master:

As we can see, both Report A and Report B can be navigated from the same column account number depending on which value is clicked..

This is a great enhancement in 11G. Finally we can do without Gonav function.

Thank you

Until next time

Read More

OBIEE Interviews and how to excel

Hello again

One of the biggest challenge for a lot of junior OBIEE professionals is to excel at interviews. In the past few years, I have had the opportunities to both take interviews for the new project and to interviews other candidates for projects that I had. When it comes to interview for OBIEE, specially when this is not an entry level opening, my goal as the interviewer is to find out what kind of experience the candidate has, that's all.

I am sure there are many ways to get to know the candidate's experience, but there are some very common questions that I have been asked and also used during the interview. The question sounds very casual, yet it is in my opinion the most important question that I can ask in order to generate my first impression of the candidate, specially when the interview is done over the phone. Ironically. so many candidates have come unprepared for this question. Want to know what it is?

It is:

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

COULD YOU PLEASE TELL ME A LITTLE BIT ABOUT YOU?

You see, this is a very open-ended question. It is one of the best way for me to know the way you perceive yourself, the way you communicate to me and the way you organize your thoughts when it comes to presenting yourself.

The most common response that I have gotten from candidate is this:

Hi my name is so and so ----duh? you think I don't have your resume in front of me?.

I went to such and such university ----- Thanks for the info, but who cares.

I have been doing OBIEE for 8 years ------well I can read, but talk is cheap.

My first project is blah blah blah, my role was blah blah blah, my responsibility was blah blah,

then I went to this project and my role was blah blah, then third project blah blah. ---- I am bored just listening to all this and still have no clue who you are..

You see, this is the common answers I got 8 out of 10 times. So at the end, how am I going to make a decision if every candidate speaks the same way?

So here is how to make yourself stand out by answering this question the right way.

1. Don't state any facts that you have already listed on your resume. It is ok to mention it here and there, but don't need to make it sound like it is scripted.

2. Focus on talking about how you have come along all these years, what your experience was like. How did you started in this field and what have you learned.

3. Talk about your understanding of OBIEE. If you have been doing OBIEE for years, you are likely to have developed your philosophy and understanding of the technology and the project implementation. State your belief and explain why it makes sense to you over the years. This demonstrates not only you can think on your own, but also is the best way to prove to others that you do have experience.

4. Last but not least, talk about the values you have contributed to your past projects. This is not the same as stating your role and responsibility as those I can read on your resume. Your contribution would be anything challenging your project has faced, and how you helped overcoming these challenges. It could be how to make the unreasonable deadline, how to significantly improve the performance which was killing the project, how you have created a system or a process that optimized the implementation of the project. Dig deep and show them what value you can provide.

Prepare to talk for no more than 5 minutes, Chances are, you will be interrupted as a lot of the interviewers like to pick up some key words you say and start asking questions related to it. If they do, then just go along and drop your story. The key here is to be creative, to relax and present yourself professionally. I guarantee this is the most popular question in any interviews. Mastering how to answer this question will not only get you far in OBIEE, but just about any interviews. I seriously advice to do some research and prepare yourself for this question.

Now just as a bonus, I will reveal you another question that I like to ask my candidates. You see, I am not too crazy about spending 1 hour grilling you with one technical question to another, as most of those questions can be researched online or geekinterview, it doesn't showcase your experience and skills. Sure I may ask 1 or 2 of those questions, such as 'what is hierarchy?', 'what is star-schema?', 'How to configure Ibots?'. But trust me, anybody who do some research will easily find the answer of those, and I am getting bored with it.

The second question that I like to ask right after you have bored me to death with your self-intro is:

-

-

-

-

-

-

-

-

-

-

-

-

Are you ready?

-

-

-

-

-

-

-

-

-

--

-

--

COULD YOU TELL ME WHAT IS OBIEE?

ah ha, so what is OBIEE?

OBIEE is Oracle Business Intelligence Enterprise Edition ------- Duh? You think I don't know?

OBIEE is a reporting tool -------- Well, so is excel.

OBIEE can create charts and graphs ---- So can excel, but excel is free.

I am not going to tell you what is OBIEE as there is no one standard way of answering it. While this may give you some ideas, I still want you to do some research on this. But keep in mind, what is it about OBIEE that costs so much more money than excel? If you are the Oracle sales and you try to convince me the CEO to buy OBIEE with millions of dollars, how would you do it? Think of it NOT just from what technical features OBIEE has, think of it from a higher level perspective as what benefit does OBIEE provide to my business. This is the best way you can showcase your knowledge and experience of OBIEE.

Now go research. If you want, feel free to post your findings on the comment box.

Until next time

Read More

How to run stored procedure from DAC

Hello Again

It's been a while.. This time I want to share something I just learned over the past few months, it is how to execute a stored procedure directly in DAC without having anything to do with Informatica (as discussed in the previous post).

Let's say we have a store procedure or package 'mck_client_wip_load' in stance 'mck_build_revwip_pkg', or mck_build_revwip_pkg.mck_client_wip_load. In order to execute this stored procedure in directly DAC, we need to create 'action', in this case 'task action'. After all, we will execute DAC task to do the execution.

In the action task window, create a new task with a name. In my case I just use 'MCK_BUILD_REVWIP_PKG_MCK_CLIENT_WIP_LOAD'

Now we have to define the value of this action as show:

Now the most important part is to enter the commend for executing the Store Procedure in Action. The place to enter the commend is shown:

The code that I entered here is:

BEGIN

mck_build_revwip_pkg.mck_client_wip_load(@DAC_$$PARAMETER1),(@DAC_$$PARAMETER2),(@DAC_$$PARAMETER3);

END;

Notice that there are 3 parameters in this code. That is because this specific store procedure requires 3 input values at run time. If the store procedure doesn't need any input values, then it would simply have been:

BEGIN

mck_build_revwip_pkg.mck_client_wip_load;

END;

If you look at the left pane of the above image, you will notice that '@DAC_$$PARAMETER1' is available for you to click on under SQL Statement pane.

In order for this to happen, we need to create these parameters in task. So let's go there:

In the task, we enter the task action name 'MCK_BUILD_REVWIP_PKG_MCK_CLIENT_WIP_LOAD' in both commend for incremental load and full load field.

Then in 'Execution Type' field we select 'SQL file'.

The values for folder name and Primary Source and Primary Target aren't important in this case

as we are not dealing with informatica at all. The value for Primary Source should be the connection parameter for which database the store procedure is located.

Now, in the parameter tab under the Task, we create 3 parameters:

Each of the 3 parameters also have a hard coded value in this case.

This is pretty much it and the rest is simply adding this task in the subject area and execution plan for running it.

When running this execution plan, it is as if executing the following statement directly in the DB with the parameter values:

BEGIN

mck_build_revwip_pkg.mck_client_wip_load(@DAC_$$PARAMETER1),(@DAC_$$PARAMETER2),(@DAC_$$PARAMETER3);

END;

Until next time!

Thanks

Read More