How To Extract Data from Database Using QlikView

QVNewbie#3 :How To Extract Data from Database Using QlikView

We will be seeing  How to Connect QlikView to Oracle database. ODBC / OLED connection can be used to establish a Connectivity between QlikView and Oracle DB.

To Setup ODBC Connectivity, We need to have ODBC driver installed in your Computer. Once the ODBC Drivers are installed follow the below steps to Setup ODBC Connection.

• From Windows, click Start, then select Settings, Control Panel, Administrative Tools and Data Sources (ODBC). The ODBC Data Source Administrator window appears.

                       

• You can Setup the DSN based on User DNS or System DNS, Below is the Difference

For Example if Mike has logged in to the system and created a User DNS and Logged off. If Jerry Logs into the same System, Jerry will not see the DNS created by Mike since the DNS which Mike Created is User DNS and It is visible only to the user who Created it. But if this has been Created as System DNS, then that can be share by both of them. It is based on your Requirement whether you want to go with System or User DNS. Refer to Microsoft documentation for difference between User / System DNS.

• Click the System DSN tab, and click Add to create a new data source. The Create New Data Source window appears. Your Oracle client driver should appear on this list:
• Look at the difference between Setting up the

• Select your Oracle client driver from the list, and click Finish. The ODBC Oracle Driver Configuration window appears.
• In the Data Source Name field, type the name that you want to use for this ODBC data source.

• In the TNS Service Name field, type the name of your Oracle service. This is the name that is specified in the tnsnames.ora file that is defined for your Oracle client.

• In the User ID field, type the username that you use to log into the Oracle database. The Oracle ODBC Driver Configuration window should now look something like this:

Click OK to create the ODBC data source for your Oracle client. The new data source now appears in the ODBC Data Source Administrator window:

• Now your ODBC connection is ready to use. Open QlikView and go to script editor or Ctrl + E.
• Select ODBC from drop down menu of the Database as Show below.  You may also ‘Force it to 32 Bit’ (if you are using x64 bit windows machine  and you have  only the 32bit Oracle ODBC Driver installed in your system then you`ll have to force the ODBC to use ‘Force 32 Bit’).

                             

• Click Connect to see the list of Data sources which has been already setup from above steps. Select the Data Sources which you want to establish a Connection to QlikView and Key-in the Credentials and click OK

• Once Connection is successful, you`ll see the Connection string as shown below. Your Credentials will be Encrypted,

           

• Click Select to see the list of tables / Views / Synonyms available in the database. Select your required database object and say OK. You`ll see the ‘SQL Select’ Code in QlikView Script Editor and hit run in QlikView to Extract the data from Oracle Database.Above approach can be used in any database which allows ODBC connectivity.

            Thanks for reading the post and Happy Qliking !!!.