Looker–Power BI Connector

The Looker–Power BI Connector lets you use Microsoft Power BI Desktop to connect to data from a Looker Explore.

Setting up Power BI Desktop to connect to Looker

The general steps to use the Looker–Power BI Connector are as follows:

  1. Verify the requirements.
  2. Enable the connector on your Looker instance.
  3. Download and save the connector file: Each user who wants to access the Looker–Power BI Connector must download the Looker_1.0.0.mez file and save it in a specific directory on their computer.
  4. Set up Power BI Desktop for a custom connector: Each Power BI user must configure their Power BI Desktop security settings to use a non-certified custom connector.

The sections on this page describe these steps in detail.

After you complete the steps to connect Looker with Power BI Desktop, you can connect to Looker data from Power BI and publish reports in Power BI. You can optionally set up the on-premises data gateway to refresh your Looker reports using Power BI service (Power BI online). See the Using Power BI service with the Looker–Power BI Connector documentation page for more information.

Requirements

To set up the Looker–Power BI Connector, you need the following:

  • Microsoft Power BI Desktop installed on your computer.
  • A Looker instance that meets the following requirements:
    • The instance must be hosted by Looker. (Looker (Google Cloud core) instances are hosted by Looker and support the Looker–Power BI Connector.)
    • The instance must be running Looker 23.10 or later.
  • A Looker user account on the Looker instance with the explore permission, which is required to access Explores in Looker. If you want to work with queries with more than 5,000 rows, you also need the download_without_limit permission (see Query row limits for information on downloading limits).

Enable the connector on your Looker instance

The Looker instance you want to use with the Looker–Power BI Connector must be enabled for the Microsoft Power BI connector:

  • For Looker (Google Cloud core) instances, BI connectors are enabled by default.
  • For Looker (original) instances, BI connectors are disabled by default.

Your Looker admin can enable BI connectors on the BI Connectors panel in the Platform section of the Looker Admin menu.

Download and save the connector file

To download the connector file, follow these steps on the computer with Microsoft Power BI Desktop installed:

  1. To download the connector file, click the following link: Looker_1.0.0.mez
  2. When the download is completed, move the Looker_1.0.0.mez file to the directory [Documents]\Microsoft Power BI Desktop\Custom Connectors. (Create the folders on your computer if they don't already exist.)

Setting up Power BI Desktop for a custom connector

To set up the Looker–Power BI Connector on the Power BI Desktop side, follow the Custom connectors instructions on the Microsoft Power BI website.

As it says in the instructions, under Data Extensions, you will select the option (Not Recommended) Allow any extension to load without validation or warning. Select OK, and then restart the Power BI Desktop.

Connecting to Looker data from Power BI Desktop

Once you have downloaded the Looker_1.0.0.mez connector file and set up your Power BI Desktop application for a custom connector, you can use Power BI Desktop to connect to data from your Looker instance:

  1. From the Power BI Desktop toolbar, select Get Data > More...
  2. In the Get Data dialog, enter Looker in the search field.
  3. In the search results, click Looker (Beta) (Custom), and then click Connect.
  4. In the Connecting to a third-party service dialog, click Continue.

  5. Power BI Desktop will display a Looker sign-in dialog. In the Host field, type in the URL of your instance. For example: example.cloud.looker.com.

    Make note of the exact URL that you enter here. If you intend to set up Power BI service with the Looker–Power BI Connector, you will need to perfectly match this URL to the value you enter in the Host field when you create a connection between the on-premises data gateway and Looker data. For example, example.cloud.looker.com or https://example.cloud.looker.com or https://example.cloud.looker.com/.

  6. Optionally, use the Disable Preview Optimization drop-down to disable the Preview Optimization feature.

  7. Select the DirectQuery option to create a live connection to your data on Looker.

  8. Click OK.

  9. In the Looker dialog, click Sign in.

  10. In the Looker login screen, sign in to your Looker instance.

  11. Power BI Desktop will return to the Looker sign-in dialog, with a message that says that you are signed in. Click Connect.

  12. Power BI Desktop will display a list of the Looker models that you have access to, each represented as a folder. Click the Looker model that you want to access, and then select the checkbox next to the Looker Explore that you want to load in Power BI Desktop. To see a model, you must have Looker user access or group access to a model set that contains the model. To access Explores, you must have the Looker explore permission.

  13. Click Load.

Power BI Desktop will load in the fields from your selected Explore in the Fields section. You can then use the Looker data from the Explore to create reports in Power BI Desktop. Field names will appear in a single list in the format ViewName.FieldName.

Preview Optimization

Power BI Desktop typically runs a preview query that returns the first 200 rows of your data (the preview is basically a SELECT * LIMIT 200 query). For a Looker Explore, this can be a very large query, because Looker Explores can involve many joins and hundreds of fields. Although the LIMIT 200 argument constrains the results of the preview query to 200 rows, the preview query initiates a full table scan on your database.

The Looker–Power BI Connector uses the Preview Optimization feature to prevent Power BI Desktop from running a preview query when it connects to your Looker Explore. When the Preview Optimization feature is enabled (the default), the Looker–Power BI Connector disables Power BI Desktop's preview query, so Power BI Desktop will return an empty table for the preview query. If you want Power BI Desktop to run preview queries on your Looker Explore, you can disable the Preview Optimization feature.

To enable Power BI Desktop's preview queries, set the Disable Preview Optimization value to TRUE when you connect to Looker data from Power BI Desktop.

Monitoring the Looker–Power BI Connector

A Looker admin can view Looker–Power BI Connector usage using the Query API Client Properties group of fields in the System Activity History Explore. An entry is created in the History Explore every time a new query is run.

In the Query API Client Properties group of fields, the API Client Name shows a Power BI value to identify Looker–Power BI Connector entries.

The following is an example of a System Activity URL that shows Power BI usage. Replace <instance_name.looker.com> with your instance URL.

https://<instance_name.looker.com>/explore/system__activity/history?fields=query_api_client_context.name,user.name,history.created_date,history.created_time_of_day&f[query_api_client_context.name]=Power+BI&sorts=history.created_time_of_day+desc&limit=5000

Things to consider

Query row limits

Queries from the Looker–Power BI Connector will automatically include a LIMIT 5000 statement unless the Looker user account has the download_without_limit permission. If the Looker user account has download_without_limit, queries from the Looker–Power BI Connector have no imposed query row limit.

Explore filters

If the Looker Explore is defined with always_filter or conditionally_filter LookML parameters, the filters will be applied to queries in the Looker–Power BI Connector, even though the filters won't be visible in Power BI.

Supported dimension group timeframes

For the dimension_group of type: time, only the date and time timeframes are supported with the Looker–Power BI Connector. Other timeframes will be hidden.

Known issues

The following is a known issue with the Looker–Power BI Connector:

  • Numeric dimensions and measures both render as measures (see Dimension and measure fields for a description of dimensions and measures). To use a numeric dimension as a dimension, you must first change it to Not Summarized in Power BI Desktop.