19 Ekim 2017 Perşembe

ODI 12c Knowledge Module - Using unmapped source columns

Hi

In this entry  I am going to show how  to  select an unmapped column  and insert into our target table.This method  mostly useful when you need to developed your custom query
( such as hierarchical or Subquery refactoring )

Step1:
I prepared following interface which maps country information.










Target mappings:























As we can see above , i did not map swift_code column and set its parameter to UD1



Step2:
Now i am taking standart IKM-SQL Control Append KM and modify its "insert new rows" section
 as follows


insert into     <%=odiRef.getTable("L","TARG_NAME","A")%>
(
        <%=odiRef.getTargetColList("", "[COL_NAME]", ", ", "", "UD1")%>
)

<%for (int i=odiRef.getDataSetMin(); i <= odiRef.getDataSetMax(); i++){%>
<%=odiRef.getDataSet(i, "Operator")%>
select  <%=odiRef.getPop("DISTINCT_ROWS")%>
        <%=odiRef.getSrcColList(i,"","[COL_NAME]","--",",\n","")%>
from    <%=odiRef.getFrom(i)%>
where   <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>
        JRN_FLAG <> 'D'
        <%} else {%>
        (1=1)
        <% } %>
<%=odiRef.getJoin(i)%>
<%=odiRef.getFilter(i)%>
<%=odiRef.getJrnFilter(i)%>
<%=odiRef.getGrpBy(i)%>
<%=odiRef.getHaving(i)%>
<%}%>


and after execution my final query














odiRef.getSrcColList    (Source : Oracle.com )

Description

This method available in LKMs and IKMs, returns properties for a list of columns in a given dataset. This list includes all the columns of the sources processed by the LKM (from the source) or the IKM (from the staging area). The list is sorted by the column position in the source tables.

Parameters

pDSIndex
Index identifying which of the datasets is taken into account by this command.
pStart         
This sequence marks the beginning of the string to generate.
pUnMappedPattern
The pattern is repeated for each occurrence in the list if the column is not mapped.
pMappedPattern
The pattern is repeated for each occurrence in the list, if the column is mapped.
pSeparator
This parameter separates patterns.

pEnd
This sequence marks the end of the string to generate.


10 Nisan 2017 Pazartesi

PL/SQL - Boyer Moore algorithm


Hi
  In computer science, the Boyer–Moore string search algorithm is an efficient string searching algorithm that is the standard benchmark for practical string search literature.It was developed by Robert S. Boyer and J Strother Moore in 1977.The algorithm preprocesses the string being searched for (the pattern), but not the string being searched in (the text).

The key features of the algorithm are to match on the tail of the pattern rather than the head, and to skip along the text in jumps of multiple characters rather than searching every single character in the text. (Source:Wikipedia )


Following code is an implementation of Boyer-Moore uses PL/SQL

CODE:






















TEST:
PATTERN:='abacbb';
TEXT:='abacaxbaccabacbbaabb';-- FOUND

PATTERN:='74879';
TEXT:='1256564114132775648574879577437273127615761516541189456588322';
--FOUND

PATTERN:='cite';
TEXT:='You have got a funny face, man';  --NOT FOUND

PATTERN:='banana';
TEXT:='I wish I had_an apple instead of'; --NOT FOUND

PATTERN:='74879';
TEXT:='125656411413277564857487 9577437273127615761516541189456588322';
-- NOT FOUND

PATTERN:='LEAD';
TEXT:='JIMY_HAILED_THE_LEADER_TO_STOP';--FOUND

6 Nisan 2017 Perşembe

CASE/WHEN-changing rows to columns


Hi ,
In this entry i am gonna show  how to change row to colums using case when statement.

At first lets say we have a following table

CREATE TABLE  TEST_1
(
FACTORY_NAME VARCHAR(100 CHAR),
SALES NUMBER(20),
PRODUCTION NUMBER(20)
)

Current View:
















What i want :





Step1:
creating a dummy table
      (
      SELECT
      'TOTAL_SALES'  AS  TEXT FROM DUAL
      )
      UNION
      (
      SELECT
      'TOTAL_PRODUCTION'  AS  TEXT FROM DUAL
      )









Step2:
Combining it with my main table  (no join needed )

SELECT
*
FROM
(
      (
      SELECT
      'TOTAL_SALES'  AS  TEXT FROM DUAL
      )
      UNION
      (
      SELECT
      'TOTAL_PRODUCTION'  AS  TEXT FROM DUAL
      )
),
(
   SELECT
   FACTORY_NAME ,
   SALES AS TOTAL_SALES,
   PRODUCTION  AS TOTAL_PRODUCTION
   FROM
   TEST_1
)









Step 3:
Adding Case when structure to prepare main view

SELECT
    TEXT,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_1'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_1'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_1,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_2'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_2'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_2,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_3'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_3'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_3
FROM
(
      (
      SELECT
      'TOTAL_SALES'  AS  TEXT FROM DUAL
      )
      UNION
      (
      SELECT
      'TOTAL_PRODUCTION'  AS  TEXT FROM DUAL
      )
),
(
   SELECT
   FACTORY_NAME ,
   SALES AS TOTAL_SALES,
   PRODUCTION  AS TOTAL_PRODUCTION
   FROM
   TEST_1
)





,






Step 4:
Getting rid of null values. ( can be done with filtering too )

SELECT
TEXT , SUM(FACTORY_1) FACTORY_1 ,SUM(FACTORY_2) FACTORY_2 ,SUM(FACTORY_3) FACTORY_3
FROM
(

SELECT
    TEXT,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_1'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_1'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_1,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_2'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_2'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_2,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_3'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_3'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_3
FROM
(
      (
      SELECT
      'TOTAL_SALES'  AS  TEXT FROM DUAL
      )
      UNION
      (
      SELECT
      'TOTAL_PRODUCTION'  AS  TEXT FROM DUAL
      )
),
(
   SELECT
   FACTORY_NAME ,
   SALES AS TOTAL_SALES,
   PRODUCTION  AS TOTAL_PRODUCTION
   FROM
   TEST_1
)

)
GROUP BY TEXT
ORDER BY TEXT DESC




Final View:













29 Ocak 2017 Pazar

Tableau analytic functions

Hi

In this entry I will explain how to implement analytic functions  over Tableau.


Step 1)
I choose EU_Superstore/Orders as data source











Step2)
Going to sheet1  and adding my dimension ( Country , State, City )


















Step3)
Now the values I want to implement

SUM(Sales) Over ( Partition by Country )
SUM(Sales) Over ( Partition by Country,State )
SUM(Sales) Over ( Partition by Country,State,City )

To do that first I am adding my measure  CALC_1  ( Formula  : SUM([Sales])  )
into my measure section.


















By using my CALC_1 variable  I am gonna create a new calculation and define its partition






















Step4)
Dragging new measure into report  section  ( showing country total )























Step5)
Now using  CALC_1 , I am gonna create two more  variables as same as the first one but their partition types will be different.

For  SUM(Sales) Over ( Partition by Country,State )





















For SUM(Sales) Over ( Partition by Country,State,City )





















Final result will be look like this:




30 Mayıs 2016 Pazartesi

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

4 Mayıs 2016 Çarşamba

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





5 Nisan 2016 Salı

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