Wednesday, September 28, 2016

Generating custom module in ODI - A Cleaner Approach

Hi Guys,

There is lot of posts on internet on how to create your own custom fact groups and integrate them in load plans. But almost all of those requires you to intervene in ODI Metadata tables which sometimes leads to haphazard.
In this post, I'll give you the alternate approach that Oracle recommends and works well for all of your custom modules. Also, I'll mention all the bottlenecks that I had dealt during this phase.

Load plan generation in BIACM works through a utility called LPG aka load plan generator which ultimately crawls through the entire ODI code for all the checked Fact group and the associated dimension groups and creates a master load plan which is nothing but a amalgamation of many load plans that resides in dev components. System components tell you the ordering in which the selected dev components should pick up. It is not that convoluted as it sounds. We'll go step by step to understand what's happening and how can we use all these to integrate our custom module.

Below are the steps delineating the process of generating a load plan out of custom fact group
  1. Create Fact Group & Dimension Group (through BIACM)
    1. Goto Manage Business Intelligence Applications > Business Intelligence Applications Offerings tab
    2. Check the box against Oracle Custom Analytics > Custom Functional Area for Custom Analytics.
    3. Now under associated dimension and fact groups click on the + sign to add the fact group followed by the dimension group

  2. Now the next step is to create Dev Components (individual LP consisting of scenarios) in ODI
    1. SDE Dimension LP (_DS scenarios)
    2. SDE Fact LP(_FS scenarios)
    3. SIL Dimension LP (_D scenarios)
    4. SIL Fact LP(_F scenarios)
  3. Add dev components to respective system components (remember the ordering based upon the dependency)
  4. Add Fact group & dimension group name(from step 1) to flex-field value of each individual table at module level. It helps LPG in segregating tables based upon the Module
  5. Create foreign keys in fact table to refer the associated dimension.
Below are some of the issue that I have encountered.

Q: DSN not configured for LS. ODI repository not configured properly , please check ALL the logical schemas associated with the chosen PLV
Make sure in your logical schema, below flexfield values matches exactly with the logical schema of source system defined in BIACM 
  • PRODUCT_LINE_VERSION_KEY,
  • DATA_SERVER_TYPE,
  • LS_DATASOURCE_NUM_ID
  • LS_SCHEMA_TYPE
Q: Load plan generation failing with null 
The trick is to atleast add a single foreign key to a dimension that has its scenario attached in respective dev component.

Q: SDE Dimension flows are not coming in generated load plan
Add a foreign key in fact to that dimension which has its sde also.

reference: https://blogs.oracle.com/biapps/entry/load_plan_generator_an_inside
Read More »

Tuesday, August 9, 2016

ODI - Load huge source data in chunks using variable loops

Hi Guys,

In this post we'll walk through an issue I faced during one of the projects where source data (MySQL DB in our case) was so huge that ODI was not able to handle & was giving GC Overhead limit exception.
We took out the source query & filtered the data according to a specific year and ran again. This time since the source query was substantiated to a limit was giving out the appropriate results.
This gave us the idea to develop some kind of loop in ODI that will recursively run the interface/mapping iterating through the years present in source.
This article mainly focuses on developing this loop by using variables in ODI that will refresh and run our interface/mapping iteratively.

First of all, lets focus on different type of variables offered by ODI and how we can leverage them for our development.

Set Variable:  Set Variable step of type Assign sets the current value of a variable. We will use this to initialize our counter variable.

Refreshing Variable: We will extensively use this kind of variable.Refresh Variable step allows you to re-execute the command or query that computes the variable value. We will create counter using this type of variable which will increment itself every time this variable is referenced.

There are other variables offered as well such as evaluate, declare & increment variable but we are not going to discuss them here as they will not be required in our case. You can always google them though.

Now, we'll go through the above diagram in sequential order explaining each of the step in detail.

  • (Requirement) Each time the mapping runs, it should truncate the existing data first.
    • I have created procedure having truncate statement & tied that as the first execution step. Guys please note that there are multiple ways you can handle this but I found this approach a bit easy & quick.
  • X_COUNTER: The 2nd & 3rd object in our diagram is referencing the same variable but is performing different operation in both cases.
    • X_COUNTER when referenced as a set variable in 2nd step is used to initialize the counter to a value of -1
    • Now, in the 3rd step same variable is used as a refresh variable which will increase every time it is referenced. Let me explain the query behind this variable
select #X_COUNTER+1 from dual where #X_COUNTER+1 < (select count(DISTINCT ) from )

In above query, whatever the value of x_counter at that moment will increment by 1. Also please note that we will only increment this variable by the limit of distinct years present in our source table. That is, a single loop will run for a particular year. Likewise, we'll only iterate through all the years. (comment below for any doubts here) X_END_SCEN: This is an empty variable(you can attach mail step instead of empty variable which will notify you through mail once the load get finishes) which is referenced through X_COUNTER when the above query is failed to return any value i.e. X_COUNTER has been iterated through all the years and now the value of X_COUNTER will be greater than the count of distinct years.
  • X_CDW_ACTIVITY_YEAR: This again is are refresh variable which will output the specific year based upon the x_counter value. Below is the query:
select DISTINCT  from  order by 1 limit #X_COUNTER,1

The above query will return a single year based upon our X_COUNTER value since select query will list down all the distinct years in increment order and limit will extract the specific value based on the number(e.g. if x_counter=2 then limit 2,1 will fetch the 2nd value). Also, please note that our source is MySQL and the above query may not work for other db(its just one way of getting value based upon counter, you can use other approach as well)

  • Mapping: After refreshing all the variables, next step will be the main mapping, from where the final source query will generate. We have to just make sure to add the filter in our mapping for the particular column i.e. and assign the variable value to it i.e. X_CDW_ACTIVITY_YEAR in our case. Each time this mapping runs, it will fetch the latest value from the variable i.e will generate the query for that specific year.
Guys, please note we can have many approaches based upon the requirement. In fact, we can have nested loops where the source query is been filtered through multiple variables as given below



Hope you understood the concept. Please feel free to comment for any doubts or suggestions. Happy coding :)
Read More »

Friday, May 6, 2016

Configuring Exalytics Machine for Virtualization

This post describes the tasks to perform when configuring Exalytics Machines for virtualization. The tasks include deploying Oracle Virtual Machine Server (Oracle VM Server) on the Exalytics Machine and configuring Oracle VM Server with Oracle Virtual Machine Manager (Oracle VM Manager)

Read More »