Home • Resources • Minutes to Migrate: How to quickly load and analyze data in your BI tool

Tech Blog

Minutes to Migrate: How to quickly load and analyze data in your BI tool

Gathering huge volumes of data is pointless unless the insight generated is applied within the business. Loading...Data visualization tools are therefore essential in bringing data to life. That’s why we’ve made it easy to get the process up and running.

How easy?

Using Exasol we can help you migrate your data, large or small, from your legacy data source in a matter of minutes. There are a variety of ways you can do this but the following step by step guide will show you how to quickly load and analyze data in Tableau.

The following steps will help you migrate a set of tables from Microsoft SQL Server to Exasol using a database migration script provided on the public Exasol GitHub page. After recreating the tables and importing the data from SQL Server to Exasol, we can create a live connection to Tableau Desktop and visualize the data.

So here’s how you do it…

1. Query the MS SQL Server instance to show data. We at Exasol are fans of DBVisualizer but you can use more or less any SQL Editor. The screenshots are using DBVisualizer.

2. Connect to the Exasol database using DBVisualizer.

3. Navigate to here to our Github page for the SQL Server Database Migration script We have many other scripts for other database dialects e.g. Teradata, Oracle, Postgres, MySQL, etc.

4. Copy the SQL file and paste into SQL Editor.

5. Run each of the sections of the SQL file.

**see the link for the full script

    --Create schema to store migration script    create schema if not exists database_migration;
    --Create database migration script    create or replace script database_migration.SQLSERVER_TO_EXASOL(...) RETURNS TABLE AS …
-- Create a connection to the SQLServer database    create or replace CONNECTION sqlserver_connection    TO 'jdbc:jtds:sqlserver://192.168.1.42:1433'    USER 'user'    IDENTIFIED BY 'exasolRocks!';
-- Finally start the import processexecute script database_migration.SQLSERVER_TO_EXASOL('sqlserver_connection',true,'AdventureWorks%','%','%',false);

6. After running the EXECUTE SCRIPT – copy and paste the output of the Lua script into a new tab in DBVisualizer.

7. Run the DDL and Import statements.

8. Update the object tree and show data has been imported.

9. Open Tableau Desktop.

10. Create a new database connection (connect to Exasol).

If you want to see a hands-on demo of this process, check out our webinar that talks you through it here.

Zachary Adda