Connecting to SQL Server

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

Sisense enables easy and quick access to databases, tables and views contained with SQL databases.

You can import your data into a Sisense ElastiCube, or connect your data to a Sisense Live model, using Creating Live Models and Adding Live Connections.

Adding a SQL Server Live Connection

To add a SQL Server Live connection:

  1. Open a live model. See Creating Live Models and Adding Live Connections for more information.

  2. In the Model Editor, click . The Add Data dialog box is displayed.

  3. In the Add Live Connection dialog box, select SQL Server; the SQL Server Database Connect area is displayed.

  4. In Location, enter the computer/server IP address of the database. To connect to a database running on your own computer, enter localhost.

  5. In User Name and Password, enter your SQL database credentials.

  6. (Optional) Select Encrypt Connection to configure the driver to encrypt all communication with the SQL Server instance before sending it over the network, and then select Trust Server Certificate.

  7. (Optional) In Additional Parameters, enter additional configuration options by appending key-value pairs to the connection string.

    Example:

    • Specifying application intent: ApplicationIntent=ReadOnly
    • Specifying an alternate port, such as portNumber=1234
    • Examples for additional parameters can be found here.

  8. Click Next.

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

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

  11. Click Done; the table is added to the model.

See Publishing Live Models for information about how to publish the model and begin creating visualizations with it.

Note:

Sisense connects to your SQL database through the default port 1433. This port should be open to Sisense so a connection can be made. Contact your IT department if this port is closed.


Importing from an SQL Database

To import data from your SQL database:

  1. In the Data page, open an ElastiCube or click to create a new ElastiCube .

  2. In the Model Editor, click . The Add Data dialog box is displayed.

  3. Click SQL Server; the SQL Server Database Connect area is displayed.

  4. In Location, enter the computer/server IP address of the database. To connect to a database running on your own computer, enter localhost.

  5. In User Name and Password, enter your SQL database credentials.

  6. (Optional) Select Encrypt Connection to configure the driver to encrypt all communication with the SQL Server instance before sending it over the network, and then select Trust Server Certificate.

  7. (Optional) In Additional Parameters, enter additional configuration options by appending key-value pairs to the connection string.

    Example:

    • Specifying application intent: ApplicationIntent=ReadOnly
    • Specifying an alternate port, such as portNumber=1234
    • Examples for additional parameters can be found here .

  8. Click . A list of tables in the database are displayed. All tables and views associated with the database appear in a new window.

  9. From the Select Table list, select the relevant database you want to work with, and click Done. To view a preview of data contained in a particular table, select the table or view in the list and in click the Preview Table.

  10. After you have selected all the relevant tables, click Done.

Note:

When running a query, verify that the results correspond to the intended query. For example, verify that the date range queried returns the results from the dates selected. If this does not work as expected, there is a problem with the live provider. Querying a live preview is written in SQL and runs directly on the live provider (this is not a Sisense issue).