Analyze data with BI Engine and Looker

Looker is an enterprise platform for business intelligence, dataapplications, and embedded analytics. Looker helps you explore, share,and visualize your company's data so that you can make better businessdecisions.

How Looker works

Looker lets data experts at each organization describe their data usinga lightweight modeling language called LookML. LookML tells Lookerhow to query data, so everyone in the organization can create easy-to-readreports and dashboards to explore patterns of data. Looker offersadditional features for creating custom data applications and experiences.

Looker's platform works with transactional databases like Oracle andMySQL as well as analytical datastores like BigQuery, Snowflake, Redshift,and more. Looker lets you create consistent data models ontop of all your data with speed and accuracy. Looker offers a unifiedsurface to access all of an organization's data.

Looker integration with BigQuery

Looker supports hosting in Google Cloud. Because Looker isplatform independent, it connects to data in BigQuery as well as other publicclouds.

You don't need Looker to use BigQuery. However, if yourBigQuery use case includes business intelligence, data applications, orembedded analytics you might want to review Looker as a provider ofthese services.

If you already have a Looker instance running, see theinstructions for connecting Looker to BigQuery.

Get started with Looker and BigQuery

The BI Engine seamlessly integrates with anybusiness intelligence (BI) tools, including Looker. For moreinformation, seeBI Engine overview.

Create a BigQuery dataset

The first step is to create a BigQuery dataset to store yourBI Engine-managed table. To create your dataset, follow thesesteps:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, click your project.

4. In the details pane, clickmore_vertView actions, and then clickCreate dataset.

5. On theCreate dataset page, do the following:

   • ForDataset ID, enterbiengine_tutorial.

   • ForData location, chooseus (multiple regions in UnitedStates), themulti-regionlocation where public datasetsare stored.

   • For this tutorial, you can selectEnable table expiration, and thenspecify the number of days before the table expires.

     

6. Leave all of the other default settings in place and clickCreate dataset.

Create a table by copying data from a public dataset

This tutorial uses a dataset available through theGoogle Cloud Public Dataset Program. Public datasetsare datasets that BigQuery hosts for you to access and integrateinto your applications.

In this section, you create a table by copying data from theSan Francisco 311 service requests dataset. You can explore the dataset by using theGoogle Cloud console.

Create your table

To create your table, follow these steps:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, search for thesan_francisco_311 dataset.

4. Click the dataset, and then clickOverview> Tables.

5. Click the311_service_requests table.

6. In the toolbar, clickCopy.

   

7. In theCopy table dialog, in theDestination section, do thefollowing:

   • ForProject, clickBrowse, and then select your project.
   • ForDataset, selectbiengine_tutorial.

   • ForTable, enter311_service_requests_copy.

     

8. ClickCopy.

9. Optional: After the copy job is complete, verify the table contents by expandingPROJECT_NAME> biengine_tutorial andclicking311_service_requests_copy> Preview. ReplacePROJECT_NAME with name of your Google Cloud projectfor this tutorial.

Create your BI Engine reservation

1. In the Google Cloud console, underAdministration go to theBI Engine page.

   Go to the BI Engine page

   Note: If prompted to enableBigQuery Reservation API, clickEnable.

2. ClickaddCreate reservation.

3. On theCreate Reservation page, configure your BI Enginereservation:

   • In theProject list, verify your Google Cloud project.
   • In theLocation list, select a location. The location shouldmatch thelocation of the datasetsthat you're querying.

   • Adjust theGiB of Capacity slider to the amount of memory capacitythat you're reserving. The following example sets the capacity to2 GiB. The maximum is 250 GiB.

     

4. ClickNext.

5. In thePreferred Tables section, optionally specify tables foracceleration with BI Engine. To find table names, do thefollowing:

   1. In theTable Id field, type part of the name of the table that youwant accelerated by BI Engine—for example,311.

   2. From the list of suggested names, select your table names.

      Only specified tables are eligible for acceleration. If no preferredtables are specified, all project queries are eligible for acceleration.

6. ClickNext.

7. In theConfirm and submit section, review the agreement.

8. If you accept the terms of agreement, clickCreate.

After you confirm your reservation, the details are displayed on theReservations page.

Connect using Looker

1. Log in to Looker as an administrator.

2. In the Looker documentation about BigQuery,complete the following sections:

   1. Creating a service account.
   2. Configure an OAuth for a BigQuery connection in Looker.
   Note: Ensure that the service account you create uses the same billingproject as the project for which you enabled a BI Enginereservation.

3. Click theDevelop tab and selectDevelopment Mode.

4. Generate a LookML model and project for your dataset. For moreinformation, see theinstructions for connecting Looker to your database.

5. Using theExplore menu, navigate to an explore associate with the newmodel file nameExplore 311_service_requests_copy (or whatever you namedyour explore).

You have successfully connected Looker to BigQuery.You can use the System Activity feature in Looker to generate aLooker usage report and analyze the performance of your queriesagainst BigQuery-specific performance metrics.To explore various BigQuery BI Engine query performance metrics, seeBigQuery BI Engine metrics.

Clean up

To avoid incurring charges to your Google Cloud account for the resources usedin this quickstart, you can delete the project, delete theBI Engine reservation, or both.

Delete the project

The easiest way to eliminate billing is to delete the project that you createdfor the tutorial.

To delete the project:

1. In the Google Cloud console, go to theManage resources page.

   Go to the BI Engine page

2. In the project list, select the project that you want to delete, and thenclickDelete.

3. In the dialog, type the project ID, and then clickShut down to delete'the project.

Delete the reservation

Alternatively, if you intend to keep the project, then you can avoid additionalBI Engine costs by deleting your capacity reservation.

To delete your reservation, follow these steps:

1. In the Google Cloud console, underAdministration go to theBI Engine page.

   Go to the BI Engine page

   Note: If prompted to enableBigQuery Reservation API, clickEnable.

2. In theReservations section, locate your reservation.

3. In theActions column, click themore_vert icon to the right of your reservation and chooseDelete.

4. In theDelete reservation? dialog, enterDelete and thenclickDELETE.

What's next

There are many additional options related to administeringLooker, customizing its data model, and exposing data to users.For more information, see the following resources:

• Looker documentation
• Looker best practices
• Looker training