Sql Real time scenarios interview based

• Inventory project domain i had one scenario.The below business requirement
• This is Realtime project scenario.

Tables1

Item_number	Revision	Booking status
Jabanggh06	432	conformed
Jabanggh07	433	Pending
Jabanggh08	434	Not available
Jabanggh09	435	process

Table 2:

Products	category
T-shirts	Men
Shoes	Men
wathes	Men

Business Reuirement:

Item_number	Products
Jabanggh06	T-shirts
Jabanggh07	T-shirts
Jabanggh08	T-shirts
Jabanggh09	T-shirts
Jabanggh06	Shoes
Jabanggh07	Shoes
Jabanggh08	Shoes
Jabanggh09	Shoes
Jabanggh06	wathes
Jabanggh07	wathes
Jabanggh08	wathes
Jabanggh09	wathes

Sql query:

select item_number,PRODUCTS from

((select item_number from Table1)

cross Join

(select PRODUCTS from table2));

SCD Type 1 Implementation using Informatica PowerCenter

• Real time example :
• Slowly Changing Dimension Type 1 do not preserve any history versions of data. This methodology overwrites old data with new data, and therefore stores only the most current information. In this article lets discuss the step by step implementation of SCD Type 1 using Informatica PowerCenter

• The number of records we store in SCD Type 1 do not increase exponentially as this methodology overwrites old data with new data  

Step1:create the source and target definitions show the below

Target table (Business reuqirment)

Step:2  From particular repository  to source and target drag into desinger show the below                             

     

Step 3:  create the one expression transformation connected to all ports from the source definition 

Step 4:

  create the one Lookup T/R and looking into target table TGT_CUST, and create the one port IN_CUST_ID,this  port keep into input and output both and also comming from expression t/r 

Set the properties 

Step 5:

1. once match the condtion in lookup transformation ,the return port is CUST_ID column ,this column connect to expression transformation.
2. Before create the Update strategy  transformation and drag the all ports from expression transformation should be except the CUST_ID column.and this column drag the from lookup t/r.
3. In update strategy transformation put the condtion in properties tab like.

Step 6: Keep into connected all ports from the update strategy t/r in to target.

With out connected lookup transformation how to implements scd type-2 informatica

SCD Type 2 a new record with the new attributes is added to the dimension table. Historical fact table rows continue to reference the old dimension key with the old roll-up attribute; going forward, the fact table rows will reference the new surrogate key with the new roll-up thereby perfectly partitioning history. 

   Source table name :Customer_Test

Target Table Name:CUSTOMERS_DIM_TEST

                                                       Mapping over view 

Step 1:

          First defined the soure and target definitions in Desinger.

Step2:

create the one unconnected lookup ,It is lookup the target table and create the one input port like 

 this CUSTOMER_ID_in

1. Go the lookup properties set the Lookup policy on multiple match=Use any value
2. Lokup condition CUSTOMER_ID = CUSTOMER_ID_in
3. Lookup sql override :
5. SELECT TO_CHAR(CUSTOMERS_DIM_TEST.CUST_KEY ||','||CUSTOMERS_DIM_TEST.LOCATION) as CUST_KEY, CUSTOMERS_DIM_TEST.CUSTOMER_ID as CUSTOMER_ID FROM CUSTOMERS_DIM_TEST WHERE   CUSTOMERS_DIM_TEST.END_DATE IS NULL

Step 3:

create the expression_1  transformation and drag to  all ports from source qualifer, 

And create the one ouput port LKP_VALUE

:LKP.lkp_CUSTOMER_ID(CUSTOMER_ID)

Again create the three output ports names:

LKP_VALUE_O=LKP_VALUE

lkp_CUST_KEY=SUBSTR(LKP_VALUE,1,INSTR(LKP_VALUE,',',1,1)-1)

lkp_CUST_LOCATION=SUBSTR(LKP_VALUE,INSTR(LKP_VALUE,',',1,1)+1)

Step4:

Again create the expression transformation ,the expression transformation name exp_CUSTOMERS_DIM_TEST,

1. create the 4 outputs 
2. INSERT_FLG=IIF(ISNULL(lkp_CUST_KEY),'Y','N')
3. UPDATE_FLG=IIF(NOT ISNULL(lkp_CUST_KEY) ANDsrc_LOCATION<>lkp_CUST_LOCATION,'Y','N')
4. Begin_Date=sysdate
5. End_Date=sysdate

Step5:-

create the one route transformation ,the router transformation name rtr_CUSTOMERS_DIM_TEST

* Drag the from all the second expression t/r  all ports  to router transformation 

• create the two groups like 
• INSERT=INSERT_FLG='Y'
• Update =UPDATE_FLG='Y'

Step 6:-

• create the two update stratgey t/r
• one for update and another one for update 

Step 7:-

create the squence t/r  and two out port like NEXT_VAL,CURRVAL,AND connect  to the 

from sql t/r nextval port  to taget_insert instance connect.

From update _update T/r  to connect target_update insteces,

Note:-Keep the target key column  primary key ,if not there in database  asking to DBA team or you can put the informatica level primary key column.

Thanks You Folks 

please let me know you have any update 

Gmail:informaticafolks@gmail.com

Facebook: dwbi folks

Blog:http://dwbifolkstalk.blogspot.in/

use unconnected lookup transformation in informatica level scd type-2

SCD Type 2 a new record with the new attributes is added to the dimension table. Historical fact table rows continue to reference the old dimension key with the old roll-up attribute; going forward, the fact table rows will reference the new surrogate key with the new roll-up thereby perfectly partitioning history. 

   Source table name :Customer_Test

Target Table Name:CUSTOMERS_DIM_TEST

                                                       Mapping over view 

Step 1:

          First defined the soure and target definitions in Desinger.

Step2:

create the one unconnected lookup ,It is lookup the target table and create the one input port like 

 this CUSTOMER_ID_in

1. Go the lookup properties set the Lookup policy on multiple match=Use any value
2. Lokup condition CUSTOMER_ID = CUSTOMER_ID_in
3. Lookup sql override :
5. SELECT TO_CHAR(CUSTOMERS_DIM_TEST.CUST_KEY ||','||CUSTOMERS_DIM_TEST.LOCATION) as CUST_KEY, CUSTOMERS_DIM_TEST.CUSTOMER_ID as CUSTOMER_ID FROM CUSTOMERS_DIM_TEST WHERE   CUSTOMERS_DIM_TEST.END_DATE IS NULL

Step 3:

create the expression_1  transformation and drag to  all ports from source qualifer, 

And create the one ouput port LKP_VALUE

:LKP.lkp_CUSTOMER_ID(CUSTOMER_ID)

Again create the three output ports names:

LKP_VALUE_O=LKP_VALUE

lkp_CUST_KEY=SUBSTR(LKP_VALUE,1,INSTR(LKP_VALUE,',',1,1)-1)

lkp_CUST_LOCATION=SUBSTR(LKP_VALUE,INSTR(LKP_VALUE,',',1,1)+1)

Step4:

Again create the expression transformation ,the expression transformation name exp_CUSTOMERS_DIM_TEST,

1. create the 4 outputs 
2. INSERT_FLG=IIF(ISNULL(lkp_CUST_KEY),'Y','N')
3. UPDATE_FLG=IIF(NOT ISNULL(lkp_CUST_KEY) ANDsrc_LOCATION<>lkp_CUST_LOCATION,'Y','N')
4. Begin_Date=sysdate
5. End_Date=sysdate

Step5:-

create the one route transformation ,the router transformation name rtr_CUSTOMERS_DIM_TEST

* Drag the from all the second expression t/r  all ports  to router transformation 

• create the two groups like 
• INSERT=INSERT_FLG='Y'
• Update =UPDATE_FLG='Y'

Step 6:-

• create the two update stratgey t/r
• one for update and another one for update 

Step 7:-

create the squence t/r  and two out port like NEXT_VAL,CURRVAL,AND connect  to the 

from sql t/r nextval port  to taget_insert instance connect.

From update _update T/r  to connect target_update insteces,

Note:-Keep the target key column  primary key ,if not there in database  asking to DBA team or you can put the informatica level primary key column.

Thanks You Folks 

please let me know you have any update 

Gmail:informaticafolks@gmail.com

Facebook: dwbi folks

Blog:http://dwbifolkstalk.blogspot.in/