Read the release
 
 

by Jerod Johnson | August 18, 2016

Access Your Data Through the Power BI Gateway

Do you have on-premises or cloud-based data that you would love to access from Power BI (or any of the new Microsoft workflow tools like PowerApps, Azure Logic Apps, and Flow)? Then our drivers are just the products for you. Paired with the Microsoft Power BI gateway, the CData JDBC and ODBC Drivers and the Sync Application let you access your data exactly where you want.

This article will discuss connecting to your data through the Microsoft Power BI gateway using two distinct methods. First, using the CData Sync Application and second, using the CData JDBC or ODBC Drivers. We will reference QuickBooks as the data source, but the principles can be applied to any of the more than 60 data sources that we support.

Connecting to Your QuickBooks Data

Option 1: Using the CData Sync Application

The CData Sync Application for QuickBooks (free 30-day trial) lets you replicate your QuickBooks data to a SQL Server database. To do so, you will need our Sync Application and access to a SQL Server database Replicating your QuickBooks data takes three simple steps:

  1. Configure your connection to QuickBooks (Online Help for a local QuickBooks instance and a networked or remote instance).
  2. Configure your connection to the SQL Server database (Online Help).
  3. Select tables, set a replication interval, and click Run.

With your data replicated to a SQL Server database, you can make use of the DirectQuery for SQL Server feature of the Power BI gateway and you can configure the Sync Application to ensure that the data in the SQL Server database is, at most, 15 minutes old.

Option 2: Using the CData JDBC Driver

The JDBC Driver for QuickBooks (free 30-day trial) comes with a built-in command-line interface (CLI) for running a MySQL proxy for your QuickBooks data. You can either configure the proxy with an INI file or configure the proxy directly using the CLI. Both configurations are described in the Online Help documentation (INI file, CLI). In either case, you start the MySQL proxy by using a command similar to the following:

java -jar cdata.jdbc.quickbooks.jar [ options ]

Option 3: Using the CData ODBC Driver

The ODBC Driver for QuickBooks (free 30-day trial) comes with an installed service and CLI that will act as a MySQL proxy for your QuickBooks Data. Like the JDBC Driver, you can configure the proxy using an INI file or using the CLI. See the Online Help for more information (INI file, CLI). You can start the MySQL proxy by starting the CData QuickBooks MySQL Remoting service manually or running the following command:

CData.ODBC.QuickBooks.Remoting.exe [ options ]

With a MySQL proxy for your QuickBooks data, you can utilize the native connectivity to MySQL databases from the Power BI gateway. It is worth mentioning that the on-premises data gateway does not support live/DirectQuery connections to MySQL databases, so it will be up to you to manually refresh or schedule a refresh of the data in Power BI.

QuickBooks Data in Power BI Using the On-Premises Data Gateway

This section gives an abridged walk-through for connecting to your data through an Enterprise Power BI Gateway. For more thorough instructions, please refer to the documentation found on the Power BI site:

Our abridged instructions follow.

Step 1: Install the Power BI On-Premises Data Gateway

The first step will be installing the Power BI on-premises data gateway onto your machine. You will need to install the gateway on the machine that contains the SQL Server database or on which the MySQL proxy is running. It would be ideal to install the on-premises data gateway, but you will be able to get some base functionality if you are using a personal gateway. For more information, and to download the gateway, visit the Power BI site or choose Download Data Gateway from Power BI Online.

Step 2: Add a Data Source in Power BI Online

Once you have installed the Gateway, you can add a data source to the Gateway by signing in to Power BI Online and selecting the Manage Gateways option from the Settings menu (the gear icon towards the top-right of the page).

With a Gateway selected, click on +ADD DATA SOURCE and fill in the appropriate fields. You can use the in-network name or IP address of the Server machine hosting the data. When using the CData ODBC or JDBC driver, you will need to use the Basic authentication method, connecting with the username and password that you configure for the MySQL proxy. If you have replicated your data to a SQL Server database, then you will need to use the appropriate credentials for accessing the SQL Server database (either Windows credentials or database credentials).

Step 3: Connect to the Data Source from Power BI Desktop

Now that you have added data sources via the on-premises data gateway, you will need to publish the data sets that you wish to access from Power BI. To do so, You will need to install Power BI Desktop and access the data in the SQL Server database or the data exposed by the MySQL proxy. Click Get Data, select the appropriate data source (SQL Server Database or MySQL Database), and enter the appropriate credentials, including the Server name, database, and user/password.

Microsoft offers the following instructions when it comes to connecting to the data source and creating the data set:

Note: Server and database name have to match between Power BI Desktop and the data source within the on-premises data gateway!

The link between your dataset and the data source within the gateway is based on your server name and database name. These have to match. For example, if you supply an IP Address for the server name, within Power BI Desktop, you will need to use the IP Address for the data source within the gateway configuration. If you use SERVER\INSTANCE, in Power BI Desktop, you will need to use the same within the data source configured for the gateway.

Step 4: Publish a Dataset to Power BI from Power BI Desktop

Once you have connected to the data source from Power BI desktop, you are ready to publish a new Dataset to Power BI Online. While connected to a data source, select the Tables you wish to publish as a dataset (they will be named in the format [database].[table_name]) and click Load. With the data loaded, click the Publish button. You will now be able to access the data from Power BI, through the on-premises data gateway.

If you have configured a DirectQuery connection (i.e.: to a SQL Server database), then any time the SQL Server database is updated, you will immediately have access to the updated data through the published Dataset. If you have configured another type of connection (i.e.: to the MySQL proxy for the ODBC or JDBC driver), then you will need manually update the dataset (or configure the dataset to refresh) in order to see any local changes to the data.

Creating Visualizations of Your Data in Power BI

With the Dataset linked to Power BI, you are now able to create visualizations, reports, and dashboards based on your data. The chart below shows the balances in a QuickBooks company listed by name. This is a very simple example of the powerful visualization features available in Power BI.

More Information

Read can read more information, and see a complete list of the data sources we support, on the platform-specific pages on our website:

We offer free 30-day trials for all of our products, so find your data source, download a trial, and start working with your data in Power BI today!