SQL Data

Displayr is able to read raw data from SQL databases. Add an SQL database to your project using Insert > New Data Set > SQL.

Requirements

• The database must be an SQL database.
• Displayr must be able to connect (over the public internet) to the database server. This may require your network administrators to reconfigure firewalls.
• You must know:
  • type of database (brand, e.g. Microsoft SQL Server)
  • server name
  • database name
  • user name
  • password
• You need to know how to to write SQL queries, or have someone who can help you.

Overview

The overall process looks something like this:

1. Get your SQL statement working using your normal database tools.
2. Using Displayr, get that same SQL statement working (see Setup, below). Now you have the raw data in your Displayr project, and it will update it each time you open the project. If the data fetch fails then your database server is not configured to accept connections from the public Internet (or at least app.displayr.com).
3. Using Displayr, set up the project. That is, set value labels, variables types, combine multiple-response variables together, etc.
4. Using Displayr, create your tables, charts, etc.

Setup

Name	Whatever name you will use to refer to this data.
Data provider	The name of the ADO.NET data provider to use to connect to your database. This must match the type of database being used. e.g. System.Data.SqlClient for SQL server. See Data Providers below.
Connection string	Identifies the server, user name, password, etc required to get to your database. e.g. Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=True; You can find more examples at http://www.connectionstrings.com.
SQL command	An SQL query that will return the data you want to use in Displayr. e.g. This query finds the number of completed respondents for the last 40 days, broken down by date (MySQL): select date(Updated), count(*) as CountInQuota from Respondent where Updated > adddate(curdate(), -40) and Progress = 'Q' group by Date(Updated);
Maximum cases	Limits the number of rows of data that will be fetched from the database. This helps protect you from queries that explode into enormous numbers of rows, and clog up both Displayr and your database.
Automatically refresh every	The number of hours old that your data may be before Displayr will automatically refresh it. The dashboard will slow down while refreshing data, so don't set this lower than necessary.

Data Providers

Each database vendor requires a different data provider. The table below maps vendors to a data provider.

Vendor	Data Provider
Microsoft SQL Server, Oracle, MySQL, Postgres, etc	Use the data provider with a matching description.
Amazon Redshift	Use the System.Data.Odbc data provider. Your connection string should look like this: Driver={Amazon Redshift (x64)}; Server=XXX.redshift.amazonaws.com; Database=XXX; UID=XXX; PWD=XXX; Port=5439

Contact Displayr support if you need a data provider other than those listed above.

How Displayr Interprets the Data

Each output column becomes a variable within Displayr. You can control the name of the variable by using as (see examples above). Displayr will automatically recognise date and date/time columns as Displayr dates, nvarchar/char as text, and everything else as numeric data. Some column types (e.g. binary data) cannot be used by Displayr, and will cause an error.

Suggestions

• A password is usually included in the connection string, so have your database administrator set up a database user account that is only able to read data, and only the data you need.
• Don't experiment with your SQL if you are attaching to an important database - have a database administrator help you.
• Only select the columns you need in Displayr, don't use select *. This will speed up your queries and avoid problems that might occur with data Displayr cannot understand.
• While it is not possible to list the supported data types for every database vendor, Displayr will generally accept text, numeric and date/time data. e.g. VARCHAR, CHAR, NUMERIC, DATETIME.
• Use where clauses to fetch only the rows you need.
• Displayr provides no help for getting your SQL right. Therefore get your query working in a proper database tool first, and only then paste it into Displayr.