multiple db connections vs. centralized/ redundant db

[snfctech]

I have a project to create a dashboard that will connect to existing systems as well as create new features based on combining data from the existing systems. For example, the dashboard will be able to generate "orders" containing data merged from "members" (MS Access DB), "employees" (MySQL DB) and "products" (flat file), and there will also be new attributes particular to "orders."

At first I thought it would be most efficient to have my application connect to each of the systems separately and perform cross-vendor joins between the different databases. But then I thought that creating a centralized/redundant db (built with scripts pushing and pulling data between the systems) might also be useful because it would empower some semi-technical staff to use products like OOBase, which can only make a single connection.

Are there any other advantages to creating a centralized/redundant DB like the one I'm talking about? Or are multiple direct connections the best approach?

Thanks in advance for any tips.

[gits]

the creation of a 'redundant' database is a common solution in the world of BI (business intelligence) where you might use a data warehouse (the mentioned DB loaded with pre-aggregated data from productive DBs) to allow easy and high performant reporting ... it basicly relies on a update-rule-set that is responsible for the actuality of data in the data warehouse and the needs of the users ... it reduces the requests to the production DBs and thus increases performance for the production applications ...

this concept is not very handy when it comes to write operations ... since you would need to re-update the productive databases ... so basicly a data warehouse is (always) a bit out of sync and is just used for read-operations (at least it should)

so as you might see ... your approach depends on the needs of your app ... especially how you would need to have everything in sync ... depending to that it could be recommended that when sync is no issue and you might have a 'kind of data warehouse' then it is not a bad approach ... otherwise everything stands or falls with the syncing scripts, rules etc.

kind regards

[snfctech]

Thanks for youre reply, gits.

this concept is not very handy when it comes to write operations ... since you would need to re-update the productive databases ... so basicly a data warehouse is (always) a bit out of sync and is just used for read-operations (at least it should)

Which is precisely my quandary. Does it make sense to build the type of centralized DB I'm talking about, which has both a "warehouse-like" ELT component, and a basic ER schema for an active operational system making inserts, updates, etc.? If so, does it still make sense for me to study any traditional warehouse design principals (a la Kimball or Inmon)?

I still don't have all my business requirements yet, so hopefully I wont have to write to the redundant parts, and those tables can be managed by their respective operational systems. But I would still have foreign keys in my new entities that relate to them.

The other alternative I've been considering is to skip the ELT part entirely and create multiple DB connections on the application level with cross-vendor joins. Nobody seems to be recommending that - although it would potentially be a simpler solution.

[gits]

I guess you mean ETL (Extract Transfer Load) instead of ELT ... and i wouldn't mixup things. as far as I understand it, you have an operational part where you have to write things to operational tables ... now you would have to do the inserts/updates there and then you would have to extract that data directly back to the warehouse? ... when you could have a delay ... then it seems to be ok to try such a mixed solution otherwise the datawarehouse feels like starting to loose its advantages with unburdening the operational systems from read operations, and looks a bit like overhead. Often the simpler or more pragmatic solutions are the better ones :)

kind regards

[snfctech]

Sorry, I meant ETL (Extract Transform Load).

Yeah, I don't think our data or business needs are such that our reporting is much of a burden on our operational systems. I'm leaning toward having both operational and redundant/read-only tables in the same MySQL DB on the same server.

It will have an ETL layer, but I don't think I should call it a "data warehouse", per se.

Sounds like you think this is an okay approach.