SQL Data

Q is able to read raw data from SQL databases. Add an SQL database to your project using File > Data Sets > Add to Project > From Database (SQL).

How to add an SQL database to an existing project

1. Open the existing project.
2. File > Data Sets > Add to Project > From Database (SQL).
3. Fill in the SQL Configuration (see the rest of this page for instructions).
4. Press OK.
5. Set up your project (see Setting Up Files With No Metadata).

See also Multiple Data Files for more information on working with a project the contains multiple data files.

Creating a project using SQL as the only data source

1. Start a new project using File > Import New Data File (New Project), but select a dummy data file (e.g, just use an existing data file; it does not matter which one as it will be removed later).
2. Follow the steps in the previous section.
3. File > Remove File and remove the data file added in step 1.

Requirements

• The database must be an SQL database.
• Any machine opening the Q project must be able to connect (over the network) 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
• The ADO.NET data provider appropriate to your brand of database must be installed on any machines that will open the project. Some data providers are included with Q (Microsoft SQL Server, ODBC, OLE DB), and you can install Oracle and others on your machine (see Installing Data Providers). Displayr supports Microsoft SQL Server and MySQL, but we can add others on request.
• 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 Q on your PC, get that same SQL statement working (see Setup, below). Now you have the raw data in Q, and it will update it each time you open the project.
3. Using Q on your PC, set up the project. That is, set value labels, variables types, combine multiple-response variables together, etc.
4. Using Q on your PC, create your tables, charts, etc.
5. Displayr only: Upload the project to displayr.com. If it does not work then probably your database server is not configured to accept connections from the public Internet (or at least app.displayr.com).

Installing Data Providers

Q can use any ADO.NET data provider you install on your machine. Here are some we have tried.

Microsoft SQL Server	You don't need to install anything. This is already part of Q.
Oracle	Download the "64-bit ODAC" (or "32-bit ODAC" if on a 32-bit Windows machine) from Oracle and install it. Use the Oracle.DataAccess.Client data provider. (An Oracle data provider is supplied with Q, but it will not work on 64-bit machines and may not work with newer Oracle databases.)
MySQL	Download and install the latest MySQL Connector/Net
Amazon Redshift	Download and install the ODBC driver (you mostly likely need the 64-bit version). 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

Displayr

If your project will be uploaded to Displayr, contact Q support if you need a data provider other than Microsoft SQL Server, ODBC, OLE DB or MySQL.

Your database server will need to be accessable from the public Internet, but you can restrict it to the IP address at app.displayr.com.

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.
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 Q. If you have set up joins, then the field you are joining to will be in the ID column of the join table. 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); e.g. This query extracts age from a database, matching on customer IDs. In this example the join table (see Uploading Join IDs for SQL Data) is set to "#CUSTIDS". select C.Age from Customer C inner join #CUSTIDS I on C.CustomerID = I.ID;
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 Q 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.

How Q Interprets the Data

Each output column becomes a variable within Q. You can control the name of the variable by using as (see examples above). Q will automatically recognise date and date/time columns as Q dates, nvarchar/char as text, and everything else as numeric data. Some column types (e.g. binary data) cannot be used by Q, 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 Q, don't use select *. This will speed up your queries, reduce Q memory usage, and avoid problems that might occur with data Q cannot understand.
• While it is not possible to list the supported data types for every database vendor, Q 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.
• Q 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 Q.

See also

• Uploading Join IDs for SQL Data
• File Formats Supported by Q

Further reading: Key Driver Analysis Software