6.0 Tutorials

You can translate the document:

Connecting to data sources

Overview of the Process

Let's organize all this data in a coherent view of the marketing promotion performance. The process will look like this:

  1. Connect Denodo to the data sources, to retrieve the data in real time.

  2. Combine the data performing the needed transformations and normalizations.

  3. Create our final reports ready for consumption.

  4. Publish the data for the client application.

To organize our virtual objects, we are going to create a database, named "bitutorial", and some virtual folders. Go ahead and create four folders, named "01-sources", "02-base views", "03-data model" and "04-reports".

Connecting to data sources

The first step is connecting to the data sources. As we saw in the Basic tutorial, Denodo will store just metadata - no data will be imported from the source (unless we explicitly tell Denodo to do so, through caching) instead the data will be retrieved in real time. We have two different sources, the Excel file and the database.

Database

The first connection will be to our database of sales and products. This is running in a MySQL instance so what we need to do is to right-click over the "01-sources" folder and select New > Data source > JDBC. That opens the JDBC data source dialog:

Here we do the following:

  • Type the name of the datasource, in our case "salesdb".

  • Select MySQL 5 as the database adapter.

    Remember to install the JDBC driver for MySQL, as described in the Data Virtualization Basics tutorial.

    NOTE

  • Set the database URI. In our example the database server is running in localhost and the default port, so our URI is jdbc:mysql://127.0.0.1:3306/salesdb. Modify these settings as needed for your environment.

  • Set the login and password for your db.

  • Make sure "Test connection" is selected.

  • Click on "Connection Pool configuration", and in the new dialog make sure "Test connections" is selected. Click "Ok".

  • Click .

After these steps we have created our data source for the sales database. When we click on Create base view Denodo will show us the introspection screen where we will select which tables are we going to import.

Go ahead and select all the tables under the "sales" database, and click Create selected. Below you can see all the schemas of the views that we are creating:





Excel marketing file

The second connection will be to the Excel file. This file contains two worksheets as discussed earlier; we have to consider them separately so we will create two different connections to the source, one for each worksheet.

To create the first one, we right-click over the "01-sources" folder and select New > Data source > Excel. In the following dialog:


  • Type the name of the datasource (for example, marketing).

  • Select the type of file, in this case Excel 2007 or later.

  • Choose the file location: select "Local" for a file in your local filesystem, and then click on "Configure", "Browse" and use the browse dialog to choose the file you downloaded.

  • Work sheet: the name of the worksheet is "Marketing promotions", without the quotes.

  • The start cell is A1, and end cell is I43.

  • Finally, make sure you have "Has headers" and "Stream tuples" selected.

Click , that will create the data source. Let's create a base view from it, by clicking on Create base view.

You will see the schema of the new view, change the data types as seen in the following screenshot:

and click to create the final base view.

Now it's time create the base view for the second worksheet. The process is almost identical to the first workseet. Start by right-clicking over the "01-sources" folder and selecting New > Data source > Excel, and configure the next dialog in the same way as before, except:


  • The worksheet is named "Country lookup".

  • The start and end cells are A1 and B36.

After clicking we have our data source ready:

And by clicking on Create base view we access the base view editing dialog, where we will edit the final view as follows:

Click . Now we have our two Excel views in addition to the views coming from the database.

We are almost ready, but let's first move the base views to the "02-base views" folder so they are well organized. Just select all of them and drag them to the destination folder. After that is done we will start doing data combinations.


Add feedback