Version: 2019

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

You can import data from a database, Hadoop, or Google Big Query by selecting tables to retrieve data from the source. An intuitive visual interface makes it easy to build to import your data by dragging and dropping tables.

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.

You can also use the following procedure to access a Salesforce database on a Windows machine. See How to Import Data from a Salesforce Report to access Salesforce on a Mac.

To import data from a database by picking relational tables

  1. Create a blank dossier 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, the Import from Tables dialog opens, bypassing the Select Import Options dialog, allowing you to type a query to import a table. If you select SAP Hana, you must build or type a query, instead of selecting tables.

    or

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

    The Select Import Options dialog opens.

  1. Select Select Tables 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.
  2. 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. 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. 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.

  4. 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.

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

Related Topics

Import Data

Best Practices for Importing Data from a File

Connect to a Data Source

How to Manage Database Connections

Edit Catalog Options Dialog

How to Set User Privileges for Shared Data Sources