Connecting to MySQL
  • 16 May 2022
  • 3 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Connecting to MySQL

  • Dark
    Light
  • PDF

Note:

This topic describes how to import data into Sisense's ElastiCube, and how to use Live Connect.

Sisense enables easy and quick access to databases, tables and views contained with MySQL databases. Sisense supports connections to MySQL V5.6 and later. However, to use custom columns or tables in Live with MySQL, then you need MySQL V8.0+.

Note:

For the list of supported connectors, see Introduction to Data Sources.

You can import your data into a Sisense ElastiCube, or connect your data to a Sisense Live model, using Live Connect.

Importing Into a Sisense ElastiCube

To import data from your MySQL database:

  1. In the Data page, open an ElastiCube or create a new ElastiCube.
  2. In the Model Editor, click ; the Add Data dialog box is displayed.

Add_Data_dialog.png

  1. Click MySQL; the MySQL Connect area is displayed.

MySQL_dialog.png

Note:

The connection options available to you depend on your Sisense deployment.

  1. Enter the following details:

    • Location: Enter the computer/server IP address of the database. To connect to a database running on your own computer enter localhost.
    • If Windows Authentication is configured with the database in the User Name and Password fields, enter your database credentials.
  2. (Optional) Select Use SSL if you are connecting to an SSL server. Enter the relevant information to configure verification between the client and the MySQL server over SSL:

    • In the Private Key field, enter the path of the location of your file containing the your private key.

    • In the Certificate field, enter the path of the location of your PEM file containing the client’s certificate.

    • In the Authority field, enter the path of the location of your PEM file containing the trusted SSL certificate authority.

  3. Click ; a list of tables in the database are displayed. All tables and views associated with the database appears in a new window.

  4. From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or view to see a preview of the data inside it. When you select the table or view, two new options are displayed at the bottom of the list, Import Relationships and Add Custom Import SQL .

  5. (Optional) By default, existing relationships between tables are automatically replicated in the ElastiCube . You can disable this by toggling the Import Relationships switch.

  6. (Optional) Click + to customize the data you want to import with SQL. See Importing Data with Custom Queries for more information.

  7. After you have selected all the relevant tables, click Done . The tables are added to your schema in Sisense.

Connecting Data to a Sisense Live Model

To add a MySQL live connection:

  1. Open a live model. See Creating Live Models and Adding Live Connections for more information.
  2. In the Model Editor, click .
  3. In the Add Live Connection dialog box, select MySQL.
  4. Under the Connect tab, enter the following credentials:
    • Location: The IP address of your data source.
    • User Name: The user name to access data source.
    • Password: The password to access your data source.
  5. Click Next.
  6. Under the Select Data tab, on the left side is a list of schemas located in your data source. Select the relevant schema. You can find the schema easily by searching for it through the search field at the top of the list to filter the schemas displayed. After selecting the schema, a list of the available tables and views are displayed.
  7. Select a table or view. You can find the table easily by searching for it through the search field at the top of the list to filter the tables displayed.
  8. Click Done. The table is added to the model.
    See Publishing Live Models for information on how to publish the model and begin creating visualizations with it.

Limitations

  • Performing a top/bottom ranking filter on widgets requires version 8 or higher of the live connected MYSQL database. As a workaround for this, you can import your data into the ElastiCube.

Sisense uses MySQL JDBC Driver to establish the connection with the MySQL data source (applies to both ElastiCube and Live).
For information on additional parameters that can be used, see this section of the MySQL Connector Developer Guide.


Was this article helpful?