Re: Replication, DTS, Triggers?

If it goes the way I would hope, we would do incremental pulls of
new/updated rows on top of the initial ETL. It would likely be about 50,000
rows per day from 100 source databases into the consolidation DB. From what
your saying it sounds like replication is the best starting point. It sounds
like a good foundation and that DTS and triggers might be useful for
augmentation on top of replication.


"Joe Yong" <NO_jyong_SPAM@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4VS6e.10236$Zn3.5808@xxxxxxxxxxx
> Depends on what your other needs/constraints might be. Are you pulling the
> entire table/DB across or just selected rows (eg. new/updated rows since
> last ETL)? How many rows?
>
> DTS is a great tool for pulling stuff in and making some (hopefully not
> very
> complex) transformations along the way but if you have large datasets,
> that
> might not be such a good idea. Also, if you're doing incremental or
> differential pulls, DTS will need a bit of extra work to ensure you're
> pulling just the rows you want and not everything.
>
> Replication works great if you're doing incrementat/differential pulls as
> it
> takes care of the row tracking for you. Performance is generally great for
> basic transactional replication and you can also do transformations on the
> articles you want to publish (don't know what the performance hit might be
> though). Repl can actually use DTS features to tweak the data its moving
> before publishing to subscribers.
>
> I'd avoid triggers if possible but that's just me. Triggers are easy to
> implement and easy to wreak havoc with also. If you're not careful, it is
> easy to overwhelm the system with lots of triggers firing non-stop.
>
>
> joe.
>
> "Bob Castleman" <nomail@here> wrote in message
> news:eWAgA3tPFHA.688@xxxxxxxxxxxxxxxxxxxxxxx
>> Scenario:
>>
>> Multiple customers running identical databases in a hosted enviroment.
>> Need to do point-in-time ETL of select tables into a single consolidated
>> database for read only reporting.
>> Point-in-time does not have to be the same for each source database.
>> A data transformation must take place adding unique identifiers to data
>> as
>> common tables will be consolidated (I.E PK would go from a single column
>> such as GL_TransactionID to two columns such as
> CompanyID,GL_TransactionID).
>>
>> This seems like a pretty straight forward task, but I can do this anyway
>> I
>> like. I could set up views with triggers, use replication or DTS or some
>> combination of the three. I'm a bit overwhelmed with all the options.
>>
>> Any suggestions?
>>
>> I have no customer requirements. This is a proof of concept to my boss to
>> show that we can do enterprise level integrations, data warehousing and
>> data mining with our line-of-business application. I need to show a basic
>> consolidation of P&L and Balance *** data. Basically I can do it anyway
> I
>> like.
>>
>> Bob Castleman
>>
>>
>
>


.

Loading