Thank you Fred ! I have generally stayed away from 'normalized' data here since the quantity is relatively low. It may be time to rethink that in this situation. A dblink was my initial choice. I will try both methods and see which fits best.
Susan On Thu, Jan 19, 2012 at 5:45 PM, Grooms, Frederick W < frederick.w.gro...@xo.com> wrote: > ** > > Susan,**** > > ** ** > > Since you are on Oracle there are a couple of ways to get the Org data. > (MS SQL and others have similar abilities, just different names)**** > > You can link across a dbLink (I personally prefer to make a local Oracle > view that access the data across the dbLink. This way the Remedy View form > is against nothing but the local database).**** > > You can use a Materialized View to pull the Org data into the Remedy > database (A Materialized view can be told to auto refresh every X interval > behind the scene).**** > > You can use scripts to update the data in Remedy from the “master”.**** > > With any of these you can create a View form in Remedy against the Org > data (I have used all 3 and they all have their place where they work the > best). With the last 2 you have the advantage that you can add whatever > indexes you need without affecting the master copy. The View form can be > created with every field’s change flag disabled so no one can mess with the > data in Remedy, they would have to update the master.**** > > ** ** > > Next thing to look at is normalization of data. Do you really need 30 > fields from Org into Site? **** > > Personally I only store a key of the parent “Org” into the child “Site” > (unless you are in a many to many situation, then you need an intermediate > “mapping” form, but I don’t want to get into that here). **** > > i.e. In Site you would have an Org_ID field that holds a unique key for > the Org data. (This is the way I have always designed my Remedy forms).*** > * > > ** ** > > If you want to view the combined data exactly like they see now for Site > you can create a Join form (While the Admin tool would not let you use a > View form in a Join, Developer Studio 7.6 does).**** > > ** ** > > Hope this helps**** > > Fred**** > > ** ** > > ** ** > > ** ** > > *From:* Action Request System discussion list(ARSList) [mailto: > arslist@ARSLIST.ORG] *On Behalf Of *Susan Palmer > *Sent:* Thursday, January 19, 2012 4:32 PM > *To:* arslist@ARSLIST.ORG > *Subject:* Suggestions requested for bringing in data from a separate > database**** > > ** ** > > ** **** > > Hi Everyone,**** > > **** > > Currently we house all data necessary for various Remedy operations in > Remedy forms. It has a 'flow-down' mentality so top feeds to next level > and that level feeds to next level etc. All other systems we have access > the necessary data from Remedy.**** > > **** > > Of course there is always the issue of data correctness, which really has > nothing to do with Remedy specifically. But it's been decided that some > specific data will be stored in another separate database and all systems > will access it from there (much like they do now accessing it from > Remedy). The new database will be much more accurate (tongue in cheek) and > will not have data correctness issues.**** > > **** > > That's the basic background.**** > > **** > > So I'm looking for solutions that provide me the easiest way to access > that data stored in the separate database seamlessly. I don't want to > bring it all into a Remedy form and then access it from there since > basically that takes me to where I currently am (which works just fine but > is being discouraged). For instance, all information about an > Organization is in the Organization Information form and when a 'Site' > belonging to that Organization is created much of the Org info is > automatically set into the new Site record. So instead of starting at the > Org form to create a Site I need to start at the Site form and pull in the > Org data (sql set fields). Or maybe I do a display form for the Org data > (view form with data displayed) and still create the Site from that display > record via set fields. Either way how do I get that Org info without using > a menu selection (600 choices) or needing to know exact verbiage. We need > to have the Org information stored in the Site record for easy reporting > and querying, although maybe that's just my mindset. I would think it > would be less efficient to always be calling to another database. We're > talking about approximately 30 fields from the Org record.**** > > **** > > I'm just having a little trouble putting my arms around this and shifting > my Remedy centric thinking. We're talking about 600 Org and 50,000 Sites > but that number will double in 1-2 years and continue at that growth. > Relatively small in comparison to many systems but needs to be scalable.** > ** > > **** > > I'd appreciate any help triggering my thought process towards the most > correct path which may be one that hasn't even crossed that mind.**** > > **** > > Thanks,**** > > **** > > Susan Palmer**** > > ShopperTrak**** > > 200 W Monroe St 11th Fl**** > > Chicago, IL 60606**** > > 312-529-5325**** > > spal...@shoppertrak.com**** > > **** > > ARS v7.5P4**** > > Oracle 10.2**** > > We currently use v7.5P7 client tool but will be moving to web (although > many postings question that decision)**** > > Our prod server services US, CH, UK, Middle East**** > > ** ** > > ** ** > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"