how do we impliment lineageid on data warehouse staging envrioment?

Hi,

I'm modeling a datawarehouse for a company, in order to model the staging database, microsoft has recomend to use the lineageId on the staging area in order to capture net change rows.
my question is, how do we impliment this?
Regards.
keplanAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
I guess SQL Replication would be anothe option and that btw puts their own triggers and implement their own internal "LineageId" structure with GUID data type which I'm not big fan of.
Database Miroring or some sort of StandBy solution from where you can read data and populate your Warehouse and that's about all I'm aware off as SQL "native" methods and please see more about SQL DML triggers at: http://msdn.microsoft.com/en-us/library/ms191524.aspx
0
 
lcohanDatabase AnalystCommented:
I believe what you're talking about is to populate warhouse DB from OLTP DB by using SSIS.
In theory I believe this would work however if you check on the web there are many issues related to "lineageId" in SSIS.

http://sqlserverpedia.com/blog/sql-server-bloggers/five-things-ssis-should-drop/

Here's what I did to accomplish similar task for our data warehouse behind a 24/7 e-commerce web site: I used triggers on the parent on-line tables that will populate queue staging tables on any INSERT/UPDATE/DELETE and SQL jobs to "move" data into the warehouse from the satging tables. Sounds like a lot of work however by doing this we have control of the code and data plus we know what goes where at any given time.
0
 
keplanAuthor Commented:
is this to be impliment on Source database, I guess, you re talking about impliment a trigger on source database to monitor the chages on the dataset, and populate staging area based on the source transaction or changes to the record.
My question is, if we are not able to add any trigger on the data source system, what options are avaible to the developer to identify the chages or insert new record?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
keplanAuthor Commented:
excellent answer
0
 
lcohanDatabase AnalystCommented:
In that case you should mark the right answer/solution.
0
 
keplanAuthor Commented:
answer is good
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.