Static vs Dynamic Lookup in Informatica

Difference between Static vs Dynamic Lookup In Informatica

Static Lookups: 

These are are used when you want to lookup a value on a static table or a SQL query based on certain matching conditions. It caches the values when the static lookup in created at the begining of the execution of the session. If the values in the lookup tables are changing and you want the changed values to be reflected during execution of the session then you need to use dynamic cache. 
When you create a lookup transformation it by defaults creates a static cache. Static lookups can be connected or unconnected depending on weather you want multiple or single value to be returned from the lookup.  

Dynamic Lookups: 

These kind of lookups are required when you want the changes happening in the lookup table to be reflected during the execution of the mapping/session. You can create dynamic lookups by setting Dynamic lookup Cache property in the properties section of the lookup transformation. When you make the lookup dynamic, you will observe in ports tab that a new field called NewLookupRow is created automatically. This NewlookupRow port will indicate if a row has been inserted or updated in the cache. In the dynamic lookup the records gets inserted or updated in the cache depending on weather the rows existed or not existed in the cached. 

Use of NewLookupRow in Dynamic Cache:

NewLookupRow port can have the following values:-

0 - Indicates that powercenter did not update or insert the row in the cache.
1 - Indicates that powercenter inserted the row into the cache because it is a new row
2-  Indicates that powercenter updated the row in the cache because the lookup value has changed.

Using the above values you can decide using update or filter transformation what you want to do with those rows coming of the source. Like for example if you want to insert it into the target table or update it? If NewLookupRow=1 then insert it to the target table or if it equal to 2 then update the record in the target table.

How to Loop in SQL server?

How to Loop in SQL server? 

The below example shows how to loop in microsoft SQL server and insert records in chunks: This makes inserts faster.

SET IDENTITY_INSERT dbo.table1 ON /* This is required if you are adding data to Identity columns */

DECLARE @cnt INT = 2006;

WHILE @cnt < 2016
BEGIN
select * from table1 where [Reporting_Period_SID]= @cnt
  SET @cnt = @cnt + 1
 END

SET IDENTITY_INSERT dbo.table1 OFF

Selecting top 3 (n) or Max 3 (n) records for each group by condition

Use the example to select top 3 records for each group. The row number is broken by the Sales_Reporting_Period and then is used to filter the top 3 records.

SELECT Company_code, Run_sid, Curr_ind, Sales_Reporting_Period from

(SELECT          AF.Company_Code,

                BB.Run_SID ,

              BB.Curr_Ind

              ,BB.Sales_Reporting_Period , ROW_NUMBER()

    over (

        PARTITION BY Sales_Reporting_Period

        order by Sales_Reporting_Period, BB.Run_Sid Desc

    ) AS RowNo

         FROM

                       [dbo].[Run_Dim] BB,

                       [dbo].[My_Reserve_Fact] AF

                       Where

              AF.Run_SID = BB.Run_SID

              group by

                      AF.Company_Code,

                     BB.SalesReporting_Period

                  ,BB.Curr_Ind

                     ,BB.Run_SID)Q1 where RowNo <=3

SQL server and azure troubleshooting - common issues

 SQL server and azure troubleshooting - common issues

1) Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value '000005EW84' to data type int.

Resolution: this issue happens when there is character in the column that you are converting to integer. Use a case statement like below to remove those records that have a column. In the example below policy_number is the column that has characters.

case when isnumeric(policy_number) = 1 then cast(policy_number as int) else 0 end as policy_num

2) Cannot insert explicit value for identity column in table 'Mytable_Dim' when IDENTITY_INSERT is set to OFF.

Resolution: You're inserting values for OperationId that is an identity column.

You can turn on identity insert on the table like this so that you can specify your own identity values.

SET IDENTITY_INSERT Table1 ON

INSERT INTO Table1
/*Note the column list is REQUIRED here, not optional*/
            (Mydata_SID,
             MyDate_Desc)
VALUES      (20,
             ''My data")

SET IDENTITY_INSERT Table1 OFF

3)Msg 515, Level 16, State 2, Line 45

Cannot insert the value NULL into column 'Party1_SID', table 'dbo.Party_Dim'; column does not allow nulls. INSERT fails.

Resolution: The column Party1_Sid does not accept null values.  Insert a value of -1 to the column whenever the value is null. Isnull() function can be used to test if a column value is null.

4) Msg 120, Level 15, State 1, Line 45 The select list for the INSERT statement contains fewer items than the insert list.

Resolution:The number of SELECT values must match the number of INSERT columns.
Match the number of columns on select and insert statements..

5) Msg 264, Level 16, State 1, Line 45

The column name 'Party1_SID' is specified more than once in the SET clause or column list of an INSERT.

Resolution: Remove any duplication of columns in the query. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

6) Arithmetic overflow error converting numeric to data type numeric.

Resolution: Increase the numeric precision. If there are datatype such as  numeric(11,2) you might have to make it high so that all the values can fit into the datatype. For example, make it numeric(20,2).

7) Msg 1033, Level 15, State 1, Procedure test_vw, Line 56
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

 Resolution: Remove order by from the view statement.