Connect to a Database Data Source

From DMXDeepInsight
Jump to: navigation, search

Before You Begin

  • Check the database data sources DeepInsight supports.
  • Ensure the data conforms to the Data Source Limits.
  • Ensure you have this information:
    • Name of the server hosting the database and port number
    • Database name
    • Username and password
    • Note: Connection information like credentials is saved so you can easily create multiple data sets from the same data source without having to re-configure the data source.

  • Check whether you have database drivers installed on your computer. DeepInsight needs a JDBC driver to connect to a database. If the driver is not already installed on your computer, then you can download it from the vendor’s website.

Create Database Data Source

Database data source connections can be created in three simple steps.

Step1: Create Connection

  1. Open the Database tab.
  2. Select your database.
  3. We need:
    • the connection name, database type, and database name from where the data should be imported. You can add multiple database/schemas in the database name field separated by a comma. DeepInsight will retrieve only those from the database. But if you leave this field blank, then DeepInsight retrieves all databases/schemas from the database.
    • the IP address of the host where the database instance is running and the port number database is using.
    • the username of the database for accessing the database instance and the corresponding password for the username provided.
  4. Click Test Connection, to establish a connection with the database.
  5. After the connection is established, click Save & Proceed. DeepInsight saves the database information in the My Connections pane.

    If the connection is not established, follow these tips for troubleshooting.

    • Check if the database is running.
    • Check if the server name or the port number is misspelled or incorrect.
    • Check if a firewall is blocking the entered port number.
    • Contact your system administrator if none of the issues mentioned above resolves connection issue.

Step 2: Build Query

Now we create a dataset by importing data from tables/schema listed in the Datasets panel and creating and executing a SQL query.

  1. Type a name for your dataset. Remember this name cannot be edited again so be sure about it.
  2. On the left side of the page, expand the Data Sources node and other nodes inside it, to import tables.
  3. If you want to work with data from more than a single table, then you can also join tables. Drag-and-drop selected tables to the Table Visualizer area and create a join. You also use the Where clause while creating the dataset.
  4. If required, you can limit the max results of the returned query.
  5. After you are done, execute your query.

Step 3: Configure and Save

After you save the dataset:

  • DeepInsight lists the dataset in the Datasets panel, along with its import status. You can also see a notification related to it in the notification center.
  • DeepInsight prompts you to create a new dataset or create/open a workbook.

If the query executes successfully: DeepInsight imports data. The color code of the dataset changes from yellow to green in the Datasets panel and shows a success notification in the notification center. During import, DeepInsight automatically identifies a format for the data and categorizes it into different datatypes. You can change the default mapping before saving the dataset. If the query does not execute successfully: DeepInsight changes the color code of the dataset from yellow to red in the Datasets panel and shows a failed notification in the notification center.

About Adding Multiple DB names

You can add multiple comma-separated database names while creating a DB connection. DeepInsight retrieves only those databases that are user-defined and display them in the Data Sources node. This table describes DeepInsight’s behavior of retrieving databases based on user-defined values.

Scenario Result
Single database name DeepInsight retrieves database.

Same behavior applicable to all databases.

Multiple databases with an incorrect separator. For example: node;nemo;starkfly DeepInsight retrieves all databases in the database.
Comma-separated multiple databases with incorrect names DeepInsight retrieves all databases in the database.
Single database with the incorrect name DeepInsight retrieves all databases in the database.
Comma-separated multiple databases with only one correct database name DeepInsight retrieves the database with the correct name.
Multiple databases with only one correct separator. For example: node;nemo,starkfly DeepInsight retrieves database with comma-separator. For example: nemo,starkfly

Create New Datasets Without Re-specifying Connection Information

  1. On the Configure page, click the Database tab.
  2. In the My Connections panel, click the configured database connection using which you want to create a new dataset. The database connection details are displayed.
  3. Click Test Connection and then click Update and Proceed.
  4. Build and execute your query.
  5. Configure and save your new dataset.
  6. DeepInsight prompts you to create a new dataset, add or open a new workbook.

After you save the dataset,

  • DeepInsight lists the dataset in the Datasets panel, along with its import status. You can also see a notification about it in the notification center.
  • DeepInsight prompts you to create a new dataset or create/open a workbook.

Edit Connection Details

You can edit database connection details anytime and during any phase of your analysis. You can:

  • edit the database name and description
  • edit the hostname, port number, username, and password fields

Follow these instructions to edit a database data source connection.

  1. On the Configure page, click the Database tab.
  2. In the My Connections panel, click the configured database connection using which you want to create a new dataset. The database connection details are displayed.
  3. Edit connection details.
  4. Click Test Connection and then click Update and Proceed.
  5. Perform steps required to build a query.
  6. Perform steps required to save the dataset.

Edit a Dataset

You can edit the SQL query used for creating the dataset.

  1. On the Configure page, in the Datasets panel, click the DB dataset to update its query.
  2. In the SQL box, you can type the new query, or paste a copied query.
  3. Click Execute.
  4. (Optional) Map datatypes.
  5. Click Update.

    Note: If the update succeeds, then DeepInsight will import data from the data source. DeepInsight changes the color code indicating the status of data import in the Datasets panel. You can view the data import failed, or success message in the notification center.

Delete a Dataset

Before you delete a dataset, you must first delete the visualizations created on it otherwise DeepInsight will show a warning error.

  1. On the Configure page, in the Datasets panel, click the database dataset you want to delete.
  2. Click <icon> or remove the dataset from the Data Sources field. DeepInsight will show a confirmation message on the UI.

Delete a Database Connection

When you delete a database connection, the connection between DeepInsight and database data source is deleted, and the database data source is immediately removed from DeepInsight.

Special Conditions

There could be conditions because of which a database connection cannot be deleted. This table lists those conditions and instructions you will perform before you can delete a database connection.

Condition Instruction
Your connection has linked datasets.
  1. Delete the linked datasets.
  2. Delete the data source connection.
You have created worksheets using a dataset.
  1. Delete worksheets.
  2. Delete datasets.
  3. Delete the data source connection.
You have added your worksheets to dashboards.
  1. Remove worksheets from dashboards.
  2. Delete worksheets created on the dataset you want to delete.
  3. Delete dataset.
  4. Delete the data source connection.