14 May 2014 Integration of Linux/UNIX OBI installations with MS Access

Posted at 17:28h in Oracle by

Introduction

 

Although most of the organizations that use OBI use some kind of ERP software, there are still divisions or departments where some decentralized, separate data is in use. Quite often those small solutions use MS Access as a database, as it’s cheap and doesn’t require a lot of technical knowledge. If there is a need to make this data available in the data warehouse, it usually requires an ETL process.

Anyway, sometimes there may be a need to connect directly to MS Access and retrieve the data directly from such a source. Unfortunately, there is no ODBC driver that would allow creating the connection from Linux server (where OBI is usually installed) to an MS Access database; hence you may think that creating this connection is impossible. Well, in this article I will solve this problem, describing one of the possible workarounds.

 

Concept

 

Before we get to the details, let me explain you briefly what we are going to implement. The whole concept is simple: we will install Oracle Database Gateway – which will ‘translate’ MS Access tables to be seen as Oracle tables. Then, using DB link we will connect DWH to the Oracle Gateway and we will create synonyms on the ‘db-linked’ tables. Finally, those synonyms will be imported to the RPD.

graph

 

But what is Oracle Gateway actually?

 

Oracle Database Gateway is a product that translates the heterogeneous data source – which may be not only relational database (MS SQL Server, Teradata, Sybase – see documentation for full list), but any data source for which ODBC driver is available (MS Access, MS Excel, also any of previously mentioned RMDBSes) – to be seen as Oracle Database. The license for Gateway for ODBC is included if you already own Oracle DB, Gateways for other products have to be purchased separately. Oracle Database Gateway is not a standalone product, it requires Oracle Database to work. For the official Oracle’s summary on the Gateway, please check the following document: Oracle Database Gateway

 

Requirements

 

Before we start, we need to download some software that will be necessary to configure and run the connection between OBI and MS Access. We also have to make sure that a few more components are available:

 

    • OBI environment: up, running & rock stable –your local virtual machine you use to play with RPD is fine, even if it freezes once in a while.

 

    • MS Windows machine, where you should have administration rights and which can be reached (through network) from the DWH that OBI uses. If you are short of Windows licenses, your local PC may also be used, if only it satisfies the previously mentioned networking condition. At that machine Oracle Database Gateway will be installed & Access database will also be located there.

 

    • Client machine, with Oracle client, Oracle SQL/development software & Oracle BI Administration Tool. Exactly like the PC you use each day in the office.

 

 

  • Package including ODBC Driver for MS Access: ODBC Driver download – if you don’t have the required ODBC Access driver installed in your system yet.

 

After making sure you have all components mentioned above, let’s roll with this! This tutorial will have the following parts:

 

    • #1: Configuring ODBC connection to MS Access database

 

    • #2: Installing and configuring Oracle Database Gateway

 

    • #3: Creating the required database objects in the DWH

 

  • #4: Importing MS Access tables to OBI

 

 

Step 1: Configuring ODBC connection to the MS Access database

 

This step is very simple. Copy the MS Access file containing the database you would like to integrate with OBI to the machine where you will install Oracle Gateway. This is mandatory, as Gateway cannot use a database located on network share, it has to be a local one. Now you would have to configure the ODBC connection for this database.

If  you are a BI developer, you probably already did this a few hundred times, so let me go through that briefly: ’Control Panel’->‘Administrative Tools’->‘Data Sources (ODBC)’->‘System DSN’->‘Add’->’Microsoft Access Driver’. Now chose the file with the database, set credentials (if necessary), data source name & description, and that’s it! If you don’t have the ODBC driver for MS Access installed, or if you just have the version supporting mdb file format (up to MS Access 2003), but you want to use accdb file, please download and install the ODBC driver for MS Access mentioned in the previous paragraph.

 

Step 2: Installing and configuring Oracle Database Gateway

 

If you have already downloaded the Gateway, you may start its installation. Extract the content of the downloaded zip file and then run file ‘setup.exe’. In the following steps set installation path, choose which gateway drivers you would like to install (for the need of this exercise only the Oracle Database Gateway for ODBC is required, which is, by the way, the only driver that is free of charge) and also configure listener of the Gateway, setting its name, network protocol and specifying the port number (assuming you chose TCP to be used, which in 99,99% you did). If you don’t use listener’s default name, please note & save it somewhere as it will be necessary in the following steps.

After the installation is finished, there are still a few files to be edited.

 

  • #1: GATEWAY_INSTALLATION_PATH/hs/admin/init[SID].ora

 

This file is used to connect Oracle Database Gateway to the previously created ODBC connection. You choose whatever you want to use as a SID – for this exercise let’s use ‘msacc’ as a SID, hence, the file should be named ‘initmsacc.ora’. As such a file doesn’t exist in the file system, to create it make a copy of ‘initdg4odbc.ora’ file that is created during the installation of Gateway. Then change its name and edit the content to be following:

 

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>#

# HS init parameters

#

HS_FDS_CONNECT_INFO = [NAME_OF_ODBC_CONNECTION_CREATED_IN_STEP1]

HS_FDS_TRACE_LEVEL = OFF

 

Of course string [NAME_OF_ODBC_CONNECTION_CREATED_IN_STEP1] shall be replaced by the required value. After editing the file, save, close and proceed to the next one:

 

  • #2: GATEWAY_INSTALLATION_PATH/network/admin/listener.ora

 

Here the content should be as following:

 


[LISTENER_NAME_SET_DURING_GATEWAY_INSTALLATION] =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = [YOUR_HOSTNAME.YOUR_DOMAINNAME])(PORT = [PORT_CHOSEN_DURING_INSTALLATION]))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC[PORT_CHOSEN_DURING_INSTALLATION]))

)

)

ADR_BASE_[LISTENER_NAME_SET_DURING_GATEWAY_INSTALLATION] = [GATEWAY_INSTALLATION_PATH]

SID_LIST_[LISTENER_NAME_SET_DURING_GATEWAY_INSTALLATION] =

(SID_LIST=

(SID_DESC=

(SID_NAME=[SID_NAME_CHOSEN_IN_PREVIOUS_PARAGRAPH])

(ORACLE_HOME= [GATEWAY_INSTALLATION_PATH])

(PROGRAM=dg4odbc)

)

)

 

For those of you who wonder what line:means: it just tells gateway to use ODBC Gateway. ‘PROGRAM’ parameter would have different value if you install and use other Gateway Below there is an example, with values & paths set:

 

 (PROGRAM=dg4odbc)

LISTENER_GTW =

(DESCRIPTION_LIST =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = pc04.acme.org)(PORT = 1521))

 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

 )

 )

ADR_BASE_LISTENER_GTW = C:\product\11.2.0\tg_1

 SID_LIST_LISTENER_GTW=

 (SID_LIST=

 (SID_DESC=

 (SID_NAME=msacc)

 (ORACLE_HOME=C:\product\11.2.0\tg_1)

 (PROGRAM=dg4odbc)

 )

)

 

Attention! If you want to integrate/import more than one MS Access database into OBI, repeat the bold part for every database you want to import, of course giving each of them a different name. Save, exit and reboot Windows.

After rebooting Windows, you may open ‘services.msc’ console (Menu Start->Run->Type ‘services.msc’->hit Enter key), and there, you shall find a service named ‘OracleOraGtw11g_home1TNS[listener_name]’ and start it if it hasn’t started yet. The configuration of Gateway is now finished and it shall be ready to use! To verify it, run command

 

C:\>lsnrctl status [LISTENER_NAME_SET_DURING_GATEWAY_INSTALLATION]

at the machine where the Oracle Gateway is installed and see listener’s status.

 

Step3: Creating the required database objects at the DWH

 

To make MS Access tables available for the import in OBI RPD, you need to do 3 things:

 

  • #1: Create TNS entry at OBI’s Data Warehouse pointing to listener’s SID created in the previous step:

 

The following entry should be added to ‘tnsnames.ora’ file used by your DWH:

 

[SID_USED_BY_MSACCESS_DB_BEHIND_GATEWAY]_connection  =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=[HOSTNAME_OF_MACHINE_WITH_GATEWAY])(PORT=[PORT_USED_BY_GATEWAY]))

(CONNECT_DATA=(SID=[SID_USED_BY_MSACCESS_DB_BEHIND_GATEWAY]))

(HS=OK)

)

So, in case of our exercise, the values should be as follows:

msacc_connection  =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=pc04.acme.org)(PORT=1521))

(CONNECT_DATA=(SID=msacc))

(HS=OK)

)

(of course if you configured more than one SID in listener.ora file in a previous paragraph, then you should create a tns entry for each of them!) To check if the created tns entry works correctly, you can execute the ‘tnsping’ command at the server where your DWH is installed.

 

#2: Create a database link at the Data Warehouse pointing to the MS Access database behind Oracle GatewayTo do that, run the following command:

CREATE DATABASE LINK [CHOSEN_NAME_FOR_DBLINK] USING [TNS_ENTRY_NAME]

So, in our case this would be:

 

CREATE DATABASE LINK ms_access_db USING msacc_connection ;
  • #3: Create synonyms for all tables of the MS Access database that you want to import to OBI

 

Now, for each table you want to import to OBI RPD, you have to execute:

CREATE SYNONYM TAB_SYNONYM FOR “TAB_NAME”@MS_ACCESS_DB;

Remember about quotation marks! They are obligatory! If all your steps were successful, you should now be able to access data from MS Access in your Oracle DWH just by issuing:

SELECT * FROM TAB_SYNONYM;

Please also remember, that if you want to access only certain columns of such a synonym, you also need to put their names between quotation marks, e.g.:

 

SELECT “some_column_name” FROM TAB_SYNONYM;

Otherwise, it doesn’t work. Now your objects should be ready to be imported to the RPD.

 

Step 4: Importing MS Access tables to OBI

 

Now the last step – import of the metadata. When we import Oracle tables to the OBI we can do that automatically, just ‘ticking’ the required table on the list of tables and then moving it to existing physical containers. Here the story is different, as the metadata of db-linked tables is not present in Oracle system dictionaries, hence it cannot be imported automatically (as, in the end, OBI’s automatic metadata import is a SQL query against ‘all_tables’ and ‘all_tab_cols ‘ dictionaries). So to import the metadata, we need to create the physical table manually (giving it the name of the synonym), and then, one by one, also create the physical columns manually. Bear in mind to put column names in quotation marks, like it was suggested while querying the columns in the SQL tool. You also need to remember that for some MS Access data types, the data type that should be used in OBIEE is not that obvious. For example ‘YES/NO’ type in MS Access translates to INTEGER in OBIEE.

To check if metadata was imported correctly, please save the repository, then right click on the physical table name and choose ‘View Data’ from the menu. And that’s it! Now you have your MS Access tables available in OBI RPD and you can add them to existing stars, create new hierarchies and do whatever you want!

 

Conclusion

 

MS Access databases are still in use in many places and it may be sometime useful or even necessary to add them to an existing BI system.  As you could find out, this operation is not that complex, and, once done correctly, may be easily repeated in the future, as just part of the steps needs to be repeated for any new database that has to be imported. If you have any issues with the configuration described in the article, please let me know in the comments, maybe I will be able to help you.

Tags:
Karol K
karol.kanicki@clearpeaks.com