What Databases can I use InSights with?
InSights Into Data in Version 1.1 supports:
- Microsoft SQL Server
- MySQL & MariaDB
In version 2.x we will be adding support for other databases, such as DB2, SQLite, MS Access, Sybase, and OLE/ODBC connections.
Can I use InSights with our company data?
If your data is held in one of the supported databases above - then the simple answer is Yes. If it not in the list above - contact us let us know what database technology you are using. This will help us prioritize which database technology gets added in what order.
What do I need to use it with our data?
Well - first of all you would need to download our trial 🙂 After that you will need to do a few things:
- work with your IT department to get access to your data. You will need the IP address of the database server, the name of the database and a user id and password to access the database server.
- When you first start the program you will be asked to configure the connection. After that, the program will automatically create the few tables that it needs to run inside the database itself.
- After this point - you are able to start using InSights!
- NOTE: You should work with your IT support to setup and configure InSights. We will be happy to supply any information to them so that they are comfortable with what the program does and how it works.
What SQL Syntax do you use?
We actually use the native SQL syntax for each database. Our system senses which database is currently being used and does syntax checking based on that. This is true of the Visual Query Builder as well! The only time we vary from this is when we use our SQL extensions to create truly reusable queries.
Reusable Queries - what are they?
In the past you might have queries that needed data from the user when they ran the query. It had to manually entered into the SQL - which introduced the possibility of errors. Take a simple query like - find me all the Male patients in a table. You would have to have a WHERE clause like "Sex = 'M'". Now what happens if you want Female patients? Or Transgender? You have to change the SQL! Now for this simple example it is not that hard. But what if you needed a list of the all the patients on a particular drug, between certain dates and that have not had a particular lab test. Now you are introducing a level of complexity to the next level and huge possibilities of misspelling things like drug names. And what date format do you use? So - you get the idea that these sorts of SQL statements are not truly reusable.
InSights gets around this issue by introducing SQL extensions that allow the program to prompt the user at run time to choose (or enter) a value that will be substituted into the SQL when it is run. So - in the above example we would have a LUSTRING extension that would prompt the user to enter the Sex of the patient they are looking for. InSights has extensions for Strings, Numbers, Dates and Table Lookups. The benefits to this approach are huge in terms of query reuse and error reduction!
What if I accidentally change the data?
The simple response here is that you cannot! InSights is designed to be Read-Only!! It does not allow the user to run anything but Select SQL statements, so that you cannot run Insert/Update/Delete statements and therefore change the data.