Write Back Functionality in OBIEE

.

About Write Back Functionality:

One of the interesting attributes that OBIEE provides is the facility to enable users to add/update data back to the database. The user can have a column for which values can be entered in the user interface (UI) section on their platform and this can be updated in database. This could have multiple benefits as end users may want to rank their customers or rate their regional business based on performance, and be able to use this data from time to time. This converts OBIEE into a useful reporting tool and mini application for modifying business data.

Requirements for implementing the functionality:

Implementing write back requires the configuration of multiple objects within the architecture i.e. Database, Connection Pool, Presentation, BMM and Physical Layers, UI privileges, Column/Table properties etc.

Example on implementing the Write back functionality:

Here I am going to demonstrate how to make the Attribute2 column in the Product table (Sample apps) to be a writeable column.

  • Edit instanceconfig.xml

This is the initial step to enabling Write Back in OBIEE. Open the instance config file from the location – <Middleware>/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obipsn

Under <DSN>, Add <LightWriteBack >true</LightWriteBack >

  • Enable Write Back in the Repository tables

Open the RPD in Offline mode. Then expand the Logical table Product in the BMM layer. Double click on the column Attribute2 and in the general tab enable ‘Writeable’.

image 1

In the presentation layer expand the table Product, double click on the column Attribute2, and in permissions change this column as Read/Write for BI author.

image 2

  • Setting direct database request permission

In the RPD, goto manage > Identity > application roles > BI Author > Permission> select execute Direct DB request> select Allow

image 3

  • Disable cache for physical tables

Select the SAMP_PRODUCTS_D table in the physical layer and disable cacheable option.

Double click on D2 customer > unselect override source table and cacheable.

image 4

Deploy the modified RPD and restart the BI Presentation services.

  • Grant write back privilege to users

Log on to OBIEE presentation services > Administration > manage privileges > Write Back property and click on denied: authenticated user > granted: to authenticated user

  • Create Analysis for Write Back

Create a new analysis with columns P1 Product and P6 Attribute2. Open the column property of Attribute2, select the Write Back tab and enable it. Save the analysis.

image 5

  • Create write back XML template

Goto <Middleware>/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

Append the attached tags to the Write Back template.xml file (attached Write Back template.xml for reference)

<WebMessage name="wb_prod_attribute"> -- This web message is the reference for this block in the presentation

<XML>

<writeBack connectionPool="Sample Relational Connection"> -- Set the name as in the RPD file

<insert></insert>

<update>

UPDATE SAMP_PRODUCTS_D SET ATTRIBUTE_2='@2' WHERE PROD_DSC='@1' –- define the update query and refer the columns with their position in the answers

</update>

</writeBack>

</XML>

</WebMessage>

image 7 image 6

  • Enable Write Back in table view

Open the saved analysis > table view > edit view > Table view property > Write Back tab > Select enable Write Back and provide the name as wb_prod_attribute (Saved WebMessage name in the xml). Save the Analysis.

image 8

With this step, we have completed the configuration of Write Back in OBIEE. Now this should be tested in order to validate the Write Back configuration.

  • Testing the Write Back Option

Open the saved report > Click on Update.

This changes the column attribute2 to writeable. Change the value and click apply

image 9

Edit the column to the desired value.

image 10

Click Apply and Done

Now open the SQL developer and check the Product in the edited row.

SELECT PROD_DSC,ATTRIBUTE_2 FROM SAMP_PRODUCTS_D where prod_dsc = ‘7 Megapixel Digital Camera’

image 11

Now we can see that the changes made in the answers are reflected in the DB.
By using this simple this technique OBIEE can act as a front end form for updating data in the database.

Pop up effect in OBIEE using jQuery

.

In this article of our OBIEE Customization Series I will show you how to introduce the Pop up effect in OBIEE using jQuery. The tooltip can provide additional details (like an explanation of a KPI) or load content such as target report.

Motivation

For our example let’s imagine we have a summary level report with purchase orders listed. We can navigate to order details level, but that would require leaving the current page or opening the content of detail report in a new window. What if we could have a summary level report and peek into details on the same page? Implementing the dynamic navigation to a report inside the tooltip will allow us to achieve that.

Pop up effect in OBIEE using jQuery

Because OBIEE Answers and Dashboards allow using web technologies such as HTML5, JavaScript, CSS3, and AJAX we are able to customize the look and user interface of any report just like we can do with websites. To achieve that, we will use the most popular JavaScript library called jQuery. It’s an optimized .js file containing Document Object Model (DOM), events, effects and Ajax functions that can be called from the report. JQuery is widely used in website development and allows implementing complex components using pre-defined methods defined in library. For the tooltip we will need also a plugin, an additional library that defines the effect. There are many available plugins for tooltip functionality both free and requiring paid license. Many UI libraries such as jQuery UI or Kendo UI offer, among others, a tooltip functionality. For the example in this article we will use the Tipped tooltip library, which offers a lot of customization options but requires a licence for commercial use.
As an example in this article we will invoke a tooltip that loads the contents of target report when hovered on a column’s value. To create this effect we will need to use AJAX technology. Such method is already included in the jQuery library. We will need to specify the target page or report from which the external content will be loaded.

Implementing the tooltip

Let’s start by creating a simple report showing Order Number, Number of Order Lines and Order Cost.

graph1

Our objective here is to provide additional information of the orders displayed in the first report but without navigating. Therefore let’s create another simple report that will contain more details on a given Order and its Order Lines. We need to add a filter - Order Number is prompted to enable navigation.

graph2

Let’s go to the results tab and edit the views. The contents of this report will be displayed in a tooltip. We can add a pie chart with breakdown of the line cost and save the report as Order Lines Report.

graph3

Let’s get back to the main report. We will need to add a static text view with code to load libraries. Those files could be referred pointing to the file on the internet (we can do it by loading jQuery library from google developers and for example jQuery UI library which contains tooltip functionality). However we should have those files on our OBIEE server. Often internal network security settings disallow loading content (especially script files with .js extension) from external sources. A one-domain policy is used to prevent security breaches. Because of that we will need to save those library files on OBIEE server. To be able to link a file on a OBIEE server we will need to create a virtual directory on OBIEE server in weblogic settings.
When we have the virtual directory created we can refer to the libraries using such link: http://localhost:9704/userfiles/jquery.1.9.0.min.js

graph4

 

First, we need to load the jQuery library:

<script type="text/javascript" src="http://localhost1:9704/user_files/jquery-1.9.0.min.js"></script>

Tipped libraries:  

<script type="text/javascript" src="http://localhost:9704/user_files/Tipped/tipped-3.1.8/js/tipped/tipped.js"></script>
<script type="text/javascript" src="http://localhost:9704/user_files/Tipped/tipped-3.1.8/js/excanvas/excanvas.js"></script>
 

Tipped CSS stylesheet, which contains the styles used for tooltip customization:

<link rel="stylesheet" type="text/css" href="http://localhost:9704/user_files/Tipped/tipped-3.1.8/css/tipped/tipped.css"/>

Then let’s edit the column formula to dynamically generate a tooltip with navigation to Order Line Details.

graph5

We need a couple of elements here. Instead of just "Order"."Order Number" column value we need to embed some more elements in the formula code. The code inside will contain HTML tags so let’s mark Constains HTML Markup option.

graph6

For our example we will need to use the following column formula: '

'<div id="popup' ||"Order"."Order Number"|| '">
' ||"Order"."Order Number"|| '
</div>'
||'<script type="text/javascript"> Tipped.create("#popup' ||"Order"."Order Number"||'", "saw.dll?Go&path=%2Fshared%2FReports%2FOrder%20Lines%20Report&Action=extract&p0=1&p1=eq&p2=%22Order%22.%22Order%20Number%22&p3='||"Order"."Order Number"||'",
{ajax: true,
 skin:"white",
 hook: "bottomright",
 border: { size:1},
 afterUpdate: function()
{}
} );
</script>'

Explanation:
To separate HTML formatting from the OBIEE column value ("Order"."Order Number") we will need to use single quote (') and to merge HTML formatting with the column value we need to use concatenate (||).

<divmarkup is a placeholder for our tooltip. Its id is generated dynamically by concatenation with value of the "Order"."Order Number" column.
Here we initialize Tipped script and call plugin’s method to create a tooltip. The syntax of the script to create a Tipped tooltip is as follows:
<script type="text/javascript">
Tipped.create("#target", "url", { ajax: true });
</script>

# is a jQuery selector that points to a single element with the given id attribute, in our case #popup div concatenated with Order Number. Then there is a url with the external content to be loaded inside ("url"). The important part here is the ajax: true option, which allows loading of external content.

<script type="text/javascript"> Tipped.create("#popup' ||"Order"."Order Number"||'

So basically, the popup will display whatever contents are displayed in the url, therefore we can pass a GOURL link to the detail report passing the order as a parameter. Note the Extract parameter which displays just the results of target report in a format without the paging control, hot links and other elements.

"saw.dll?Go&path=%2Fshared%Reports%2FOrder%20Lines%20Report &Action=extract&p0=1&p1=eq&p2=%22Order%22.%22Order%20Number%22&p3='||"Order"."Order Number"||'"

Here we specify the formatting options of the tooltip. For Tipped tooltip to load external content we will need to use  ajax: true option. Tipped offers richness of customization options for skins, positioning and effects.

{ajax: true,
 skin:"white",
 hook: "bottomright",
 border: { size:1},
 afterUpdate: function()
{}
}

Because the column formula does not include the whole script (it references to the scripts loaded in the Narrative View) we will get a “Formula syntax is invalid” error:

graph7

We can ignore it, as it will work correctly with the whole script at the report execution. We also need to change the Data Format Column Properties:

graph8

We need to override Default Data Format in order to treat text as HTML:

graph9

Let’s make sure that report has the Static Text view added to the Compound Layout.

graph10

Now we can try out our development. The table with Orders should display the tooltip with navigation to target report with the Order details.

graph11

On hover, the order details from the target report are displayed in a tooltip:

graph12

The contents of the tooltip are displayed dynamically based on the navigation condition (in our case Order Number)

Maintenance and other considerations

We need to take into consideration that by embedding scripts to the OBIEE reports we are adding another layer of complexity.  This is especially true for the maintenance reasons as it requires more time and skills for the end users and developers to edit and create reports that contain scripts. Another concern should be that adding too many scripts to the report may negatively affect the performance of the report. We also need to take into consideration the web browsers used by users in the company. Some of the plugins and the latest versions of jQuery library are fully compatible only with the latest versions of browsers.

Conclusions

Thanks to the possibility of using many web technologies such as JavaScript we are able to customize OBIEE reports and dashboards. Tooltip can be a valuable tool to enhance the functionality of OBIEE reports, allowing users to get detailed information when hovering over a link.

Do you have another method to achieve the pop-up effect? Please leave us a comment below, we would be happy to answer it! If you would like to know more about visual enhancements in OBIEE, read our blog posts series about enhanced visualization:

Rich Visualizations in OBIEE with Javascript

Oracle Discoverer De-Support: how do you visualise your future BI and reporting?

.

If you are still using Oracle Discoverer for some or all of your reporting and BI requirements, you are probably struggling to deliver on user demands for better mobile/tablet access and richer information visualisation to improve interpretation, understanding and informed decision-making.

Also, it’s not new news that Oracle Discoverer is on its way out.  It ceased to be Oracle’s strategic BI toolset as long ago as 2006, when OBIEE came to the fore as Oracle’s new flagship BI platform.  Premier Support ceases in June 2014, and Oracle recommends all customers to migrate away from Discoverer by June 2015.

Many customers invested considerably in Discoverer prior to the release of OBIEE, and through challenging economic times have sweated the asset rather than undertake the transition to a more state-of-the-art platform.

Now that Discoverer is moving into its final stages of life, it is time to revisit the strategy for current Discoverer customers.

Why should I be migrating away from Discoverer?

From a business viewpoint, you are already well behind the curve in terms of the BI capability offered to you by Discoverer – and that could easily mean that you are falling behind your competitors who have gained the edge of working with newer, more able technology.

The releases of Discoverer around 2006, including Discoverer for OLAP, were actually pretty good at the time and brought it to a comparable level with other products of the day.  But just look around at what else has changed since then in the IT world – mobile devices, social media, advanced visualisation, big data – so that today, even if Discoverer is doing what it says on the tin, it is not equipped to move forward in any way.  Many end-user capabilities that are considered “must have” today are just not possible with Discoverer; indeed, even in its heyday, whilst Discoverer was a good workhorse, it never did score particularly good ratings for usability.

The last “features” release of Discoverer was 11gR1 in June 2009, and the terminal release of bug fixes and minor features was 11.1.1.7.0.  According to Oracle’s latest Statement of Direction (March 2014), there will be no further releases.  Premier Support ceases in June 2014, and Oracle recommends all customers to migrate away from Discoverer by June 2015.

A common objection from some customers is that they have a large estate of Discoverer reports and it would be far too major, complex and costly a task to replace them all.  Some customers have many hundreds of Discoverer reports, so at first glance it may indeed look like a huge task and not worth the cost.

However, a closer look may reveal that:

  • Actually, a large percentage of the “hundreds of reports” is redundant now – many of them are old versions or multiple temporary re-hashes of the same report.  So it may be that only tens of reports are still fit for active purpose.
  • Many of the reports are being used solely as a staging post to extract data to Excel for wrestling into shape for the final output – this is clearly an inefficient, error-prone and costly (in human time) process
  • There is a now cottage-industry of Excel (or other tools) producing newer report requirements that were not built into the Discoverer suite because it was not meeting the business need.  Again this could be costly, slow and error-prone.  Maybe the IT department thinks that Discoverer is still delivering what is required, but the business has moved on and may even have purchased other BI tools
  • Every time the underlying systems are upgraded, the whole Discoverer suite has to be re-tested and modified – Discoverer does not have the same metadata capabilities as OBIEE.  Many customers implemented Discoverer direct against transactional systems or eBusiness Suite without a data warehouse, meaning that every change to an underlying database could mean major rewrites of the Discoverer reporting suite.  The cost of a few iterations of this could outweigh the cost of replacement.

What do the changes to available Support mean?

When Premier Support ends (June 2014), the main change is that Certification with most new third party products/versions and new Oracle products ceases.  You can choose to keep the rest of the available Support features by paying an additional fee for Extended Support (free to June 2015).

If you choose not to opt for Extended Support, or in any case from June 2017, then you are on Sustaining Support.  This provides updates, fixes, security alerts, critical patch updates and upgrade tools/scripts for pre-existing issues only – not for anything new that occurs.  Also, there is no further certification against other Oracle or third-party products.  Basically you could maintain the software as-is, but if you were to change any of your source systems, database versions, etc, then you would be running the risk that if it broke, you would have no Support redress.

You are maybe planning on moving forward in other areas – e.g. an R12 Upgrade to eBusiness Suite, or migrating your database to 12c.  If you have any such major changes planned, it makes even more sense to reconsider whether Discoverer remains fit for purpose or whether it is now time to look at its replacement.  If you plan to move to the Fusion edition of eBusiness Suite, BI Foundation Suite becomes a pre-requisite.

Oracle specifically recommends that you should migrate by June 2015.

What should I replace it with?

Conventional wisdom, and Oracle recommendation, says:

  • Migrate the licenses to Oracle BI Foundation Suite or OBIEE.  There may be a partial rebate on the costs if you have a current Support contract, but this depends on versions and needs to be checked with your Oracle Account Manager (ClearPeaks can facilitate and advise on this)
  • For each Discoverer report, consider whether it is more appropriate to replace with Answers (the OBIEE analytic tool) or BI Publisher (the OBIEE tool for more structured reporting)
  • If you are an eBusiness Suite customer with packaged Discoverer content, replace with Oracle BI Applications

However, there are further considerations in making your decisions:

  • If you run direct against source databases – e.g. eBusiness Suite – would you benefit from changing to a datamart/warehouse approach to both future-proof your reporting and open up your data for analysis by making it available in a more digestible form for business users?
  • If your Discoverer “reports” are in reality being used for data extraction to another BI toolset, should you replace this manually intensive process with a proper ETL tool such as Oracle Data Integrator?
  • If your user base is small, could you migrate to the less costly BI Standard Edition One? (there are restrictions on this product so you would need advice)
  • If the business users have already moved on and adopted – in part – an alternative BI tool, does it make sense to enhance it to an Enterprise approach and incorporate the Discoverer content into the chosen BI toolset?
  • Would the use of a discovery-based BI tool fit your requirements better, or complement your more structured reporting?

ClearPeaks specialises not only in OBIEE/Foundation Suite, Oracle BI Applications and Endeca, but also in leading third-party BI products, so we are well-placed to provide advice on your best approach.

How should I go about it?

It makes sense to consult the expertise of a specialist BI implementer since, having waited this long before migrating, you will want to get it right!

ClearPeaks can help you to review your current estate of Discoverer reports with a thorough Health-Check and analysis of usage, together with capturing some of today’s aspirational requirements of the business rather than necessarily just doing a like-for-like replacement.  We can assist with licensing considerations, reviewing alternative replacement strategies, and making sure that you get the best fit at the most acceptable cost, with a solution that will hopefully live for as long as your Discoverer solution did!

We can help you to plan the migration, train appropriate IT staff / power users in the new technologies, run and test the migration scripts if appropriate (although in many cases it is more effective to use the old report as a design template and rebuild from scratch), and generally assist you through the implementation.  Or if you prefer –and are all busy with the day-job – we can take the problem away and build your new solution on one of our servers then commission it when ready to switch over.

Just give us a call and we will be delighted to help assess what is right for you!

OBIEE Customization Series – How to impress managers

.

When it comes to BI tools comparison one of the points in discussion is the Data Visualization or also referred as Data Discovery Capabilities.

Why data visualization matters? The human brain is not able to extrapolate data and map it correctly on its context. We are not capable to look at a set of data and at first sight understand the trending and its criticality.

With the use of visual aid like graphs, charts, maps we provide to the audience the capability to understand the data, its trend, the critical parts, and to have a clear picture of the overall situation.

The dashboard designer has to think about how visual aids can help the user to consume the dashboard effectively. The goal here is that the user must be provided with a clear idea of a particular business situation at a glance. Giving clear understanding and keeping things simple will be one of our great assets for user adoption as users will see the benefits from the first time.

You are starting to understand the importance of visualizations on the BI projects and then it is time to know how to implement it, and probably time to add a talented designer into your BI team structure.

On this new visualization series we will cover from the very basic graph creation to the principals of dashboard design and how to present it on a professional fashion, in a nutshell:

Integration of Linux/UNIX OBI installations with MS Access

.

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.
  • Oracle Database Gateways 11gR2 for Windows is necessary, it can be downloaded here: Oracle Database Gateway for Windows and now choose file win32_11gR2_gateways.zip (32bit version)  or here: Oracle Database Gateway for Windows  and download file win64_11gR2_gateways.zip (64bit version). OTN account is required to download any of them.
  • 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:

 (PROGRAM=dg4odbc)

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:

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.

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav