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

If you arrived here from Workstation, see the Workstation Document Authoring Help.

You can import data from a database, Hadoop, or Google BigQuery by building your own SQL query, or script, to retrieve data from the source. An intuitive visual interface makes it easy to build the SQL query that imports your data by dragging tables, selecting columns, defining joins, and specifying filter conditions.

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 Import Data from a Salesforce Report to access Salesforce on a Mac.

To import data from a database by building a SQL query

Beginning with MicroStrategy 11.0 queries for importing data from Google BigQuery will be built using standard SQL.

Create a blank dashboard or open an existing one.
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.

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.

or

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

The Select Import Options dialog opens.

Select Build a Query and click Next. The Import from Tables dialog opens. If you selected a 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 fromTables dialog varies based on the source of the data.

In the Data Sources/Projects pane, click on the data source/project that contains the data to import.
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.
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.
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.
Click Update namespaces in the Available Tables/Datasets pane to refresh the namespaces.
Click Update in the Available Tables/Datasets pane to refresh the tables/datasets.
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.

If you select multiple tables from the same database instance that all have the same structure (that is, the same columns and data type), a dialog opens. Select whether to treat the tables as a single partitioned table.

You can add tables from multiple database instances. Select a different data source connection in the Data Sources pane and add the table as described above.
To include a column in the import, double-click it in the Query Builder pane. The column name appears in bold to indicate it is included and it appears in the Preview pane.
To include all columns in a table/dataset in the import, double-click Add all columns in the corresponding table/dataset in the Query Builder pane. The column names appear in bold to indicate they are included and they also appear in the Preview pane.
To exclude a column from the import, hover the cursor over the name of the column in the Preview pane, click the drop-down arrow, and choose Delete. The column name no longer appears bold in the Query Builder pane and is removed from the Preview pane.
To remove a table and all of its columns from the import, click Delete to the right of the table name in the Query Builder pane. The table is removed from the Query Builder pane and the corresponding columns are removed from the Preview pane.
To edit your query, see Customize Your Query While Importing Data from a Database, Hadoop, or Google BigQuery.
To create joins across multiple tables, see Define Joins between Columns in Database Tables for more information.
To perform basic functions on column data, right-click the column in the Query Builder pane. Choose Basic Functions and the function (for example, Sum, Average, Minimum). The function appears in the Preview pane for the corresponding column.
To perform more advanced functions on column data, right-click the column in the Query Builder pane. Choose All Functions, the function type, and function. Complete the fields on the Function Arguments and Expression dialogs.
To create a simple filter that determines which data to import, right-click the column to base the filter on. Choose Basic Filters and a filter. Then, enter the value(s) to compare the data to and click OK. A filter icon appears next to the data column.
To create more advanced filters based on complex expressions, see Create Advanced Filters for Importing Data from a Database.
When you are satisfied with your data selections, click Execute SQL . A preview of the data appears in the Preview pane. If you want to modify the SQL query for selecting the data, see Import Data from a Database, Hadoop, or Google BigQuery by Typing a Query .

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

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

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.
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.
View the new or updated datasets on the Datasets panel.

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

To import data from a database by building a SQL query

Related Topics