We are using pl/sql for our ETL jobs. It's a pita to maintain, but it does what it does well. There are a number of things we take into account during the ETL: - parse diary fields into sub-tables (user/time/entry) - translate enum data types - convert date/time data types - rearrange/munge/convert the data into a structure that is more suitable for a report - store historical data (daily snapshots) - and many other things
Every time a production change is implemented that impacts the db structures, the ETL jobs must be updated and the target db updated. We only add things to the ETL job as they are required for reporting purposes. That's to say that what makes it to the data warehouse is only a subset of the total data. Btw, this is one of the most painful areas when considering upgrading the ITSM applications. Data retention policies will not simply allow us to toss the data, people still want their historical data for reporting, all reports have to be converted, etl jobs must be rewritten from scratch, historical data migrated to the new schema, etc. Axton Grams On Tue, Apr 8, 2008 at 9:27 AM, [EMAIL PROTECTED] < [EMAIL PROTECTED]> wrote: > My reporting team is looking to create a small reporting warehouse > with data pulled from Remedy production. We're using ARS 7.01 patch 5 > on MS SQL Server 2005. > > We've spent quite a bit of time looking at our options and I wanted to > pose a few questions to those of you on the list who are using ETL > tools with Remedy. The questions are around unlimited-text fields and > date translation. > > A little background: Our first choice was to use MS SQL Integration > Services, utilizing the AR ODBC driver to avoid T-table translation. > Problem is, those jobs hang on unlimited-text fields (Description, > Worklog, Audit Message, etc.). The problem definitely seems to be > SSIS and Remedy ODBC, since the same queries work in Access and > Crystal. After going up the support chain we were reminded that SSIS > was unsupported, while Access and Crystal were. So we've run into a > dead end here. > > So our remaining options seem to be: > > A) AR ODBC for the small fields and straight SQL for large fields (no- > go) > > B) Use straight SQL against t-tables for everything (would like to > avoid if possible) > > C) Use Access or some other AR ODBC-compatible tool as an intermediate > step to populate the data. (ugh) > > So my questions would be: > > 1: Is anyone else selecting unlimited-text fields such as Worklog > using the AR ODBC driver? Have you run into any problems with rows > that have large amounts of text? Have you found a workaround? > > 2: If you use straight SQL to pull Remedy data, how do you handle the > translation of date/time fields? (The nice thing about AR ODBC is it > handles Daylight Savings Time conversions for us.) This is > particularly a problem with the Worklog field which has dates embedded > in the text. > > 3: Are there any other simple and cost-effective ETL tools you > recommend for Remedy? > > Any comments are greatly appreciated! > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
