Solved

how do we impliment  lineageid on data warehouse staging envrioment?

Posted on 2011-09-22
6
229 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 39

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 39

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:keplan
ID: 36618911
excellent answer
0
 
LVL 39

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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