MicroStrategy ONE

Import Data from a Database, Hadoop, or Google BigQuery by Typing a Query

You can import data from a database, Hadoop, or Google BigQuery by writing your own freeform script, or query, to retrieve data from the source.

You can import data from a database using SQL, from third-party web services using XQuery, from Salesforce.com using SOQL, or from Hadoop using HiveQL.

Beginning with MicroStrategy 11.0 you can import data from Google BigQuery using either legacy SQL or standard SQL. You must include the prefix #legacySQL or #standardSQL in your query. If no prefix is included, standard SQL is used by default.

The query prefixes #legacySQL and #standardSQL:

  • Are NOT case-sensitive.
  • MUST precede the query.
  • MUST be separated from the query by a newline character.

These are requirements of Google BigQuery SQL syntax. For multiple SQL passes, a prefix should be added to each SQL pass.

Importing data from a database requires a connection to a data source. You can create the connection before you begin importing or during the import process.

If you are importing from Google BigQuery:

  • You must activate the Google BigQuery API on the project's Google account and enable billing for the project. See the Google BigQuery documentation for more information.
  • You must have the Client ID and Client Secret that Google BiqQuery provides when MicroStrategy Web is configured as a remote access application.
  • 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. See Google BigQuery Quota Policy for the latest data restrictions.

To import data from a database by typing a query

  1. Create a blank dashboard or open an existing one.
  2. Choose Add Data > New Data to import data into a new dataset.

    or

    In the Datasets panel, click More next to the dataset name and choose Edit Dataset to add data to the dataset. The Preview Dialog opens. Click Add a new table.

    The Data Sources dialog opens.

  1. To import data from a specific database, select the corresponding logo (Amazon Redshift, Apache Cassandra, Cloudera Hive, Google BigQuery, Hadoop, etc.). If you select Pig or Web Services, go to step 5.

    or

    To import data without specifying a database type, click Databases.

    The Select Import Options dialog opens.

  1. Select Type a Query and click Next. The Import from Tables dialog opens. If you selected a specific database, only the data source connections that correspond to the selected database appear. If you did not select a database, all available data source connections appear.

    If necessary, you can create a new connection to a data source while importing your data.

    The terminology on the Import from Tables dialog varies based on the source of the data.

  1. In the Data Sources/Projects pane, click on the data source/project that contains the data to import.
  2. If your data source/project supports namespaces, select a namespace from the Namespace drop-down list in the Available Tables/Datasets pane to display only the tables/datasets within a selected namespace. To search for a namespace, type its name in Namespace. The choices in the drop-down list are filtered as you type.
  3. Expand a table/dataset to view the columns within it. Each column appears with its corresponding data type in brackets. To search for a table/dataset, type its name in Table. The tables/datasets are filtered as you type.
  4. MicroStrategy creates a cache of the database’s tables and columns when a data source/project is first used. Hover over the Information icon at the top of the Available Tables/Datasets pane to view a tooltip displaying the number of tables and the last time the cache was updated.
  5. Click Update namespaces in the Available Tables/Datasets pane to refresh the namespaces.
  6. Click Update in the Available Tables/Datasets pane to refresh the tables/datasets.
  7. Double-click tables/datasets in the Available Tables/Datasets pane to add them to the list of tables to import. The tables/datasets appear in the Query Builder pane along with their corresponding columns.
  1. Type your changes into the Query Builder pane to edit the generated script. Only include the columns you want to import.
  2. If you need to delete your script and start over, click Clear .
  3. When you are satisfied with your query, click Execute SQL to run your Freeform script. If you entered a multi-pass SQL statement, the system returns the data from the last select pass. A preview of your query results appears in the Preview pane.
  1. Click Prepare Data if you are adding a new dataset and want to preview, modify, and specify import options.

    or

    Click Add if you are editing an existing dataset.

  2. Click Finish if you are adding a new dataset and go to the next step.

    or

    Click Update Dataset if you are editing an existing dataset and skip the next step.

  3. If you are using MicroStrategy ONE Update 12 or later, the following three steps do not apply.

  4. The Data Access Mode dialog opens.

    Click Connect Live to connect to a live database when retrieving data. Connecting live is useful if you are working with a large amount of data, when importing into the dossier may not be feasible. Go to the last step.

    or

    Click Import as an In-memory Dataset to import the data directly into your dossier. Importing the data leads to faster interaction with the data, but uses more RAM memory. Go to the last step.

  5. The Publishing Status dialog opens.

    If you are editing a connect live dataset, the existing dataset is refreshed and updated.

    or

    If you are editing an in-memory dataset, you are prompted to refresh the existing dataset first.

  6. View the new or updated datasets on the Datasets panel.

Related Topics

Import Data

Import Data from a Database, Hadoop, or Google BigQuery by Building a SQL Query

Import Data from a Database, Hadoop, or Google Big Query by Selecting Tables

Best Practices for Importing Data from a File

Connect to a Data Source

How to Manage Database Connections

Edit Catalog Options Dialog