August 1, 2016 at 3:01 am
Hi!
We are a company doing surveys with different kind of questionnaires. There is a company in Mumbai (MySQL) specialized in Type 1 questionnaire and one in Peru (SQL-Server) specialized in Type 2. I also work with a CRM system as Salesforce and want to connect these data as well.
I thought about getting all these data into one in-house database using ETL. I was curious if it is possible to virtually connect these data warehouses so I do not need a DW in-house and can query the data where they are stored but can connect to them. Is this possible? I could not find anything but maybe I am looking with the wrong vocabulary.
br!
August 1, 2016 at 5:59 am
The first shop to stop in for this kind of problems is SSIS, Sql Server Integration Services.
😎
August 1, 2016 at 6:58 am
So you mean buiding a data warehouse (in-house) and get the data via SSIS into it?
August 2, 2016 at 6:52 am
Hi Neophilius,
I think you are referring to a Data Virtualisation solution like Teiid (open source) or Denodo. These allow you to model a layer over disparate sources, so you can query them as it they are a single data source. Commercial solutions can be expensive.
It's all very cool, but this isn't a recommendation, there are lots of things to consider first.
E.g.
Is it overkill for what you need?
Would the latency for querying remote databases be acceptable?
Time zones: would you need to live query during their overnight batch windows etc?
November 21, 2017 at 10:45 am
You mentioned:
We are a company doing surveys with different kind of questionnaires. There is a company in Mumbai (MySQL) specialized in Type 1 questionnaire and one in Peru (SQL-Server) specialized in Type 2. I also work with a CRM system as Salesforce and want to connect these data as well. I thought about getting all these data into one in-house database using ETL.
Reply: Yes, you need to pull the required data (based on business rules defined by Analysts or management) from the respective sites, into a data-warehouse (in-house), then point your reports to it. Otherwise you would need to create a data-warehouse on each of those sites (a lot more work). The catch is how do you connect and get the data from those sites (Mumbai, Peru, etc.) daily ? There are many methods to do it, but each will depend on your skill and speed / latency of network connection.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply