Importing SQL Data to a New or Existing Model

To import data from an SQL database to a new model or existing model, you specify the source data and then specify how you want to import the data into model dimensions.

Prerequisites

Procedure

  1. Select the appropriate option:
    • If you're creating a new model:
      1. Select Start of the navigation path (Main Menu) Next navigation step  Create Next navigation step  ModelEnd of the navigation path.
      2. Select Start of the navigation pathGet data from a datasource Next navigation step SQL DatabasesEnd of the navigation path.

    • If you're importing into an existing model:
      1. Select Start of the navigation path (Main Menu) Next navigation step  Browse Next navigation step  FilesEnd of the navigation path.
      2. Open the model you want to update.
      3. Switch to the Data Management screen.

      4. Select Start of the navigation path Next navigation step DatasourceEnd of the navigation path.
      5. Select SQL Databases.
  2. In the Import Data or Create Model dialog, do the following:
    1. Choose a connection type.
    2. Choose an existing connection, or create a new connection.
    3. Select Copy a query from a model to copy and edit an existing query, or select Create a new query to create a new query.
  3. To copy and edit an existing query, follow these steps:
    1. Select a query from the list, and 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. Make changes to the query.
    4. 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.
    5. Select Hide Preview.
    6. 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.

    7. Select OK to save your changes.
      You can continue to work on other tasks while the dataset is being uploaded in the background.
  4. 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.

    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 dataset is being uploaded in the background.
  5. When the draft data is finished uploading, open it from the Draft Data list (for a new model), or the Draft Sources list (for an existing model).

    The data appears in the data integration view.

Next Steps

After the initial import of raw data, continue with the data-preparation task before completing your model: Preparing Data.