Increasingly common, innovative business projects have a need to integrate various databases to extract information. Those of us, who were in the industry for long enough, have seen it all. Relational and non-relational databases, CSV or Excel files. Most probably, those sources were not even designed to be used together. Traditionally, some kind of ETL project had to be created. It would load the data into “stage” tables, apply necessary transformations (like data type unification), and finally load the data somewhere. Time of the project would be measured in days or weeks rather than hours. Moreover, qualified staff is required, with knowledge about all source systems and the destination system. Using data virtualization tool like Lyftron, time-to-solution can be cut down to hours/minutes for one person. This article will show you step by step how to do it.
We will describe how to integrate 3 data sources: SQL Server, Oracle and, MongoDB. First, we will import metadata from source systems, then we will show how to join data from those disparate sources using T-SQL and access data using a single data interface.
To work with external data sources we need to import metadata from those sources. Let’s use Oracle Northwind database and see how to do it in practice using Lyftron. Step 1. Go to the Databases panel, click Add Database and fill necessary fields. Select “Oracle” from the list of databases supported by Lyftron. We support over 100 data source types.
Step 2. Definition of new connection requires us to tell Lyftron how to reach the Oracle server. In our case, TNS Alias was provided alongside login and password. To make sure that connection can be established, click “Test Connection” button.
Step 3. Select which tables are we interested in and click the “Create” button. For this example, only the Northwind database will be useful, so that was our selection.
Virtual Database “OracleDatabase” was created and metadata for this Virtual Database was imported from Oracle’s Northwind database into Lyftron. Keep in mind that no data was loaded from Oracle.