A practical intro guide to Oracle Data Admin Console (AKA DAC) Part 3

In Part 2 we went over the basic setup in DAC, so the next is building tasks:

To build task, simply go to Design --> Task --> New:

In the 'edit' tab, we start filling in the fields. Notice there are two fields: commend for incremental load and commend for full load. Usually, when creating informatica workflows, there are 2 separate sessions, one for full load and another for incremental load. For simplicity, I only create 1 workflow, therefore in these 2 fields, just enter the name of the informatica workflow name.

In the 'folder name' field, enter the name of the logical folder name that we created.

The primary source and primary target field is where we enter the parameter name of the DB source and target that is defined in the session property in informatica workflow manager.

If you want to know the detail of every field under task, please refer back to the DAC guide for more info

Now that the basic info are entered, we then add the source table and target table under this task using the table we already imported earlier:



Now that a task is create, we then need to create the subject area. The subject are will be eventually used by the execution plan that we will build.

Stay tuned for part 4

Read More

A practical intro guide to Oracle Data Admin Console (AKA DAC) Part 2

Picking up from part 1, let's continue on:

Log in to DAC first:



After logging in, an interface like below is likely to be what you will see:


Ignore the objects you see because a lot of what you see from the image are pre-configured stuffs. Our goal is to create a new process in DAC that will handle the informatica workflow that I just created, so assume nothing exist for now(well, except the custom container that I will take it for granted this time), there are a couple of setups we need to have in order for DAC to communicate with Infomatica and the DB so that our workflow can work. We need:

New physical datasources

New source and target DB tables that is defined in Informatica mapping

New physical and logical folders that refer to the informatica workflow folders

So, let's start by creating new physical datasource first:



Notice the name of the new physical datasource has to be the same name as the database connection name used in Informatica session. So in this case we have ORA_R1213 and Mckinsey_SDE_Forklift_DW.

After having done that, let's get on to importing new tables using the datasource we just created (DAC document will show you other ways to create these tables as well, read more if interested ):





Now, let's create the physical and logical folders.


Remember, the physical folder has to refer to the repository folder that exist in Informatica workflow manager, the logical folder will be used by DAC task, and logical folder has to associate to a physical folder.

Knowing that, I have created 1 physical folder called SDE_MCK_Forklift and 1 logical folder called Mckinsey_Extract_Forklift, and associate them with one another:





So now that we have the physical/logical folders ready, the necessary DB tables in place, we are now ready to build task.

Stay tuned for part 3

Read More

A practical intro guide to Oracle Data Admin Console (AKA DAC) Part 1

It's been a while since I post last time. I want to step aside from OBIEE just briefly and talk about this tool known as DAC. If you have worked on OBIEE projects, you are likely to come across this tool. Especially if the project uses OBI Apps, this tool is guaranteed to be there as a management layer between informatica workflow and OBIEE. It's primary purpose is to simplify and centralize the process of scheduling and streamlining the ETL workflows done by Informatica.

In other words, DAC is a separate application in its own right, just like Informatica, OBIEE and many others. Oracle has provided some documents about this tool and how to configure it. I highly recommend to go through the documents to gain the thorough understanding if it and use it as a master reference in your actual project. However, I personally found the documents lack practice examples for its many features. Therefore, I decided to create this series of articles to give a more practice examples with demonstrations on the information that DAC guide may have mentioned.

The intention of this series is to help beginners who have read the DAC guide but still aren't so sure where to start with DAC. I was a beginner before, I still remember a lot of questions and uncertainties I had after reading the document, therefore, I am going to make my case study as basic and simple as possible just to show how it works in DAC from the perspective that tailors to the beginner as much as possible.

Ok, let's begin with a workflow that I already created in Informatic as you can see:
The workflow SDE_MCK1213_FND_LANGUAGES is created and saved under the repository folder SDE_MCK_Forklife as shown:




The mapping of the same name: SDE_MCK1213_FND_LANGUAGES is what the workflow session is using. It's a basic mapping with the following informations:

Source table: FND_LANGUAGES

Target table: FND_LANGUAGES

Source DB: ORA_R1213

Target DB: Mckinsey_SDE_Forklift_DW








So now that we have all of the basic information about this workflow, we want to use DAC to run this workflow so that the data will get loaded.

Knowing this is the requirement, how are we going to start our configuration in DAC?

Find out in part 2 of the serie.

Stay tuned.

Read More

Convert a date type into year&month format

I am sure some other people have blogged this somewhere, but I'd like to add this to my list of topics because I have encountered this type of requirement quite often.

Basically, let's say we have a column called 'shipped date' that has data like 'YYYY-MM-DD'. If we want to change this data so it displays only year and month, like 'YYYY-MM' or 'YYYYMM', then how would we do it?

First of 'YYYY-MM-DD' is date datatype but once we change it to 'YYYYMM', it no longer is date anymore. Therefore a database function 'To_char' is needed for such a conversion.

In OBIEE, there is a function 'evaluate' that can help executing database functions within the Admin tool.

Therefore, the entire statement is something along this line:
EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), "Purchase_Facts"."PO_DATE", 'YYYYMM')
if that's all we want to do.

Here, %1 and %2 are the number of parameters we are passing. We are dealing with only 2 parameters in this case: year and month, so we have %1 and %2.

So having followed the syntax, we define this statement in physical column mapping, which is in the LTS of the logical table in BMM layer, as you can see in the below testing sample:



This gives the physical definition of the column to be 'YYYYMM' instead of the original 'date' data.

Try it and test it for yourself..

Thanks
Until next time.

Read More

IBOT: Difference between the two Data Visibility setting

I know this is a very basic point about setting up IBOT, I still think it's worth noting on the blog for those who only knows the difference conceptually but not practically.

As you can see, there are 2 optional for 'Data Visibility' setting when creating any IBOT:


So what exactly does IBOT do when the setting is 'personalized' and when is not 'personalized'?

Personalized setting: The IBOT will execute the report and delivery its content for each individual recipient in distinct sessions. In other words, if you have 5 recipients for this IBOT, then when the IBOT is running, in the session monitor screen, you will see 5 sessions with the exactly same SQL being executed, each for one of the five recipient on the list. The feature of allowing subscribers to customize IBOT is enabled.

Not personalized setting: The IBOT will execute the report and delivery its content to all recipients at one time, the session will run as the userid that owns this IBOT. In other words, in the same session monitor screen, you will only see the report executed once and only, and the userid that the session belongs to will be the creator of that IBOT. On the other hand, the feature of allowing subscribers to customize IBOT is disabled.

So, knowing the difference, the rest is the business decision to make. Depending on the environment, hardware and software resources, and the demand of the BI system, if having too many query sessions running on the server box at the same time is not a good thing, then avoid personalized IBOT setting, especially when dealing with many IBOTs of similar requests. If you want to give certain users the ability to customize the IBOT because it is more important, then 'personalized' the setting. You just can't have both in one single IBOT.

Thanks
Until next time.

Read More