Oracle Warehouse Builder OWB 11g New Features Part 2!

This is a continuation from Part 1: Part 1. Again, due credit to Oracle for providing some of this information to us on OWB 11g!

Let's recap on the unified repository addition. The single repository owner for OWB 11g is named OWBSYS, which owns the actual repository. This user does not functionally own objects but owns them physically from a schema perspective. The single unified repository enables maintaining a single copy of OWB database objects in OWBSYS (tables, views, PL/SQL packages, and so on).
Benefit of Pre-seeded OWBSYS Unified Repository Schema?

Previously, each Oracle database that was utilized as an OWB repository required a user with SYSDBA privileges. Beginning with OWB 11g, SYSDBA privileges are no longer required. An OWBSYS schema is created during installation of Oracle Database 11g, release 1. OWBSYS holds the metadata, which is divided into workspaces. To start using Warehouse Builder, you merely need to create a new workspace. You do not need SYSDBA privileges. HOORAY!!

Accessing Workspaces Instead of Repositories

Previously, users accessed the repository as a whole. Therefore, users were referenced as repository users and repository owners. Beginning with OWB 11g, the term repository is replaced with workspace (from the user connection perspective). Thus, instead of granting access to a repository, you grant access to a workspace. Because of the use of workspaces in a single schema, creating workspaces is simplified.

When defining the repository, an administrator creates one or more workspaces, with each workspace corresponding to a set of users working on related projects. A common practice is to create separate workspaces for development, testing, and production. Using this practice, you can allow users (such as your developers) to access the development and testing workspaces but restrict them from the production workspace.

Default OWB Install Uses Database Oracle Home

Default install of the OWB set goes into the database Oracle home. However, you can still install OWB into its own Oracle home.

When you install the OWB 11g server to the Oracle database home as a database option during installation of Database 11g, the OWBSYS schema is automatically created.

When you install OWB 11g to its own Oracle home other than the Database 11g home, or to an environment using Database 10g, release 2, an OWB script is provided for you to manually create the OWBSYS schema.

There is now a SINGLE Control Center Service!
Instead of a Control Center Service per repository (as required in OWB 10g), OWB 11g requires only a single Control Center Service for the database instance, serving the OWBSYS schema.The single Control Center Service has the following characteristics:

• Services requests from multiple workspaces.
• Executes as the OWBSYS user.
• Maintains Control Center log data, including the workspace label.

A workspace owner is an OWB administrator who manages a particular workspace and registers users of that workspace. In the diagram above, workspace owner 1 owns workspace 1 and workspace owner 2 owns workspace 2.

If a workspace is created with a new database user as its owner, the workspace must be created in the OWB Repository Assistant using database DBA credentials.

A workspace is equivalent to an OWB 10.2 repository. There can be multiple workspaces in a repository.

There are minor security changes to support workspace user management. Beginning with Oracle Warehouse Builder 11g, release 1, the preferred method of implementing metadata security is through the user interface available in the Design Center (as described in the Oracle Warehouse Builder User’s Guide). If, in a previous release, you implemented security by using a PL/SQL package, Oracle Warehouse Builder 11g, release 1, supports that implementation.

Workspace users connect to a workspace after the workspace owner grants them access. These users work essentially in the same way as repository users work in OWB 10g. However, workspace users operate at what is now called the workspace level (rather than the repository level).

Next time we will discuss new connectivity options and the OWB connector to Siebel.

Oracle Warehouse Builder OWB 11g New Features!

OWB 11g has a myriad of new features that make this tool more and more accessible to new ETL technicians. The differences in installation and repository structure in the 10gR2 release were significant enough to make the 'understanding' of the architecture easier. Well now OWB 11g has taken this to another level. Not only that but the new features or enhanced architectural features make this product very attractive. Over the next few weeks I will be blogging a bit about the new features and enhancements. (due credit to Oracle Corp for providing some of this information and diagrams).
This is part1. My blog also has a part 2,3,4,5 as well as 6.

The first thing to mention is what Oracle felt were the goals for OWB11g. Why have a new release?
A few reasons:

• Better integration with the Database
• Easier installation because OWB is a database option.
• The SYSDBA requirement removed in a repository installation (very nice IMO)
• Reduced need for DBA credentials
• Database 11g as a source AND target.

Packaging remains the same basically as 10gR2 (core features and options).

• Siebel ERP/CRM connector is new though!
  

Repository Structure has changed from 10gR2 to 11g:

• The illustration below represents the OWB architecture prior to OWB 11g.

• OWB 10g requires one OWB schema per OWB repository. Multiple OWB repositories on an Oracle Database instance require maintaining multiple OWB schemas for multiple OWB repositories on that instance.
• Furthermore, OWB 10g requires one Control Center Service per OWB repository (This means multiple control Center Services).
• OWB 10g Does Not Install to the Database Oracle Home
• OWB 10g and earlier releases cannot install to the Database Oracle home; these versions of OWB require their own Oracle home. Because OWB 11g is integrated as an option in the Oracle database, the default OWB 11g installation is to the database Oracle home. OWB 11g architecture resembles the following:
• A major feature of the new OWB 11g architecture is the single unified repository (above) for each database instance, with a single Control Center Service.
• The single repository owner for OWB 11g is named OWBSYS, which owns the actual repository. This user does not functionally own objects but owns them physically from a schema perspective. The single unified repository enables maintaining a single copy of OWB database objects in OWBSYS (tables, views, PL/SQL packages, and so on...

Next time I'll explore more about repositories, workspaces, and the OWBSYS user, and Control Center Services.

Nifty little OBIEE Blog for beginners - OBIEE 101

I saw this blog today that I felt was worth sharing for beginners. Looks like real-world experience and good shots of various issues. not bad! link

APEX and BI Publisher Integration

We're working on a big client project that has quite a few technologies, including APEX and BI Publisher. We don't have them working together actually, but I have done some APEX work and lots of BIP, so I wanted to direct you to Dimitri Gielis's blog and this post specifically: integration

Oracle OBIEE and pre-built Analytics (Siebel) Architecture

This posting won't focus on the build it vs buy it thought process (which is valid considering these nice pre-built analytics solutions really cost a bundle!). Instead I wanted to first discuss OBIEE and Oracle E-Business suite or Oracle Apps integration. Now, there should be no mystery that a huge amount of operational data is flowing through your Oracle apps database. It should also be no mystery that you'd probably like to get as much value out of that data and see what it contains...forecasting, trending, historical data...you name it. That's where BI and OBIEE really come in.

Now with OBIEE, you can have a world class operational system with APPS and a world class BI and data warehousing solution using OBIEE. The big sticky here is what do we do? Custom develop the whole underlying architecture to get OBIEE up and running? We're definitely not going to report off of the apps database with our analytic reports! We need a solid data warehousing environment, data marts, and strong ETL to get the right data out of apps, and into our Data Warehouse. THEN we can consider OBIEE and its reporting capabilities.

You might however consider using the pre-built analytics that can be purchased. These can roll-up lots of reports, pre-built ETL, and many DW structures that are already there for your deployment. The typical client will be able to immediately use 60-80% of this pre-built package right out of the box. So what does this all look like from a bird's eye view? Well let us JUST look at the concept of the pre-built analytics below:

High-level analytical queries, like those commonly used in Oracle BI, scan and
analyze large volumes of data using complex formulas. This process can take a long time when
querying a transactional database, which impacts overall system performance.
For this reason, the Oracle Business Analytics Warehouse was constructed using dimensional
modeling techniques to allow for fast access to information required for decision making. The Oracle Business Analytics Warehouse derives its data from operational applications, and uses Informatica’s data integration technology to extract, transform, and load data from the transactional database into the Oracle Business Analytics Warehouse.
The Oracle Business Analytics Warehouse architecture comprises the following components:

DAC client. A command and control interface for the data warehouse to allow for set up,
configuration, administration, and monitoring of data warehouse processes.

DAC server. Executes the instructions from the DAC client. The DAC server manages data
warehouse processes, including scheduling, loading of the ETL, and configuring the subject areas
to be loaded. It dynamically adjusts its actions based on information in the DAC repository.
Depending on your business needs, you might incrementally refresh the Oracle Business
Analytics Warehouse once a day, once a week, once a month, or on another similar schedule.

DAC repository. Stores the metadata (semantics of the Oracle Business Analytics Warehouse)
that represents the data warehouse processes.

Informatica Server. When the server is loading or refreshing the Oracle Business Analytics
Warehouse, it is recommended that the server be dedicated to that activity. However, when the
ETL server is not loading or refreshing the Oracle Business Analytics Warehouse, you can use it
for other purposes.

Informatica Repository Server. Manages the Informatica repository.

Informatica Repository. Stores the metadata related to Informatica workflows.

Informatica client utilities. Tools that allow you to create and manage the Informatica
repository.

OBIEE post install OC4J and Windows Services

I wanted to download the Oracle Business Intelligence Enterprise Edition PLUS 10.1.3.3 recently. I didn't want to upgrade from my previous version since I had done so much work on it, it had become a war-zone after all the experimenting I had done.

Part of my post install was to get rid of the annoying automatic OC4J startup on Windows. The other part was to create simple .BAT files to startup and shutdown the multiple services that OBIEE uses.
If you've done the install as a simple non-Application Server install that uses the standalone OC4J container, read on:

After you have installed BI EE, you are asked to reboot your computer (this is for a Windows install). After you reboot and login, you will notice a DOS window which shows OC4J starting. Even if you set all the BI Services to "Manual", this particular command is still fired at startup.
This is because the command to start OC4J is set in the Windows Registry.
If you go to the Windows registry (type "regedit" in your "Start --> Run" textbox) navigate to "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" you will notice that an entry named 'oc4j' is created during the installation, which starts up the OC4J window you'll see every time you startup.

So I found that entry in the registry, I selected it, and I deleted it. We STILL have the ability to start and stop the OC4J process through our OBIEE start menu link:

What one can do is to create a batch file for Windows that will actually start and stop this OC4J process that we deleted out of the registry, AND while we are at it, we can also add the startup and shutdown actions for the other OBIEE processes too. This way, we have a master start and stop for all necessary services through one initiation.

To do this, open a Windows program like 'notepad' and enter the following for the startup.bat file:
This is how my "Start_BI" file looks like:
net start "Oracle BI Server"
net start "Oracle BI Presentation Server"
net start "Oracle BI Java Host"
C:\OracleBI\oc4j_bi\bin\oc4j.cmd -start

I then saves this file from notepad as Start_Oracle_BI.bat and chose the option for 'Save As Type' as 'All Files'.
Now the shutdown.bat file:
net stop "Oracle BI Server"
net stop "Oracle BI Presentation Server"
net stop "Oracle BI Java Host"
"C:\Program Files\Java\jdk1.5.0_08\bin\java.exe" -jar C:\OracleBI\oc4j_bi\j2ee\home\admin.jar ormi://localhost:23791 oc4jadmin oc4jadmin -shutdown force

One thing to note is that in the above .bat file scripts is that some of these locations are hardcoded.
Make SURE you have the necessary files in the right place, OR change the locations in the script to point to the right path. ALSO note in the shutdown script that the word oc4jadmin appears 2 times. This is because we assume that oc4jadmin is both the login and password. If you registered a different password, you'll need to change that in the shutdown .bat file as well. If you don't Oracle will not authenticate and allow you to shut down the oc4j process!

Now all I need to do is to click on my .bat files to execute them, which starts and stops all the important services!

MIX at Oracle

You can now find me and some colleagues at MIX, Oracle's idea site to put people in touch with one another. Just search for me by name. Mix is here: link

BI Publisher / XML Publisher & Oracle Applications 11i Template and Font Manager

Just to let everyone know, this is a more in-depth posting that is related to the end half of this post: more on fonts and templates

BI Publisher and Microsoft Word...check boxes, headers, and footers Oh My!

One should not underestimate the fact that if you are going to be doing RTF templating with BI Publisher Desktop, you better be more than a little familiar with Microsoft Word. Of course we know you can use other tools for templating as well like Adobe Acrobat. The focus of this post is to study an area that has plagued me a bit- Header and Footers.

There is a lot you can do in header and footers, and even more that you CANNOT do. So, unless you're going to be making VERY simple headers, or you'd like your headers created dynamically, or other fancy bits...I'd suggest becoming familiar with the following XSL tags:

These nice tags make your life SO much easier when you're trying to do fancy things in your headers that Word just doesn't seem to like. If you have already tried to use BI Publisher's ever-so-nice tool set that lets you directly insert form fields, stop right there. You can't use it in a header, at least not yet! You have to code the actual XSL element into the header, that look like the tags above instead of simply doing a drag and drop from the XML palette.
Where the 'body' tags come in very handy is when you're trying to incorporate special characters and check boxes and things of that nature- or advanced logic. Ever need BI Publisher check boxes embedded into your template and in the header? Not fun! Here's how I got this to work, and what I tried that did NOT WORK. If anyone has tips or extra hints on this, comment or drop me a line.

As you can see, above I have a Quotation and Acknowledgement checkbox structure. You can use simple if/else logic in your header since you're likely using the 'start:body' and 'end body' tags I mentioned. These tags, again, allow you to define a header and footer using xsl and allow you more flexibilty in terms of what is allowed in a header and footer.

Now, the actual check boxes themselves are WingDings font. I inserted them into my template using the MS Word options: INSERT-->SYMBOL--> and then chose either the checked box or the empty box. Since this IS a font, and not one that is native to Oracle Applications (since it is a TTF or true type font), you will need to map to the font locally using your xdo.cfg file (to run it locally), or if you need it to appear in a BI Publisher Report out of Oracle Apps, you need to register to the font in Oracle Apps' Font Manager.

To see how to map fonts either locally or in Apps, please see my other post on fonts (yes, a barcode IS a font): fonts post. That post will lead you through the process, and you will understand where the screen shot below came from. One final thing to note that is not included in that post is a special little area JUST for checkboxes. If you need to register the WingDings font for a checkbox to Apps, in the font/template manager there is an area for checkbox characters seen below. I entered the following corresponding with the WingDings character codes entered through Word:

Android!

A Video on Android, the next generation of software stacks that will be embedded in devices like phones. No this doesn't mean google is making a new phone!

Jack of all Trades or Specialist?

I was in a recent discussion with some friends of mine that went on to work at a handful of different places, Intel, Oracle, Microsoft, NVidia, AMD, Google, Pixar to name a few.

I've been thinking about this early in my career and even now. I really think what I have heard from 1 of my former VPs, as well as what Google puts out as its 'defined philosophy' rings very true. They state:

It's best to do one thing really, really well.

Google does search. With one of the world's largest research groups focused exclusively on solving search problems, we know what we do well, and how we could do it better. Through continued iteration on difficult problems, we've been able to solve complex issues and provide continuous improvements to a service already considered the best on the web at making finding information a fast and seamless experience for millions of users. Our dedication to improving search has also allowed us to apply what we've learned to new products, including Gmail, Google Desktop, and Google Maps. As we continue to build new products* while making search better, our hope is to bring the power of search to previously unexplored areas, and to help users access and use even more of the ever-expanding information in their lives.

So in the end when I think of the strongest technical and techno-business people I know, sincerely, they are well rounded and know 'enough' about a lot of things, but they are KILLER at one or 2 particular skills, with almost no exceptions. When you follow your path, so to speak, it tends to rub against so many other areas that naturally fall onto your plate for you to digest. I've met too many people that get 'good' at a 'lot' of things. These people are smart and fast learners, but will always be below so-called subject area experts. I also firmly believe that the people that build the tools, and don't just employ them to build other things and use them are the ones that are the real experts. While there are thousands of DBAs, and even many top DBAs, and many great developers and managers....there simply are nowhere near as many experts that BUILD these tools at the actual software companies.

Oracle Partner Network Partner of the Year Titan Award.

We were the recipient of the 2007 Oracle Partner Network Partner of the Year Titan Award. This was awarded to our company on behalf of Oracle and Piocon's technical innovation at one of our clients. This award was offered in the Healthcare Solutions area. This is a very prestigious award, that is only given to 10-12 partners worldwide. The Oracle Partner Network is comprised of thousands of partner firms. It is good to see us moving in the right direction. I was used to seeing awards like this at my previous employer, where I was able to work and contribute on a project that eventually won ANOTHER Oracle Partner of the Year award for our client at that time. This is a great team award, as these are rarely given for small 2-3 man projects.

Oracle Warehouse Builder OWB R2 Process Flows

I recently was contacted by one of my former clients that had a pretty good question. I actually had this questions asked a few times by various parties, so here is a summery and an answer!

"Quick OWB question for you: Is it possible to execute process flows from outside of OWB once they have been created. Wondering if there is a template provided by oracle(like “Sql_exec_template for OWB mappings) to execute Process flows…"

Well, OWB does contain a pl/sql package called WB_RT_API_EXEC with a function inside it called RUN_TASK that submits execution requests to the OWB runtime engine. The requests could be for mappings, process flows or scheduled jobs. The function spec looks like:

function run_task

( p_location_name in varchar2
, p_task_type in varchar2
, p_task_name in varchar2
, p_custom_params in varchar2 default null
, p_system_params in varchar2 default null
, p_oem_friendly in number default 0
, p_background in number default 0
) return number;

This is actually the same function used by the sqlplus_exec_template.sql script in OWB 10gR2 that you mentioned in your email. It can be called using (assuming a repository owner connection exists):

(this is an example…)

declare
result_num number;
begin
result_num:= wb_rt_api_exec.run_task('PFMOD_LOCATION1', 'PROCESS', 'MY_PKG/MY_PF', ',', ',', 0 ,0);
end;

The full documentation of the function can be found in /owb/rtasst/wb_rt_api_exec.pl

As usual, if you have further good questions never hesitate to checkout Oracle OWB Blog and Oracle Technology OWB Forum .

Google Analytics

OK, people know that I like Business Intelligence and BI in general. But I am seriously blown away with how nice Google Analytics is. The tool simply rocks. Even non-techies can use this tool on their websites. And when it comes to consulting with this tool for clients....hahah evil laugh) I am only getting started.

Go have a look yourself to see how amazing this tool is: google analytics

Oracle Virtual Machines @ the Oracle Virtualization Technology Center

Wow. I like it. I am downloading this now and will report my findings.
Check it out at Oracle's OTN site: link
Also see a compelling article here about why VMWare is 'good' for your Oracle DB: click here!

Fantasy Football 2007

I say its fine every now and then to blog about something that is not technical in nature or that exclusively deals with Oracle. After all, I am looking at data, databases, security, apps, frameworks, and other stuff that fries my eyes during the day.....so why not talk about this stuff at night?

My Fantasy Football Team sucks. Part of this is on me and my drafting. MOST of it is on my team.
With a great Running back draft for me, I have brandon Jacobs (NY Giants), Larry Johnson (KC), and Rudi Johnson (Cincinnati). ALL of them were hurt for multiple games this season. None have been great. They have had games here and there. NO consistency.

I had 2 top tier Wide Receivers in Lee Evans and Larry Fitzgerald. Lee Evans caught less footballs than I have eyeballs in his first 2 games. That's not good. It took Fitz quite a while to realize he was supposed to be catching balls too.

My QB? Philip Rivers. Cry me a river, you bastard. I luckily took the Cleveland QB -Derek Anderson- and he is playing well! My Cowbody 'D' is performing relatively well too.

My Tight end (always a hard drafting choice outside of Antonio Gates) was Vernon Davis of SF. He had high hopes this season. Me too. I think he has played..?! Anyone seen him lately? IS he out there? Are ANY 49ers out there??

SAD thing is with all the injuries and crappy play by my crappy team, I might make the playoffs.
Who is our #1 guy in the league? he has Brady, Moss, Patriot's 'D', Adrian Peterson, Braylon Edwards, Hines Ward, just to name a few....LOL. Rediculous.

I'm out.

Oracle BI Publisher Tips

I know I have a fair amount of BI Publisher posts here, and I will continue tp update old ones too. However I figured I would mention a few gotchas and little tips here that have confused me sometimes and I know confused the heck out of clients and some smart colleagues I've worked with as well. I'll be updating this post here and there.

XML Nodes and comparing tags in different nesting levels:
coming soon.

The dreaded copy paste... a binding problem?:
Do not, under almost any circumstance copy-paste things inside your microsoft word BI Publisher RTF template. Pay special attention to form fields and XSL code. I have copy-pasted these form time to time from one place in my code to another and it failed to work. But wait! You might be thinking I pasted the elements into parts of a template that SHOULD work differently because of other tags involved, such as posting something outside of an if/else statement, or pasting something outside of the body tags, and so forth. NOPE! It happened to me multiple times if I merely pasted the code or form field right next to the original! It would not render!

Pay attention if your output data behaves or displays differently after a copy-paste. Simply TYPE what you want to alleviate this problem :)

BI Publisher Barcoding in Oracle Applications 11i Tips

(to see a follow-up and more in-detail posting related to this post, go here- link) Now that I have some nice screen shots to accompany this, here we go. Note: I did use some of the material that can be found on Tim Dexter's Oracle BI Publisher Blog, just to give some due credit. I merely added more details for some of you that like to SEE things working too.

First, this will not cover how to develop a BI Publisher Template, that is a whole 'nother topic. This will also not talk about how to register the template or the data definition in Oracle Applications. That will be covered in another posting as well. This is more focused on how to get barcodes installed, into your templates, and to make sure they're registered in Apps as well and appearing on your reports.

Finally I will be covering some 2-d barcoding later. This post is for standard 1-d barcodes. For those that don't know, 2-d barcodes are the funky blurry looking square barcodes that UPS, Federal Express and some others use. It lets them scan the barcode in any direction, not just up and down.

1. Install the barcode font in your WINDOWS_HOME/fonts directory

2. Check your fonts directory to make sure you have the font installed and it is present.

3. To test the output you need to let the publisher engine know where the font is on the desktop. Under the Bi Publisher client install directory you'll find a config directory and under there an 'xdo example.cfg' file. Here I also show a finished xdo.cfg file too. NOTE that any time I reference this xdo.cfg file, I am referring to the local file on your machine. Editing this won't help you see fonts in Oracle Apps- you need to load the font to Apps to see your templates using it there. This is also covered below. I mention the xdo.cfg file because people like to test templates locally on their own computer first.

Open it up and you'll see a sample entry for a font:


Here is an example of an 'after' of what your changes may look like in this file: (note the directory and that we are only showing the portions we changed with respect to the above XML)

font family="CarolinaBar-B39-25D1"
style="normal"
weight="normal"
and...
truetype path="C:\WINDOWS\Fonts\CarolinaBar-B39-25D1-Normal.TTF"

Note you can have more entries too, for example if you need a font to be in italics, or another entry if you need the font to be in bold. Then again, remember we are dealing with barcodes here... :) Just change the entry to point to your font via the path and for the family attribute put in the name you see in Word when selecting the barcode font.

4. Rename the file to xdo.cfg for the changes to take effect.

5. Open Word, and test your template. I will use this standard template seen below. Note the fields near the top in the Vendor and the Ship To boxes.


6. Now we need to make sure also we have an asterisk * placed just before and after our fields that need to be coded into barcodes. These are wrapping or ‘escape’ characters for the barcode. So do this as seen in the next picture:


7. Then, using Microsoft Word, I highlight the fields that will be barcoded, as can be seen below. I then proceed to choose from the font menu my barcode font of choice for those highlighted areas. Note you must have registered them properly to even be seen in Word.


8. And there we have it seen below, at least in the template!

Now you need to deploy to the server. The template is simple enough, but we need the server publisher engine to know the location of the font. For both EBS and standalone there is an interface to load the font to the server, the steps are now pretty straightforward. In EBS just use the Font Manager.

Many of you will have seen the Administrator module on the Template manager in the E Business Suite ... the new properties UI is great and much easier to use but you can now also manage those troublesome MICR, Barcode and other fonts through the manager too.

XML Publisher 5.6 has a new tab: Administration. This replaces the xdo.cfg configuration file. Now fonts can be uploaded and stored in the database instead of stored on the file system.

Under the Administration tab are sub tabs: Configuration, Font Mappings and Font Files and Currencies.

To install a font requires only a few steps.

1. Log in as XML Publisher Administrator.

2. Navigate to Administration->Font Files->Create Font File.

3. Fields are 'Font Name' and 'File'. For Font Name choose any descriptive name. File will browse your PC to locate the font file.

4. Navigate to Font Mappings Tab->Create Font Mapping Set.



5. Mapping name is the name you will give to a set of fonts.

6. Mapping code is the internal name you will give to this set.

7. Type: 'PDF Form' for PDF templates. 'FO to PDF' for all other template types.

8. Now, Create Font Mapping (this allows you to add multiple fonts to a font mapping set). After you created the Font Mapping Set itself, you need to look in the bottom right AGAIN and choose the button Create Font Mapping (this allows you to add multiple fonts to a Mapping Set that you just created). Screenshot of the new screen below:

9. Note: Font Family is the exact same name you see in Microsoft Word under Fonts.
If you don't use the same name the font will not be picked up at runtime. THIS IS IMPORTANT!

10.The following screen shot shows us a screen with a font mapping set, with 2 font mappings inside of it. You can see the font family how it is defined:

–Note the font family MUST match the font name (not Filename) in the fonts directory that you uploaded the font from! Look below!

10. Style and weight must also match how you use the font in windows.
Normal and Normal are good defaults. If you want bold or italics, you must have the fonts registered in the windows directory we discussed, as well as in your xdo.cfg file.

11. Navigate to Configuration General-> FO Processing->Font Mapping Set.
This can also be done at data definition and template level under Edit Configuration.
A screenshot below shows the way to get to the Edit Configuration if you need to do it AT the data definition or template level. :


Hierarchy is Site-> Data Def -> Template.

13. Select your new mapping set.


14. Make sure the font is not referenced under File->Properties->Custom in the RTF template file.

15. Upload a template that uses your special font and test using preview or in EBS.

Some quotes I think are awesome.

"You're walking around blind without a cane pal. A fool and his money are lucky enough to get together in the first place."

" Guess you think you taught the teacher a lesson that the tail can wag the dog huh? "

"Ever wonder why fund managers can't beat the S&P 500? 'Cause they're sheep, and sheep get slaughtered."

"And where does your CEO put his million-dollar salary? Not in the company stock; he owns less than one percent. You, the shareholders, own the company. That's right, you! And you are all being royally screwed over by these, these bureaucrats, with their luncheons, their hunting and fishing trips, their corporate jets and golden parachutes. "

"Teldar Paper has 33 different vice presidents each earning over 200 thousand dollars a year. Now, I have spent the last two months analyzing what all these guys do, and I still can't figure it out. The new law of evolution in corporate America seems to be survival of the unfittest. Well, in my book you either do it right or you get eliminated."

"Confidence is contagious. So is lack of confidence."

"Dictionary is the only place that success comes before work. Hard work is the price we must pay for success. I think you can accomplish anything if you're willing to pay the price."

"If it doesn't matter who wins or loses, then why do they keep score? "

"Once you learn to quit, it becomes a habit."

"The difference between a successful person and others is not a lack of strength, not a lack of knowledge, but rather a lack of will. "

"I've never known a man worth his salt who in the long run, deep down in his heart, didn't appreciate the grind, the discipline. There is something in good men that really yearns for discipline and the harsh reality of head to head combat. "

"If you think it was luck, I'll do it again."

BI Publisher / XML Publisher and Barcoding

My client awaits my return next week with bated breath and part of my fun responsibilities will be to make sure I can get nice and working bar codes on their BI Publisher Reports feeding from EBS 11i. Fortunately I won't be alone in my effort, but from previous client experiences, people had to jump through hoops and hurt themselves to get this working. I just haven't been hurt yet :) In the coming weeks I will update this posting with my findings. As always, the most helpful blog I always refer to for all things BI Publisher is Tim Dexter's Blog. Is this guy trying to win an Oracle Ace or something? :)

OK, UPDATE! We got this working. It was not the more complicated 2-D bar coding that needs nice Java classes and so forth, but I will STILL show some nice screen shots and of course refer to good blogs where I dug up the info to do this. There are HUNDREDS of people out there that need help with this so I will show as many screen shots and steps as I can without compromising my client! Tune in very soon!

OK. Have an update with details. See the post titled 'BI Publisher Barcoding in Oracle Applications 11i'

The OWB Repository Upgrade Assistant!

Just what in the world is this little hidden Wizard tool doing? Let's find out more about the OWB Repository Upgrade Assistant, shall we?

First, what does it do, and why do we use it?

Next, how do we use it; exactly where do we find it?

Any issues we know of?

Oracle Warehouse Builder 10g R2 - OWB and Oracle RAC

Right now I am working hard on making sure we have all of our connections to sources, targets, the metadata repository, and any other loose ends tied up on a nice start to yet another Data Warehousing and BI project- using the Oracle suite of tools!

One thing I don't have much experience with that I wanted to share pitfalls about (and accept tips from readers as well) is using an Oracle RAC instance as our ultimate 'Target Schema' in our ETL using OWB. We have a multitude of data sources, of course, and we have our unified repository on the RAC instance. We also have our Target schema and tables on this same RAC instance. Nothing crazy just yet! Here is where the fun begins (note to self: it is always interesting to say its fun when it is actually a bit painful)....

First, the Oracle RAC instance is using ASM. A bit abour ASM- ASM is a new feature in Oracle Database 10g that provides the services of a filesystem, logical volume manager, and software RAID in a platform-independent manner. Oracle ASM can stripe and mirror your disks, allow disks to be added or removed while the database is under load, and automatically balance I/O to remove "hot spots." It also supports direct and asynchronous I/O and implements the Oracle Data Manager API (simplified I/O system call interface) introduced in Oracle9i.

Oracle ASM is not a general-purpose filesystem and can be used only for Oracle data files, redo logs, control files, and the RMAN Flash Recovery Area. Files in ASM can be created and named automatically by the database (by use of the Oracle Managed Files feature) or manually by the DBA. Because the files stored in ASM are not accessible to the operating system, the only way to perform backup and recovery operations on databases that use ASM files is through Recovery Manager (RMAN).

ASM is implemented as a separate Oracle instance that must be up if other databases are to be able to access it. Memory requirements for ASM are light: only 64MB for most systems. In Oracle RAC environments, an ASM instance must be running on each cluster node.

The diagram below shows a very basic topology, and yes this RAC is using ASM.



Now that we have a few of the basic ideas out in the open...the real issues come into play for people like myself that don't know exactly how RAC is going to work with OWB services that are trying to deploy tables, data, and other things to a RAC target. Furthermore, what is going to happen with the OWB Repository, and the properties files that are created when a repository is created via OWB? Well we're learning.

First I'll mention a few details about installing OWB in a RAC environment, and in particular- the repository setup on the RAC cluster.

To install OWB in a RAC environment:
Create the RAC environment as described in the Oracle Clusterware and Oracle Real Application Clusters Installation Guide specific to your platform. If you have not already done so, it won't hurt to review the most recent Oracle Warehouse Builder Release Notes available for your version of the install!

For each computer to host Warehouse Builder components, configure the tnsnames.ora file located in the OWB_ORACLE_HOME\network\admin directory. Also be sure to set the initialization parameter MAX_COMMIT_PROPAGATION_DELAY to a value of zero.
Definitely configure tnsnames.ora for each Oracle Database server that will be a Warehouse Builder data source OR target. If you fail to configure tnsnames.ora for any host or database server, you may encounter a repository connection error such as "The connection to the repository was lost, because of the following database error: ORA-12154:TNS:could not resolve the connect identifier specified". This is a common error!

Then review any last-minute install requirements before launching the Universal Installer and installing OWB.

To install an OWB Repository in a RAC environment:
When OWB is installed, you'll then want to install an OWB repository. To launch the Repository Assistant on Windows, from the Windows Start menu, select Programs and navigate to the Oracle product group you installed in the previous step. Select Warehouse Builder, Administration, and then Repository Assistant. (To launch the Repository Assistant on UNIX, locate OWB_ORACLE_HOME/owb/bin/unix and execute reposinst.sh.)

The Repository Assistant prompts you to define users and an owner for the repository.

Now for each RAC node, make sure to register each one! So for each node, launch the Repository Assistant and select the Advanced Set up option. Connect to the node using the net service name. Select the option for registering the RAC instance. (see below)

If the software is installed on separate disks, copy rtrepos.properties to each node in the cluster. If you did not install to a shared disk, then you must manually copy the file /owb/bin/admin/rtrepos.properties from the primary node to each node in the cluster. <-- This is yet another point I forgot so please make a note of it! See metalink note below!

Then install the Warehouse Builder software on the client machines.

When complete the installation process, you can launch all the Warehouse Builder components.
When launching OWB components such as the Design Center, Control Center Manager, and Repository Assistant, select the log on option SQL*Net connection and specify the net service name you assigned in the tnsnames.ora file. Since you can connect to Warehouse Builder repositories using a net service name, you can embed RAC properties into the connect string to utilize RAC functionalities such as connect time failover, load balancing on server and load balancing of connections. Finally, the Control Center Service requires that service names for the individual nodes in the cluster be available. If these are not present after the RAC installation, you must manually ensure the availability.

For RAC, it is recommended that you install the Warehouse Builder components on each node of the cluster. The Control Center Service is required on to each node of the RAC cluster.You can achieve this in a single installation of the Warehouse Builder software if you install on a shared disk such as an OCFS or NTS shared disk.

Metalink Note On OWB + RAC:
Applies to:
Oracle Warehouse Builder - Version: 10.2.0.1.31This problem can occur on any platform.

Symptoms
The Warehouse Builder Runtime Service fails to startup on a Real Application Cluster (RAC).The following can be found in the Runtime Service log file or in sqlplus depending on the way the Service is started.service startup failure reason ORA-29532: Java call terminated by uncaught Javaexception: oracle.jdbc.driver.OracleSQLException: ORA-28239: no key providedORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 40ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 153ORA-06512: at "OWB_REP_OWNER.WB_RT_SERVICE_CONTROL", line 286ORA-06512: at "OWB_REP_OWNER.WB_RT_SERVICE_CONTROL", line 314ORA-06512: at line 1

Changes
Switch from one RAC node to another RAC node.

Cause
The file rtrepos.properties created into the owb_home/owb/bin/admin during the Unified Repository creation has not been deployed (copy) into the owb_home/aow/bin/admin on the other node(s).

Solution
On the RAC node used to perform the Unified Repository creation, locate the file rtrepos.properties in owb_home/owb/bin/admin and copy it on the other node(s) into the owb_home/owb/bin/admin directory.

Oracle PL/SQL Tokenizer and String Formatting

Several times in my past projects I needed to do some complex actions on Strings or Varchars.  Doing this in Java is a no-brainer with the Tokenizer.  Sadly, I either didn't look hard enough or didn't see it in the PL/SQL base packages, but I created a nice little tokenizer that solved my problems.  I've used this little nifty code bit several times now.

-----------------------------------------

--This is the tokenizer proc that is called several times that breaks down the strings by

--using the input parameters of 1. where to start, 2. the delimiting character or string

--and 3. the string to be broken down. It returns the extracted piece, and also the next

--position in the string so if the tokenizer needs to be called again, it will automatically

--start at the next position to extract the subsequent piece.

--Gregory Partenach v1 October 2003



PROCEDURE tokenizer ( iStart IN NUMBER,

                                        sPattern IN VARCHAR2,

                                        sBuffer IN VARCHAR2,

                                        sResult OUT VARCHAR2,

                                        iNextPos OUT NUMBER )

IS

    nPos1 number;

    nPos2 number;

BEGIN

   nPos1 := 0;

   nPos2 := 0;

   nPos1 := iStart;

   nPos2 := Instr (sBuffer, sPattern, iStart);

IF

   nPos2 = 0 then

      sResult := Rtrim(Ltrim(Substr(sBuffer, nPos1)));

      iNextPos := nPos2;

ELSE

   sResult := Substr(sBuffer, nPos1, nPos2 - nPos1);

   iNextPos := nPos2 +1;

END IF;

END tokenizer; -----------------------------------------

Setting up Oracle Portal 10g to use Oracle Reports PART 1

I know many of you have been through this. I also know quite a few that haven't. Since I have had to integrate Reports with Portal in many different ways, I figured I would show one way I implemented this. I've had top-level consultants and co-workers tell me certain configurations don't work. Fortunately, in my experiences I have gotten the integration between Reports and Portal to work every single time. This will not go into detail about the Reports Server configurations...let's stick to the Portal side of things.

This procedure will set up a report server in Oracle Portal that will be used to call your Oracle Reports.

Log into Oracle Portal.


Click the "Administer" tab.


Click "Oracle Reports Security Settings".




Click "Create Reports Server Access".

Enter a name and display name for the Reports Server and select a provider.


Enter the Reports Server name and the web gateway URL for PDF reports. The Reports Server name can be looked up in Oracle Enterprise Manager. The current production gateway may look like "https://my.site.com/reports/rwservlet". The two steps after this screen are unnecessary, so you may click the "Finish" button on this screen when done.

When you've created an Oracle Report and are ready to access it in Portal, you will need to upload it to the server. The current path on the server where the reports can be placed (for example) may look like: /u01/app/oracle/product/10.1.2/OAS/as_biportal/reports/samples/demo.

Setting up the Report for use in Portal- You should first have your Oracle report created and know the name of all the parameters that will be passed into the report. Click "Create Reports Definition File Access".

Enter the name and display name for the report and select the provider. Typical names can look like "RPT_ORDER_STATUS" and display names like "Report - Order Status", etc.

Select the report server to use and the file name of the report (the report server was created in the prior step above). The file name is the actual name of the Oracle Reports .rdf file for the report.

Select the destination type and format. For PDF reports, the type should be "Cache" and the format should be "PDF".

Enter the parameters for the report. The "Name" field should match exactly the parameter names you defined within the Oracle Report. The two steps after this screen aren't always necessary, so you may click the "Finish" button on this screen when done.

The report definition is now created and you will be returned to the below screen. Click the "Access" tab.

Select the "Publish as Portlet" checkbox and click the "Apply" button. The screen will refresh.
Then click the close button.

You will be returned to the below screen. Click the "Customize" link to expose the report parameters to Portal.

A popup window will be displayed listing the custom parameters you defined for the report, as well as several system parameters. For each custom parameter you defined, check the box labeled "Visible to user". This will expose the parameter to Portal screens that the report is placed on so that data can be passed from the screen and into the report.

In the "Additional User Parameters" field you can enter the following code:
&userid=[username]/[password]@[host string]
Note that we don't have the 'Visible to user' option checked here. This is because this parameter is being passed to the Report as a security and data filtering action. Depending on who is logged into Portal, the user's name and password will be passed to the Report and the appropriate data will be retrieved to display on the Report. We don't want this parameter to be visible to anyone, the username and password are passed transparently.

You can then click the "Save Parameters" button when done and then close the popup window manually. The report should now be ready for use in portal screens. You can also click the "Run Report" button after you've saved the parameters for the report. Depending on your report and the parameters that it requires, the report may not display correctly, but it should give you an idea if the report is at least being accessed and called correctly from Portal.

Next time we will continue by showing how to create a Portal page for your Oracle Report.

First post on this blog

Blogging is not new to me, but I am creating yet another one. This Blog will focus on my professional areas of interest. Namely, Oracle and other technical subject areas.

A bit about me without giving you an explicit resume- (if you want my resume, want to see where I worked, or more- please contact me through email partench@gmail.com or look over a briefing at http://www.linkedin.com/in/cali97 ). I've been doing technical work in some fashion since 1996. I've been to a few good schools, racked up a couple of higher degrees, and even considered going into healthcare/medicine until I realized it was almost as corrupt as politics.

I've been lucky enough to work on projects and software that made it to film (Pixar's "Finding Nemo"), lucky to work 1 on 1 with Shaun O'Brien on many projects, as he was my direct mentor at my previous employer and is also employed by my current employer now too! I can't shake that guy?! Now, saldy he was hired away to be a Principal Product Manager at ORACLE for JDeveloper and the ADF Framework. Congrats to him. Unfortunately, 90% of my work is buried and hidden deep in my client's vaults never to be seen by outside eyes.

My areas of specialization and interest are in Oracle Database Development, Data Warehousing, Business Intelligence, Informatics, Tuning, Motion Picture Visual Effects, Graphical Rendering Engines, ...I could go on!

On a lighter note, the real person side of me is enjoying city life in Chicago. I live just a block away from the Lincoln Park zoo. Its quite a change from San Francisco! I enjoy trying new restaurants, taking in art museums as well as creating a bit of my own art, and of course seeing the Chicago Bears play on Sundays. I'd like to get into rock climbing when time permits, and I'm already planning a trip or 2 to check out some vineyards in Michigan and maybe Napa Valley as well. Again....I could go on!