Importing data from Google BigQuery

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

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

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:

  3. Click Google BigQuery.

  4. If you are not logged into your Google account, sign in using your Google username 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.

  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.

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

  4. Select one of the following:

  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.

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:

  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

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:

  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.

  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:

  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:
      bigquery_sql_join.gif

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

  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.