Blog| Matillion ETL for BigQuery

Using the Dynamics NAV Query Component in Matillion ETL for BigQuery

dynamics nav matillion bigqueryMatillion uses the Extract-Load-Transform (ELT) approach to delivering quick results for a wide range of data processing purposes: everything from customer behaviour analytics, financial analysis, and even reducing the cost of synthesising DNA.

The Dynamics NAV Query component in Matillion ETL for BigQuery presents an easy-to-use graphical interface, enabling you to pull data from Microsoft Dynamics NAV directly into BigQuery. Customers are using this to pull their ERP data into BigQuery, to combine with other data from different sources to give a complete view of their business.

The connector is completely self-contained: no additional software installation is required. It’s within the scope of an ordinary Matillion license, so there is no additional cost for using the features.

 

Dynamics NAV Query Component in Matillion ETL

 

URL

The first step in configuring the Dynamics NAV Query component is to give Matillion your Dynamics NAV system’s URL. This will allow Matillion to connect into it. Include the port number at the end of the URL:

 

Dynamics NAV Query Component in Matillion ETL - URL

 

Authentication

Next Matillion must have the authentication to access Dynamics NAV. You provide access by authenticating with a Dynamics NAV user. The selected user must have the necessary permission to read the required data. The Username and Password is input into the component:

 

Dynamics NAV Query Component in Matillion ETL - authentication

 

Server Instance

This is the NAV Server instance name to connect into:

 

Dynamics NAV Query Component in Matillion ETL - Server instance

 

Data Source

Next you can choose the data you want to load into BigQuery from the Data Source drop down. This is a list of the tables in the Dynamics NAV system:

 

Dynamics NAV Query Component in Matillion ETL - Data Source

 

After choosing the data source, the next step is to choose the required columns from the table in the Data Selection. This will form the new table which is created in BigQuery.

 

Dynamics NAV Query Component in Matillion ETL - Data Selection

 

Data Source Filter

You can add a filter to the data you are bringing through into BigQuery using the Data Source Filter.

Connection Options

The driver supports some additional parameters you may want to explore. However, none of the Connection Options are mandatory and the Dynamics NAV driver usually gives you sensible defaults. Should you wish to explore your Connection Options, further details on the options are available here.

Running the Dynamics NAV Query component in Matillion ETL for BigQuery

Before you can run the component, you need to name the Target Table. This is the name of the new table you are creating to write the data into in BigQuery. Finally you need to specify a Cloud Storage Staging Area. This is a Cloud Storage bucket which will temporarily store the results of the query before the data is loaded into BigQuery.

This component also has a Limit property which you can use to force an upper limit on the number of records returned. We recommend using either a limit or a filter to reduce the number of rows returned and to improve the speed of your job.

You can run the Orchestration job, either manually or using the Scheduler, to query your data and bring it into BigQuery.

 

 

Dynamics NAV Query Component in Matillion ETL - Run Successful

 

Advanced mode

The Dynamics NAV Query component offers an “Advanced” mode instead of the default “Basic” mode. In Advanced mode, you can write a SQL-like query over all the available fields in the data model. This is automatically translated into the correct API calls to retrieve the data requested.

 

Dynamics NAV Query Component in Matillion ETL - Advanced mode

 

Dynamics NAV Query Component in Matillion ETL - SQL

 

Transforming the Data

Once you have brought through the required data from Dynamics NAV into BigQuery, you can use it in a Transformation job, perhaps to combine with existing data:

 

Dynamics NAV Query Component in Matillion ETL - Transformation

 

In this way, you can build out the rest of your downstream transformations and analysis, taking advantage of BigQuery’s power and scalability.

Useful Links

Dynamics NAV Query Component in Matillion ETL for BigQuery
Integration information
Component Connection Options

If you’re a new or existing BigQuery user, download our free eBook below. The 60+ page guide has loads of useful advice and best practices on how to get the most out of your data warehouse setup.

Matillion-Optimizing Google BigQuery - Email banner