Creating a Dataset from SQL Databases

You can import data from SQL databases into a dataset.

Prerequisites

Procedure

  1. Create a new dataset and choose a data source:
    1. Select Start of the navigation path (Main Menu) Next navigation step  Create Next navigation step  DatasetEnd of the navigation path.
    2. Select Data from a data source.
    3. Select SQL Databases.
    Note
    From the acquire data panel, select the filter icon to narrow down the number of data sources in the list. You can filter by data source type or by category.
  2. In the Create Dataset dialog, do the following:
    1. Choose a connection location.
    2. Choose a connection type.
    3. Choose an existing connection, or create a new connection.
  3. To create a new query, follow these steps:
    1. Select Next.
    2. Select Freehand SQL if you want to manually type an SQL statement, which adds a new card representing that query to the linking page.
    3. Drag and drop one or more tables from the list, or search for tables by name or description.
      When you add tables with matching primary keys, a join is created.
    4. If two or more tables are joined, you can select the (Inner Join) icon to change the type of join to one of the following options:
      • Intersecting data only (Inner Join) – This is the default option.
      • All primary data (Left Outer Join)
      • All data (Full Outer Join)
      • Remove matched data (Exception) – This option removes rows from the left-hand side table that have a match in the right-hand side table.
    5. Select Preview to review the data; if two or more tables are joined, the Joined Table Quality chart will show the number of accepted rows from the first table as well as matched values, duplicated values, and omitted values from other joined tables.
      The values are displayed on the next page.
    6. Select Hide Preview.
    7. Select View SQL to show the SQL query generated for joined tables.

      You can then select Save as Freehand SQL Query to create a freehand SQL query based on the content of the View SQL dialog.

      Note
      When creating a Freehand SQL query, if the query contains parameters that are shown with question marks, the Freehand SQL query cannot be edited.
    8. Select Next to build your query.
    9. Type a name and description for your query and select Done.
      You can continue to work on other tasks while the data is being uploaded in the background.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

Related Information