Importing data from Google BigQuery

You can import data from Google BigQuery to create dashboards, reports, and documents. You can:

  • Import data from a BigQuery project while creating a dashboard or a document. Your imported data is saved and can be used immediately in the dashboard or the document. For steps to create a dashboard, see Creating a dashboard. For steps to create a document, see the Document Creation Guide.

  • Import data from a BigQuery project and save the data to a folder location. If desired, you can create a dashboard, report, or document immediately from the imported data.

You can import data from Google BigQuery into MicroStrategy Web by:

When you import data into MicroStrategy Web, the data is saved as an Intelligent Cube. For background information on Intelligent Cubes, see the OLAP Services Guide.

Prerequisites

  • You must have the Web Import Data and Web Import Database privileges.

  • Your Administrator must have enabled importing data from Google BigQuery into MicroStrategy Web. Steps to do so are included in the Installation and Configuration Guide as part of deploying MicroStrategy Web.

  • When an administrator configures MicroStrategy Web as a remote access application, Google BigQuery provides a Client ID and a Client Secret. This information is required to complete the steps to  configure the OAuth parameters for Google BigQuery.

  • You must activate the Google BigQuery API on the project's Google account and enable billing for the project. For steps, refer to the BigQuery documentation.

  • Google BigQuery restricts the amount of data you can import at one time. If your project contains a large amount of data, consider adding filters to your query to retrieve only the data you need. For the most up-to-date data restrictions, see  https://developers.google.com/.  

To import and publish data from Google BigQuery by picking tables

  1. Log in to the MicroStrategy project in which you want to import data.

  2. Do one of the following:

    • To import data into a dashboard to use as a dataset:

      1. Click the name of the dashboard to run it.

      2. From the toolbar, click the arrow next to the Add Data icon Add Dataset icon, and then select Import Data. The Connect to Your Data page opens.

    • To import data into a document to use as a dataset:

      1. Open the document in Design Mode or Editable Mode.

      2. In the Dataset Objects panel, click the Add Dataset icon Add Dataset icon. The Select Dataset dialog box opens.

      3. Click Import new data. The Connect to Your Data page displays.

    • To import data into the project: On any page, click Create on the icon bar on the left, and then click Access External Data. The Connect to Your Data page opens.

    • Select the data source

  3. Click Google BigQuery.

  4. If you are not logged into your Google account, sign in using your Google user name and password.

  5. If a message is displayed asking you to allow MicroStrategy to access your Google BigQuery data, select Allow Access. The Select Import Options dialog box opens.

  6. Select Pick Tables and then click Next. The Import from Tables page opens.

  7. From the Projects panel on the left, select the project that contains the data to import. A list of the tables in the selected project is displayed in the Available Datasets panel.

  8. To display only the tables in a single Google BigQuery dataset in the Available Datasets panel, select the dataset's name from the drop-down list. To search for a dataset, type the name of the dataset in the field.

  9. To view a list of columns in the table, click the arrow icon next to the table name. Each column in the table is displayed, along with its data type. You can filter the list of database tables by typing the name of a table in the search field.

    • Select the data to be imported

  1. To add a table to your imported data, click and drag the name of the table from the Available Tables panel to the Editor panel in the middle of the page.

  2. If you want to preview and modify your data before importing, click Prepare Data. A preview of the data to import is displayed on the Preview page. You can modify your data before it is imported. For example, you can define data columns as attributes or metrics, insert column headers, and so on.  For steps, see Previewing your data and specifying data import options.

    • Save the imported data

  3. Click Finish. The Save Data Options dialog box opens.

  4. Select one of the following:

    • To connect live to the database to retrieve data on the fly, click Connect Live. The Intelligent Cube will be saved without importing data. Connecting live is useful if you are working with a large amount of data, when importing into the dashboard may not be feasible.

    • To import all the data into your dashboard now, click Import as an In-memory Dataset. The Intelligent Cube contains the imported data. Importing the data leads to faster interaction with the data, but more RAM memory will be used.

    • The Save As dialog box opens.

  5. Browse to the location to publish the imported data to, then type a name and description for the published data in the Name and Description fields.

  6. Click OK. Your data is saved and published as an Intelligent Cube.

    • If you imported data while creating a dashboard or a document, the data is added to the dashboard or the document as a dataset. For steps to work with datasets on a dashboard, see Adding and removing datasets from a dashboard. For steps to work with datasets on a document, see the Adding Text and Data to Documents chapter in the Document Creation Guide.

    • If you are not creating a dashboard or a document, the Data Imported page is displayed. You can use the imported data to create a dashboard, report, or document. To do so, select one of the following:

Customizing your query while importing data

By default, when you select data to import by dragging and dropping tables, selecting columns, and so on, MicroStrategy automatically generates the SQL query required to import your data from the database. You can view and edit the query directly to customize the way your data is imported.

To customize your query when importing data

  1. Click the Edit SQL icon Edit SQL icon. The query that will be used to import your data is displayed in the editor panel.

  2. Type your query in the editor pane as you would when importing data using a Freeform script. As you type your custom query, keep in mind the following:

    • Queries are written as single-pass SELECT statements. Other standard SQL statements, such as CREATE, are not supported by Google BigQuery. For a list of supported SQL clauses, functions, and operators, see the BigQuery Query Reference.

    • When aggregating over a column that has a large number of distinct values, BigQuery recommends that you use a GROUP EACH BY clause instead of a GROUP BY clause.

    • If you are creating a join between tables, the syntax of the JOIN clause you write depends on the size of the tables you are joining. It is helpful to know before creating a join whether the tables contain more than 8 MB of compressed data, Google BigQuery's maximum for tables joined with the default JOIN clause. Lookup tables typically do not contain more than 8 MB of data, but fact tables may. If you do not know the size of the tables you are joining, see Combining data in tables with joins for steps to create a join between tables without knowing their size.

      • If you are creating a join on two tables and one of the tables contains less than 8 MB of data, the smaller table must be on the right side of the JOIN clause. In the sample code shown below, the underlined table is the smaller table.
        bigquery_sql_join_small_table.gif

      • If both tables contain less than 8 MB of data, the order of the tables in the JOIN clause does not matter.

      • If you are creating a join on two tables and both tables contain more than 8 MB of data, use a JOIN EACH clause, for example, FROM table1 JOIN EACH table2.

      • If you are combining data from three or more tables and only one table contains more than 8 MB of data, use JOIN clauses, with the large table on the left side of the first JOIN clause. In the sample code below, the underlined table is the largest table.

      • If you are combining data from three or more tables and two or more tables contain more than 8 MB of data, use JOIN EACH clauses, for example, FROM table1 JOIN EACH table2 JOIN EACH table3.

  3. To run your custom query, click the Execute SQL icon Execute SQL icon. A preview of your query results is displayed in the Data Preview pane below.

  4. Once you are satisfied with the results, you can finish importing your data, starting with Save the imported data.

To undo customizations to your query

  1. If you have chosen to edit your query directly, you can undo your customizations and return to selecting the data that you want to import by dragging and dropping data on the editor panel. To do so, click the Convert to Query Builder icon Convert to Query Builder icon. A confirmation message is displayed, notifying you that any changes that you made while manually editing the query will be reverted.

  2. Click OK.

Combining data in tables with joins

You can combine the data in two tables by creating a join between the tables. For example, if the first table contains City and Revenue columns, and the second table contains City and Profit columns, you can relate the data in the tables by creating a join between the City columns.

To combine data in three or more tables, create a join between two of the tables, then create a join between one of those two tables and a third table, and so on, until all of the tables are joined.

The syntax of the JOIN clause you write depends on the size of the tables you are joining, so it is helpful to know before creating a join whether the tables contain more than 8 MB of data, Google BigQuery's maximum for tables joined with the default JOIN clause. Lookup tables typically do not contain more than 8 MB of data, but fact tables may. The procedure below provides steps to create each type of join, depending on the size of the tables you are joining. The procedure also provides steps to create a join when you do not know the size of the tables you are joining.

Prerequisite

  • The steps below assume that you have connected to a Google BigQuery project and added at least two tables to the editor pane. For steps to import data from a Google BigQuery project, see Importing data from Google BigQuery.

To create a join to combine data in tables

  1. If you know the size of the tables you are joining, do one of the following:

    • If one table contains more than 8 MB of data and the other table does not, click and drag the name of the column from the larger table onto the column from the smaller table. An inner join is created and a line representing the join is displayed in the editor pane, running from the first column to the second column.

    • If both tables contain more than 8 MB of data or both tables contain less than 8 MB of data, click and drag the name of the column from one table onto the column from the other table. The order does not matter. An inner join is created and a line representing the join is displayed in the editor pane, running from the first column to the second column.
      If the tables you joined contain more than 8 MB of data, edit the SQL query used to import your data, as described in the following steps.

  2. If you do not know the size of the tables you are joining, click and drag the name of the column from one table onto the name of the column from the second table. Test whether the join you created is valid by clicking the Execute SQL icon Execute SQL icon.

    • If a preview of your data displays in the Data Preview pane below, then the join is valid and was successfully created. The second table you joined contains less than 8 MB of data.

    • If an error displays stating that the JOIN operator's right-side table must be a small table, then the second table you joined is too large and the join could not be created.
      Click the join and select Delete. Create a new join by clicking and dragging the name of the column from the second table onto the first table. Click the Execute SQL icon Execute SQL icon.

      • If a preview of your data displays in the Data Preview pane below, then the join was successfully created.

      • If the error message displays a second time, then both tables you are joining contain over 8 MB of data. Edit the SQL query used to import your data, as described in the following steps.

  3. You can change the type of join used or delete the join. You can create inner joins or left outer joins between two tables, as described below. Google BigQuery does not support other join types, such as a full outer join or right outer join. In addition, Google BigQuery uses the default equals (=) operator to compare columns and does not support other operators.

    To change the type of join used or delete the join, click the line representing the join and select one of the following:

    • To only include records in which the joined columns from both tables satisfy the join condition, select Inner Join (default).

    • To include all records from the column in the first table and only those records from the column in the second table in which the join condition is satisfied, select Left Outer Join.

    • To delete the join, select Delete.

  4. If you are creating a join between two tables that contain more than 8 MB of data, you must edit the SQL query used to import your data. Do the following:

    1. Add any additional columns of data that you want to import and create any filters, aggregations, or expressions based on the columns you are importing.

    2. Once you have defined the data you want to import, from the top of the editor pane, click the Edit SQL icon Edit SQL icon. The SQL query used to import your data is displayed in the editor pane, as shown in the example query below:

    3. After the word JOIN, highlighted in blue, type EACH, as shown below:

  5. Continue importing your data, as described in Select the data to be imported, or customizing your query, as described in Customizing your query while importing data.