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

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.

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

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:

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: