SQL Data
Jump to navigation
Jump to search
Displayr is able to read raw data from SQL databases. Add an SQL database to your project clicking either + or + Add Data Set in the Data Sets tree and then selecting 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. See Displayr Outbound IP Address.
- 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:
- Get your SQL statement working using your normal database tools.
- 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).
- Using Displayr, set up the project. That is, set value labels, variables types, combine multiple-response variables together, etc.
- 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 |
| Snowflake | Use the Snowflake.Data.Client data provider. Your connection string can look like this: account=XXX;user=XXX;password=XXX;db=XXX;CONNECTION_TIMEOUT=30 |
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.