Can data warehousing be used to export data to LH tables?

- Oystein -

Data warehousing can be used to export data from LH tables into other databases, by converting to SQL syntax. But it doesn't work the other way round, that is you will not be able to import data from other databases into OI/LH.

Not directly, but of course you can read the data from the non-LH database using either a connection or a dataset, and then write it to a linear hash table.

Dave

Arjun, David - thanks for your replies.

Just to explain why I asked: I want to give web access to a LH database A. For various reasons the access will not be to the original A database, but to a filtered and simplified copy B (also LH). So I thought warehousing would be ideal for automatically updating B when A was changed, provided one could warehouse to LH.

- Oystein -

Rather than warehouse, I would write a quick and dirty MFS that will write records that meet your criteria in the other LH table.

Am I on the right track here or am I missing something?

Dave

Dave,

I don't know, but I'll certainly keep your suggestion of using an MFS in mind. I must confess I haven't thought everything through yet. I'm actually not sure if the warehousing or export to the web database can be done automatically or if it must be initiated by a human. I'm sure different users will have different preferences.

If many of our users say they need the ability to make ad hoc subsets of their main database web accessible, I believe it must be a human-initiated process. I'll have to give them the ability to search and collect specific subsets to export.

(It sounds like I haven't discussed matters with my users. I have, but many of them will not have strong opinions before it's implemented.)

I know that many users will not like to have their data web-accessible immediately. Data will often have to go through revisions. This might be another argument for having a human-initiated process. But not necessarily. Perhaps we could add an extra Ready for the web field where the users can tick off when the record is ready to publish. Such a field could be used in an automatic process. (But I might have to implement the possibility of withdrawing a row from the web database too.)

What I do know is that even records ready for the web will have to go through some filtering. I think I can handle that with sets of rules. I think the most complicated rule will be something like "in so-and-so AMV group don't export the values of so-and-so fields if the value of a certain other field X in the group is "such-and-such" (like "don't publish the name and address of donors (X[i]=donor") because they might not want their relatives to know they gave away valuable artefacts to the museum").

(I you have a very long memory you might remember me making postings on this subject a long time ago. That was before OI had proper web capabilities and I tried to develop something myself, with everything published as static html documents. I wanted to include filtering capabilites but my web export never got beyond the prototype stage.)

- Oystein -

Oystein,

Because you're dealing with movement from one LH table to another, I still think that an MFS is the best solution for automating the process.

Obviously the original table does not have to be the same as the destination table(s).

From what you're writing, sounds like you can have the MFS check for the presence of different flags (ex: remove this record from warehouse, remove this MV line from warehouse, etc.). At Write and Delete time, it would check the record you're writing to the original table and add/update/delete the corresponding record(s) in the destination table(s). I keep saying record(s) and table(s) because I'm thiking that you can make the destination data up "on the fly" based on your original record and only write out what you need.

I hope this method will give allow you to add even the most convoluted logic to what you need to warehouse and what you don't want to show

Dave Pociu

d.pociu@snet.net

Dave,

Thanks for further input.

Pro (or at least not con): I wrote an MFS once as an exercise, and it worked, so I think I can do it again.

Pro: The original database has one main table and various related tables. For the web database I've decided to have a simpler structure with just one table. There's not much point in having a complicated structure when the web database is read only. When I export a main table row the new row will have some fields with values taken directly from the original data (F) fields, and also some values from symbolics, where I pull in data from the related tables. The simple structure of the web database and the simple relation between original rows and web database rows could make an automated MFS-based process easy to realize.

Con: But it could still turn out that users want a more direct and explicit control over the export/warehousing process, so I haven't decided yet.

Con: Also with museums objects and historical photos there's no need for real-time speed exactly. If I chose to use a batch-like process the web database will be more static, and I don't have to worry the database will change under my users' noses. E.g I don't have to lock when I browse indexes (I've made my own index lookup functions).

Btw - that argument can be countered with a mixed strategy, with an MFS exporting to an *intermediate* database, in web database format. That intermediate database could then be copied wholesale to the web server at regular intervals - daily or weekly.

- Oystein -

View this thread on the Works forum...