Solved

how do we impliment  lineageid on data warehouse staging envrioment?

Posted on 2011-09-22
6
228 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
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 …

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now