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"
