phantom occurences while performing lookup

Question

I'm performing a lookup in a dimension table using a t-sql function.

Structure is SCD6 and it's performed in sql server 2005 on top of windows server 2005

Dimension table looks like this

KEY   LABEL   ROWSTART   ROWEND

-1     NULL     01-01-2000

1      A          01-01-2004

2      B          01-01-2006    01-01-2007

The lookup is performed by matching the table and using the rowend and rowstart to fetch the active key.

Now the following happens. My transactions need the surrogates and for instance I have 100 transactions 

with LABEL instance "A". 

After doing the load into the DWH 99 transactions have key 1 in place which corresponds with label A , and 1 transaction, with label A, has been assigned surrogate "-1" which  should only occur when fields are empty. The

latter is the phantom occurrence.

I perform the lookup via a function which is called in  a stored procedure. The stored procedure is next being called in a SSIS package.

This is happening at several places, at irregular intervals. 

Other people have looked at it but can't explain what is happening as all code is OK. Could it be a hardware problem? it's hard to imagine the database is inaccurate but it is

 

---

Business Intelligence professional

Answer 1

Can you share the details of the lookup function?

Answer 2

I agree.  Please start sharing some of your setup details, including code, otherwise we can't help much.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 3

Ok, first look at the stored procedure which updates the surrogate in the staging table.

When I run them separate , after the master package and such problem occurred, everything works out well. As I told, it happens incidentally, and I think it is "stress" and somebody else mentioned it could be software/hardware problem.

I fill each DWH table with one package. Because I have little data dumped, I use Sprocs which call functions , so in that way, I can maintain my lookups easy (each keytype lookup has one t-sql function) rather than the approach using the lookup component which would require each package to be updated when lookup strategy changes.

When issued at the staging table the data is loaded into the package which might do SCD6 and error handling and so on using SSIS components.
Any help welcome

ALTER

 

PROCEDURE [etl].[Usp_Up_Projectledger_Surrogates]

/*

Author :
Creation date : 07-09-2009

Modification date :

 

*/

AS

--SK_

 

SET

 

TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN

 

 

 

 

TRAN UPDATE

[DWH_staging].staging.[Projectledger]

SET

 

SK_Costcenter = (

 

                               SELECT etl.fn_lookup_SK_Costcenter (

                                                                                [Global Dimension 1]

 

                                                                                ,getdate()

 

                                                                                 ,NULL

 

                                                                                 )

 

                                )

                               FROM

 

[DWH_staging].staging.[projectledger]

 

--SK_Organizationkey

UPDATE

 

SET

 

SK_Organizationkey = (

 

                                     select etl.fn_Lookup_SK_OrganizationKey_Costcenter_Admunit (

                                                                                                                         [Company]

 

                                                                                                                        ,[Global Dimension 1]

 

                                                                                                                        ,getdate()

 

                                                                                                                        ,NULL

 

                                                                                                                         )

 

                                                                           )

                                                    FROM

 

[DWH_staging].staging.[projectledger]

                                                   COMMIT TRANSACTION

--
Functie

ALTER etl.fn_Lookup_SK_Costcenter

 

 

(

nvarchar(20)

 

@Costcentercode

,

@Startdate datetime

,

 

@Enddate datetime

)

 

RETURNS

 

bigint

AS

BEGIN

 

-- Declare the return variable here

 

DECLARE @Result bigint

 

-- Add the T-SQL statements to compute the return value here

 

SELECT @Result = (

 

                          SELECT SK_Costcenter

 

                         FROM [DWH].[dbo].tbl_Dim_Costcenter

 

                        WHERE rtrim(BK_Costcentercode) = @Costcentercode AND

                       RowStartDate

<= coalesce(@Startdate,getdate()) And

 

                       coalesce(RowEndDate,getdate()) >= coalesce(@Enddate,getdate())

 

 

                       )                 

 

-- Return the result of the function

 

RETURN coalesce(@Result,-1)

END

GO

SET

 

ANSI_NULLS OFF

GO

SET

 

QUOTED_IDENTIFIER OFF

GO

 

 

---

Business Intelligence professional

Answer 4

Your using the Isolation level as Serializable.
That could be one of the issues.

---

Hope this helps !!
Sudeep      My Blog

Answer 5

Hi sudeep

I added it 1 week ago the isolation level.
Before I also had this problem so it's not the isolation level.

BTW: why do you think it causes problems? it jus makes sure the table is isolated from any other thing, although it's technically impossible given my package something else is adding data

---

Business Intelligence professional

Answer 6

lets identify the bottleneck and behaviour of the system .
there are few commands which will helps to identify the bottleneck.

1. To see any blockings execute the below command and see who blocked by whom
command : sp_who2

2. To see what type of lock the user holding the transaction ( possibly due to isolation level) check the locks and who is executing that.
command : SP_LOCK <PROCESS ID.. GET THIS FROM SP_WHO2.. FROM ABOVE COMMAND>

3. to see any open transactions in your database which are not committed

command :dbcc opentran()


4. If you found any opentransaction in above command to see what statement the process is executing
command : dbcc inputbuffer(<enter the id from the output of the dbcc inputbuffer .. above command>)

let us know what you got from the above...

---

Please mark the post as answered to help others to choose the best.
chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)