I’m lazy, why doesn’t anyone have instructions I can follow already! I guess it’s time to give back to the Google seekers of this world. After going through the process I can understand why no one has written a guide, it’s not a straight forward process.
As this sort of thing can be very version specific I tested these instructions using the following versions of software
- Windows Server 2012 R2 running on AWS
- Qlik Sense 2.2.4
- Oracle instant Client version 126.96.36.199.0 64bit
Two things need to be downloaded for this to work from here Instant Client Downloads for Microsoft Windows (x64)
- Instant Client Package – Basic
- Instant Client Package – ODBC
Once you have the files follow the steps below to get things working:
- Unzip the instantclient-basic-windows.x64-<version number>.zip
- Move the folder instantclient_<version number> into a permanent location. In my case c:\app\
- Edit the system environment variables on the server add the folder location to the end of the PATH variable. eg %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Amazon\cfn-bootstrap\;C:\app\instantclient_12_1
- Add new system variable
Variable Value: <Path to where you want to store tnsnames.ora file>
- Create a text file called tnsnames.ora in the folder specified in step 4 and create an entry for your desired DB which should follow the below format
<addressname> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>)) ) (CONNECT_DATA = (SERVICE_NAME = <service_name>) ) )
- Unzip instantclient-odbc-windows.x64-<version number>.zip
- Move the contents of the instantclient_<version number> folder into the instantclient_<version number> folder from step 2
- Run instantclient_<version number>\odbc_install.exe
- To test if things work setup an odbc Data Source (64-bit) as a System DSN and test the conneciton.
- You may need Microsoft Visual C++ 2010 SP1 Redistributable Package (x64) if you have the below error when adding a new ODBC Data Source.
- In Qlik Sense you can create a new connection at this point ODBC driver. If you get an error message “You do not have access to the data source” as I experienced the only solution that worked for me was in this post and the solution wasn’t ideal. I intend to talk to Qlik to see if there is a better solution.
All the code, all the fun – Ben
Ben writes blogs about the technical side of BI the code, all the code and not much other than the code.
You can read all of Ben’s blog here.
Don’t forget, we can train your team in the art of agile business intelligence at any time!