30 Mayıs 2016 Pazartesi

Import location for ODI 12c knowledge modules


ODI 12c  KMs  can be imported  from the directory below



4 Mayıs 2016 Çarşamba

ODI 12c Split & Aggregate


In this entry , i will show how to use split and aggregate objects of ODI 12c.

* Source Table: data_1

What i am gonna do is  implementing a multi insert mapping  which loads data into different tables.

Target tables:
data_3 ( it only holds data whose group_name  is  'GROUP1'  )
data_other ( it only holds data whose group_name  is not 'GROUP1'  )

Step1: Adding tables and split object

Step2 : Setting up split object properties

Step3: Adding aggregate object  for data_3 table

Final View:

Physical Diagram:


data_3 table:

data_other table

5 Nisan 2016 Salı

ODI 12c - creating MySQL master repository


In this entry i will show how to create a master repository over MySQL db.

Version :  MySQL Server 5.6

At first , logging into Master repository creation wizard and i have following menu

I am gonna create my master repository over  test database so

JDBC URL :    jdbc:mysql://

*If you are using MySQL (release 5.5.14 or higher) to setup a repository, and the database charset is set to utf8, you will need to add the following parameters to the repository's jdbc URL to operate non-ASCII data correctly in Oracle Data Integrator Studio
( Source : http://docs.oracle.com/cd/E28280_01/install.1111/e16453/manual.htm#ODING492  )

JDBC URL :   jdbc:mysql://

Registry Database Name : test   ( Db  i am gonna use for master )

User:    Any user you create with admin privileges over MySQL
( root user cant work here bcoz ODI makes it uppercase automatically so it raise a connection error )

DBA User :  You can use root user  in here or the user you create above.

Result  will be look like this :

ODI 12c might raise warning for some of the parameters in MySQL.In order to avoid this ,you can set following parameters in your my.ini file (located under your mysql  installation folder) .

collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

1 Nisan 2016 Cuma

ODI 12c - Implementing For Loops


in  previous versions of ODI   i had a chance to create  "for loops"  in packages , so now i am gonna explain how we can implement a for loop in ODI 12c universe.

First i am gonna reverse a table that contains branch information.

Table structure as follows:

What i want to do is  to get all data except for the one with maximum ORDER_NUM ( Normally we can do this by using bulk SQL but this time i am gonna create  a for loop and making  row by row insertion.  )

As usual i need variable objects  to implement a standart for loop.

Variable:MAX_COUNT ( holds total number of rows in our source table )

Variable: ORDER_ID_VAR ( set to 0 )

and now I am gonna create an interface that let me transfer data one by one into my target table.Inside the mapping , filter object help me to associate interface with my variables.

Lastly i will create a package to implement my for loop.As you can see below , at first  i am refreshing my variables to assign initial values then ORDER_ID_VAR_INC object ( set variable ) is used for incrementing ORDER_ID_VAR.

ORDER_ID_CHECK_1 (Evaluate variable)  is used for checking whether ORDER_ID_VAR less than MAX_COUNT or not. If it is true , our interface will be called for execution otherwise beep object will be started to finish execution.

target table will be look like this

and operator log for execution

9 Mart 2016 Çarşamba

Qlikview static maps reporting


While trying google map over qlikview  , i faced  with a web-view error ( i guess because of IE10 or something like that) so  i decided to explain how can we  show  our data  over static mapping reports in qlikview.

At first I am gonna prepare my data over an excel file:

And now come back to Qlikview i add following script to read data in excel

After loading our script we have following fields:

Now we gonna choose an image for our scatter chart.In below  I can see the coordinates of countries.By using  that info  i am gonna create my temp data for country coordinates.

And now i add my temp data into script section.

After loading script  , i am adding my scatter chart into my sheet , and specify settings as follows.

1) Choose  Scatter Chart

2)Dimension tab I am adding COUNTRY field.

3) For expression tab i am adding  X , Y and SUM(GDP)  as follows
(In my map I just wanna see SUM(GDP) value as popup so i checked the "Text as Popup" for SUM(GDP)

4) Now for presenting my chart i disable Show X-Y label in order to get a better map view.

5)Axis tab  should be set as follows. (Scale values has to be specified otherwise data cannot pin the correct spot on the map )

6) Last part  we gonna set image for our chart in Colors tab.
(Color-->Frame Backgroud--> Image--> ( Set File ) & Check Plot Area Only

7)Final screen would be look like this

23 Şubat 2016 Salı

tableau - TOP N filter over BarChart


In this entry i will show how to implement a "top n filter" object over our reporting sheet.

First of all , here below we have following chart which contains sum(profit) over region and country for each person.

Now what i want to do is to show Top 2 profits  for each person.

In data panel  creating  a "Calculated Field" and use following formula.

Step 2
Dragging  created object (TOP-N filter) into Filters part.

Step 3
Right click onto TOP-N filter  click "edit table calculation"  and
choose  Compute Using --> Advanced

Step 4
Advance section we gonna select partition and field values in order to define our ranking.

Finally Top-n filter is ready.
Now i am gonna define the range which is top 2 values and Results will be look like this...

21 Şubat 2016 Pazar

tableau Date Formating


In this entry i will show how to format date in tableau reporting screen.

At first I choose excel as my datasource  and  use Orders (Sample - EU Superstore) and I want to see people who have made successful orders and details about it.

In here we have  a column called  ShipDate  and now i want to create a variable  that only shows me YEAR.MONTH information for  aggregations.

Formula for calculated Field:


22 Ocak 2016 Cuma

Oracle Finding first day of the month


Following sql helps you to find first day of previous months.

select  trunc(sysdate) from dual


First Day of Current Month:
select    ADD_MONTHS (   LAST_DAY (  TRUNC (SYSDATE)  )  ,   -1  )+1   from dual


First Day of  eleven months before:                                      
select    ADD_MONTHS (   LAST_DAY (  TRUNC (SYSDATE)  )  ,   -11  )+1   from dual


21 Ocak 2016 Perşembe

Oracle Finding Last_Day of Previous months


Following sql helps you to get previous months  last_day values:

Current Date:
select  trunc(sysdate) from dual

-- One month before
select   ADD_MONTHS (LAST_DAY (TRUNC (SYSDATE)),-1)   from dual

-- Eleven months before
select   ADD_MONTHS (LAST_DAY (TRUNC (SYSDATE)),-11)   from dual