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.
4  1912   gits 5,390
Recognized Expert Moderator Expert
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
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 5,390
Recognized Expert Moderator Expert
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Craig |
last post by:
I've developed an single sign on web application that we'd like to use with
all the applications we're developing. The application's connection string
is stored in web.config. I've gotten some basic functionality worked out and
I'm attempting to test it on another application, whose connections string to
another database is also stored in web.config. I make a reference in the
testing application to the authentication application dll, but...
|
by: news |
last post by:
Well, I wrote my first PHP class today. Yeah!
But to get it to work, in each function within the class I have to
repeat the database connection lines, and that just seems redundant;
there has to be a better way that I'm just not bright enough to think
of.
Any suggestions?
(It's the first 3 lines of each of the two functions below. When I have
it fully written, there will be about 10 similar functions, each
repeating those three lines.)
|
by: Abhishek Srivastava |
last post by:
Hello All,
In IIS 6.0 We have a concept of worker processes and application pools.
As I understand it, we can have multiple worker process per appliction
pool. Each worker process is dedicated to a pool. If I assign only one
application to a applicaton pool and have multiple worker processes
assigned to that pool. Will my application be processed by many worker
processes?
|
by: jasonsgeiger |
last post by:
From: "Factor" <jasonsgeiger@gmail.com>
Newsgroups: microsoft.public.in.csharp
Subject: Multiple Clients, One port
Date: Wed, 19 Apr 2006 09:36:02 -0700
I'm been working with sockets for a short while now using a server
program a former coworker started. The program listens on a port for
incomming connections. When a valid connection is made (we send this
init string into the socket from the clients) the server closes the
socket so...
|
by: Yelena Varshal via AccessMonster.com |
last post by:
Hello,
What are the pre-requisites / conditions for the ability to create
multiple connections to MS ACCESS database and what is the precedence of its
application? adModeShareDenyNone in the code, Exclusive checkbox in the ODBC
source, Tools->Options->Advanced->Shared in the Database Options, what else?
I have a problem when multiple connections could not be opened at the same
time from my code on one server but could be on another server....
| | |
by: rshekhtm |
last post by:
Hi everyone,
I would like to get your opinion on a technique I came up with when
faced with the problem of redundant code in every web method
(authentication, logging, exception handling). Normally, my web methods
would look something like this:
public ReturnType GetSomeData(SomeType param1)
{
|
by: D. Yates |
last post by:
Hi,
I'm about to embark on a project that will both send and receive information
to/from our client computers in the field.
The area that I still need to finalize is the method of communication and
|
by: keerthyragavendran |
last post by:
hi
i'm downloading a single file using multiple threads...
how can i specify a particular range of bytes alone from a single
large file... for example say if i need only bytes ranging from
500000 to 3200000 of a file whose size is say 20MB...
how do i request a download which starts directly at 500000th byte...
thank u
cheers
|
by: =?Utf-8?B?Vmlua2k=?= |
last post by:
Hello everyone,
I have this code for TCPListenPort. The code works fine, but my manager is
asking me to establish multiple connections to the same port. How can i
acheive that
below is my code
Int32 port = Int32.Parse(ConfigurationManager.AppSettings);
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
