OBIEE 11G – Error in Importing Metadata

Recently I start creating a repository (11g version) in offline mode and I want to import metadata which failed by the error ‘The connection has failed’.. I have searched for a solution and I will explain this in the next sections.

We can import some metadata from the Oracle BI Administration Tool > File > Import > from Database …  In the Import dialog box, we have to select a connection type, in my case ‘OCI 10g/11g’. Next step is to enter a Data Source Name (orcl) and a username and password from which you want to import the tables.

Import Metadata

By clicking on the ‘next’-button it troughs an error ‘The connection has failed..’ This was very weird because my Oracle DB and Listener were up-and-running. I had checked this before to make a connection via SQLdeveloper.

At this point I had to find a solution before I could go further on my repository modeling. On Oracle forums I found a nice tip which solves the problem.

The root cause can be found in the following directory:

<biee11>\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup

There you will find a file named ‘user.cmd’ respectively ‘user.sh’. When you open the .cmd file you will see that you can set a TNS_ADMIN. This was not done earlier and this causes the problem of importing metadata through the wizard.

Solution: you have to set the TNS_ADMIN to an appropriate path such as <biee11>\Oracle_BI1\network\admin. This is the location where your tnsnames.ora is stored.

Edit User Command

Afterwards, you have to save the .cmd file and try again to import some metadata in the Administration Tool.

Note: maybe you have to close the Administration Tool or restart your BI-services. For me it was sufficient to restart the Administration Tool and everything was working fine!

Important remark: when you are creating Dashboards & Answers or you are working in Online mode and you want to retrieve some data for a certain table by selecting the option ‘View Data’, you will also get the error: ‘The connection has failed..’. This problem will only occur when your tnsnames.ora is not stored in the Oracle_BI1 directory.

My Oracle Sources

Here’s a post with Oracle sources(blogs, twitter, websites,…) I use.
To get a nice overview I use igoogle.
It’s easy to use, you can embed bookmarks, RSS feeds, gadgets, … into your igoogle page.
And it’s available wherever you are(at work, at a customer, at home,…).

First page with Oracle and  Oracle Forms bookmarks(google bookmarks) and a twitter gadget.

 

Another tab with oracle related blogs.  A lot of blogs with interesting stuff.  The Amis blog is one of my favorites with lots of interesting technical articles.

 

And a last oracle related tab for SQL, PL/SQL and database.

 

And of course all pimped up with the iAdvise igoogle theme ;-)

When you have some tips to add more interesting links, sources, … feel free to comment.

Hot or Not … – Java For Enterprise Apps

A new article has popped up on the world wide web titled ‘Java is a Dead-End for Enterprise Apps‘ … this will certainly juice up some communities :-)

If you read along in the article you will find different interesting aspects regarding enterprise application development. The discussion that’s unveiled in the article regarding ‘Java is too complex for business application development’ is something I won’t get into. What I do find interesting in the article are the different technologies and frameworks being discussed such as JavaFX, Ajax, Spring, Hibernate, … and the focus on richer User Experience that leads to new frameworks and new insights.

The end-user wants to do more in his data entry / web applications … He wants to be able to interact in an intuitive and integrated way with co-workers, interchange data with other applications enterprise-wide and most of all reuse existing services within the company to reduce the manual workload.

In other words when we look at the applications of yesterday, today and tomorrow we see a steady leap towards interchangeability, interaction, social networking with a clear focus on Business Process Reengineering and let’s not forget most of all user-experience is key.

As mentioned in the article Business Process Modeling is becoming more and more a decisive factor in defining the strategy of a company and it’s IT development. In other words business process reengineering (BPR) is needed to rethink how work is done today within the organization in order to improve customer service, cut operation costs and improve agility and interchangeability.

For a more compelling story, have a look at: Java the Javatar … a must see for every IT consultant.

BI Publisher caching results problem

If you are experiencing behavior that indicates that your report is being cached and you can’t refresh it then it could be that a reporting setting is indicating that it can cache your result set.

To check this setting go to “Edit Report” and look at the bottom in the list of options. You will find a check-box with the label “Enable document cache”. Unchecking this check-box will probably solve your problem.

By default, all Oracle Enterprise Repository reports uses Document Caching to reduce database roundtrips on the generation of report data. While the data is cached, the rendered report may not always show the most recent activities in Oracle Enterprise Repository. Setting the cache expiration changes depending on how frequently BI Publisher needs to refresh the data for the second and subsequent rendering of a report.

It is possible to change the default cache expiration (30 minutes) under Admin -> Server configuration -> Cache Section.

More information can be found in the BI Publisher.

Oracle Discoverer to Oracle BI Enterprise Edition

Tuesday I went to a session regarding migrating Oracle Discoverer to OBIEE which was a very interesting resume on do’s and don’ts when migrating.

First of all only metadata is migrated, not the reports itself. When you think about that is a logical approach as well because you don’t want all your front-end reports and logic to be delivered in OBI. OBI has a lot of more features which you wouldn’t be using when migrating as well metadata as reports.

Tip: Use different rpd-files when you have loads of data to resolve performance issues because BI Server reads through the whole rpd-file when starting up.

How to start migrating:

  • Export ‘End User Layer’ in discoverer to a .eex file
  • The migrate tool itseld is a windows based tool which you can use command-line to migrate the .eex file to an .rpd file. You can change the properties of the migration tool in the MigrationConfig.properties file.
  • Copy the .rpd-file to your BI Server/Repository folder
  • Define which .rpd-file to start-up with in the NQSConfig.INI file
  • Update the .rpd-file online
  • Login to your OBI-environment and open up the Answers-tab to look through the Subject Area and data that has been created using the .rpd file. From now on you start creating your own reports as a business user.

What isn’t migrated when opening the rpd-file in OBI EE:

  • You have to reestablish the connection to your database
  • You have to define a password for your Administrator user otherwise your BI Server won’t come up on your linux environment. This is a very important tip: because it takes a while to find out which error occured when you’ve migrated your discoverer metadata to an .rpd file and your BI Server crashes.

PL/SQL Developer, Quiz thyself

This morning I went to a session of Steven Feuerstein: PL/SQl Developer, quiz thyself. A very interactive and fun presentation where the attendees had to answer questions regarding SQL and PL/SQL.

First we played a game of mastermind in which you can sharpen your deductive skills as Steven mentioned. Playing mastermind improves your skills to debug your code and eliminate factors out.

You can find all the material of his presentations and the quizes in the demo.zip file which is on his website (have a look at training, seminars).

Some items I’ve noted during the session:

How to continue past exceptions

  • Use format_error_stack to display you errors instead of SQLERR using the DBMS_UTILITY package
  • Use DBMS_ERRLOG.create_error_log to create an error log table based on your table on which your performing the DML on
  • Surpress exceptions at row level => only possible way is when using log_errors()
  • Exception raising is expensive in PL/SQL it’s better to trap exceptions

Performing bulk processing:

  • Use forall to perform bulk processing instead of looping over cursors and performing DML within the loop

Ask for improvements for 12G release: Use the website ‘I love PL/SQL and …’ to add your suggestions regarding functionality to add in 12g release.

  • plitblm holds all functions you can use when working with associative arrays
  • format_error_backtrace gives you the possibility to get the line number where the error was raised. Have a look at Quest Error Manager to get the error-log framework

The entire session was very interesting, especially because it was a fun and practical game where you can teach thyself.

Oracle Open World 2010

Me and my colleagues arrived saturday afternoon in San Francisco, we rushed to our hotel and after a refreshing shower after a whole day trip we went to our first event: drinking and eating ;o)

It was a fun event with a lot of benelux partners but we didn’t keep up very late because of the jet lag.

The next morning we went on a sailing trip with the Oracle Partners, had a nice lunch and then went to pick up our badges at Moscone West. As expected there are a lot of people attending Open World.

Regarding the Jet Lag, my internal clock is still running in the Belgium timezone … hopefully that’ll sort itself out asap ;o)

Today we’re going to start quizing ourselves with Steven Feuerstein …

To all, have a wonderfull Open World and don’t miss our session regarding Forms Modernisation Wednesday !

Forms modernisation at OOW

For the people, partners, customers attending Open World and interested in Forms and fusion technologies I’ve made a little resume of the interesting sessions you could attend:

  • S315945 : Oracle Forms in the Middle of Middleware with Oracle Product Management – Wednesday, September 22, 13:00 | Marriott Marquis, Salon 9
  • S317234 : Moving from Oracle Forms to Java and Oracle Application Development Framework – Tuesday, September 21, 09:30 | Hotel Nikko, Carmel
  • S313982 : Forms2Future: Journey into the Future for Organizations on the Oracle Platform – Tuesday, September 21, 13:00 | Hotel Nikko, Golden Gate
  • S313280 : PL/SQL Developer, Quiz Thyself! – Monday, September 20, 10:00 | Hotel Nikko, Bay View

Sessions regarding Fusion Middleware, Enterprise Architecture, Upgrading to 11g:

  • S313466 : Oracle Fusion Middleware as an Enabler for Transformation and Innovation – Monday, September 20, 15:30 | Moscone West L3, Rm 3018
  • S317629 : Best Practices in Enterprise Architecture: Case Studies – Tuesday, September 21, 17:00 | Moscone South, Rm 301
  • S316135 : From Oracle Forms to a Service-Oriented Architecture with Oracle SOA Suite 11g – Tuesday, September 21, 17:00 | Marriott Marquis, Salon 9
  • S317403 : Oracle Internet Application Server 10g to Oracle Fusion Middleware 11g R1 – Wednesday, September 22, 10:00 | Marriott Marquis, Salon 8
  • S315685 : Stay Away If You Are Technical: This Is Oracle Fusion Middleware for Business – Sunday, September 19, 16:30 | Moscone West L2, Rm 2010
  • S317474 : Oracle Fusion Middleware Application Server Roadmap – Monday, September 20, 11:00 | Marriott Marquis, Salon 9
  • S316409 : Oracle Fusion Middleware Architecture: Choices, Choices, Choices – Monday, September 20, 17:00 | Marriott Marquis, Golden Gate B
  • S316855 : Oracle Fusion Development Platform: Oracle JDeveloper and Oracle ADF Overview – Tuesday, September 21, 11:00 | Marriott Marquis, Salon 9
  • S316075 : Telenet: The SOA Challenge – Tuesday, September 21, 14:00 | Marriott Marquis, Salon 4
  • S316906 : Adding Web 2.0 Interfaces to Your Enterprise Applications: The Oracle Fusion Way – Thursday, September 23, 09:00 | Moscone West L3, Rm 3016
  • S316615 : Migrate: Oracle Application Server Containers for J2EE to Oracle WebLogic Server – Sunday, September 19, 15:30 | Moscone West L2, Rm 2010

If you’ve got other interesting sessions to add, please comment so no one misses out on interesting tips, tricks during Oracle Open World.

Of course besides these sessions there are enough things to see, do and visit up and around San-Francisco such as the cable car, fisherman’s warf, bicking (preferably with a tandem … hilarious) on golden gate bridge, eat a delicious Cioppino, visit Sausalito, …

Hope to see you all there !