Solved

how do we impliment  lineageid on data warehouse staging envrioment?

Posted on 2011-09-22
6
231 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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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.
In this article I will describe the Copy Database Wizard 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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

740 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