QlikView ODBC and OLE DB Architecture – OLE DB vs ODBC
QlikView ODBC and OLEDB – What are they, when we need them and what is the difference between ODBC and OLEDB. To answer all your questions, we have come with this tutorial. Before that, don’t forget to bookmark Free QlikView Tutorials Library.
The tool QlikView is a data analysis tool which makes data an important part of the software’s functioning. This brings in light the process of connecting QlikView with different data sources to fetch data into QlikView files. Two main frontiers in establishing a connection of the application with the data source are ODBC (Open Database Connectivity) and OLEDB (Object Linking and Embedding Database).
What is ODBC?
ODBC is an Open Database Connectivity API (Application Programming Interface). It is an open or public API and uses to establish connections between applications and databases. It does not depend on any particular operating system or database management system for running. Any application which is using ODBC API can easily port to other operating platforms with minimal tweaks in the data access code.
The independence from database management systems specificity lets the ODBC created applications to retrieve data from any database. The user programs simply use SQL queries to access data from databases regardless of the DBMS and intermediate layers. The DBMS independence achieves by converting the SQL requests into DBMS specific or understandable queries (DSN) in order to connect and fetch the require data from the database. The ODBC Driver acts as an intermediate between the application and database and translates the application specific commands and functionality to DBMS specific commands and functionality. The applications that can use ODBC for database connectivity is known as ODBC-complaint and needs to install an ODBC Driver to connect to any database. The ODBC drivers must support the DBMS to fetch data.
ODBC Architecture & Components
Now let’s learn better about how ODBC works through understanding it’s architecture.
There are four components of ODBC:
- The ODBC Driver
- Driver Manager
- Data Source
To start with, the ODBC Application is the platform at the user end from where data source access sessions can initiate. The ODBC complaint applications call API functions to submit SQL commands. Also, when the data is fetched from the database using the SQL statements, the data format and storage space is defined by the ODBC application level.
Now, the API function calls sent by the application process by the ODBC Driver. The ODBC Driver acts as a middle layer between the application and the data source. It transforms the SQL command syntax into associated syntax accepted by DBMS. The data traffic of sending SQL requests to the database and sending the resultant data back to the application passes through the ODBC driver. The driver has a dynamic link library.
ODBC Driver Manager
Every DBMS and application have a specific ODBC driver which manages and installs by an ODBC Driver Manager. The driver manager also maps the data source name to a specific driver data link library (DLL). It is also used in loading the driver for the application and links the ODBC application with the data source.
ODBC Data Source
Finally, the fourth component is the data source from where the data is to be retrieved and used in the application. The data source has all the associated services like its O.S., DBMS, network platform and required protocol.
What is OLE DB?
OLE DB stands for Object Linking and Embedding Database. Whereby linking it meant linking the object with the data source and by embedding it is meant to add the source data into the object. OLE DB is also an Application Programming Interface (API) that allows applications to access data from varied data sources. It is in fact a group of interfaces (APIs) that are used to gain access to data sources to fetch different kinds of data files like an object database, spreadsheets, personal databases, Indexed- sequential files, SQL-based DBMS files etc., which are not all necessarily SQL using file formats. The OLEDB is a Microsoft product launched as an extension of the former ODBC API. Its design is based on Component Object Model (COM). OLE DB was basically introduced with the intention of providing the users access to a data source other than SQL-based (but supports SQL data too).
The OLE DB helps in facilitating data access for the application from the data sources. The access to the data source is possible through a set of abstractions or components in OLE DB namely; Data source, Session, Rowsets and Command. These four are also termed as OLE DB objects (in the context of object-oriented programming). By these four abstractions playing as a bridge between the application and data source, the client can gain access to varied (not only SQL) sources and types of data without getting into the technicalities of the process.
Have you checked DataFlair’s latest article on QlikView Database Connections
OLE DB Architecture
To fetch data from the data source, the OLEDB of an application first initializes the OLE. Followed by connection establishment with the data source. After this, a data access command is issued to be sent to the data source. Then the data sent as requested is processed and the OLE is uninitialized releasing the data source object.
The OLE DB conceptually divided into two sections; Consumers and Providers. Consumers are the user applications that require the data and the providers are those who implement the APIs and in turn, fetch the required data from the data sources. There are exclusive providers for each type of data source supported by OLE DB.
QlikView ODBC and OLEDB
QlikView supports both OLE DB and ODBC interfaces for data source connections. Plenty of databases can be accessed using QlikView ODBC Drivers and OLEDB Providers. The QlikView OLEDB Provider and ODBC Driver must be correctly installed for the QlikView software to connect to the data source and fetch data.
- In QlikView, the user can connect through either of the two interfaces by going to the Script Editor and selecting the connection type from the bottom left in the script editor dialog.
- If you select OLE DB, then you will get a Data Link Properties dialog if the correct OLE DB provider is installed otherwise not. And in the case of QlikView ODBC, a dialog box will open where an authorized user can enter his/her userId and password and choose a DSN from the available DSN list.
- If a DSN is to create, then it is done through the Qlikview ODBC Data Source Administrator.
- In the tab User DSN, you can create a user data source and, in the System DSN you can create a system data source.
- Upon clicking the Add button you will get a list of all installed drivers from which you have to choose a driver corresponding to your data source. In the case of the SQL database, you can select an SQL ODBC driver. Click Finish once you have chosen a driver.
- In the next dialog, you will ask to enter a name for the data source, description of the data source and name of the server you are connecting to. Click on Finish or Next once you have filled all the details.
- You can run a test connection to ensure the connection has been set up properly.
- Finally, in the script editor, when you select QlikView ODBC as the database and click on the Connect button. You will get the name of the newly created data source in the list (if not, check the Show User DSNs box at the bottom). Select the DSN and the connection statement will display in the script.
- QlikView supports both 32-bit and 64-bit ODBC drivers. You can manage the drivers from the Drivers tab in the ODBC Data Source Administrator (in a 64-bit operating system).
Don’t forget to check the article on QlikView Directory Service Connector
QlikView ODBC vs OLEDB
As we know that both Qlikview ODBC and OLEDB interfaces use to connect user applications to the data source. They work as query translators and so are used by many DBMS vendors who allow access to their data only through ODBC or OLEDB. However, both connections are different from each other in some ways.
In the QlikView ODBC connection type, the application needs to establish a connection to the data source using the data source specific drive. The connection is established using Database Source Name (DSN) which in simple words, is an instruction in such a format and structure understandable to the database. The structure of the DSN is- protocol: subprotocol: host: port:
Whereas, the QlikView OLEDB does not use the DSN system to set up a connection with the data source. The OLEDB connection string can simply type in the editor of the document and it stays there in the document. In fact, QlikView OLEDB prefers for connecting with databases by QlikView users as well. Because it is more convenient to initiate the connection (no DSN setup), it is also much faster than ODBC and a reliable source as well.
So, this was all about QlikView ODBC and OLEDB Tutorial.
Hence, we studied about the QlikView ODBC and OLEDB, in which we explored what is ODBC and OLEDB. Is this information, helpful to you? Share your feedback with us!
Get up to 110+ FREE QlikView Tutorials and become the master of BI Software.