I keep seeing things pop up and people having trouble getting the Discoverer / BIP integration working.  I will say this is not the easiest of configurations, but with some additional steps anyone should be able to complete the task.  For those of you that have been following along, the latest Discoverer Cumulative Patch (CU4, p6357481) was released and  the Discoverer / BIP integration was not included with created a new interop patch for everyone numbered 6622352.

Just a couple notes on the install.  If you are applying the patch to a unix system you’re going to have to run the dos2unix command on the CreateOIDContainer.sh file in the <OH>/discoverer/util directory.   Also, notice the CreateOIDContainer.sh file, yes its case sensitive and don’t forget to edit the file and replace the %ORACLE_HOME% directive with your actual Oracle Home path, while you’re at it make sure you chmod 750 CreateOIDContainer.sh too.  Can you tell it was a windows guy who wrote the script .

Anyways, before executing the CreateOIDContainer.sh script you’ll also need to properly setup all your paths which includes:
export ORACLE_HOME=<Your Oracle Home Path>
export LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$ORACLE_HOME/jlib:$LIBPATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$ORACLE_HOME/jlib:$LD_LIBRARY_PATH


The big kicker that bit me this last time was that I was getting an error that the Discoverer product container doesn’t  exist in the OID Repository.  Well, for some reason that container doesn’t actually exist until a user is created in the OID through something like OIDDAS, since this client was using Server Chaining to bring in all their users (another blog post soon) we had never actually created a “new” native user in OID.

Needless to say the 10.1.2.3 Discoverer patch set can’t come soon enough which includes this patch with it.  Till then feel free to drop me a line if you have any questions.  If you want some more in depth examples of the config let me know and I’ll get some screen shots the next time I do this.

I’ve always been intrigued by all the Oracle BI user groups out there, IOUG vs ODTUG vs OAUG. All of them have great leadership but none of them have great participation from the members. Over the years in working with all of them I’ve noticed that there aren’t many differentiators between them. An interesting questions popped up on Mix the other day wanting to merge all the BI related SIGs. Personally, I think its a great idea, but I know there are a lot of opinions out there on it. So lets hear it, make your voice heard in the mix discussion.

https://mix.oracle.com/ideas/13754-merge-all-bi-related-sigs

We’re all here for the same reason, trying to bring great BI to the world (sounds like a great campaign slogan).

Well, as many of you know, Dan Norris and I used to work together at ITC. He decided to go join some company known for handing out mints that tend to appear in peculiar places. In his post today he linked to the new site I’ve been working on. It’s obviously not live, but I linked in the blog section tonight. I still have some work to do on the templates in both the blog and wiki sections, some organization of the wiki, a couple entries in the FAQ, and a digg style rating system for users. I’ve worked out all my kinks with Amazon’s S3 service for the torrent downloads, now I’m in the process of uploading the initial VMs.

The first ones to be released will be a generic Oracle Enterprise Linux Update 5 VM with all the pre-configuration completed for database and application server installs. Next up will be a Portal 10.1.4 / BI 10.1.2.2.0 vm, an 11g database vm, and then hopefully Dan’s RAC vms.  The IdM VMs will come after that.  I’ve also had offers from Mark and John @ Rittmanmead.com for some of their BI and Data Warehousing VMs, hopefully I can catch up with them for dinner before the BIWA summit.

I’ve been busy with a whole bunch of client stuff lately, when Dan left ITC he also left me with a pretty healthy pipeline to deliver to, and I had already been booked for a client through the end of the year. (BTW, any Fusion Middleware guys need a job?) Needless to say, I’ve been a little busy lately and the horrible hotel upload speeds haven’t helped the situation in pushing things to Amazon.

So for now, go ahead, start using it. Tell me whats good, whats bad, what works, what doesn’t work, and I’ll do my best to keep on top of things. The OracleVMs.com project forum (http://www.oraclevms.com/forums/project.php?projectid=8) is the best place to log bugs, issues and feature requests. Feel free to start putting them there and I’ll slot them into the release cycle.

In the IOUG Fusion Task Force meeting this week, we were discussing what could be provided to build a better community around the Fusion Middleware world and it’s ever growing list of products and acquisitions. A lot of us are classic Oracle guys that have been doing Java, ADF, App Server, Portal, Discoverer, etc. since its first release. We’ve always known the standard Oracle Metalink, Forums, and ListServs for Oracle help when we need it. Now with so many acquisitions it’s getting incredibly hard to catch up and the communities for many of the new products don’t exist.

One of the big questions that came up was where have all the developers gone. For some reason the term “The Lost Developers” popped into my head, which of course popped the bad 80’s movie “The Lost Boys,” and in turn this bad graphic. (Trust me you don’t want to try and understand whats in my head)

But in all seriousness, where did everyone go. I know a lot of the people went to start their own independent consulting shops, some stayed with Oracle, but what about the rest of the world? What about all the customers and other implementation partners? I went through, looked at the acquisition list, and couldn’t find user groups or message boards for many of them. Maybe I’m looking in the wrong places or haven’t been taught the secret handshake yet, but here is the list I came up with:

Agile: Nothing
AppForge: Palm and Windows Media Local User Groups, nothing centralized
Bharosa: Nothing
Tangosol: LCUG (http://wiki.tangosol.com/display/LCUG/Home)
HotSip: Nothing
Siebel (Analytics): ITtoolbox Group (http://siebel.ittoolbox.com/groups/technical-functional/siebel-analytics-l)
SigmaDynamics: Nothing
Sleepycat: Nabble Forums (http://www.nabble.com/Berkeley-DB-f2899.html)
Stellent: Stellentforums.com and regional user groups
Context Media: Nothing
Oblix: Nothing
Octet String: Nothing
Thor Technologies: Nothing
TimesTen: Nothing
TripleHop: Nothing

Yes there are the Oracle boards, but many of them aren’t trolled by the experts of the acquired companies yet. So what happened? Where did everyone go? Right now I’m working on building a lot of pre-built virtual machines for my side project (thanks again for the people volunteering to help), but on the newer components I’m having to learn a ton as I go and it would be helpful to bounce ideas / questions off of people who have already been there and done that. I’m sure a lot of them are having the same problems now trying to deploy on to the Fusion Middleware stack.

So here it is, an open invite to come out of the corners and reveal yourselves. Where is everyone hiding? How can we build a better collaborative Oracle development world? I would love to hear people’s feedback. Maybe we need a myspace or facebook for Oracle people? I’m only half joking here, there sure are enough of us to keep it busy. What features would make it a kick ass collaboration environment? Forums? Wiki? Torrents? Instant Messaging? Desktop Sharing? Blogs Provider? Maybe just an Aggregator? Rent a VM development environments? Calendaring? Mapping? Presence? Ok, enough web 2.0 buzz words (crap, there was another one).

Call me, email me, IM me, post comments here, I just want to figure out how to make it easier on all of us.

I finally got a chance to sit down and figure out which sessions I want to attend at next week’s Collaborate conference. I’ve been busy working on some proof of concepts and Oracle BIEE training material for the last couple weeks, as well as taking over the lead on a project from one of our developers. With giving 5 presentations and client meetings it really didn’t leave much room for going other presentations. So without further ado, here’s my planned schedule, more of a reference for me than anyone else.

I’m glad to be heading out next week, I work out of my home office most of the time when I’m not at a client site so this is going to give me a chance to meet face to face a lot of the people I talk to on a daily basis. Along with a bunch of friends who I don’t get to see very often.If I’m not presenting and you want to try and catch me around, either give me a call or stop by the IT Convergence booth in the vendor hall. I should have all my presentations and papers up here by the end of next week (as soon as I finish them up).

As I said in my last post I was hoping to give daily updates, well I’ve been having too much fun meeting new people and catching up with friends.  I’ve been keeping track of everything and have posts ready to go, I just haven’t had time to give them the once over before posting them.  Lots of great stuff at the conference so far, BI its just plain exciting right now, hopefully Oracle’s customers will be able to afford it ($225K a proc, ouch!!!).  The JSF / Ajax / SOA presentations have been great.  I love Oracle right now, the only problem is trying to keep up.  Also, look for a site that I’ve promised for a long time to come online soon.  I think I’m getting final clearance soon to go ahead with it, keep your eyes peeled.  I think its going to be a lot of fun for everyone.

I just had to do a quick demo with the default Discoverere Video Store tutorial and ran into a bit of a problem with the create scripts that I thought I’d warn the world about.

Basically in the new database release 10gR2, they no longer support setting a QUOTA on temporary tablespaces. So instead of tossing a warning and letting a script continue they are throwing an ORA-30041 error. Many 3rd party and even some of Oracle’s tools (Discoverer) have this built into the create user scripts.

Heres the steps that led me to the problem and also how I worked around it. Hopefully this will save some others from the pain in the future.

Step 1: Connect to the database as a database user and create an EUL now

Step 2: I selected Yes to the previous screen and selected the create EUL button.

Step 3: I selected to create a new user

This is where the Oracle scripts need to be changed, and since they are embedded into Discoverer (from what I could tell), I am unable to change them. But the error didn’t pop up until later.

Step 4: I selected the Users tablespace for the default and TEMP for the temporary.

Step 5: The errror

So the fix was pretty simple, I just went into the database and dropped the user I had just created and ran the following script to create the user by hand. This is what I do normally but for some reason was being lazy today.


CREATE USER "DISCO_EUL" IDENTIFIED BY "DISCO_EUL"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
GRANT CREATE DATABASE LINK TO DISCO_EUL;
GRANT CREATE MATERIALIZED VIEW TO DISCO_EUL;
GRANT CREATE PROCEDURE TO DISCO_EUL;
GRANT CREATE SEQUENCE TO DISCO_EUL;
GRANT CREATE SESSION TO DISCO_EUL;
GRANT CREATE SYNONYM TO DISCO_EUL;
GRANT CREATE TABLE TO DISCO_EUL;
GRANT CREATE TRIGGER TO DISCO_EUL;
GRANT CREATE TYPE TO DISCO_EUL;
GRANT CREATE VIEW TO DISCO_EUL;
GRANT GLOBAL QUERY REWRITE TO DISCO_EUL;
GRANT QUERY REWRITE TO DISCO_EUL;
GRANT CREATE SEQUENCE TO DISCO_EUL;
GRANT CREATE SESSION TO "DISCO_EUL";
GRANT CREATE USER TO DISCO_EUL;
GRANT GRANT ANY PRIVILEGE TO DISCO_EUL;
GRANT ALTER USER TO DISCO_EUL;
GRANT "CONNECT" TO "DISCO_EUL";
GRANT "RESOURCE" TO "DISCO_EUL";


So I restarted the process to create the EUL, instead this time I selected to user a user that was already created.

And of course, this time it was successful…

When I went to install the VIDEO5 data I received the same error and used the same work around to solve the problem. OK, thats enough for today.

Also, I haven’t forgotten to analyze the data differences between the two geocoders, I just realized they were in 2 different coordinate systems and I’m putting together a bigger article on how to do the conversions.

In my “Spatially Enabling Your Oracle Business Intelligence Solution” presentation today a question was asked about free geocoding services. The Navteq and TeleAtlas spatial data is expensive and there are some free alternatives out there. One of those options for US data is the website geocoder.us . The data is gathered from the US census data and is definitely not as accurate as the commercial providers, it does an extremely good job at making it easy to geocode your data and is free for non-commercial purposes. Thats right FREE!!! If you do decide to use it for commercial purposes their pricing is more than reasonable in my opinion, as of today they charge $50 US Dollars to geocode 20,000 addresses. There developer documentation is available online and they provide four different web service interfaces to access the data (http://geocoder.us/help/). As promised, here is a simple function that uses their CSV webservice to geocode an address and return it as the Oracle Spatial native datatype sdo_geometry.

CREATE OR REPLACE FUNCTION geocode_address_geocoder_us (
street varchar2 --Includes street number and street name
,city varchar2 --Name of the city for the address
,state varchar2 --US Standard Postal Abbreviation for the state
-- Official List: http://www.usps.com/ncsc/lookups/abbr_state.txt
,zipcode varchar2 --Either the 5 digit or zip+4 notation for the address
)
RETURN sdo_geometry IS

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE;
WHEN OTHERS THEN
RAISE;
END geocode_address_geocoder_us;
/

The code uses a function from an askTom article to urlencode the query string sent to the geocoder.us webservice. The function is available here: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10444643777538. This function is included inside of HTMLDB and Oracle Portal, but I can’t assume your using those products so there you have it. The geocoder.us service will attempt to make changes to the address if it doesn’t follow the address listed in the census data. I will warn that this function does not take into account any of those changes to the address that the geocoder returns. I can write a procedure, if need be, that takes that into account if anyone needs one.

Tomorrow night, I’m going to do some analysis to compare how close the geocoder.us data matches the free geocoder data available from Navteq for San Francisco.

A week out at Oracle World is enough to kill anyone, I thought that I would get a week of rest away from work. Boy was I wrong. It seemed like everyday I was busy from 7am to 10pm. The BI Customer Advisory Council was really good. There are a lot of people out there doing some cool stuff with Discoverer. Its amazing how many different ways a ‘Ad-hoc Query’ tool is interpretted by so many different people.

I really enjoyed taking a look at the new OWB “Paris” it really helps in automating and prototyping Discoverer EULs. Jean-Pierre Dijcks and John Leigh really put on a good presenation about this, you can find it here .

I went to the inaugural Oracle Spatial Special Interests Group meeting on Tuesday night. It really wasn’t what I expected, way too many sales guys and not enough people who are in the meat everyday. And the technical people I met were more interested in spatial data rather than rendering information in a map. They are having a follow up meeting at a conference in March. I’m not sure whether or not I want to go yet.

Speaking of Spatial and GIS, a lot of people want mapping built into Discoverer. Being able to select data like from a parameter from at map and also displaying maps like another graph type. I think its a great idea. Obviously, since I went through so much pain implementing it in the previous versions of Discoverer. I’m happy to continue adding the functionality but if its something they want to support I’ll let them. By not having an API into their cache and backend it really makes it painful to having to rewrite the integration with almost every upgrade. I think a lot of people aren’t thinking about the cost of the spatial data if they enable mapping in Discoverer though. I haven’t found a good source of cheap, accurate and detailed spatial data for Oracle yet. Hopefully they’ll realize the need for people to support custom geography as well since most corporations have their own regional, market and retial store geographies that would need to be included as well.

Its really going to be a fun couple of years for the Oracle BI team. It looks like Larry is back to supporting them and the rest of the tools stack and there is a definite drive to have the best BI tools in the industry. I think with the right group of people they can be there pretty quickly

Most people have mixed emotions when have to goto a conference. First you think, ahh a nice 4 days away from the office, then the reality sets in that the work doesn’t stop when your gone and chances are your gonna have to work twice as hard when you get back just to catch up. I’m actually looking forward to this year’s Oracle World Conference.

The Business Intelligence group is going to launch a huge number of new applications. Oracle Discoverer Drake will finally be released. I’m going to be on the customer panel for the Mini Keynote called “Oracle and Gartner unveil the new Oracle Business Intelligence 10g.’ My first 5 minutes of conference fame.

I’m also looking forward to taking a look at the new Oracle Warehouse Builder (OWB) Paris from what I’ve heard it does a great job at creating OLAP warehouses and automatically generates the Discoverer OLAP EULs. Should be great for doing some rapid OLAP prototyping.

Lastly, I’m geeked to see Oracle’s plan for SOA, while probably nothing ground breaking, it’ll be interesting to see what components Oracle is going to implement for their SOA stack, whether its just a container and BPEL or if its actually a big competitive stack with logging, auditing, security, realiability, etc. Most importantly how closely does it follow the standards that are out there.