20140710

Creating Java method on Oracle database using Toad

 Creating java function on toad


CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED ODI."Hello_return" as 
public class hello_return{
public static String world(String name) {
return "hello world " + name;
}
};
/


CREATE OR REPLACE function Hello_return(name VARCHAR2) RETURN VARCHAR2
as LANGUAGE JAVA NAME 'hello_return.world(java.lang.String) return String';
/





20131020

Indexes



Indexes must be created according to below conditions

  1. WHERE statement, also ORDER BY, GROUP BY statements can be considered.
  2. On LARGE tables
  3. Nonvolatile data; not frequently updated
  4. 2- 4% data selectivity 
  5. On Primary Key

Bitmap Indexes

  1. Low cardinality; distinct value of the data must be low like gender M, F
  2. Huge tables
  3. Nonvolatile column
  4. Suitable for Data wwarehouses not OLTP
  5. High performance to get, counts and sums and OR operations
  6. Smaller than b-tree indexes
  7. Keeps NULL values on the contrary of other index types

Order of difficulty for rebuilding indexes
  • Update( Delete and then insert operations )
  • Delete
  • Insert


Block Anchor: The first record in each block of the data file is called the anchor record of the block, or simply the block anchor.

Types of Single Level Ordered Indexes
(Binary search)

  • Primary Index (Indexing  field is key)
  • Clustering Index ( Indexing dept_id  on employee table, dept_id is not key)
  • Secondary Index (Can have both Unique or Duplicate Values)

Multilevel Indexes (B-tree)

  • Reduces the number of blocks accessed

The first-level =PK
Second level =Index of The first-level



Source: Fundamentals of Database Systems; R. Elmasri, S.B. Navathe
Database Design Management&Adm. Notes


20130623

ODI Package Schedule From DB Table




You can manage more than one similar ODI scenario executions out of ODI. Name packages by a standart in ODI. Sort packages in a database table and set serial or parallel execution in a column and flag scenarios to execute.

CREATE TABLE PACKAGE_SCHEDULE
(
  SCENARIO_NAME  VARCHAR2(20 CHAR),--Senaryo ismi
  OK             NUMBER, --Çalisacak senaryo; 1/0
  SERIAL_CODE    NUMBER,--Seri çalisan için sirano
  PARALLEL_CODE  NUMBER  --Paralel çalisacak senaryo 1/0
);


Package_Schedule data

SCENARIO_NAME OK SERIAL_CODE PARALLEL_CODE

PCK_001 1 1 1
PCK_002 1 1 2
PCK_003 1 2 0
PCK_004 1 3 1
PCK_005 1 3 2
PCK_006 1 3 3
PCK_007 1 4 0



Materialized view selects scenarios with OK=1 and ODI folders.


CREATE MATERIALIZED VIEW MV_PACKAGE_SCHEDULE
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT ROW_NUMBER () OVER (PARTITION BY 1 ORDER BY A.SERIAL_CODE,A.PARALLEL_CODE) ROW_ID,
A.SCENARIO_NAME,  SERIAL_CODE, A.PARALLEL_CODE,FOLDER.I_FOLDER
  FROM PACKAGE_SCHEDULE A,SNP_SCEN SCEN,SNP_PACKAGE PACKAGE,SNP_FOLDER FOLDER
 WHERE A.OK = 1
   AND A.SCENARIO_NAME IS NOT NULL
   AND A.SCENARIO_NAME = SCEN.SCEN_NAME
   AND SCEN.I_PACKAGE = PACKAGE.I_PACKAGE
   AND PACKAGE.I_FOLDER = FOLDER.I_FOLDER;



MV provides current scenarios and row number to execute and last folders with refresh.


ODI Variable: VAR_PKG_SCH_ROW_ID

SELECT MIN(ROW_ID) FROM MV_PACKAGE_SCHEDULE



MV is refreshed in the beginning of the package by  a ODI Procedure.

ODI Procedure: refresh MATERIALIZED VIEW

BEGIN
dbms_mview.refresh( list => <%=odiRef.getOption( "MV_NAME" )%> );
 END;

Refreshing variables.

ODI Variable: VAR_PKG_SCH_SCENARIO_NAME

SELECT SCENARIO_NAME FROM MV_PACKAGE_SCHEDULE
WHERE ROW_ID=#VAR_PKG_SCH_ROW_ID

ODI Variable: VAR_PKG_SCH_MAX_ROW_ID

SELECT MAX(ROW_ID) FROM MV_PACKAGE_SCHEDULE


ODI Variable: VAR_PKG_SCH_SERIAL

SELECT SERIAL_CODE FROM MV_PACKAGE_SCHEDULE
WHERE ROW_ID =#VAR_PKG_SCH_ROW_ID

ODI Variable: VAR_PKG_SCH_PARALLEL

SELECT PARALLEL_CODE FROM MV_PACKAGE_SCHEDULE
WHERE ROW_ID =#VAR_PKG_SCH_ROW_ID


ODI Variable: VAR_PKG_SCH_MAX_SERIAL_ROW_ID

SELECT MAX(ROW_ID) FROM MV_PACKAGE_SCHEDULE
WHERE SERIAL_CODE=#VAR_PKG_SCH_SERIAL

Give parallel or serial execution by VAR_PKG_SCH_PARALLEL.



Serial scenarios go to SERIAL step and OdiStartScen call scenario name with  #VAR_PKG_SCH_SCENARIO_NAME  variable.



Parallel scenarios go to PARALLEL named subpackage. First Step is set to Refresh #VAR_PKG_SCH_SCENARIO_NAME. Scenarios  go to OdiStartScen object and call scenarios name by  #VAR_PKG_SCH_SCENARIO_NAME variable and run as Asynchronous.



#VAR_PKG_SCH_MAX_SERIAL_ROW_ID variable decides which scenarios to executes parallel in PARALLEL package and after parallel scenarios executed, OdiWaitForChildSession "-POLL_INT=1" "-MAX_CHILD_ERROR=ALL"  waits for all scenarios to finish.




After parallel scenarios finished and return to SERIAL package, VAR_PKG_SCH_ROW_ID  variable is set to VAR_PKG_SCH_MAX_SERIAL_ROW_ID variable. Variable value from subpackage is carried to main package.
If row num is smaller or equal to  VAR_PKG_SCH_MAX_ROW_ID variable, then row_num is incremented by 1, if row num is bigger, then package end with OdiWaitForChildSession "-POLL_INT=1" "-MAX_CHILD_ERROR=ALL"  to wait all sub sessions to end.




When main package is executed, firstly PARALLEL package runs and calls 001, 002 in parallel and 003 in serial, then again PARALLEL package runs and calls 004, 005, 006 and lastly 007 runs. You can see 3 package is executed in parallel in last PARALLEL package.


New order for package schedule:

SCENARIO_NAME OK SERIAL_CODE PARALLEL_CODE

PCK_001 1 1 0
PCK_002 1 2 1
PCK_003 1 2 2
PCK_004 1 3 0
PCK_005 1 4 1
PCK_006 1 4 2
PCK_007 1 5 0



Package order and parallel/serial execution is changed, first 001 runs in serial, then 002, 003 runs in parallel and then 004 runs in serial again. 005, 006 runs in parallel, you can see from PARALLEL and finally 007 runs and package ends.

Paralel ve seri yi değiştirerek tekrar çalıştırdığımızda ilk olarak seri 001 paketi giriyor, sonrasında 002, 003 paketleri ve tekrar seri olarak 004 paketi, resimde görülen PARALLEL paketinde çalışan 2 paket 005 ve 006 ve son olarak 007 paketi çalışıp paket sonlanıyor.




20130525

ODI - Listing Target tables of A Package




SELECT DISTINCT POP.TABLE_NAME
FROM  SNP_PACKAGE PAC--Packages
, SNP_STEP STEP --Package steps
, SNP_POP POP--Interfaces
WHERE PAC.PACK_NAME =:V_PACK_NAME
AND PAC.I_PACKAGE=STEP.I_PACKAGE
AND STEP.I_POP=POP.I_POP
ORDER BY 1 ASC

20130413

ODI Regenerate Folder

You can regenerate scenarios under the folder you wanted with variables.

You don't need to give folder path, you can get folder number from ODI repository and regenerate/generate scenarios with OdiGenerateAllScen object.


You can use the sql below gives folder number and scenario relations for advance.

SELECT SCEN_NAME, FOLDER.I_FOLDER
FROM SNP_SCEN SCEN,  SNP_PACKAGE PACKAGE, SNP_FOLDER FOLDER
WHERE SCEN.I_PACKAGE=PACKAGE.I_PACKAGE
AND PACKAGE.I_FOLDER=FOLDER.I_FOLDER


I have build a MV to store which scenarios to execute and their folder numbers. And variable's sql is like this: 


VAR_PKG_SCH_FOLDER:

SELECT I_FOLDER FROM TGARYSR.MV_TDM_PACKAGE_SCHEDULE
WHERE ROW_ID=#VAR_PKG_SCH_ROW_ID

ODI Folder Hierarchy

You can list main and under folders in ODI with this sql.



SELECT I_FOLDER, FOLDER_NAME, PAR_I_FOLDER,CONNECT_BY_ISCYCLE  CYCLE, LEVEL,  SYS_CONNECT_BY_PATH(FOLDER_NAME, '/') PATH
   FROM SNP_FOLDER
     WHERE level <= 9
   START WITH  PAR_I_FOLDER IS NULL-- MAIN_FOLDER
   CONNECT BY NOCYCLE PRIOR I_FOLDER=PAR_I_FOLDER 
   AND LEVEL <= 10;

20130307

Searching DB functions and procedures in ODI Repository

You can find database functions and procedures  where used in ODI with repository tables as contains executed sessions' information.

select *  from SNP_SESS_TASK b
where def_txt like '%<DB_SCHEMA.FUNCTION_NAME>%'

select *  from SNP_SESS_TASK b
where def_txt like '%<DB_SCHEMA.PROCEDURE_NAME>%'

20130302

ODI KM Adding Order by Option

You can add Order by statement to queries by editing KM.I have edited IKM SQL Control Append to provide Order by. 

1) Add an option to KM named USE_ORDER_BY, its type is Checkbox and default value is False.
This option determines you want an order by statement at your query.


2)Add second option to KM named ORDER_BY, type is Text. You will get order by values to your query by this option.



3) Editing Insert New Rows detail of KM. Adding below three line code after having clause. That's it!


<% if (odiRef.getOption("USE_ORDER_BY").equals("1")) { %>
ORDER BY <%=odiRef.getOption("ORDER_BY")%>
<%} %>





 If USE_ORDER_BY option is not used, empty value of ORDER_BY option get error.

And executions of KM appears as such below;

At this execution, I checked the KM to not get errors if ORDER_BY option value is null.


There is no prove of ORDER BY I'm glad.

 Second execution to get  Order by clause.
Tadaaaa! :)

Note:Editing Knowledge Modules at ODI is developer friendly and ease of use, but sometimes leaving the KM at its own preferable as risk of new versions ODI are supporting the edits at future.

20130214

Dimensional Modelling Tips


1) Surrogate key; certain need for SCD Type 2

Construct SCD Type 2 dimensions both with Natural keys and changing attributes.And use surrogate keys.










Production Employee Table










Emp_noEmp_nameStart_dateEnd_dateDepartmentManager


1Smith01/01/10
IT0


2Tiger01/01/10
Sales0







*Updated as 1



















DWH Employee Dimension










Surrogate_keyEmp_noEmp_nameStart_dateEnd_dateDepartmentManager

11Smith01/01/10
IT0

22Tiger01/01/1001/01/13Sales0

32Tiger01/01/13
Sales1












The example above shows emp_no=2 is updated for manager=1, we want to track this change at DWH. If you use natural key as PK of dimension, you will need to add manager, start_date, end_date attributes to fact table to be ensure uniqueness.

If you just use natural key at fact table, you cannot track change of this record. Employee could change the department, in this point cost center calculations will be wrong. With use of surrogate key at SCD Type 2, you will not need to use dates and secondary attributes at fact table.

Using dates of OLTP systems can cause wrong results, as employee's end_date is null, end_date doesn't answer the change we want at DWH. Surrogate key contains department and manager flag, is the right answer.
2)Alternate Reality

SCD Type 3 provides past and current status at the same record, it called alternate reality. In the example, you can see employees' first and last departments at the same record. This method will be efficient while querying customer's first and last segment defined in large boundaries at one record.


Production Employee Table












Emp_noEmp_nameStart_dateEnd_dateDeptManager



1Smith01/01/10
IT0*Upd as SAP


2Tiger01/01/10
Sales1

































DWH Employee Dimension












Surrogate_keyEmp_noEmp_nameStart_dateEnd_dateFirst_DeptCurrent_DeptManager

11Smith01/01/1001/02/13ITIT0

22Tiger01/01/1001/01/13SalesSales0

32Tiger01/01/10
SalesSales1

41Smith01/01/10
ITSAP0










3)Role Playing with Dimensions
Dimension role playing is one dimension joins fact table more than one role.

Fct_product_order_sales table contains sales and order date. Dim_time table joins the fact table with views as different dimensions from sales_date and order_date.



4) Junk Dimensions

Optimal dimension table number for datawarehouses recommended is between 5-15. Dimension for every flag in datawarehouse increases the number of dimensions, but it is necessary for readibility. Collecting theese flags into one dimension or related flags into some dimensions can reduce number of dimensions. Also this solution makes easy to support and maintain the datawarehouse.








customer_flag_idcountrysexsales_first

1domesticfemalenew

2domesticfemaleold

3domesticmalenew

4domesticmaleold

5domesticfemalenew

6domesticfemaleold

7domesticmalenew

8domesticmaleold

9abroadfemalenew

10abroadfemaleold

11abroadmalenew

12abroadmaleold








5)Large Changing Dimensions(Demographics)

Following changes in big, "rapidly changing monster dimensions" like Customer with SCD Type 2-3 is difficult to build, maintain and support. Specifying frequently changing attributes and building a separate dimension for these columns is recommended.

Instead of keeping age, income and city in customer dimension, define boundaries and build a different dimension like shown below.







Demographic KeyAgeCountryIncome(TL)

120-25TR1000-3000

225-30TR3000-5000

325-30Other-






In the fact table, you can track changes in customer with customer key and demographic key.

6)Aggregate Tables

To increase query performance, most simple and cheap solution is creating aggregate fact tables. Daily transactions can rolled up to monthly numbers in aggregate tables. Users usually queries the aggregates, if they are convinced that datawarehouse is providing true results while development and test phases. Query numbers and mostly queried fact tables can be monitored and aggregations can be re-arranged.

7) Factless Fact Tables

Factless fact tables have no measures and contains just relations of dimension keys. You can get number of changes of dimension keys in certain time intervals. These calculations can be get in reporting area otherwise too complex and no need to keeps these repeating measures in fact tables.

You can get number of registrations and attendances to an activity with count and count distinct from a fact table shown below.

Fact_activity
time_key
attendant_key
place_key
activity_status_key
attendant_status_key
Select time_key, activity_key, count(attendant_key) #Registrationscount(case when attendant_status_key='A' then attendant_key end) #Attendances From Fact_activityGroup by time_key, activity_key

Source: 
The Data Warehouse Toolkit - Ralph Kimball, Margy Ross

20130116

OdiStartScen with Variable


It is possible to execute scenarios with OdiStartScen using variables as package names goes with naming convention as below. You don't need to use package_names one by one.


Table named T_PACKAGE holds pkg_no and pkg_name that we will use for loop and variables.

pkg_no pkg_name  
1 Pkg_001
2 Pkg_002
3 Pkg_003
4 Pkg_004
5 Pkg_005
6 Pkg_006
7 Pkg_007
8 Pkg_008
9 Pkg_009
10 Pkg_010
11 Pkg_011
12 Pkg_012



ODI_VAR_PACKAGE_NO, Value: Default 1 Type:Number ODI variable is created.
ODI_VAR_PACKAGE_NAME, Type:Text ODI variable that gets package name according to pkg_no from the table.


SELECT 'PKG_'||PKG_NAME from T_PACKAGE
WHERE PKG_NO=#ODI_VAR_PACKAGE_NO


ODI_VAR_MAX_PACKAGE_NO, Type:Number ODI variable which gets max pkg_no to know where to end loop.

SELECT MAX(PKG_NO) FROM T_PACKAGE

ODI Variable values cheched with show_parameter procedure during main package execution.
Language: Jython,  Option:VAR




raise  '  \n THE VALUE OF THE VARIABLE <%=odiRef.getOption("VAR")%>  IS  –  <%=odiRef.getOption("VAR")%> '


Enter #ODI_VAR_PACKAGE_NAME  for Scenario name in ODIStartScen properties. If you need, you can also get Scenario Version from variables.


OdiStartScen "-SCEN_NAME=#ODI_VAR_PACKAGE_NAME" "-SCEN_VERSION=001" "-SYNC_MODE=1"

 If you had problem with variable take value, add project before variable:
#PROJECT_NAME.ODI_VAR_PACKAGE_NAME




In the Evaluate step, we  check #ODI_VAR_PACKAGE_NO <> ODI_VAR_MAX_PACKAGE_NO, if so then go to next step, Increment #ODI_VAR_PACKAGE_NO and increment variable +1.



After refreshing variables, you can continue from ODIStartScen step and then evaluate variables, as such you can execute scenarios with loop.

20121012

ODI Object Dependencies




·         Under Models and below datastore

1.     Populated By contains source tables.



2.      Used to Populate contains related table’s source tables.



1.     Used In
¨       As a Source contains interfaces that related table is source in.



¨       As a Target contains interfaces that related table is target in.



¨       In a Package contains packages that related table is source in interfaces and that interfaces used in.

·         Interface

1.     Used In contains packages that related interface is used.
2.     Used By contains load plans that related interface’s scenarios used in.



·         Variables  
1.     Used In contains packages and package steps that variable used in.



2.     Used By contains load plans that variable is used.



·         Used in under Procedure, package, function, sequence, knowledge module  contains load plans that related object is used.



Source and target tables of procedures and variables can be populated from odi repository tables:

SELECT  *
  FROM  snp_sess_task
 WHERE      1 = 1
        AND DEF_TXT LIKE '%COMN_TDIM_CUSTOMER_DESC%'
        AND TASK_NAME1 = 'Procedure'


 For oracle object dependencies see: http://www.harunkucuk.com/?p=310