Import location for ODI 12c knowledge modules

Hi

ODI 12c  KMs  can be imported  from the directory below

Installation_Directory\ODI\odi\sdk\xml-reference

Example:
C:\ORACLE\ODI\odi\sdk\xml-reference

ODI 12c Split & Aggregate

Hi

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:

Results:

data_3 table:

data_other table

ODI 12c - creating MySQL master repository

Hi

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://127.0.0.1:3306/test

*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://127.0.0.1:3306/test?useUnicode=yes&characterEncoding=utf8


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) .

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
innodb_file_format=Barracuda
innodb_large_prefix=1
log_bin_trust_function_creators=1

ODI 12c - Implementing For Loops

Hi

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

Qlikview static maps reporting

Hi

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

tableau - TOP N filter over BarChart

Hi

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.


Step1
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...

tableau Date Formating

Hi

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:

Result:

Oracle Finding first day of the month

Hi

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


select  trunc(sysdate) from dual

22.01.2016

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

01.01.2016                                                                    

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

01.03.2015

Oracle Finding Last_Day of Previous months

Hi

Following sql helps you to get previous months  last_day values:

Current Date:
select  trunc(sysdate) from dual

22.01.2016

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

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