Configuring Incremental Loading In Oracle BI Apps Part 2

In the previous post, I briefly talked about how Incremental loading works and how to go about it at the high level. So, it's time to get to the details.

So let's start with an easy example that there is 1 source and 1 target, which is PA_EVENT table. This table has 3 unique columns "Task_ID", "PROJECT_ID" and "Event_Num".

Before we start with the mapping, let's make these 2 columns the primary key in target designer:

In Dac, make sure we have 2 global parameters, or source system parameters:

$$LAST_EXTRACT_DATE: This is a variable that the value is determined at run time. DAC_SOURCE_REFRESH_TIMESTAMP is the default run time variable that stores the history of timestamps of all the updates made in the source table in the database.

$$LAST_UPDATE_FILTER: This is a parameter that I have created for informatica to use in it's source filter. This parameter's value is static with the logic: LAST_UPDATE_DATE > TO_DATE('@DAC_$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')

In other words, when I enter this parameter in the source qualifier, at run time the above column will be added as a where clause in the sql statement, filtering out all of the records that the last_update_date is less than last_extract_date from the source level.

So now let's start implementing the incremental logic in Informatica.

As you can see, the beginning of the mapping is very simple and straight-forward. We are going from source to applying the filter in SQ transformation, now here we are.

So now, everything that comes before lookup transformation should be the records that it's corresponding last update date is greater than the previous recorded timestamp, not all of the records stored in the source table.

Therefore, from these filtered records, we need to see whether we are going to insert them in the target or update them if it already exist in the target.

How do we know what to do? Well, we will look at the three unique columns "TASK_ID", "PROJECT_ID"and "EVENT_NUM" and see if they match from both source and target. This is where lookup transformation comes into the picture:

From there as we can see, there are 6 columns in the lookup, 3 are from the source, the primary key columns. The other 3 are created to be the lookup columns, there I am calling them LOOKUP_INPUT10, 11 and 12. These columns in reality, will be referring to the target columns of the same. So I am really just going from source.Task_ID to target.Task_ID here. Although it may look fancy.

Now, we need to set the lookup conditions as how we want to compare these fields. We have the option of setting source.Task_ID equal target.Task_ID or great or less or whatever. This time, I am setting the condition to be equal:

Now at run time, informatica doesn't know what lookup_input10 is TASK_ID. The value is going to come from DAC which we will get to later. Therefore, we need to specifically tell Lookup transformation about these things, therefore, we define lookup override in the property:

What I entered there is:

SELECT

$$LOOKUP_INPUT10 AS LOOKUP_INPUT10,

$$LOOKUP_INPUT11 AS LOOKUP_INPUT11,

$$LOOKUP_INPUT12 AS LOOKUP_INPUT12

FROM

$$TARGET_TABLE --

Each of the "$$" represents a parameter name that we will later define in DAC. So this SQL will be executed at run time and the result of this statement will be used to compared to the 3 columns from the source based on the lookup conditions.

In our particular case, think of it like this:

Source: The filtered 3 unique columns from the source has a list of records that are recently updated after the last loading.

Target: Select 3 columns from the target table

Lookup condition: (if ) Source = Target

Then generates 1 lookup output that holds the result of each comparison and bring it into expression transformation for assigning meanings to the lookup output:

So we are good so far. We will continue on finishing the mapping in my next entry:

Until next time:

Read More

Configuring Incremental Loading In Oracle BI Apps Part 1

Hello

As we all know, BI Apps is a package of tools with out of the box configuration catered to specific business area. It consists of pre-build Informatica ETL mappings, Dac Tasks and OBIEE data models & dashboards.

More often than none, we have to customized these pre-build objects in order to have it fit in to our specific project environments. These customization includes adding new fields across the ETL, changing the data models, adding incremental logic to non-incremental mapping and so on. Today, let's talk about how to configure Incremental Loading logic in BI Apps environment.

There are many different ways to implement incremental loading in BI Apps environment, the way I am going to do is by passing parameters from DAC into Informatica for incremental loading. The logic of the mapping is more or less similar to the standard Informatica practice.

For beginners, let's break down the process as follow"

1. Determine how many columns of the target table are unique columns that should be primary keys.

2. Filtering the source based on certain columns to determine what data are new data and what are old data. Usually, for each type II or type III Slowly Changed Dimension tables, "last_update_date" is almost the best column to filter on in source qualifier transformation.

3. Use lookup transformation to compare the unique columns from source to target and provide 1 output.

4. The value of the lookup output is either null or not-null as the result of comparing the source and target field. If everything in the source already exist in the target, then the lookup outcome is (depending on the lookup condition) null or not.

5. In the expression transformation, create conditions for null or non-null scenarios by assigning values such as "I" or "U".

6. In the update strategy transformation, using DD_Insert, DD_update commends in update strategy expression for each of those values from expression transformation.

The next article, we will look up the detail steps from Informatica to DAC on how to actually implement the incremental load

Until next time!

Read More

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