Solved

how do we impliment  lineageid on data warehouse staging envrioment?

Posted on 2011-09-22
6
232 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:keplan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36587474
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
 

Author Comment

by:keplan
ID: 36596484
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
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 36601998
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:keplan
ID: 36618911
excellent answer
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36931814
In that case you should mark the right answer/solution.
0
 

Author Closing Comment

by:keplan
ID: 36939911
answer is good
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question