|
OBIEE and Source Control?
One very difficult aspect of using OBIEE (or APEX for that matter) is that it doesn't lend itself very well to source control.
There is one RPD (metadata) file in use at any given time. Changes to this environment will affect anyone using or developing on the presentation server layer (Dashboards/Answers/etc). For the web catalog (Answers/Dashboards/Prompts/etc) you can make changes, but again, it will affect anyone who is also using the tool. If you want to tweak a report that has prompts or filters, you need to save everything off to your own folder in order to work on it or risk colliding with others or worse, messing up the report beyond repair (also known as FUBAR). Developers usually need to break things to fix them and giving them an environment where they can do this (also known as tinkering), without repercussions, should be high on the list of must-haves. Inspired by a meeting I attended last week and Jake's recent welcoming of VirtualBox into the Oracle fold, I decided to think (yikes) my way through a possible solution. After the meeting last week, I was convinced I could build an environment using subversion as the source control tool. Tie that in with Jira, Fisheye and ultimately Bamboo and you'd have a pretty sweet environment to work in. How to do it though? 1. Set up source control. SVN is free and runs on Linux. Free. 2. Convince a multitude of developers to install and configure OBIEE on their own workstations. Yeah...not so sure about that. I accidentally said in that meeting, "I don't know a single, good developer who doesn't have a local install of Oracle (the database)" May have been just a tad hyperbolic...I like to tinker and appreciate those environments which allow me to do so. Having a local sandbox has been indispensable for me. 3. Not sure where or what 3 is. That's where I got hung up...until reading Jake's post. How about this then? 1. No subversion (for the time being). 2. Virtualize the development environment. 3. Hand out the VDIs to the developers, and let them run with it. When they make changes, they can promote them through the usual channels. Once those changes are accepted/merged with the development environment, a new snapshot is taken and distributed. Having the snapshot of dev is the key I think. Those who don't like to tinker, who just like to get their job done, won't have to worry about configuring their environment. They'll just fire up the VM and do their work. For those that do like to tinker, they can fiddle with the VM as much as they want without fear of breaking things for everyone else. If they need a fresh start, just get the original VDI and go crazy again. This hasn't been completely thought at (if you couldn't tell). I haven't considered passwords or other such sensitive data. It sounds good in my head though. What's the worst that could happen? Labels: design, obiee, source control ¶ 2/08/2010 11:43:00 PM 2 Comments Links to this post 
Random Things: Volume #13
A brief respite for me...I get to work remotely this week.
Admittedly I have gotten into the habit, finally, of traveling, so it's a little strange to be home (never thought I would say that). There is no shortage of respect from me for those who travel all the time. It's a hard life. OBIEE Performance Our Usage Tracking reports are a tad slow and I've been looking into the logs trying to decipher them. Unlike the database which has a multitude of resources, OBIEE has hardly any. Christian did point me to this Mark Rittman article, which is good, but not great (not because the author is lacking...there just isn't that much to go on). rnm1978 suggested MOS, but I don't have access right now...besides, it seems to be having problems again anyway. While I'm on the subject of performance and rnm1978, I should link up to this article which highlights 3 recent posts by Cary Millsap. All 3 are excellent reads and require your immediate attention. Kate The reason I am home this week is so that I can go to a Doctor's visit with Kate. We finally found a place where they might be able to help diagnose her. Most of her doctors have been more concerned with keeping her alive (healthy) and haven't worried too much about her developmental delays (still not talking...but she can sign "daddy"). Anyway, the place is called The Tridas Center and we're excited/nervous. Excited about finding her better help and nervous about the possibilities (more specifically, what a diagnosis would mean). ¶ 2/08/2010 03:49:00 PM 2 Comments Links to this post
OBIEE: ORA-12154 could not resolve connect identifier
I'm playing around with OBIEE using the SH schema (sample schema). I've imported the tables successfully, created the logical and presentation layer when I decided to change the driver from ODBC 3.5 to the OCI driver. When I went to run a simple report to test the Times dimension, I received this:
OK, no worries. I tried to update the row count through the Administration tool and got this:  Barnacles. So I begin to troubleshoot. First though, here's the setup. OS: Windows Vista Ultimate Database: 10gR2 (local) OBIEE: 10.1.3.4 (local) There is no other Oracle software running on the machine. First I went to Google. Nothing really exciting there, but it did remind me of the OBIEE forums (which I posted to for the first time yesterday). So I did a search there. Reading through a couple of posts confirmed what I had done. 1. tnsping my local database. Check. 2. Connect to the local database via SQL*Plus. Check. 3. Check environment variables. TNS_ADMIN is setup to point to my one and only tnsnames.ora file on the machine. Hold up...what about the PATH variable? I've seen before, in my millions of local installations in Windows, where the order of paths will affect how certain things work. Here's what mine looked like (hard returns added for readability): C:\oracle\bin;Hmmm...I wonder if having the database path first is causing this? Why would it though? OBIEE doesn't have it's own tnsnames.ora...so it shouldn't, right? I moved c:\oracle\bin behind the final OBIEE path. Guess what? I was able to connect. I'm not sure exactly why this happened, I wasn't able (or was just too lazy) to find the exact reason in the forums (I'm sure Christian or Robin will leave me a note), but it worked. Yeah for me. ¶ 1/27/2010 12:36:00 AM 5 Comments Links to this post
OBIEE: Tooltips in Answers
The title was OBIEE: Hack The Gibson...I didn't hack the Gibson nor I am I 733t.
But I did manage this little trick.  I have involved myself in a situation where we don't change the column names in either the logical or presentation layer. Instead, we use a database (Oracle) table and pull in the values using a row-wise repository variable. I won't get into all that, but I will demonstrate the basics. First off, in the Administration tool, open up the properties of a column, I'm using PROD_NAME from the SH schema PRODUCTS table. Check the "Custom display name" and just leave the default value. Then enter in some text into the Description box...doesn't matter what it is.  You can see that the text I put into the Description box matches that in the tooltip from Answers. Now comes the fun part, where the real hacking begins. There are 2 parts to get this accomplished: 1. You need to add the reference to the javascript file 2. You need to change the javascript function used by OBIEE to accomodate the call Neither one of those is easy...especially so for the uninitiated. I'll start with #2. Looking at the page source for that frame, I found a bunch of javascript references. Of particular note was treeutility.js. Once I found the file which is located in <BI_HOME>/oc4j_bin/j2ee/home/applications/analytics/analytics/res/b_mozilla At approximately line 1359 you'll see this: strBuf.append(this.getSelectHtml() + " title='" + saw.encodeHTMLAttrValue(this.getToolTip()) + "'>"I know enough to be dangerous naturally. The BoxOver javascript call is pretty simple, you just need to add "parameters" to the title attribute. Something along the lines of: header=[Description] body=[some text goes here]So just add those to the javascript function above and let OBIEE, with it's call to saw.encodeHTMLAttrValue(this.getToolTip()), do its work. Now the fun part, how do you get that reference to the javascript file to show up? This is how you normally reference a javascript file in HTML <script language="JavaScript" src="res/b_mozilla/browserdom.js"></script>How to add that to the template is the hard part (for me anyway). I "grepped" the source code in the <BI_HOME> for treeutility.js. I got 3 hits back: <BI_HOME>\web\msgdb\messages\deliverstemplate.xml <BI_HOME>\web\msgdb\messages\commonuitemplates.xml <BI_HOME>\web\msgdb\messages\criteriatemplates.xml Despite the stern warnings not to edit those files, I monkeyed with them anyway. Wherever I found the reference to treeutility.js, I added another line referencing boxover.js. Stopped the BI Server and BI Presentation Server, cleared the browser cache and restarted. Nothing. After searching the OBIEE forums, I found a link to these (HTML) documents which talked about changing the styles and skin for the Presentation layer. In particular, this one. To make a really, really, really long story short, I added a file to the customMessages folder (which I had to create) underneath my language (l_en), called custommessages.xml.  The contents of that folder are as follows: <?xml version="1.0" encoding="utf-8"?>Note that the first 3 attempts are commented out, so I only really need that last WebMessageTable entry. One thing I still need to resolve though, is that my bullet points disappeared (first screenshot above). But...I got what I needed and I can tinker a bit more to get the desired results. Before doing this on your production system, please ensure that you are allowed to modify the supplied javascript files. The custommessages.xml file follows the guidelines set forth, so I think that's safe, just not sure about the javascript. ¶ 1/25/2010 10:36:00 PM 1 Comments Links to this post
OBIEE: Connect to Remote RPD
There is occasion were you will not have physical access to the machine which hosts your RPD.
How to connect? First create a new ODBC Data Source, you can find that in Control Panel --> Administrative Tools  Choose the System DSN tab  Click on Add  Then select the BI Server driver  You'll be prompted with this:  For name enter "remote_rpd", for server, select the location of your other server, in my case, it's XP  Enter the username and password to the remote RPD (if you haven't changed anything, it's Administrator/Administrator)  Go ahead and accept the defaults  You should now see "remote_rpd" in your list of data sources  Finally, open up your BI Administration tool and you should see "remote_rpd" in your list  Enter the password and voila! You are in. 
¶
1/18/2010 07:41:00 PM
3 Comments
Links to this post
Random Things: Volume #11
Work: Consulting
I just returned home from my first week consulting. I went to the northern part of Alabama and didn't have much of a connection (phone or data) most of the time. From my perspective it was a rousing success. Now I just need to wait and see if the client approved of my work. I chose to drive as it was semi-last minute (last Thursday I found out) and I haven't had a good road trip in years. I promise it wasn't a fear of flying. On the way I picked up the guy I would be working with at the Atlanta airport. We had only (briefly) communicated through email and one "get up to speed" phone call. I think it's safe to say that we got along pretty well. After a couple of hours with the client the following day, they asked how long we had been working together and were absolutely shocked to learn that we had just met. As to the work, I got to rekindle some of my OBIEE administration skills, specifically the rpd (or metadata). It was fun to get back into it as I had been prepping for this the past couple of weeks. By day 2 I was in a zone (adding physical tables, replacing logical table data sources, etc). My colleague's expertise was in Answers, so I got to see some pretty cool stuff. I'm trying to talk him into blogging and offered him a guest slot to see if he likes it. The clients were great and easy to work with...overall an A+. Google Latitude As I have a new blackberry (couldn't afford the droid yet), I managed to download my Google Contacts, GMail and most importantly, Google Maps...which ties in with Google Latitude. I could see myself driving up I-75 and even better, I could expose my location to my wife and parents (I am an only child...the center of their universe :). I even tried to use it to find my phone which I had misplaced this afternoon (it had fallen under my seat) to no avail. Basically, I believe it uses the cell towers to approximate your location, the closest I saw it was within 800 meters. Amusingly, as we're driving to Atlanta this afternoon and my father calls...WTF? he says. "I'm taking my colleague to the airport" "Are you going back?" "No, I'm headed home" I had neglected to tell him that I would be there through Thursday...he of course thought the worse. I have 2 pretty cool guest posts on the way. Mr. Meyers has one and Mr. Piwowar finally got to Part 3 of his EBS install guide. :) I'll post those next week so they don't get lost in the weekend down time. ¶ 12/18/2009 01:12:00 AM 5 Comments Links to this post
OBIEE: Install on Windows
This install on Windows is not significantly different than it is on Oracle Enterprise Linux, in an effort to maintain my Learning OBIEE page, I felt it necessary.
I know there are differences between the different flavors of Windows, but it certainly doesn't feel like it. I remember installing a 9i EE Database on XP Home 6 or 7 years ago, that was kind of fun. I'll stop blathering on. OBIEE: 10.1.3.4 Host: Ubuntu 9.04 Virtual stuff: VirtualBox Guest OS: Windows Vista Ultimate From what I understand, OBIEE was originally intended for Windows (Server) environments. All of the client software runs on Windows only. The Server software runs on both *nix and Windows. So no matter what, I could not avoid a Windows installation. Part II of Installing on OEL would be "Installing the OBIEE Client Software", which is this post. I will not only be installing the Server components, I will be installing the Clients Applications too. I'll stop blathering on...part II I've already installed Java 6. I've located my setup.exe file and start the installation  Next Specifying the directories, I accept the defaults. I'm also using OC4J. Next.  Complete. Next.  Point to the SDK, enter in my oc4jadmin password...hopefully I'll remember it this time.  Accepting the default, you don't need to enter anything here.  I am setting all the services to start manually. This is a virtual machine and do use it for other things. Next.  English. Next.  Summary. Next.  It's now running.  Small side note, OBIEE installer reported 100% within minutes of starting. It reported 100% completion for about 30 minutes. Yeah, done. Some notes on OC4J. Next.  Restart your computer. Finish.  Almost 32 minutes to complete.  I've restarted the machine. 0C4J is started automatically (you can turn this off, and I will...eventually)  Now, go into your Services applet. To get there, go to Control Panel --> Administrative Tasks. There you should see an icon that says Services. Scroll down until you see the services that begin with Oracle.  All of these are stopped (trust me). First up, start the Oracle BI Java Host Service    Next is the Oracle BI Presentation Server    Now let's test them out. Go to http://localhost:9704/  Good. Now let's login. On the right side of that page, click on Launch. Enter the oc4jadmin password you submitted during installation.  That should take you here  Now for BI Publisher  Check. And Analytics?  Check. Applications Now I just want to make sure I have all the applications. Administration Tool  Check. Catalog Manager  Check. I'll save all the connecting of all the parts for a different post. Labels: howto, install, obiee, windows ¶ 12/12/2009 01:00:00 AM 0 Comments Links to this post
OBIEE: Architecture (Pretty Picture)
Pretty picture supplied by the Oracle documentation, p. 16 in fact. H/T rnm1978 [ blog | twitter ]
 Labels: obiee ¶ 12/11/2009 01:28:00 PM 0 Comments Links to this post
OEL, OBIEE, VirtualBox and Shared Folders
I spent the week installing OBIEE in Windows (Vista Ultimate) and OEL 5, Update 4. I've always tried to install the software I'm working with, it helps to give me a better understanding of what's going on. I don't always understand at first, but after prolonged exposure, I can more easily put the pieces together.
Digression I can remember trying to install OAS on a Windows server back in 2002, multiple times. I had absolutely no idea what I was doing...just clicking buttons and stuff. In particular, there was the was this one night...err, morning, 4 AM to be exact where I was installing the software for what felt like the 200th time. Did I mention I was onsite...and in the server room? The only reason I remember that particular time was the justification I was using...LC was T minus 1 week. Post In the OEL/OBIEE article, I mentioned some problems that I had, those problems are the reason for this post. I'll recall the setup: GuestI believe that's all that is relevant. If I left something off, let me know and I'll add it in. This is a fresh install of OEL. After installation of the OS, I installed the Guest Additions so I could 1, get a higher screen resolution and 2, I could use Shared Folders. From the host, I unzipped the OBIEE software into /opt/software/oracle/obiee/linux, I then un-tarred (?) the resultant .tar file. From the guest, I logged in as root and issued: mount -t vboxsf software /mnt/software* You can see the full set of instructions on the Shared Folder post I then navigated to the directory via the mounted path /mnt/software/oracle/obiee/linux/RH_Linux/Server/Oracle_Business_IntelligenceFrom there I followed the instructions. [oracle@localhost Oracle_Business_Intelligence]$ ./setup.sh -consoleIt would just hang there. For the longest time. I poured over the documentation to see if I missed a required library or something. Nothing. I ran and reran the UnixChk.sh script multiple times (the script checks to see if OBIEE can be installed and outputs SUCCESS or FAILURE). I would start and stop this installation over and over. Going so far as to let it run overnight (which for me is about 5 hours). I could get past this part, given sufficient time, typically down to the point where it is actually writing the software to disk (presumably anyway). Installing $L(com.siebel.analytics.install.AnyStringConstants,Again, it would just sit there. Finally, on Wednesday evening I saw a friend of mine (Tom Roach, former President of SOUG) online (or did he see me?). Anyway, I told him about the situation and, since he expressed interest in my problem, I offered to show him. We then spent the next 30 minutes trying to get him access to my computer. Suffice it to say, I can do that easily on Windows. Ultimately, he talked me through enabling SSH and he was in. That's when the magic began. As I watched him type up all these commands, looking through scripts...and starting and stopping the installation. I had my own personal (*nix) trainer. First, I believe he ran [oracle@localhost Oracle_Business_Intelligence]$sh -x ./setup.sh -consoleWhich then output all kinds of stuff I didn't recognize. I asked him later what it was, the sh command let's you debug the shell script to get a better idea of what is going on. When he killed it, I noticed an error, "'run' class cannot be found" or something along those lines. Before Tom showed up, I had suspected there was something going on with my Java environment. I tried using jcontrol to change the memory allocated to Java upon start with no luck. I began installation, then ran jconsole to see what was going on with the JVM. I saw "things" that must have been the problem...none of this proved out in the end. In fact, I think I told Tom that originally, that I suspected that the Java environment wasn't set up correctly. He then noticed that it was calling Linux.jar in the same directory. So he ran that directly (to eliminate a possible error in the main script I'm guessing): [oracle@localhost Oracle_Business_Intelligence]$exec Linux.jar -consoleor something very close to that. Occasionally he'd run this "history" command. Wow! You can see all the commands you've entered. That's helpful. Next up he tried to run "strace." "File or directory not found" Tom, prefix it with /sbin or /bin. No go. It's not installed. I then began to look for it on 1 of the 4 ISO images. Found it and loaded it. ENTER STRACE COMMAND He then began to pour through the when he notices lines like this: wait4(5171, 0xbfa23aac, WNOHANG|WSTOPPED, NULL) = 0Googled those, but I believe he had his suspicions. He also found the following useful, but I'm not sure why. I think he mentioned something about the fork() and trying to change directories but being unable to. getcwd("/home/oracle"..., 1024) = 13If I can't recall very clearly it's because we started this IM at about 11:30 PM EST. It was now approaching 1 AM.Based on what he said and the problems Jake had previously, I began to seriously think it might have something to do with the Shared Folders. I wondered aloud on IM, "Could this be due to the Shared Folders and the OS's inability to write there?" Tom, "Perhaps" I asked if I should just move the software to the OS and run it from there. By then Tom was giving me a great tutorial on screen. Tom didn't get to installing the software successfully, but he gave me sh, screen, history, and strace. By reviewing the output with him and explaining what I was trying to do, Tom got me going in the right direction. After 2 hours, now 1:30 AM, Tom finally succombed. That was pure awesome...thanks again Tom for your help! I wasn't ready for sleep yet (besides being a bit of an insomniac), I had new life. First copied the files over to the guest OS. Ran the UnixChk.sh script then ran the setup.sh script. After about a minute, I was prompted for my first response. Sweet! I answered all the questions and went down to watch TV for an hour or so. I came back, answered a few more questions and it was done. Only to be destroyed later that morning and re-installed. Labels: failed, install, obiee, oel, virtualbox ¶ 12/11/2009 12:09:00 PM 10 Comments Links to this post
OBIEE: Install on OEL 5.4
I've had a fun week installing this on OEL. Six times I've started and stopped because it would just sit there and hang.
I had it installed a few weeks ago, but if memory serves me, I let it run overnight and everything was up and running. I blew that one away so I could get some more practice (and I wanted to write it up). I'll post on the problems later (thanks Tom!) but for now, these are the steps I took to install OBIEE 10.1.3.4 on Oracle Enterprise Linux 5, Update 4. For the uninitiated (i.e. Linux n00bs), it is no where near as "difficult" as installing the database. No creating "extra" users or groups...pretty much just rock 'n roll. I've already created a new VM with a user "oracle." Halfway through (this whole process) I thought about using "oraclebi" or something different as I will be installing 11gR2 on this machine, but I got lazy. Once the VM was created the very first thing I did was install Java 6 (1.6_0.17 or something) SDK with Java EE. I won't go through that installation, but you do need to remember, if you are new to Linux, that you must change the permissions on the .bin file to make it executable. Java is installed. Next up, I need to make sure the open-files parameter for ulimit is at or above 10240. How do you do that? With some help from a friend, I logged in as root and edited the /etc/security/limits.conf file. I added the following lines: #<domain> <type> <item> <value>Now logged in as oracle, I issue the ulimit -a command to see what the current setting is (should be 10240 as I specified that as the minimum (soft)) [oracle@localhost OracleBI]$ ulimit -aHow did I know to do this? It's in the docs, page 46 or so. I also need to add 2 directories: [root@localhost ]# mkdir /usr/local/OracleBIThen change the ownership of those directories to oracle [root@localhost ]# chown oracle /usr/local/OracleBIOK, we're cooking now. Wait, need to change the permissions on the location of the SDK you just installed. OBIEE provides you with a script to do this (and one to reverse it), but I just [root@localhost ]# chmod ugo+rwx /opt/SDK -RYes, I opened it up to the world...this is just a sandbox. I can do what I want. Obviously not recommended for production purposes. Almost there. Navigate to your unzipped/untarred location which for me is /obiee: [oracle@localhost ]$ cd /obiee/RH_Linux/Server/Oracle_Business_IntelligenceFirst up, run the UnixChk.sh to make sure we've got everything. [oracle@localhost Oracle_Business_Intelligence]$ ./UnixChk.shSweet. I'm running this in console mode because I can't seem to run the GUI. Both xterm and xclock work, as suggested by the installation guide, but it still fails. I'll worry about the another day. To install [oracle@localhost Oracle_Business_Intelligence]$ ./setup.sh -consoleThat "Preparing Java(tm) Virtual Machine" part is where it was hanging up before...it takes about 60 seconds to initialize. Enter 1 and hit Enter. -------------------------------------------------------------------------------Just hit enter to accept the default (Basic) Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 1Enter 1 then Enter. Choose the setup type that best suits your needs.Hit Enter for Complete, otherwise enter the number for your choice. Enter 1, Enter (Next). Now you are prompted for the location of the SDK you just installed, enter it and hit Enter -------------------------------------------------------------------------------Then enter your password, confirm it and hit "Next." Set the password for the oc4jadmin administrator user. This password should beError Message Language Selection...for me the default, hit Enter and then Next. Then Please wait... Error Message Language Selection"Confirmation Screen", hit Next -------------------------------------------------------------------------------Then the fun starts... Installing $L(com.siebel.analytics.install.AnyStringConstants,I skipped a Next there, I didn't think you would mind. Total time for installation?  Your software is installed, now what? Now you run and test it! Three things I need to do to start it: 1. Start the Oracle BI Server (ra-sa.sh) 2. Start the Presentation Services (ra-saw.sh) 3. Start the OC4J instance (oc4j) These files are located in /usr/local/OracleBI/setup [oracle@localhost OracleBI]$ ./setup/run-sa.sh startAnalytics [oracle@localhost OracleBI]$ ./setup/run-saw.sh startAnd finally OC4J [oracle@localhost OracleBI]$ ./setup/oc4j -startMaybe now it's a finally...and finally some pretty pictures. OC4J Home  OC4J Admin Login  OC4J Admin  BI Publisher  Analytics  Next up, configuring data sources. ¶ 12/10/2009 11:40:00 AM 5 Comments Links to this post
OBIEE: OEL Uninstall
I've been fighting with VirtualBox and OBIEE the last couple of days trying to get it installed. Finally resolved the issue early this morning with the help of a friend...post coming shortly.
Now I'm uninstalling it so I can repeat the process from this morning in a more lucid state. I've found the documentation for OBIEE not quite as robust as the database documentation. Specifically, it's not HTML-ified. So direct linking is (near) impossible. On page 97 of the Oracle Business Intelligence Infrastructure Installation and Configuration Guide are the uninstall directions. Nothing fancy mind you, but I've gotten in the habit lately of documenting everything I do, no matter how seemingly mundane. In OracleBI_HOME there is a directory called...uninstall: [root@localhost local]# ls -ltr OracleBIIn that directory is setup.bin, run it. [oracle@localhost local]$ ./OracleBI/uninstall/setup.bin -consoleI was running it in console mode (i.e. command line interface or CLI) because I've been having problems getting the GUI version to run. Since I'm somewhat lazy, I haven't bothered to figure out the cause (xterm and xclock do work though). Again, the documentation on OBIEE isn't as strong as it is for the database. Console works just fine though... -------------------------------------------------------------------------------Select 1 and Enter. You'll then be prompted for the oc4jadmin password -------------------------------------------------------------------------------Enter it, enter 1 and enter Enter (couldn't resist). Please wait while processes shutdown.All done. Now a little cleanup: [oracle@localhost local]$ rm -Rf OracleBIDone. Now to reinstall for the last time. ¶ 12/10/2009 09:11:00 AM 2 Comments Links to this post
Learning Oracle Business Intelligence Applications (OBIA)
In the same vein as Learning Oracle Business Intelligence (OBIEE), this will serve as a summary for me (and hopefully you) on all things OBIA. I need to get ramped up rather quickly so I should be doing a lot of writing (to reinforce) to go along with it.
Let's start with the basics.
Essentially, OBIA sits on top of OBIEE and is setup to run against PeopleSoft, JD Edwards, EBS and others. For now, I'm interested in the EBS side of things...I've been downloading and unzipping all weekend. John Piwowar [ blog | twitter ] has promised to do a series of posts in the very near future detailing the installation of EBS. I've been the fortunate recipient of his EBS wisdom this weekend which means I've only had to download 46 GBs of install files. WTF is that all about? ¶ 12/06/2009 12:37:00 AM 4 Comments Links to this post
ORACLENERD, Inc.
Today I made it official...I filed my paperwork with the State of Florida to incorporate. It seemed fairly obvious what the name should be.
If you read the previous post, you probably gathered that little fact. All of this is related to the fact that I have a new job. I'm not quite ready to talk about the details yet but I believe there is quite a bit of opportunity here. For one, I'll be working with OBIEE again. It's been about 9 months since I last worked with and I was just getting started. There may also be some work with APEX, which would be great. Finally, there's a very outside shot that I may get to work with Exadata v2 in the somewhat near future. Maybe you're wondering why I incorporated? I don't know, just seemed like fun. Actually, I'll be working as a consultant. This is my first real trek down that road. I have to learn about billable hours, successful travel planning...and probably most importantly, budgeting. I'm very excited about this opportunity. I will have to travel a bit, but I'll also get to work from home quite a bit so I believe in the long run, family time will probably be greater than when I was going to that "9 to 5" type job where I was actually away from home more than 60 hours a week. We'll see. ¶ 11/22/2009 11:38:00 PM 11 Comments Links to this post
OBIEE: Christian Berg
The other day I told the story of how Christian Berg went out of his way to give me some invaluable pointers, OBIEE: How to Migrate Your rpd.
Apparently I haven't been the only one putting pressure on him to share his knowledge because he started his blog yesterday. So take a stroll over to his site and welcome him, then add him to your feed reader of choice. ¶ 3/28/2009 12:33:00 AM 2 Comments Links to this post
OBIEE: How to Migrate Your rpd
None, absolutely none of the following is anything I produced.
Again, Twitter to the rescue.
Who is Christian Berg? Well, to me, he's the guy who commented on my first OBIEE post. We corresponded back and forth via email. After that and I believe I convinced him to join twitter. He was very helpful in his emails to me, pointing me in the right direction and so on. Anyway, shortly after my last tweet above, I received an email from Christian. It was a detailed explanation of the different ways that you could migrate your rpd file, or what you have in development/qa to production. So, with permission, I'll reprint the email here (by the way, people need to pressure him to blog, I don't want to have to keep giving him credit here ;). Christian Berg 1.) You have a full multi-user development environment which allows you to group your rpd objects in "Projects" and use a check-in/check-out mechanism against a central repository. I.e. there's the central rpd on the server, you check out a project, make your changes and check it back in to the server. [ link ] 2.) You merge your local repository with the production repository. This one you knew probably and can't use it since your local repository contains more changes than you actually want to transfer. So here's a little trick: strip your local rpd down to the bare minimum of changes you want to propagate. This way all the merge will do is an upsert and shove all your new objects into the central rpd. [ link ] 3.) You can use "Import from archive". It's been deprecated and inactivated by Oracle in order to push people to usethe "merge" functionality. However, it's still alive and kicking in the background. It's a nice feature if you know exactly what you want to transfer and if what you want to transfer is really "encapsulated". I.e. you don't start shooting into generic business models or stuff like that but have all in nice purpose-built objects which are added on top existing ones. Since - as far as "updating" goes - the import is the most brutal one. [ link ] There you have it. I would encourage all of you to do the following: 1. Let Christian know that he needs to start/resume his blog. 2. Give him lots of money by way of jobs or bonuses. He is most deserving. Labels: humility, obiee, oracle ¶ 3/25/2009 06:48:00 PM 0 Comments Links to this post
Time
Many years ago (about 6 or 7 really), my boss sent me this diagram from the Oracle Docs. In particular, he wanted me to focus on the times table.
 After a few emails exchanged I finally figured out what he was asking, well sort of. He had used a table called periods described below:CREATE TABLE periodsThis was primarily (solely perhaps) used for reporting. He had actually built this pretty cool page for the business to be able to drill down on certain key aspects of the business. That may have been my first real exposure to Data Warehousing. The point he was trying to make though was that this TIMES table was a flattened version of his PERIODS table. If you have one date, you can see how that date fell in multiple dimensions. What month it took place. What quarter it took place. What day of the year (1-365) it took place. All with just a single row of data. The TIMES table, or dimension is a standard in data warehousing for exactly this reason. I've been working on our "data warehouse" (replicated tables from the OLTP) and had been using the PERIODS methodology. There is a time dimension, but it's not populated at this time. Since I am by no means a data warehousing expert and I have virtually no experience with OBIEE, I'm trying to come up with a way to help bridge both those gaps. Use OBIEE against the relational tables to help me learn OBIEE. I've created a couple of small subject areas so far and demo'd them to the users and they love it. So do I in fact. Anyway, back to my topic. As I was thinking about TIME, I began to reminisce about time and how I got started. It's funny how I remember that exact moment sitting there with my feet propped up on my desk in my garage office. Was there a point to all this? I don't think so. Just me rambling on. I'm not sure exactly what triggered this ton Labels: datawarehouse, obiee ¶ 3/11/2009 11:39:00 PM 0 Comments Links to this post
OBIEE: Retrieve report SQL
Wow, it's been almost 2 months since I asked how to retrieve the SQL from all the Answers reports. Like I said in my previous post, it would be nice to have an Object Dependency button or something like Application Express has.
I started to think this afternoon, the reports are stored in XML files, wouldn't it be easy to just search through them and extract the SQL? Better yet, maybe Catalog Manager has a tool like that. It does! As I've gotten more comfortable with the environment things seem easier to find.
Once you select Create Report, you'll be prompted for fields you want to view:
Select Request SQL, fill in the path and name of the file you want to write it to:
You'll then see a window that indicates the report is running:
When the report is complete, you'll see the following:
Now just open up the file and browse the SQL:
Of course this won't be easy as there are 250 reports, but it certainly beats going through each individual report, opening it up, viewing the XML and copying the SELECT statements out. Plus, I really only need the Direct SQL Requests so that makes life a little bit easier. Labels: obiee ¶ 3/08/2009 06:15:00 PM 0 Comments Links to this post
OBIEE: Don't Change the Administrator Password!
I learned the hard way (naturally) that you shouldn't go and change the password for the Administrator account haphazardly.
Yes, I use the Administrator password and not a named account. Yes, I'm trying to change that. I've been handing out credentials to folks lately and decided to change the password after everyone had their new accounts. Got a call yesterday, "Hey, I'm not getting my reports via email any longer." Well, we had the big deployment last week and I figured it was somehow related. Login into the OBIEE web site (wtf is that called? Dashboard? Answers?), go to Delivers and see some alerts. Click through and read the error message: invalid username/password. What? Of course I had forgotten I had changed the Administrator password so I ran around trying to figure out why that could be. Thankfully I didn't run around too long. Could the password be stored in some config file (which I don't have access to)? I asked the DBA and he wasn't sure. I tried to use the "Run As" functionality on the iBot using the recipients username. Wait one day...Nope. That didn't work. (Can you tell I like the hit or miss strategy?) DBA sends me this link. Stop it! You really have to do all that to change the password? Kidding right? I reverted the password back to the original needless to say. I thought it would be quick and easy, but apparently not. Labels: obiee ¶ 2/18/2009 10:53:00 PM 2 Comments Links to this post
OBIEE: error : Odbc driver returned an error (SQLExecDirectW)
My method so far to make changes to our existing reports has been to
1. Open the report 2. Modify (Most, if not all, are Direct Database Requests) 3. Cut and Paste the SQL into my editor (JDeveloper) 4. Fix any issues 5. Run in SQL*Plus 6. Cut and paste back into OBIEE On occasion, I'll have to use the Catalog Manager, select report, properties, Edit XML and get the SQL from there. Obviously not the most efficient way, but it works. Others, like Mr. Berg, have suggested turning the log level to level 2 and capturing the SQL being passed to the database. I'm not that industrious yet. Anyway, I've run across this error a couple of times so far. When I do number 6 and click on Validate SQL and Retrieve Columns, I get the above mentioned error. Hmmm...I've just run this thing in SQL*Plus and it works just fine. The queries are really too big to simply eyeball either. So I went simple, in the text box I entered SELECT * FROM DUAL and clicked on Validate SQL and Retrieve Columns: error : Odbc driver returned an error (SQLExecDirectW). error : State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 17001] Oracle Error code: 933, message: ORA-00933: SQL command not properly ended at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000) error : SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "BIDEV"."Connection Pool" SELECT * FROM DUAL when mailing_group = ''EMPLOYEE'' then ''Other'' when mailing_group = ''TEST'' then ''Other'' when mailing_group = ''VIP-EXTERNAL'' then ''Other'' when mailing_group = ''SITEUSERS'' then ''Other'' else mailing_group end )')} It appears the query that previously occupied that box is still there...partially. Mr. Berg suggested clearing the cache, so I went into the Administration tool, Manage, but cache was grayed out. I asked the DBA and Linux guy to pass me the file, but they were being difficult today, so I didn't get it. In the short term, I just created a new report appended with " R" (refactored) and moved on to the next one. Hopefully tomorrow I'll be able to get some answers. ¶ 1/15/2009 12:07:00 AM 2 Comments Links to this post
OBIEE: Direct Database Request
Of course a couple of our leads asked last week if they could get access to OBIEE to do some reports. Naturally, they didn't want the point and click variety, they had the SQL (our OLTP is replicated to our DW as well).
I had no idea how to do it of course. Click click clicking around I couldn't find anything. Wait, BI Administrator. That's how you add users. OK, good enough. How do you give them access to write Direct Database Request SQL? I'm still not sure but the answer probably lies in the "Answer" section. From there, you can either select a subject area or select a "Direct Database Request." With the Direct Database Request all you need is the Connection Pool information (still not positive on how this is formatted) and your SQL statement. The Connection Pool entry looks something like this: "DEV_BI"."Connection Pool" You do not need to put the relevant tables into the Physical Layer of the Administration tool. That makes life a little easier. Labels: datawarehouse, obiee ¶ 1/13/2009 06:51:00 PM 2 Comments Links to this post
OBIEE: Copy Reports From Prod to Dev
Yes, I said from Prod to Dev.
As I recently reacquired my job via the DW guy resigning, I'm in charge of all his stuff. Which basically means I get to learn the easy way, fire. This means that I haven't had any time to "learn." I've just had to do. Fortunately so far, I haven't been asked to do a whole lot with the tool. Until now. We have a large migration in the pipe right now...massive changes to the core of our OLTP system which require massive retooling of our DW system. Naturally I've been working on the database side of things. I started out with some 150 invalid objects and I've whittled it down to 3. What about the reports though? That SQL is not stored in the database (at least as I understand it). Last week I began looking for a tool in OBIEE that would allow me to see database dependencies, kind of like Application Express' tool. Alas I couldn't find one, but I didn't have a whole lot of time to search. And I'm still learning the OBIEE lingo so I don't really know what to search for. Anyway, I chatted with my former colleague and he told me I could just update the catalog in the Dev environment. Talked to the DBA and the Linux admin about getting access but it never materialized (it was Friday I think and I was out sick today). I still don't know what all is involved other than there's some sort of file I can just swap out...which scares me. Chances are I'll write over something. So, off to Mr. Rittman's site. First, Migrating OBIEE Projects Between DEV and PROD Environments, which led me to this one, Migrating OBIEE Reports Between Web Catalogs. I hadn't seen (or heard) about the BI Catalog Manager, so I opened it up. It want's a directory or URL. Hmmm...I have no idea. I'll have to get the admin guys to help me out with this one. Let me just try the login URL and see if that works: http://bi_server_prod:7777/analytics/ Nope. But there's a hint, URL must be in the form of... http://bi_server_prod:7777/analytics/saw.dll Got it. Sweet. No outside intervention necessary. Log in to dev: http://bi_server_dev:7777/analytics/saw.dll Voila! Copy and paste the reports over and I'm done. Now the arduous task of going through each report to make sure it's not broken. Should be fun. Labels: datawarehouse, howto, obiee ¶ 1/12/2009 11:14:00 PM 1 Comments Links to this post
Learning Oracle Business Intelligence (OBIEE)
I learned how to edit a user in OBIEE without breaking anything (thus no need for a new counter Jake).
At first I thought it would be done through the web application only to find no edit button associated with the user. I had to go through the online repository, update the user, and commit those changes. Not really sure what all that means yet, but it's a start. I had a crash tutorial on this last week as the person I replaced was off to start his own company. OBIEE is a vast, vast world. Where does one go to learn OBIEE from the bottom? OTN naturally. The Oracle By Example series is excellent so that's where I started. First up, Creating a Repository Using the Oracle Business Intelligence Administration Tool . I've made it through the Physical Mappings and the Business Mappings so far, one more section to go. I can't say I completely understand all the lingo, but I'll catch on sooner or later. I'm more concerned with the basics of support at this point. Other online locations I've been reading as well: Rittman Mead Consulting is probably the best out there. Mark's articles in particular are extraordinarily detailed. John Minkjan over at OBIEE 101 seems to post quite a bit, but I haven't had an opportunity to read any of it yet. The Oracle Business Intelligence blog might be a good place in the future, though they haven't posted anything there since May of last year. I'm sure as time goes on my tastes will ebb and flow. I'll update as appropriate. Install 10.1.3.4 on Oracle Enterprise Linux 5, Update 4 --Details on issues with installation (using VirtualBox) Uninstall on Oracle Enterprise Linux 5, Update 4 Install 10.1.3.4 on Windows Vista Ultimate Connect to Remote RPD Labels: bi, datawarehouse, obiee, oracle ¶ 1/05/2009 11:02:00 PM 4 Comments Links to this post
|
Guest Authors
How To
Parallel Processing: DBMS_JOB
Write File to Disk Populate Time Dimension DBMS_CRYPTO PL/SQL: Split URL Parameters Instrumentation: DBMS_APPLICATION_INFO Create SAS Dataset OBIEE: Migrate Your rpd Application Express: A Guide
Popular
Why You Should Blog
AppDev vs DataDev Coding is Easy Fun With Linux Code Style Index Better than Tom Kyte? Previous Posts
OBIEE and Source Control?
Random Things: Volume #13 PRIMARY KEY and NOT NULL TNS-12533: TNS:illegal ADDRESS parameters Database Table Size Make Money Blogging OBIEE: ORA-12154 could not resolve connect identif... OBIEE: Tooltips in Answers The Long Drive Home OBIEE: Connect to Remote RPD Code Projects
Archives
August 2007 /
September 2007 /
October 2007 /
November 2007 /
December 2007 /
January 2008 /
February 2008 /
March 2008 /
April 2008 /
May 2008 /
June 2008 /
July 2008 /
August 2008 /
September 2008 /
October 2008 /
November 2008 /
December 2008 /
January 2009 /
February 2009 /
March 2009 /
April 2009 /
May 2009 /
June 2009 /
July 2009 /
August 2009 /
September 2009 /
October 2009 /
November 2009 /
December 2009 /
January 2010 /
February 2010 /
|
