I'm looking at what might be the most efficient way to store /retrieve /analyze records.

I have 4 "offices" entering data over a WAN. Each Office should not be able to access other Offices' records, but all will be entering the same type of information. I already plan to have one application they log in to… and one set of data entry screens, reports, etc. they will all

use. Would it be best for each, as they log in, attach to separate sub-directories that contain identical data tables. This would mean I would have to maintain 4 separate dictionaries for each table (4 directories with maybe 20 tables ea.). Or do I have one set of data tables where information is entered by all offices, but have a key field on every table named "Office_Number" (1,2,3,or 4). Then, on all data entry screens, have a hidden field for this key field that will automatically fill with the "Office_Number" depending on the User. Using one set of data tables will mean that reports run by each office will take longer since a select would have to be done to access only that Offices' records (4 times the number of records in each table). Keep in mind that monthly, or as needed, I will have to compile reports using information combined from all of the offices.

Thanks for ya'll's input.

Steve C.

How about using BOTH techniques

separate subdirectories for site specific data,

use the site as part of the key

MFS or Month end process copies rows from sites to corporate database.

Is safe to copy them because keys will not overlap. No worries that site will accidently see another's data.

If speed becomes a problem the site specific tables can be kept local to each site, replicated to corporate at report time.

Bob

Can anyone change the data centrally without satellite intervention? In other words, if the data is changed at base do the users in the field need to have the updated information or is the centralised table static?

The Sprezzatura Group

World Leaders in all things RevSoft

Bob,

All records would be unique to each office… with all keys

being specific to unique accounts in each office region, or

by employee numbers for detail time/job performance. I would still want to store the office

number in each record so that when I combine/analyze/report statwide

I would be able to identify the regions easily.

Which would be more efficient in my centrallized sorts/selects in reporting individual regions if I copy all records from the 4 offices into a single tables, to have the office number as any part of the key (up to a 4 part key), have the office number as the 1st key field, or have the office number as any normal field of the record. My thought would be the 1st key field…. but how would OI be optimized for this type of selecting?

Thanks…

Steve C.

]Can anyone change the data centrally without satellite intervention? ]In other words, if the data is

] changed at base do the users in the field need to ]have the updated information or is the centralised

] table static?

Well, If I have 4 sub-directories, one for each office, I would combine this into 1 sub-dir. for my analysis. This would be just for reporting, analysis purposes. If I need to change data I would do that in each individual Office's live data tables. I supervise all of these offices, so I would always have access to all sub-directories, while I only want each office to have access to their own info.

Steve C.

Without more information, it's hard to give real good advice.

The speed of the WAN and the timeliness of reporting are the items that need to be described further.

In a perfect world, what we would do is store each office locally. We'd then have a way of ensuring no duplication of keys. We'd do that by prefixing the key with some letters. Most likely 2 as that gives a large number of offices. It has the same effects as a multi-part key but avoids the problems of manipulating them in the window. Prefixing with a letter also helps the file hash better, but that's a topic for another thread.

We'd then have a process the moves the new record and changes back to the central servers. The best way would be to use an MFS which stores off keys for trasnfer. If speed is an issue you could get fancy to just do transactional updates for existing records.

On the server side, the program that moves in the records could start to split items out. Each office would have it's own file and there will be an additional file containing everything.

Depending on the size of the data, you could create even more files, where the data is stored monthly or weekly or yearly. Then as reports are executing, it can jump to the approriate file. So, if you are doing reports for February 2002, you can just process the monthly file.

This is a full example, containing everything to speed up a large WAN based system in general.

Depending on your needs, you could have consistant transaction based updates to the main server, minimizing lag time.

If all the data is on the WAN, the MFS could be used to update the secondary files or create transaction records for those updates.

As for the indexing, indexing on a key part is the same as indexing on a real field.

All said and done, there are a wide variety of options.

The Sprezzatura Group

World Leaders in all things RevSoft

Ditto Sprezz's answers …

If you do not need the office to make the keys unique I would put it into a regular field and use btree indexes on the search. If I have an important date, eg complete_date, I will make a symbolic called complete_month that evaluates to the last day of the month that complete date falls in, then make a symbolic called region_complete_month or complete_month_region that concatenates region*month, so I can Btree on that symbolic, get fast selection. Once I have a selected list the size of the file I am reporting from is not a critical speed factor. If you are pouring large amounts of records into the reporting table you will want to turn off indexes, pour the data in, then rebuild indexes. If you have an expensive set of calculations or selects that repeat across a family of reports and are not needed after the reports are done then build some reporting tables that are de-normalized but optimized for reporting, write an program that loads the table with the answers and enough foreign keys to xlate back to the source, then write a family of simpler Rlist reports drivens off the reporting table. This can be a good way to do financial data where you want to guarantee that all the numbers come from the same point in time. Make the 'as-of' date part of the key if you intend to keep the data forever.

While you're at it …

Call that that reporting table a 'data warehouse', spit it out as tab delimited text or XML and let your users build spreadsheets, Charts, Pivot tables or crystal reports using text or xml ole db drivers.

Hope this helps

Bob

Thanks for ya'll's insight…. it will certainly help.

Steve C.

Still mulling this one over.

If I have 4 separate directories, with 4 sets of tables and their

dictionaries, I need to keep all dictionaries up to date with a "Master" dictionaries set. Updating these should be no harder than adding them through the Database Manager as aliases, and then using "Copy_Row"

to copy and overwrite the Dict. records in all tables, right????

Just write a subroutine that does this programatically and run it every time I do maintenace on the dictionaries???? will this work…

or am I missing something like also deleting and rebuilding indexes or……??

Thanks…

Steve Carlson

Depending on how you plan on storing the databases, this old Revmedia article might be wht you are looking for.

The Sprezzatura Group

[i]Celebrate CeBIT with Sprezz Local![/i]

View this thread on the forum...