Oracle BI &DW Technical Blog and More...

WOW I haven't been here in a while...

2011-07-15T22:06:00.002-05:00

Anyway, I desperately need to write up some more posts on here, it's been over 1 year. I've pretty much been buried under BI, Proj mgmt, putting out fires (no no the building types of fires), and other stuff.

Got back from probably my 50th trip to San Francisco lately....again when I have more time I will start posting things that people can learn from and find useful!

Take Care!
-Greg

OBIEE 10.1.3.4.1 with Weblogic 11gR1 and SSO / OID

2010-05-20T19:48:00.003-05:00

I know I haven't posted in a little while...it's just been the daily grind of work and life I suppose.

I'll be updating this post later to illustrate what we did to integrate OBIEE 10.1.3.4.1 with Weblogic 11gR1 and SSO/OID. Note, this changes the game, as we're not dealing with deploying the .WAR file to the old Oracle Application Server. MOD OSSO? Not quite! Weblogic 11gR1 changed the game a bit. Hence, some of the documentation on OBIEE is not quite up to date (heck, I think it has NEVER been rewritten since 2006???)

Expecting certain things to work as they used to with the old way to setup SSO and passing credentials to OBIEE? Well, they won't be exactly the same, and it sadly wasn't documented anywhere. Nobody in the Oracle forums, nor on metalink, nor on ANY blog I had seen posted any solutions. It is only a matter of time before it should be in the docs, as I know at least 3 clients of mine installed Weblogic recently and have OBIEE, and might consider SSO. I'll do what I can to cover the solution. Note to all, this was even more difficult because at my client, Weblogic was being installed and administered by another firm that was responsible for the security and web architecture setup. So we were at their mercy a bit, but they were helpful as well.

To leave you with one last bit before I write more in a later post, look at the diagram out of the documentation below. I refer to this as the 'MASTER' plan because it should be practically memorized by most OBIEE practitioners, considering it has all the default ports, applications, and so on. I highlighted the areas that should be of concern to you. It is primarily where the BI Presentation Services Plug-in Resides, and the communication with the BI presentation services themselves. Assuming you configured SSO correctly, and you've configured MOST of OBIEE correctly, and you've done what you feel is right and you get to the point where you see the infamous OBIEE server message "You are not logged in..." well that's where the stumper comes in. In a nutshell, you likely will miss 1-2 things here because weblogic passes the presentation services something it is not expecting. You will need to change a few things in your instanceconfig.xml file to accommodate this. OR, you can dive into Weblogic, and change a few things as well. I'll try to share more next time. We had to look at log file after log file to finally get this one right. Oh, and this was all pretty much on Linux, not that it matters much.

Anyway, at my client, a large pharmaceuticals and health care insurance/benefits management company, I got to run into quite a few more advanced issues.

For one, anyone that has ever read the beginner Ralph Kimball books on dimensional modeling might be familiar with the concepts of late arriving fact and late arriving dimensions. I can get into the gory details on that later, but suffice to say, they are rampant all over the place with the health care industry! A former colleague of mine was a data architect and ETL admin at a very large insurance company, and told me how common it was, and what a pain in the rear-end as well! Now, I got to see it first-hand, and realized it was VERY common, at least in that industry. It presents some unique architectural challenges for modeling and handling of the ETL. Not a terribly hard problem to solve, but nonetheless, a good experience to get under the belt within this industry that has so much late arriving data.

Why would the healthcare industry have so much late arriving data? Well, to summarize a few points, let us think about it. Think about ALL the data that has to go from 1 place to the next. Your employer had to send your details to an insurance company. The insurance company had so set you up in the system, and provide a temporary ID, maybe not even a real card yet. You go to the doctor and they file a claim, which is even more data exchanged. You may get a bill or invoice or just a notice that a claim was filed, but it might not even be a bill yet. Maybe 1 month later it is a bill and you need to pay your portion of it that insurance didn't cover. There could be differential diagnoses, multiple prescriptions, and so forth. Yes, the claim might be filed, and it might qualify as a FACT in your fact table, but the dimensional attributes such as a drug price, diagnoses, payments, addresses, and so much more might change after the fact table is joined to it's dimensional values on certain keys. Read more about it in the Kimball Data Modeling book to see a clearer picture.

Oracle Application Performance Tuning Course

2010-03-08T21:10:00.002-06:00

I just got done custom creating a new Oracle performance tuning / SQL tuning course, and teaching the beginning of it! This class is ideal for developers that know enough about the database to be a Jr DBA (enough to be dangerous) and emphasizes application tuning, sql tuning, and things developers should be aware of. To summarize, the course that I teach is a full 3 day class on the following topics:

The Performance Review Lifecycle
Database and Table Design Considerations
Database Architecture, Processes, and Parameters that a Developer Should be Aware of
Cursor Lifecycle including soft and hard parses
Bind variables, bind variable peeking and bind variables in your SQL (Dynamic SQL)
Cardinality, Selectivity, Computation, etc.
Statistics, and all subtopics like import/export, gathering, what they do, strategies.
The Oracle Optimizer (LOTS of extensive detail on this important topic)
Understanding how to read an Explain Plan from various tools that produce one.
Other tools to help in tuning - TKPROF, Autotrace, AWR, statspack, ADDM, STA, SPA, etc.
Indexing
Optimizing Joins and join types
Hints
PL/SQL tuning
Utilizing ANSI SQL to out-perform classic Oracle SQL as you've seen it
V$Views to analyze for performance gains
How to identify a Performance Issue that you haven't seen yet.
Tuning in different environments (Oracle 8i, 9i, 10g, 11g, OLTP vs DW vs Bulk movements, etc.)
Tips and Tricks

...and that's all. The initial offering went EXTREMELY well. I was even surprised. Hopefully I will be teaching this class more often! I cannot stress understanding Oracle performance enough, especially to BIDW types who are too used to doing simple joins between a few tables, or let tools like OBIEE with their metadata layers take some of the work out of your hands...for now.

-Greg

Oracle Data Integrator Tutorial- ODI for beginners

2010-02-19T09:37:00.010-06:00

I am going to be writing up a few posts and tutorials on the basics of the architecture (see previous post) and a few beginner topics to get the masses in a mode where they are not intimidated by this tool. The previous post described various aspects to ODI, and the architecture. We saw various GUIs that ODI allows us to use. This post (and possibly another one or two) will look at the DESIGNER GUI in more detail.

One of the first things I will say is that the tool can feel a bit foreign if you come from an Informatica/OWB type background like myself. The emphasis is on building smaller and reusable pieces of code, if you will. Other tools that tend to be more ETL based (Informatica) and also more GUI and non-declarative based (OWB) are not necessarily the easiest tools to break things into small pieces. Yes, there are many that say breaking mappings into smaller pieces is a best practice, but the tool doesn't necessarily thrive on your ability to follow that practice. They don't really revolve around that practice. There are even mapplets and pluggable mappings, but these pale in comparison to the design methodologies that ODI offers. ODI thrives on code re-use.

For example, the diagram below shows a typical ETL/ELT from something that may come out of OWB or Informatica (a bit bigger, defined, and manual).
The following is the type of development that ODI supports and emphasizes for code reuse:

If you are a beginner to ODI and used other ETL/ELT tools and yoou are beginning to build out mappings you'll notice many of the mappings you'll build will be more discrete, if you will. There are also many steps that are done for you. You can build mappings quite fast in ODI actually. However, do not be too worried that that tool does quite a bit for you. Like Oracle's JDeveloper tool, you can get into most of the gory details if you'd like to fine-tune certain aspects, even if the tool does much of the work for you.

You will do the bulk of your work doing mappings in the following interface:

A brief description of what we see above in the Diagram above. First, notice that the Diagram TAB is selected on the top. This opens the diagramming tool where 'mappings' are built. In the middle are the data sources. These sources are physically dragged and dropped from the far left pane of the GUI to this 'sources' section and are then joined, filtered, and so forth. The right side of the GUI is labeled as the TARGET datastore. This is your target. For a beginner, this can be a bit confusing if you're used to OWB because here, the sources and target is separated by differnet parts of the GUI. You would drag your TARGET table to this target datastore pane in the GUI and not into the same area as the sources. So, a bit different in that respect.

Below is a zoom in on only the mapping portions of the tool. Again, on the left are the source portions of the mapping diagram, and on the left is the target datastore. You might not use the terminology 'datastore' too often, but in this tool you should get used to seeing that term a lot!
Here is another screenshot of the far left portion of the main GUI, which I tend to call the 'explorer' since it is built out like many software tool's explorer-like interfaces.
Again, one final screenshot to tie it all together what we have seen so far:

In the next posting, I will go into a bit more detail on the 'explorer' and the many tabs and options available within. As you might have imagined, these 'mappings' have been created and opened from the explorer on the left, and they are technically referred to as 'interfaces'. I'll talk a bit more on the concept of interfaces, and go into a bit more detail on working with your interfaces (A.K.A. mappings to most of us).

ODI Oracle Data Integrator Architecture Part 1

2010-02-08T16:08:00.010-06:00

The following series will be a high level look at the pieces, components, and basic workings of ODI, which should help any reader comprehend some of the finer pieces of this data integration and ELT tool.

The GUI graphical modules are listed above.

The four ODI GUIs – Designer, Operator, Topology Manager and Security Manager, are based on Java. They can be installed on any platform that supports Java Virtual Machine 1.4, including Windows, Linux, HP-UX, Solaris, pSeries and so on.

Designer is the GUI for defining metadata, and rules for transformation and data quality. It uses these to generate scenarios for production, and is where all project development takes place. It is the core module for developers and metadata administrators.

Operator is used to manage and monitor ODI in production. It is designed for production operators and shows the execution logs with errors counts, the number of rows processed, execution statistics etc. At design time, developers use Operator for debugging purposes.

Topology Manager manages the physical and logical architecture of the infrastructure. Servers, schemas and agents are registered here in the ODI Master Repository. This module is usually used by the administrators of the infrastructure.

Security Manager manages users and their privileges in ODI. It can be used to give profiles and users access rights to ODI objects and features. This module is usually used by security administrators.

All ODI modules store their information in the centralized ODI repository.

ETL Misconceptions and Mistakes Made While Working With Large Databases - 1TB or Greater

2010-02-08T11:08:00.007-06:00

I'd first like to give some due credit to a fellow blogger that has pushed out so much information on data warehouse topics, it is staggering. He is Dan Lindstedt, and this next piece on my blog is about some common etl mistakes on large systems, with my commentary. His work appears frequently on the Beyenetwork blogs.

Please note that some of these issues listed below might not necessarily make a difference if you are dealing with a smaller system with less data / rows. If you have never worked on a larger size database, some of these concepts might even seem unfamiliar or odd.

Top ETL mistakes

1) Incorporating Inserts, Updates, and Deletes in to the same data flow / same process.

This is a great point, if for nothing less than splitting the individual tasks into discrete pieces. For a target table load, try an insert job, an update job, and other jobs like deletion or massive bulk loading jobs. These will all vary based on time and loads. Inserts in many cases being simple and easy, and updates and especially deletes sometimes becoming more taxing.

2) Sourcing multiple systems at the same time, depending on heterogeneous systems for data.

This can be cause for great concern if you try this, unless the data volume is smaller, again, we are referring to larger loads and larger systems though.

3) Targeting more than 1 or 2 target tables

You might consider 2 tables at once if it has a relationship, hierarchical, parent-child, or otherwise, but this should not happen often! That should be an exception, most large volume ETL jobs should be 1 target table.

4) moving rows that are too wide through a single process

Any ways to get around this issue or mistake? Comments?

5) loading very large data sets to targets WITH INDEXES ON

Many tools allow you to turn indexes off and then recompute and turn on again after a job- datastage, owb, etc. You can code it custom if you need to. Again, you don't need indexes and keys if you have other ways to check your referential integrity.

6) not running a cost-based optimizer in the database

Big mistake here. You should always consider a routine after all loads are finished to analyze, or gather stats or other such details for your database just so that your cost-based optimizer, or other tool (in a non-Oracle database) can gather all the necessary information about new structures, indexes, and so forth.

7) not keeping statistics up to date in the database

Again, this is similar to 6, almost exactly. You'd want current statistics so the cost based optimizer takes advantage of the current structure and used the right joins and indexes, etc.

8) not producing the correct indexes on the sources / lookups that need to be accessed

You should know well enough where the majority of your lookups and/or joins are going to happen....indexes....use them! But, again, see the above mistake about keeping them during a load.

9) not purchasing enough RAM for the ETL server to house the RAM caches in memory.

Not applicable to OWB necessarily, more for things like Informatica and DataStage.

10) running on a 32 bit environment which causes significant OS swapping to occur
11) running on a 32 bit environment which causes significant OS swapping to occur
12) running on a 32 bit environment which causes significant OS swapping to occur

The original author had a major issue with #10, so as to repeat it for #11 and for #12!!!

The original author points out the following concerning #10-#12:

"Let me explain this one. Any single process started within a 32 bit environment is limited (immediately) to 2GB of addressable RAM, now - take away the RAM needed for the threads or the executable engine code... usually around 300M or more, leaving us with 1.5GB to 1.7GB of RAM to work with. Then, take away any "caching" objects that might use this RAM, and multiply that "drop" in RAM by the number of caching objects that you have....

Now, you are left with possibly 200MB, maybe 100MB of RAM left to allocate for "data reading/data writing"... and it drops from there.

Let me explain this other piece as well: Windows 32 bit OS MUST RUN CODE UNDER THE 640k BOUNDARY!!! So there's additional Limitations there, Windows 32 Bit PAGEFILE.SYS (swap/temp) is SINGLE THREADED, AND BLOCKS I/O OPERATIONS when swapping pieces to disk. Oh yes, one more: Windows 32 Bit - RAM allocation will ONLY give the application 1/2 of the requested RAM, and AUTOMATICALLY put 1/2 in the pagefile.sys swap area.

By the way, here's a tid-bit for you that you have to try to believe:
If you are running Windows 32 bit, SQLServer (either version)....
1. Change your ETL "block size" to 8k to match the Database (4k I think in SQLServer2000)
2. Change your row size to fit as many rows as possible into an 8k block, the more rows per block the faster the performance.
3. Change your commit point to 8,500 rows (this is the sweet spot)
4. Check in on NETWORK PACKET SIZES and increasing those between the ETL engine and the Database, increase them from 2k/4k to 8k - again to match the disk.
5. USE ODBC/OLE DB connectivity; do NOT use NATIVE libraries (except in SQLServer2005 direct CT-LIB). If your tool uses DB-LIB as "native" connectivity, it will be slow, slow slow - DB-LIB is 12 year old technology, and is NOT multi-threaded, nor parallel, where CT-LIB is.

Windows 64 Bit does NOT have these limitations, but requires 30% more hardware to run "cool" and efficiently, but it is FAST when installed and configured correctly on the right hardware.

Linux, and Unix do not have these limitations either.

So, architecture is everything."

13) Trying to do "too much" inside of a single data flow, increasing complexity and dropping performance

This does make the job harder to debug and sometimes you get those random out of resource errors...Beware. Split work where appropriate. If a mapping or process looks too complicated, it probably is.

14) believing that "I need to process all the data in one pass because it's the fastest way to do it."

"breaking-up" a single flow into multiple flows , and changing the width of the data sets and possibly the parallelism along the way would help the performance.

15) Letting the database "bounce" errors back to the ETL tool, dropping flow rates and throughput rates by factors of 4x to 10x.

comments?

16) "THINKING" in a transactional mode, rather than a batch mode, and processing each row, one row at a time (like they would code a cursor in a database language).

A bit hard to avoid in a row-by-row ETL tool! The parallel architecture and caching and memory sharing and a bunch of other things make it fast.

17) LOOPING inside an ETL process, because they think it's necessary (transactional processing again).

Careful here no matter which tool you are using. Looping, as it goes WAY back in simple logical programming can be a blessing or a disaster. If you need to loop, there's probably another way to do what you're attempting, in a more efficient way. This does not necessarily mean using a LOOP in an OWB process flow, but trying to loop somehow in the ETL itself, called a stored function or procedure, etc.

GO New Orleans Saints!

2010-02-05T19:57:00.004-06:00

I hope they win the super bowl and knock off those dumb Colts. They beat my Bears in 2006-2007, so GO SAINTS! Who DAT?!

Date Dimensions anybody?

2010-02-05T19:52:00.002-06:00

Here is some sample code for creating a date dimension, It's been out there before, My colleague Dave posted this on his blog at one point. I'd like to take a moment and maybe look at how Oracle implements their date dim with the OWB date dimension wizard....hmmmm!

SELECT TO_CHAR (dt, 'YYYYMMDD') date_id,
      dt date_value,
      TO_CHAR (dt, 'MM/DD/YYYY') date_formatted_name,
      TO_CHAR (dt, 'Mon DD, YYYY') date_name,
      TO_CHAR (dt, 'YYYY') year_num,
      TRUNC (dt, 'YYYY') year_start_date,
      ADD_MONTHS (TRUNC (dt, 'YYYY'), 12) - 1 year_end_date,
      TO_NUMBER (TO_CHAR (dt, 'Q')) quarter_num,
      'Q' || TO_CHAR (dt, 'Q') quarter_name,
      TRUNC (dt, 'Q') quarter_start_date,
      ADD_MONTHS (TRUNC (dt, 'Q'), 3) - 1 quarter_end_date,
      TO_CHAR (dt, 'YYYY') || '-' || 'Q' || TO_CHAR (dt, 'Q') year_quarter_name,
      TO_CHAR (dt, 'MM') month_num,
      TO_CHAR (dt, 'Mon') month_short_name,
      TO_CHAR (dt, 'Month') month_long_name,
      TRUNC (dt, 'MM') month_start_date,
      LAST_DAY (dt) month_end_date,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'Mon') year_month_name_1,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'MM') year_month_num_1,
      TO_CHAR (dt, 'Mon') || ' ' || TO_CHAR (dt, 'YYYY') year_month_name_2,
      TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'MM') year_month_num_2,
      TO_CHAR (dt, 'WW') week_of_year_num,
      TO_CHAR (dt, 'W') week_of_month_num,
      TRUNC (dt, 'W') week_start_date,
      TRUNC (dt, 'W') + 6 week_end_date,
      TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'WW') year_week_num,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'WW') year_week_name,
      TO_CHAR (dt, 'D') weekday_num,
      TO_CHAR (dt, 'Day') weekday_name
 FROM (SELECT     TO_DATE (:start_date) + ROWNUM dt
             FROM DUAL
       CONNECT BY TO_DATE (:start_date) + ROWNUM <= TO_DATE(:end_date));

Metadata reporting on the OWB 11gR1 repository- options?

2010-02-05T19:15:00.006-06:00

There are quite a few clients, coworkers and managers I've met with that feel the documentation capabilities of OWB are, shall we say....lacking a bit. Sure, there is the OWB online repository browser which is quite handy and gives you a lot of information. There is the metadata lineage and impact analysis diagramming which again is quite handy. There are various tricks one can do with OMB*PLUS or experts to whip up some simple documentation. Of course when you want to get specific, then it becomes a challenge.

Say you want a report, with minimal work, that is in a CSV or excel spreadsheet showing all sorts of values that you figure you can grab from the repository somehow. Again, you can accomplish some great things with OMB and experts, and I have done this, but many times it is too time intensive and overkill. Sometimes you might consider UDOs (User Defined Objects) which can be created with OMB scripting, which give you additional fields and properties to use on your objects. This can make things easy when you are in the beginning of a project and you can tackle documenting things as you go along.

What if you're 50% or even 90% done with your project, and someone demands something - fast. I say, look at the repository quickly, write some SQL, and get it out there fast.

Well in OWB 11gR1, the repository is broken down into things called workspaces. These workspaces have separate owners, and they have users themselves. So if you want to access the OWBSYS schema's many views that hold important metadata, you'll need to set some context for the user accessing the data- namely the various workspace owners. Below are a few steps that have been published in forums and the owb blog as well that outline what you should do if you cannot see any data in OWBSYS's views.....

First read this.

Then....

THEN, here are 2 sample scripts that are in the oracle forums as well. These should get you started, and get your mind thinking i terms of what other views you'd like to look at, and what other columns are you interested in...as well as the various ways to join the views!

--This script focuses more on mapping contents...
select
distinct 'TARGET',
comp.map_name,
comp.data_entity_name,
comp.operator_type
from
all_iv_xform_map_components comp,
all_iv_xform_map_parameters param
where
lower(operator_type)
in ('table', 'view', 'dimension', 'cube')
and param.map_component_id = comp.map_component_id
and param.source_parameter_id is not null
UNION
select
distinct 'SOURCE',
t1.c1,
t1.c2,
t1.c3
from
(select
comp.map_name c1,
comp.data_entity_name c2,
comp.operator_type c3,
max(param.source_parameter_id) c4
from
all_iv_xform_map_components comp,
all_iv_xform_map_parameters param
where
lower(operator_type) in
('table', 'view', 'dimension', 'cube')
and param.map_component_id = comp.map_component_id
group by
comp.map_name, comp.data_entity_name, comp.operator_type) t1
where t1.c4 is null
order by 2,1

--This script focuses more on source to target tables and column mappings

SELECT
S_TBL_LOC ,
S_TBL_name,
S_FLD_name,
T_TBL_LOC ,
T_TBL_name,
T_FLD_name
FROM
(
SELECT
parms.map_component_id S_COMP_id ,
parms.parameter_id S_PARM_id ,
colms.entity_name S_TBL_name ,
colms.column_name S_FLD_name ,
tbls.schema_name S_TBL_LOC
FROM
all_iv_xform_map_parameters PARMS,
all_iv_xform_map_components COMPS,
all_iv_columns COLMS ,
all_iv_tables TBLS
WHERE
source_parameter_id IS NULL
AND parms.map_component_id = comps.map_component_id
AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')
AND parms.data_item_id =colms.column_id
AND colms.entity_id =tbls.table_id
)
,
(
SELECT
parms.map_component_id T_COMP_id ,
parms.parameter_id T_PARM_id ,
parms.source_parameter_id T_Sid ,
colms.entity_name T_TBL_name ,
colms.column_name T_FLD_name ,
tbls.schema_name T_TBL_LOC
FROM
all_iv_xform_map_parameters PARMS,
all_iv_xform_map_components COMPS,
all_iv_columns COLMS ,
all_iv_tables TBLS
WHERE
source_parameter_id IS NOT NULL
AND parms.map_component_id = comps.map_component_id
AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')
AND parms.data_item_id =colms.column_id
AND colms.entity_id =tbls.table_id
)
WHERE
S_PARM_id=T_Sid

Gartner's take on oracle- quite positive.

2010-02-01T12:57:00.001-06:00

see article here

Data Warehousing Training

2010-01-31T16:48:00.002-06:00

I just got done custom creating an advanced data warehousing class, along with updating our beginner course to utilize Oracle 11g technologies. Whew, talk about a lot of work and only a short time to get it done! To summarize, the course that I teach is a full 5 day class on the following topics:

-Beginner Course Topics-
Beginning Data Warehousing
Dimensional Modeling
Oracle 11g Database options for Data Warehousing
Oracle Warehouse Builder OWB 11gR1 introduction
OWB11g Architecture and Installation
OWB11g and OWB 10gR2 differences, similarities
Exploring OWB11g Interface
Common setup of typical scenarios with remote machines and schemas
Building ETL Mappings from source to staging to target
Deep-Dive into OWB Mapping best practices and tips with the operators
Understanding Data Quality
Data Profiling and Data Rules Option within OWB11g
Advanced operators- Match & Merge, pre-post mapping operators, table functions, etc.
Understanding Workflow installation and Process Flow Basics

-Advanced Course Topics-
Oracle OLAP
Understanding Oracle's Position on OLAP & Hyperio Essbase
How to use OWB to construct full MOLAP cubes and how it differs from ROLAP
Extensive ETL Lab (2-3 hour lab encompassing many advanced techniques)
Pluggable Mappings
The Metadata Manager (Impact, Dependency, and Propagation)
Lifecycle Management (MDLs, Snapshots, Signatures, importation, exportation, comparison of
snapshots, etc.)
Security in OWB
Extra detail on Process Flows and Scheduling
OMB*PLUS scripting
OWB Experts
Advanced Tips and Tricks for project management, and high Performance

...and that's all. I built out about 90% of this class and taught it recently to pretty good reviews... Let's hope there's more students in store as I prepare to build out a new offering to my students!

Cheers
-Greg

Avatar, the movie

2009-12-27T13:46:00.004-06:00

I haven't blogged in quite a while. I apologize to any readers that come here either for discussion or to learn something. I have (for better or worse) been summoned to jury duty here in the U.S. For those that are unfamiliar, in the U.S. anybody that registers a vehicle or registers to vote will eventually be called upon to do their civic duty. Jury duty is a process where you might be selected by a lawyer to serve as one of the jurors on a case that is tried in court with a Judge and jury. I not only was summoned for duty, but I was also selected to be on the actual jury. During this time, we're asked to have as little interaction with the 'real' world as is possible, and to never talk about the case. Now that it is over, after 2 whole trying weeks, I can feel more comfortable blogging and such.

This post is going to be a review of a movie I saw this past Christmas Eve (Dec 24th), called Avatar. Most people in the world now should have at least heard of it since it cost over 240 million US dollars to produce, and another 140 million dollars was spent marketing it. Those figures may even be higher in reality. What can I say about it?

Simply Incredible!!!!!!

This has to be one of the most amazing movies I have ever seen, period. Not since the epic Lord of the Rings movies have I felt so enthralled, emotional, and pulled into a movie as deeply as this. I also should mention I saw it in 3D. They offer it in 3D and regular formats. I think seeing it in 3D was even more impressive. I could go on and on about this movie, but I won't. I will see it again for sure. My fiance and I were completely stunned with the movies grand scope and awesome graphics and environment. You completely forget that what you are looking at most of the time does NOT exist! It is graphics! The characters are realistic, and believable.

Again, this is easily in my top 10 movies I have ever seen, and I recommend it as highly as I can.

Please feel free to drop a comment on this. P.S.- I have read all the critic articles about how this movie has an underlying theme such as anti-American, anti-African native, feminist, non-feminist, anti-human, you name it. Sure, there are many themes here that pull on your emotions, but please see the move for what it is first, and then delve deeply into your own subliminal interpretations afterward. One thing is for sure, it makes me think about human irresponsibility and greed and the slow destruction of our own planet...and to this most of us are guilty.

Just because I think Google tools are cool...

2009-09-30T15:55:00.002-05:00

A marriage of google and BI...not bad. I'll link here to Mark's blog to let you see what I mean.
Google has long been (whether you knew it or not) a VERY heavy database-centric company, and they are harnessing the concept of SAAS like few others. This is a great representation of a nice tool with some 'oopmh!' (Is that a word?) I expect Google to grow more powerful in this space. We'll see. I'll be watching.

Helpful Links on Data Warehousing Fundamentals

2009-09-25T09:35:00.002-05:00

These might not all be 'fundamentals' but the articles at this link are solid. Many may not apply to you since they are great when applied to very specific situations (such as modeling multiple employee dimensions).

In general though, I think many people can learn something here. Kimball and sometimes Margy Ross here share good insight. They're not going to give you the gold nuggets that you'll likely get from their paid training, but the articles will get you thinking about your design.

HERE

I have been AFK! - away from the keyboard

2009-06-25T09:42:00.005-05:00

Well not actually. I have been in front of a computer or using a keyboard every single day practically since my last post in April! Work has been extraordinarily stressful and busy. You know...when you have 10 tasks and 2 people? :) Yes, THAT busy. In a way I suppose that's a good thing. We've had a global meltdown since the end of 2008 in the economy. Things picked up a bit since their March lows...up a good 20-30% depending on the index you measure.

There's never a shortage of work for people that really know how to do it. So, as I said...maybe the mind-numbing heaps of work is a good thing in this economic storm.

I want to apologize for not posting more regularly! I used to take it upon myself to blog while waiting for things, sitting in an airport, having a spare 30 minutes to share interesting observations on Oracle. I just haven't had that time recently :(

One link I would like to provide is courtesy of slashdot. I read there regularly. This article and the following comments (warning, MANY comments) are mostly about IT in healthcare, and why it is SO behind. It follows up a bit on my last post in April about Healthcare and Medical Informatics.

Another posting I have decided to link to simply burns me up. Very Angry! Here
Read on if you dare! I whole-heartedly disagree with this analysis, and I have many years of firshand experience!

I also wanted to mention (Sept 4 2009) that I received this very interesting comment. I didn't know what language it was, but I assumed arabic, hebrew, russian...I was not sure. So Google's translator was VERY handy! it detected the comment attached to this post was in Russian and tried to give a best translation to English. Here it is:

Если врач знает название вашей болезни, это еще не значит, что он знает что это такое. Никогда не приписывай человеческой зловредности того, что можно объяснить обыкновенной глупостью. Человек может долго жить на деньги которые он ждет. Реальность это иллюзия вызываемая отсутствием алкоголя. Женщины едят за разговорами, мужчины едят заедой

This, according to Google is suppsoed to mean:

"If the physician knows the name of your illness, it does not mean that he knows what it is. Never ascribe to human malignancy that can be explained by ordinary stupidity. A man may live long on the money that he waits. Reality is an illusion caused by lack of alcohol. Women eat for talking, men shall go eat."

Interesting. Confusing but interesting. A bit abstract!

The Wide World of Medical Informatics

2009-04-10T13:43:00.005-05:00

I got thinking the other day (and have thought this for quite a long time- as far back as 1999) that if there was one niche or sub-field that really falls right into my interests and background it is medical informatics. I have degrees in cell and molecular biology, as well as computer science. I have been fortunate to work in hospitals, medical research facilities, and in medical schools at various levels. Not to mention my career as an Oracle consultant.

I have been very very busy recently, and have decided to try and post regularly again. This particular subject motivated me to do so! To start with, the subject "The WIDE World of..." is misleading. It is true that Medical Informatics requires a very wide skill set as opposed to what most strong oracle developers individually, or most medical practitioners individually have. Each of these are very deep careers in themselves! However, we can also say the subject itself, as opposed to the knowledge needed to DO the work is not particularly wide. Nor is it very established and mature! Schools only recently in the last decade have even developed patchwork curriculum studies to earn certificates or master's degrees in bioinformatics, or medical informatics, or medical analytics.

To top it off, I have noted that such top schools including Northwestern University, right here in Chicago, Illinois, USA, don't have anywhere near the strict admissions standards applied to these newer programs in their infancy. You don't even have to take the GMAT or GRE (graduate admissions exam) to matriculate! It is recommended, but not required. Admissions In most cases, this indicates a program or field of study still in it's infancy and looking to establish itself as a solid undeniable mainstream option.

I do believe that as of now, it is undeniably necessary and important. However, most medical institutions are in the stone-age. Any very solid technical person that steps into hospitals or even doctor's offices will see some systems that are VERY complicated and state of the art. Other systems, such as records systems and software programs are buggy, poorly laid out, or cause the practitioners (nurses, doctors, executives) headaches when trying to enter data, or more importantly...retrieve data that is accurate, up to date, and understandable! The real focus...informatics...is important. The analytics and informatics methodologies in a business intelligence lifecycle simply are lacking in major healthcare organizations. I am not just talking about hospitals, but health insurance companies too!

I have personally sat in doctor's offices where multiple doctors and even medical execs can't stress enough how much they hate having to use EPIC Systems software, or Cerner Software.
I don't know enough about them to explain the issues, but I have seen the pained expression too many times. I have plenty of friends that worked from a technical or manager perspective for major healthcare insurance companies that ALL complain about the same thing: old and not too sharp technology OR sharp technology being used the wrong way which costs even more $$.

I've read it and heard it in person so many times that I've decided to try and write a few blog posts on this topic. The field is ripe. The education is now available. The technologists (Oracle BI and DW folks) are very much present and ready. The doctors know just enough of what they want but VERY few have the skills that we Oracle BIDW developers and managers have.

I want to provide a link for readers to pursue:
Saving Lives With Oracle

What might a VERY general role of the Oracle scientist, or informatics individual be?

Medical Informaticists fill a vital role in health care systems management, verifying and certifying critical computer system components:

Certifying medical systems for accuracy and completeness
Validating medical decision support systems and expert systems for clinical diagnosis and treatment
Verifying the accuracy of Oracle database output results and reports

Is it necessary to have a M.D.? I do not think so. However, this is not like ANY other consulting or business engagement. The doctor or the healthcare organization has a lot of knowledge and very detailed ideas of how things need to work. This is just not something the average consultant can pick up in a few meetings! Unless you have some experience in scientific industries, or have worked in a medical or healthcare settings, or have an actual M.D., you'll likely need to work with a person or small team from the organization that knows all the ins and outs of healthcare, diagnoses, patient records, records management, prescription management, insurance, and core hospital measures and operating practices!

Do not be held back by that though! The reason I believe this field has been too slow to advance, is lack of education, and extremely high amounts of knowledge necessary. I will continue writing on this topic. I'd like to disuss things such as data mining, analytics, and other topics that are relevant!

Oracle Warehouse Builder and SAP Sources

2008-12-08T17:50:00.005-06:00

I saw a nifty link that Antonio Romero, one of Oracle's OWB product managers, gave out. When I sat in the Publisher's and Author's round table event at the last Open World, I was lucky to sit with the head of Oracle's documentation and discuss a bit on the online documentation structure, and changes coming. As well, I heard from OWB PMs that the OWB docs would be filled out more. Here is the start of that effort, in chapter 7 - Retrieving Data From SAP Sources.
Antonio kindly brought this to attention recently.

Paper accepted for ODTUG Kaleidoscope 2009

2008-11-19T14:02:00.004-06:00

Someone out there likes me, and I'll need to find out who. But the good news is that I get to share with the community some 'lessons learned' and case studies related to OWB. My paper was an early accptance to the ODTUG 2009 Kaleidoscope conference, and I am grateful for that. The list of people already accepted is quite an accomplished group. I sincerely hope that OWB 11gR2 is released and available by that point in time, as I will then get to discuss those new features along with 11gR1/1ogR2. Although the case study aspect will not be possible for 11gR2 since it is only in beta.

So come on out to ODTUG's 2009 confernece and find out how OWB can be put to some serious use...after all, we won the 2008 Oracle Partner of the Year Titan Award in Business Intelligence and OWB was a HUGE part of that.

Grants, Grants, and More Grants - WITH GRANT OPTION

2008-10-29T16:11:00.004-05:00

ORA 01720 grant option does not exist for....

Simple mistake, but easy to fall into.

User_A creates a view called AV. This view accesses some of User_A's objects but also accesses one of User_B's tables too (table name is BT). No problem, User_A had been granted select on that table (BT) from User_B:

(as User_B) GRANT SELECT ON USER_B.BT TO USER_A

Now, however, User_A wants to grant select on that view, AV to a third user...User_C.
Even if User_C had also been granted select on User_B.BT table, USER_A would not be able to simply say:

(as User_A) GRANT SELECT ON USER_A.AV TO USER_C

instead you'll see:

ORA 01720 grant option does not exist

What this means is that USER_A tried to grant access to one of USER_B's tables to USER_C through the view AV. Even though USER_C had been directly granted select on USER_B.BT from USER_B itself, you'll still see the above error message.

Only the schema that owns the object can grant privileges to that object unless the 'with grant option' is included in the command. The 'with grant option' allows you to give the user that receives the grant (with the grant option) the ability to grant that same privilege to other users too. In a way, it is like giving a bit of your authority on one of your own objects to another trusted schema. Here is an example of the use of the with grant option:

(As USER_B) GRANT SELECT ON USER_B.BT TO USER_A WITH GRANT OPTION

What this did was it allowed USER_A to then grant USER_C select access to USER_B.BT table. It ALSO indirectly allows USER_A to grant select on USER_B.BT table through the view, USER_A.AV.

You can now perform the following with no problems:

(as User_A) GRANT SELECT ON USER_A.AV TO USER_C

Oracle Enterprise Linux, Ubuntu Linux, and more Linux

2008-10-01T23:03:00.002-05:00

So I've been building a few Virtual Machines using Oracle VM as well as VMWare using different distros of Linux to see the major fuss between them all. Seems everyone has a favorite.

At least from an Install point of view, Ubuntu was easily smoother, however OEL offered many more installation and configuring options to start right away. This I liked. I have to be honest, I could care less how pretty and customizable Ubntu seems to be if OEL can run Oracle software better in any way. I'll keep posting anything that I find to be a deal breaker for me. I am just looking for the best of breed to build out my many testbeds for all the configurations of the Oracle stack I'll need. I am sure many of you out there are in the same boat!

Oracle Open World, Titan Awards, Piocon, Events, and more...

2008-09-27T12:24:00.006-05:00

Well, we did it again! My company won the Titan award for the second year in a row. Actually both years was technically a Business Intelligence solution, but the project that won in 2007 was not using as many BI pieces as was possible, so we won the Health Care Industry Solution award. However, for 2008, we utilized so much BI it was crazy. Custom developed methods, out-of-the box functionality, you name it. And, as Antonio Romero (one of the OWB product managers) happens to ponder in the OWB blog below about the ROI.... let's just say it was so far beyond expectations that everyone was shocked in a good way.

but a preview at Oracle's OWB blog: link
and a preview at our home: link

The bottom line is this. We know DW, we know BI, and we know the real business that needs to happen behind the scenes to implement a solution that is adopted and widely used to help a company. Piocon now has 2 big reasons to boast that we're the premier provider of Business Intelligence and Data Warehousing solutions for 2 years in a row.

My Wordle

2008-09-05T23:10:00.004-05:00

Just figured I would mention what this blog's wordle looks like:

And bigger:

Techno geeks and people that love animation...

2008-08-28T08:29:00.001-05:00

My buddy Dan Norris passed this on to me today. Simple, yet brilliant. Tech people in particular will like this, and not a bad job at all on the animation aspects. VERY cool! See it here: link

Laughing Squid Web Hosting » Support & Contact Information

2008-08-26T22:12:00.001-05:00

I love me some google blogger, because it is easier than dirt! But I've played with quite a few hosting companies and these guys (Laughing Squid) are extremely good and make life using Word Press very VERY easy. This is especially good for the non-techies or managers who just wanna blog.
Laughing Squid Web Hosting » Support & Contact Information: "Laughing Squid Web Hosting"

OWB Interesting Warnings and Errors #1 PL/SQL: ORA-00907: missing right parenthesis

2008-08-26T18:08:00.012-05:00

(Please remember you may click on any picture for a larger view!)

I thought while I am writing away on the new Oracle Warehouse Builder 11g Handbook for Oracle Press / McGraw-Hill that I would still put out a few simple and useful gotchas on my blog for beginner and intermediate users. The most interesting things that seem to pop up are the odd and slightly off-target errors you may receive if you do something wrong. So here is interesting warning and error #1. Relatively straight forward, but can still trick any beginner and some intermediate users.

Let us say that we have a situation that calls for using a key lookup operator. Sometimes, when the lookup results in no matching row for the input data, we have the option to tell the operator what we would like to be output (instead of plain old NULL). For example, below, I might not have a match for my incoming data for the column tgt_unit_name. If I do not, I would output a 'default value' and not simply NULL. In the case below is where my error or warning actually arises. Note that I entered 'N/A UNIT' for my default value. If I had entered N/A UNIT without the ' ' marks, this would cause OWB to complain and issue an error that might not make perfect sense to the beginner. Let us now assume that I did NOT enter the opening and closing ' ' tick marks.
If I go ahead and try to validate the mapping that this key lookup operator appeared in, it would validate perfectly. Furthermore, if I decided to generate the code, and give it a once-over, it might look fine as well. However, when i go to my control center to deploy this mapping...the warnings will fire. So the culprit is PL/SQL: ORA-00907: missing right parenthesis. If you have a nice code tool like pl/sql developer, or toad, or sql developer, you'd likely generate this code from your mapping, and throw it into your development environment and look for problems. A beginner might go right ahead and look for some missing parenthesis. Right? Wrong! This is one area in which you have to think about something. OWB creates code. Usually syntactically correct code. It will not normally and purposely create code that has missing parenthesis. The problem is somewhere else, and it deals with the fact that we did NOT put the opening and closing ' ' around N/A UNIT. See below:

We can clearly see the right number of parenthesis are there, but one is not interpreted due to N/A UNIT not being formed correctly! Let's go back to the mapping, and the key lookup operator and make sure we enter 'N/A UNIT'. (Remember, the way you interact with OWB literally is writing code.) We make the change, and deploy the mapping, and get no errors or warnings!

So, what does the working code snippet look like now?
Note that with a nice IDE, like SQL Developer, we can see that the moment the ticks are in place and the 'N/A UNIT' value is handled correctly in the NVL, we have success. This is to illustrate to the beginner or intermediate user that everything you do in OWB is writing code under the hood. Simply forgetting to use the tick marks to form a literal can result in an error that might make you spend many minutes hunting down a missing parenthesis! Remember that OWB generates PL/SQL, but will listen to you, the user (almost too much) and do exactly as you tell it!

ORACLE Blogs Home

2008-08-15T11:05:00.001-05:00

Just in case you have been under a rock recently or even on vacation, here is a quality link right into the main aggregation site of ALL Oracle Corp Blogs...link

FULL Outer Joins FOJ in OWB

2008-07-29T10:28:00.001-05:00

See the owb blog: here

Oracle RAC ATTACK in Chicago

2008-07-24T15:28:00.000-05:00

read about it here ...

Conan, what is best in life??

2008-07-21T22:57:00.000-05:00

OWB patching part 2

2008-07-19T14:17:00.002-05:00

I should have mentioned this in my first patching post a few days back. But...test test and test your patching and what it does to your system before deploying it into a secure production system.

See this OTN forums link...a few people (only 2) are having peculiar problems already with the OWB patch. This is not terribly surprising...things take a while to get perfectly right even with a patch!

Nice new Jdeveloper ( JDEV) article up on OTN

2008-07-18T15:16:00.003-05:00

Good 'ol Shaun O'Brien has a nice new article at the top of OTN today on JDEV and some nice new add-ins and extras. Check it out! Especially nice is the discussion about Web Logic already!

Introduction

With the acquisition of BEA, Oracle has added a significant amount of technical collateral to it's already impressive listing of potential choices for project implementation. This how-to will focus on the steps necessary to make use of the same codebase under the individual flagship development environments: namely Oracle JDeveloper and Oracle Workshop for WebLogic.

The codebase to be utilized is a sample application from the Workshop side of the world, the prototypical SUN Dukes Bookstore that can be accessed at the following url: jsf-bookStore.war.

OTN

OWB patching

2008-07-14T12:20:00.001-05:00

Just to let you all know that are still using OWB 10gR2 (quite a few I bet) it looks like there is a new patch out there that came out in July, so if you're having as issue with one of the 120 bugs it supposedly fixes, get on over to metalink to investigate the new patch!

What Does Oracle have in store for OWB and ODI? Here is a Roadmap!

2008-07-08T17:09:00.001-05:00

Check out the link direct from Oracle on their plans with Oracle Warehouse Builder and Oracle Data Integrator - OWB and ODI.
check it out here.

BIWA Summit

2008-07-01T16:01:00.003-05:00

All- I am going to post and link to the summit upcoming in December here, and leave a perma-link on the right side of my page if you care to visit! Go here! link

Data Warehouse Structures: a bridge table

2008-06-28T13:29:00.005-05:00

Bridge tables; I see this topic come up quite often. They are also known in some circles as gap tables, associative tables, helper tables, or sometimes just B tables. I have referenced a few very valuable bits about this structure below. Unfortunately, google needs a few more words than just bridge table or helper table, since the card game 'bridge' actually uses a special table, thus making the search for our info a bit harder. Sheesh! Copied here for fear they might be removed for some reason...I cannot always trust that the Internet Wayback Machine will work perfectly. So here in its original glory:

Help for Dimensional Modeling

Helper tables let you design and manage multivalued dimensions successfully.

By Ralph Kimball, DBMS online magazine

The goal of dimensional modeling is to represent a set of business measurements in a standard framework. Dimensional modeling is attractive because end users usually easily understand this framework. The schemas that result from dimensional modeling are so predictable that query tool vendors can build their tools around a set of well-known structures. The classic star join schema that results from a dimensional model usually has between six and 10 dimension tables connected directly to the fact table, as Figure 1 shows. These dimension tables have a very specific and precise relationship to the fact table.

FIG 1

Think of the fact table as a set of measurements made in the marketplace. These measurements are usually numeric and taken before the creation of a given fact table. A fact table record may represent an individual transaction, such as a customer withdrawal made at an ATM, or the fact table record may represent some kind of aggregated total, such as the sales of a given product in a store on a particular day. The dimension tables, on the other hand, usually represent textual attributes that are already known about such things as the product, the customer, or the calendar.

When you are building a fact table, the most important step in the detailed logical design is to declare the grain of the fact table. The grain declares the exact meaning of an individual fact record. In these examples, the first grain is individual customer transaction and the second grain is daily product total in the store. If the database designer is very clear about the grain of the fact table, then choosing the appropriate dimensions for the fact table is usually easy.

The secret of choosing dimensions for a given fact table is to identify any description that has a single value for an individual fact table record. In this way, you can work outward from the grain of the fact table and "decorate" the fact table with as many dimensions as you can imagine. For the ATM customer transaction example, imagine that the following dimensions all have a single value at the instant of the transaction:

Calendar Date
Time of Day
Customer Account
Physical Location
Transaction Type (deposit, withdrawal, balance inquiry, and so on)
Weather Summary for the Day.

These are all excellent dimensions to attach to the transaction-grained fact table record. Each of these dimensions has many descriptive attributes. The descriptive attributes are usually textual and are the basis for constraining and grouping in the user's reports. In the case of Time of Day, the textual attributes could refer to specific periods such as Morning Rush Hour, Mid Morning, Lunch Hour, Mid Afternoon, Afternoon Rush Hour, and so on. In this case, the sole numeric measured fact is the amount of the transaction. This is the basis for Figure 1.

Strict adherence to the grain definition lets you disqualify dimensions that do not have a single value for a given fact record. In the daily product totals example, you can propose the classic retail dimensions of:

Calendar Date
Product
Store
Promotion (assuming the promotion remains in effect the entire day).

But you must omit the Customer and the Check Out Clerk dimensions that you might assume would be included because these dimensions have many values at the daily grain we have chosen.

These examples give a very powerful insight. You are more likely to disqualify a dimension if you are dealing with an aggregated or summarized table. The more the fact table is summarized, the fewer the number of dimensions you can attach to the fact records. The converse of this is eye opening. The more granular the data, the more dimensions make sense. The lowest-level data in any organization is the most dimensional. This is why I argue that the "atomic data store" must be presented as a dimensional model.

Having made a pretty tidy argument for single-valued dimensions, perhaps I should consider whether there are ever legitimate exceptions. Are there situations where you might need to attach a multivalued dimension to a fact table? Is this even possible, and what problems might arise?

Consider the following example from healthcare billing. You are handed a data source for which the grain is the individual line item on a doctor bill. The data source could be patient visits to doctor offices or individual charges on a hospital bill. These individual line items have a rich set of dimensions that we can list rapidly:

Calendar Date (of incurred charge)
Patient
Doctor (usually called "Provider")
Location
Service Performed
Diagnosis
Payer.

The numeric additive facts in this design (which are the core of every fact table in a dimensional design) would include Amount Charged and perhaps others including Amount Paid, depending on the nature of the data source.

So far, this design seems to be pretty straightforward, with obvious single values for all of the dimensions. But there is a sleeper. In many healthcare situations, there may be multiple values for Diagnosis. What do you do if a given patient has three separate diagnoses at the moment the service was performed? How about really sick people in hospitals that might have up to 10 diagnoses? How do you encode the Diagnosis dimension if you wish to represent this information?

Database designers usually take one of four approaches to this kind of open-ended, multivalued situation:

Disqualify the Diagnosis dimension because it is multivalued
Choose one value (the "primary" diagnosis) and omit the other values
Extend the dimension list to have a fixed number of Diagnosis dimensions
Put a helper table in between this fact table and the Diagnosis dimension table.

Let's not take the easy way out by disqualifying the Diagnosis dimension from the design.

Frequently, designers choose a single value (the second alternative). In the healthcare field this often shows up as the primary, or admitting, diagnosis. In many cases, your hands are tied because you are given the data in this form by the production system. If you take this approach, the modeling problem goes away, but you are left doubting whether the Diagnosis data is useful.

The third approach of creating a fixed number of additional Diagnosis dimension slots in the fact table key list is a hack, and you should avoid it. Inevitably, there will be some complicated example of a very sick patient who exceeds the number of Diagnosis slots you have allocated. Also, you cannot easily query the multiple separate Diagnosis dimensions. If "headache" is a diagnosis, which Diagnosis dimension should be constrained? The resulting or logic across dimensions is notorious for running slowly on relational databases. For all these reasons, you should avoid the multiple dimensions style of design.

FIG 2

If you really insist on modeling this multivalued situation, then a "helper" table placed between the Diagnosis dimension and the fact table is the best solution. (See Figure 2.) The Diagnosis key in the fact table is changed to be a Diagnosis Group key. The helper table in the middle is the Diagnosis Group table. It has one record for each diagnosis in a group of diagnoses. If I walk into the doctor's office with three diagnoses, then I need a Diagnosis Group with three records in it. It is up to the modeler to build either these Diagnosis Groups for each individual or a library of "known" Diagnosis Groups. Perhaps my three diagnoses would be called "Kimball's Syndrome."

The Diagnosis Group table is joined to the original Diagnosis dimension on the Diagnosis key. The Diagnosis Group table in Figure 2 contains a very important numeric attribute: the weighting factor. The weighting factor allows reports to be created that don't double count the Billed Amount in the fact table. For instance, if you constrain some attribute in the Diagnosis dimension such as "Contagious Indicator" with the values Contagious and Not Contagious, then you can group by the Contagious Indicator and produce a report with the correct totals. To get the correct totals, we must multiply the Billed Amount by the associated weighting factor. This is a correctly weighted report.

You can assign the weighting factors equally within a Diagnosis Group. If there are three diagnoses, then each gets a weighting factor of 1/3. If you have some other rational basis for assigning the weighting factors differently, then you can change the factors, as long as all the factors in a Diagnosis Group always add up to one.

You can, interestingly enough, deliberately omit the weighting factor and deliberately double count the same report grouped by Contagious Indicator. In this case, you have produced an "impact report" that shows the total Billed Amount implied partially or fully by both values of Contagious Indicator. Although the correctly weighted report is the most common and makes the most sense, the impact report is interesting and is requested from time to time. Such an impact report should be labeled so that the reader is not misled by any summary totals.

Although the helper table clearly violates the classic star join design where all the dimension tables have a simple one-to-many relationship to the fact table, there is no avoiding the issue of what to do with multivalued dimensions that designers insist on attaching to a fact table. Fortunately, designers rarely insist on attaching a multivalued dimension to a set of measurements. In most cases, you have no rational basis for assigning weighting factors, or even worse, you don't have a plausible short list of candidates from the multivalued dimension. Thus you don't waste time trying to attach the retailer's Customer dimension to an aggregated daily or monthly sales fact table. It just isn't interesting or useful.

When you attach a helper table to a fact table, you can preserve the star join illusion in your end-user interfaces by creating a view that prejoins the fact table to the helper table. The resulting view then appears to have a simple Diagnosis key that joins to our Diagnosis table in the healthcare example. The view can also predefine the multiplication of the weighting factor with any additive facts in the fact table.

I have seen two other situations besides healthcare where a multivalued dimension makes sense. In addition to the Diagnosis example discussed in this column, I have built multivalued dimensions for retail banks and standard industry classifications.

Retail Banks. There are often cases where the Account Dimension is "inhabited" by one or more human customers. If the bank wants to associate individual customers with account balances, then the Account dimension must play the same role as the Diagnosis Group dimension in the healthcare example. Banks are quite interested in both correctly weighted reports and impact reports.

Standard Industry Classifications. This term defines the so-called SIC code that is assigned to a commercial enterprise to describe what industry segment they are in. The problem with SIC codes is that all big enterprises are represented by multiple SIC codes. If you encode a fact table with an SIC dimension, you have the same problem as with the multiple diagnoses. SIC codes are really quite useful, however. If you want to summarize all the business you do selling to "Manufacturers" and "Retailers" by using their SIC code, you will need an SIC Group helper table. Here's a case where with some careful thought you may assign unequal weighting factors if you believe the enterprise is mostly a manufacturer and only a little bit a retailer.

Next month I will describe a different kind of helper table for dimensions. In this case, the helper table does not describe a many-to-many relationship but rather describes a hierarchical relationship. We can use this second kind of helper table to navigate organizational hierarchies and manufacturing parts explosions.

On the OTN forums lately...

2008-06-20T22:26:00.005-05:00

I have taken a brief reprieve from my blogging for a bit so that I can contribute more to the OTN forums. I have been posting anything from 1-5 replies daily if I think I might be able to help anyone with what I know. I think a lot of my postings have been in the OWB area for now.

I'm likely going to Oracle Open World this year, as I got an invite with the nice free full conference pass. I am not sure I'd go without that thing being somehow covered! :) Hopefully I'll see my Oracle friends out there, and my family who (some of them) live in the bay area, as well as old school friends in SF and Oakland that went to Berkeley and my old U of I buddies that live in the Silicon Valley now.

Woohoo!

OWB can be a bit 'blind' when Oracle RAC is involved!

2008-06-11T16:07:00.000-05:00

I will update this bit soon I hope. I'll post a little nasty bit that tricked us and cause our mappings to run horribly slow on a RAC environment. Hopefully when I get a little more information on RAC from Dan Norris, I'll update this with a clear explanation!

OWB 11g R2 beta and book

2008-05-31T23:29:00.005-05:00

Good news! I have been accepted into the OWB11gR2 beta, and I am putting the new features though the testing ringer to see just how well everything will turn out. I will likely not be able to mention or discuss much of it, so please don't ask. If I have been given consent, I will share at the approved time.

I am also in the early stages of bringing a massive amount of experience, projects, data, and writing manuscripts together for an even bigger project. I'll be starting very soon to write the first Oracle Warehouse Builder book ever published. It will be with Oracle Press (McGraw Hill) and will be taking advantage of all the beta experience that will be gained from the OWB11gR2 beta.

In the coming months I will update here on anything that is allowed and not confidential.

Chicago Oracle User's Group this Monday!

2008-05-10T10:05:00.002-05:00

Anyone in the the Chicago area should not forget the Chicago Oracle user's Group! My colleague Dan Norris will be speaking on Oracle Adaptive Access Manager and security in general. This will take place this coming Monday in Chicago (where else??) Check it out here! link

Data integration is not necessarily creating you a Data Warehouse

2008-05-10T10:03:00.001-05:00

Nice quick read, but older article! link

Oracle Warehouse Builder OWB vs. Oracle Data Integrator ODI

2008-04-02T10:01:00.002-05:00

Here's a snippet from a bigger article written by Mark Rittman over on OTN.

Oracle Data Integrator in Relation to Oracle Warehouse Builder

At this point, regular users of Oracle Warehouse Builder are probably wondering how Oracle Data Integrator relates to it and how it fits into the rest of the Oracle data warehousing technology stack. The answer is that Oracle Data Integrator is a tool that’s complementary to Oracle Warehouse Builder and can be particularly useful when the work involved in creating the staging and integration layers in your Oracle data warehouse is nontrivial or involves SOA or non-Oracle database sources.

For those who are building an Oracle data warehouse, Oracle Warehouse Builder has a strong set of Oracle-specific data warehousing features such as support for modeling of relational and multidimensional data structures, integration with Oracle Business Intelligence Discoverer, support for loading slowly changing dimensions, and a data profiler for understanding the structure and semantics of your data.

Where Oracle Data Integrator provides value is in the initial preparation and integration of your source data, up until the staging area of the data warehouse.

Oracle Data Integrator can integrate and synthesize data from numerous disparate datasources, including Web services and event-based architectures, and, as shown in the figure above, provides a handy graphical interface on top of Oracle Database-specific features such as Oracle Change Data Capture. Once data has been integrated and copied into your data warehouse staging area, Oracle Warehouse Builder can take over and create and populate your operational data store and dimensional warehouse layers.

Do certifications always prove you're qualified to do the job?

2008-03-29T08:12:00.002-05:00

Recently, I have come to the understanding that many certs fail to really certify whether or not someone can truly master material and do an effective job. There are quite a few individuals who are just very good test takers or students, if you will, and they study very hard or take expensive 'crash-courses' to help them pass with a high score.

There are a very few amount of certifications that I believe don't fail however. I can honestly say I have NEVER met someone that became an 'Oracle Master' that really wasn't at least a 7/10 on a 10-point scale. (Most DBAs being 3, 4 or 5.) Someone like Tom Kite or Kevin Loney being around the 10 range. This certification, of course, requires you to take hands on labs that are very difficult and stressful, along with prerequisite certifications and courses.

On the other hand, a very popular certification that seems to garner a lot of attention is PMI's PMP cert. This is for project management. Unfortunately, employers drool over this certification. PMI tried to do an extensive job of screening out people that are even allowed to TAKE the exams by having you fill out how much management work you've done, references, companies, and the whole 10 yards. Unfortunately, employers seem to feel that this means PMI has cleared this person as a top proj manager, and they don't have to have their own proven, top level project managers interview them on their PM skills and abilities.

Because of this, you have people that have 'hacked' their way through the PMI process unfairly by barely qualifying or by manipulating the system, then they take expensive crash courses, and they pass with a high score- BUT they have never truly managed a highly stressful and big-money project! They sometimes even ignore the holy trinity of constraints- resources, time, and project scope!

Does this mean a cert like the PMP is junk? Absolutely not. But it needs to be taken cautiously, and not frivolously. Interview these PMP's with your own proven top-level PMPs and see if they really can pull their weight. If you have a PMP with 20 years experience, vs one with 8-10...well, you can do the math.

Do your homework ahead of time, and make sure the PMP you're hiring is proven- and not just from the references THEY give you! Putting an PMP in charge of a project that could jeapordize the project is very dangerous, and you sadly don't realize this until the project is hitting it's critical points!

SQL Developer and the TNS Connection Type Selection

2008-03-28T18:33:00.002-05:00

I will be exploring this much further soon, but suffice it to say, sometimes when you install SQL Developer or other Oracle tools, they look at the most recent Oracle home from a new Product install. For example, I installed OWB, and it had a new home. This actually overwrote some of the paths in the Operating System, and defaulted that home as the 'main' home.

Well a tool like SQL Developer loads a nice TNSNAMES.ora listing of all your database connections and it searches through that default home. If it looks at a new home that doesn't have those listings, you drop down will have no available connections!

A VERY easy way to fix this is to launch the universal installer, look at all installed products, and while looking at the oracle homes on your box, you can use the up and down arrows on the right panel to 'rank' or put a certain oracle home as the 'main' or default home. Doing this, I then put my original database home as the default. I restarted SQL Developer, and bingo! It then read the right oracle home and the right TNSNAMES.ora entry and populated the drop down.

Not bad!

Oracle Warehouse Builder OWB 10gR2 Tutorial for you!

2008-03-26T10:59:00.008-05:00

Here's a set of slides with some basics of OWB for beginners to look over. I know Oracle has some Oracle By Example tutorials that are 'OK' at best. This is another look at the tool for you to learn from. Hopefully these will fill in some gaps as the documentation is not so great, there are no OWB books, and there is not as much useful information about this product compared to other Oracle products.

What the Oracle Database can do for your Data Warehouse

2008-03-25T16:45:00.005-05:00

There are many MANY times I write a particularly difficult query or PL/SQL package and I realize that I am re-inventing the wheel. Reinventing the wheel = BAD. (unless there is something nifty in there that hasn't been done before).

I wanted to make a quick list of links to various tutorials, articles, and direct bits of Oracle docs that will get you to understand and learn some of the database provided tools that can make your SQL writing and Data Warehouse tuning and building much easier. Some of these tools are not always easy to use the first time, but they will be easier to learn than forcing yourself to build them (or rebuild the wheel).

I'll post the 10g features for now. I'll try to get back and post the 11g features and how they work as well. Suffice to say, the 10g features are in 11g and some are enhanced. These can really save you some time. Usually the hard concepts and difficult things you seek to do are already here.
For example, how many times have you had to do some pretty tough hierarchical processing (parent-child relationships) but never used the SQL connect-by query clauses?? These parent child relationships are extremely common in a Data Warehouse.

SQL Model Calculations
The MODEL clause enables you to specify complex formulas while avoiding multiple joins and UNION clauses. This clause supports OLAP queries such as share of ancestor and prior period comparisons, as well as calculations typically done in large spreadsheets. The MODEL clause provides building blocks for budgeting, forecasting, and statistical applications.
link

Materialized View Refresh Enhancements
Materialized view fast refresh involving multiple tables, whether partitioned or non-partitioned, no longer requires that a materialized view log be present. The second link I provide here is particularly useful because it has screen shots and walk throughs to help you with MVs. Getting MVs to work well with fast refresh can take a little patience with all of their prerequisites!
link
link2

Query Rewrite Enhancements
Query rewrite performance has been improved because query rewrite is now able to use multiple materialized views to rewrite a query.
link

Partitioning Enhancements
You can now use partitioning with index-organized tables. Also, materialized views in OLAP are able to use partitioning. You can now use hash-partitioned global indexes. I also wrote a nice article in the IOUG BIWA (business intelligence, warehousing, and analytics) SIG's newsletter on the 11g database enhancements to partitioning, if you want to dig up their publications!
link

Change Data Capture
Oracle now supports asynchronous change data capture as well as synchronous change data capture.
link

ETL Enhancements
Oracle's extraction, transformation, and loading capabilities have been improved with several MERGE improvements and better external table capabilities.
link

Should I buy an iPhone...once again-

2008-03-06T14:38:00.002-06:00

I merely wanted to add this link. I have thought for a long time that this device lacked some enterprise level authority, and it looks like they will address that with the next release.

Check it out at this link to read the summit and SDK dicsussions...link

Peronally, this goes a long way for me...using VPN, exchange sync'ing, that is some decent thought there!

I might get one (when the price is under $500!)

Should I buy an iPhone?!

2008-02-22T06:52:00.002-06:00

I have seen more and more iPhones make their way into offices at my clients. I don't trust online surveys and online critiques as much as I trust live people using one. So, I decided to start asking every person I know and that I meet, "How do you like it?"

I asked them, " If you chould change anything about it, what would it be?"
"Any problems?"
"Have you had a better phone?"
"Reception quality?"

I ran the full gauntlet of questions and even got to play with their phones.

Surprisingly, the amount of complaints was VERY minimal. Price being the only downer thus far, and a few people complaining about the EDGE network and how it goes down, and how once in a while a call is dropped when travelling between 2 connection points. That was about it.

All in all, very few negatives and the positives they mentioned FAR surpassed the bad.

Now I have to take the question more seriously, "Will I get one?!"

Oracle Warehouse Builder OWB 11g New Features Part 6!

2008-02-20T14:08:00.011-06:00

This is a continuation from part 5. In part 6, we will discuss a bit more about the installation of OWB 11g, and the install options. Again, thanks to Oracle for providing some of this information to make my posting easier!

Server Install Provides Full Control Center Functionality

The Oracle home of the Oracle Database 11g installation includes the components needed to perform the following tasks for OWB:

Deployment and execution of all standard objects
(tables, dimensions, cubes, maps, and so on)
Deployment of process flows
Warehouse upgrade
Run-time scripting
Installation of Oracle Workflow

The OWB server installation includes a small set of files needed to get the Control Center running in the database Oracle home. This allows a “fully functioning” OWB install directly with the database install.

The directory structure looks like this:

Oracle Home/owb
Oracle Home/owb/wf

Discoverer deployment is the only task not supported by the OWB server install. For Discoverer deployment, you must perform the stand-alone installation described later.

Stand-Alone..When Do You Need Stand-alone Installation?

The OWB 11g stand-alone installation is required only if you must:

Deploy to an Oracle Database 10g, release 2 target
Perform Discoverer deployment

The stand-alone DVD is actually bundled with the 11g database pack.

For full functionality, OWB 11g stand-alone must be installed on the database server machine (10g R2 or 11g) into a new Oracle home (separate from the database home).

Using OWB 11g with DB 10g R2

OWB 11g is architected on the assumption that it will be used with Oracle Database 11g. This tight integration includes pre-seeding of the OWBSYS schema in the default database and placing OWB in the same Oracle home as the database.

To use OWB 11g with Oracle Database 10g R2, you must perform a few simple steps:

1. Run SQL script to create OWBSYS.

2. Run SQL script to identify OWB home to Oracle Database 10g R2.

3. Unlock OWBSYS.

4. Enable access to workspaces.

1. Run SQL script to create OWBSYS.

When using Oracle Database 10g R2, you must run a SQL script to create the OWBSYS repository schema that is needed by OWB 11g. (OWBSYS is automatically created during Oracle Database 11g installation, so this step is not needed when using OWB 11g with Database 11g.)

The script /OWB/UnifiedRepos/cat_owb.sql installs the OWBSYS database user that is otherwise automatically created when installing Database 11g.

Note: The method by which you invoke SQL*Plus is important. Do not invoke SQL*Plus from the Start > Programs > Oracle database home folder or the Warehouse Builder folder. Instead, invoke SQL*Plus from Start > Run > cmd.exe. Type the Path command and press Enter.
Verify that Warehouse Builder is near the front of the path statement so that the SQL*Plus session invokes from the Warehouse Builder installation. If not, you may need to temporarily set the path (such as Path=\bin). Then invoke SQL*Plus as SYS with SYSDBA privileges. For example, at the command line you can type the following:
sqlplus sys/ as sysdba

Connect to SQL*Plus as SYSDBA and issue the following command to create OWBSYS:

@/owb/UnifiedRepos/cat_owb.sql;

Press Enter. You are prompted for a tablespace for the OWBSYS user. Type a table such as users and press Enter.

When this command finishes successfully, you are prompted: “If you are NOT using an OWB installed in the Oracle database home, please now run reset_owbcc_home”.

2. Run SQL script to identify OWB home to Oracle Database 10g R2.

Earlier, you were instructed to install Warehouse Builder in an Oracle home other than the database Oracle home if you are using OWB 11g with Oracle Database 10g R2. To ensure access to the Control Center on the Oracle Database 10g R2 database, run the reset_owbcc_home.sql script and pass in your Oracle home for Warehouse Builder.
Run the script as a system privileged user such as SYS or SYSTEM. For example, type the following command at the command prompt:

SQL> @c:\oracle\OWB_home_11g\owb\UnifiedRepos\reset_owbcc_home;

Press Enter. You are prompted for the full path of the Oracle home for the OWB Control Center install. First read the following note.

Note: In the following example, observe the required use of the forward slashes (/) even when using a Windows machine. Note also that your response with the full path must be case sensitive. Be sure to enter the drive letter in uppercase (such as C:) and follow the case sensitivity of the folder names in the path of the Oracle home for OWB. Your response should be similar to the following example:

C:/oracle/OWB_home_11g

Press Enter.

Important note: If you specify the path to the OWB Oracle home incorrectly (using backward slashes or incorrect case sensitivity), the reset_owbcc_home.sql script finishes without apparent errors. However, when you later use the Repository Assistant to create a workspace and users, the process fails.

3. Unlock OWBSYS.

After creating OWBSYS, you must unlock the OWBSYS account and reinstate its password if the strong password option is enabled on the database. Proceed with the SQL*Plus commands to unlock the OWBSYS account and assign a password (also named OWBSYS):

alter user OWBSYS account unlock;

alter user OWBSYS identified by OWBSYS;

The requirements explained above are also mentioned in the OWB 11g Installation Guide documentation, in the section titled “Hosting the Repository on Oracle Database 10g Release 2.”

For each Warehouse Builder client installation, enable access to the workspaces hosted on your Oracle Database 10g R2 database.

4. Enable Access to Workspaces

By default, Warehouse Builder 11g, release 1 clients are set to connect to workspaces hosted on an Oracle 11g database. That is, the Warehouse Builder repository is assumed to be hosted on an Oracle 11g database.

To enable access to an Oracle Database 10g R2 repository and its workspaces, alter the file /owb/bin/admin/Preference.properties. If the file does not exist, you can create it based on the example file available in the same directory, Preference.properties.tmp.

In the preference properties file, add the REPOS_DB_VERSION_ALLOWED property and set its value to Oracle 10g, Oracle 11g. (This property may already be present.)

And that's all for part 6. Hopefully, this allows users to get OWB 11g installed in their preferred format! Thanks again to Oracle for providing some of the info contained above. If you have any questions, feel free to drop me a line. Part 7 will cover Repository Assistant Workspace Changes, and how logging in, managing users and targets, and other features have changed!

Quick Overview Diagram of BI Publisher Data Flow

2008-02-18T08:37:00.001-06:00

What is BAM and what does it have to do with Oracle?

2008-02-06T22:56:00.001-06:00

I've heard the term tossed around quite a bit. BAM. I've heard it used in many different ways, some wrong, some VERY wrong, and a few right. So, with an audience reading, here is what I have gathered, read, researched, and know about BAM and what Oracle is doing with it:

“BAM defines the concept of providing real-time access to critical business performance indicators to improve the speed and effectiveness of business operations.”
-The Gartner Group

OK, so going off of that 'authoritative' quote, let us break this down.

•BAM is largely born-of, and is reshaping, two previously distinct technology markets:

–Analytics and decision making of Business intelligence market

–Real-time and business process linkage of Enterprise Application Integration (EAI)

•BAM focuses on what is happening, not what has, or might happen
•BAM sits in-between the historical and analytical focus of BI and the forward business planning of Corporate Performance Management

BAM applications monitor day-to-day business processes such as customer orders, insurance claims and supply chain operations. BAM products are typically driven by process models. This is very different from data-driven ETL applications, which have little or no knowledge of business processes. (Aside: it is worth mentioning that strategic and tactical performance management is also likely to become business process driven in the future.)

As a BAM server tracks operational events, it maintains these events in a cache that is used by a reporting and analysis engine running under the control of the BAM server. The analysis engine can access existing business intelligence and data warehouse information to put the operational events being tracked into a business context and produces scorecards of operational business performance. The BAM environment, however, also provide the ability to do more detailed analysis and mining of information in the analytics engine.

The key benefit of a BAM environment is that operational processes can be monitored and exceptions acted on in close to real time.

Another recent trend in BI analysis tools has been to add a performance management capability that enables business users to compare the analytics produced during BI processing to actual business goals and forecasts (i.e., it puts BI into a business context). Performance management products extend the use of BI from measuring business performance to managing it. This is termed as Business Analytics. The actionable intelligence produced by these products is presented in the form of drillable scorecards that employ formal or informal methodologies to document business goals and initiatives. Some performance management products also provide rules-driven facilities to send alerts to business users when thresholds defined by the user are, for example, not achieved. Alerts reduce the amount of time business users spend in accessing and analyzing data, and reduce the reaction time required to identify and fix business problems.

BI and Business Analytics tools enable business users to react to business situations after they occur. Their predictive analytics tools component adds techniques such as data mining and forecasting to a business intelligence framework. These techniques help users to become more proactive in managing the business. In some cases, predictive tools are used to provide the business context (e.g., rules, forecasts) for the scorecards and alerts used by performance management tools.

üBAM adds real-time information to Business Intelligence
ü
üBusiness Intelligence adds information context to BAM

Both BI and BAM address the needs of monitoring business processes to enhance operations efficiency. The key differentiator relates to the how quickly the business needs to react to an event or process exception. Business problems that require near real-time information access and analytics can be best solved using BAM.

BAM furthers the BI cause by:

1. Embedding key analytics (computed in real-time) in day-to-day business processes

2. Correlating heterogeneous events and patterns to compute causalities, aggregates and thresholds, based on end-user preferences

3. Delivers the analyzed information and alerts in real-time to the users when and where the information matters

4. Providing a platform for structured and collaborative problem resolution

Using a loan as an example, BAM can send an alert to indicate a number of loans have been made in a specific area. This is very useful information, but becomes more useful when used with BI to see what the impact of these loans have on an organization's exposure in different markets. BI can put the results of BAM into a broader context and make the decision process a more informed one.

It may be that as an organization, analysis results show exposure is becoming too skewed toward a specific market. As a result of this analysis, the BAM thresholds for sending alerts can be adjusted. You make the rules, you set the thresholds, you learn real-time when these thresholds are at or near being hit.

Who uses BAM?? (click any picture to enlarge)

There will be a part 2 of this article shortly which will explore the technical and business challenges of implementing BAM. Stay tuned...

Oracle Warehouse Builder OWB 11g New Features Part 5!

2008-02-05T18:12:00.004-06:00

This is a continuation from part 4. In part 5, we will discuss a bit about the installation of OWB 11g, and how it is easier and differs from the previous counterparts. Again, thanks to Oracle for providing some of this information to make my posting easier!

Overall, the installation of OWB 11g is greatly simplified in an Oracle Database 11g environment. When you install the core Oracle Database 11g, you get the OWB back end: a pre-seeded repository schema and workflow.

Without the requirement of SYSDBA privileges (as we previously mentioned in other postings), you can make a sandbox repository and start exploring the OWB tool immediately! This was not necessarily so in the 10g versions!

Three types of OWB 11g installation possibilities can be:

Server installation as part of the Oracle Database 11g installation
Stand-alone installation using the stand-alone DVD bundled with the 11g database pack
Stand-alone installation for Oracle Database 10g R2 using special scripts
Note: Installing to Oracle Database 10g R2 requires running two special SQL scripts and a few extra steps

OWBSYS Is Part of Database 11g Installation

As part of the database installation scripting, the OWBSYS schema is seeded as part of these starter databases:

General Purpose
Transaction Processing
Data Warehouse

During installation, all of the necessary system privileges, object privileges, and roles are granted to the OWBSYS schema. The script that creates OWBSYS is located at: ../owb/UnifiedRepos/cat_owb.sql

Why?

So after database installation, OWB is ready to use. Because the necessary privileges and roles are granted, no DBA or SYSDBA credentials are required when creating workspaces.

Note: The schema does not hold a complete repository/workspace in its pre-seeded form. You must still define your workspaces, workspace owners, and users. Also, OWBSYS is locked by default. As will be explained later, you must unlock it if you are using OWB 11g with Oracle Database 10g R2.

OWB as a Custom Database Option

For custom databases, OWB is an option in the Database Configuration Assistant user interface. With the OWB option selected, these database options are enabled:

Oracle OLAP
Oracle JVM
Oracle XML DB
Oracle Intermedia
Oracle Enterprise Manager Repository

Now that we have a brief overview of the install options, next time we will look more in depth at the OWB 11g server install, the OWB 11g standalone install, and if the post doesn't get TOO long, we'll also discuss using OWB 11g on a 10gR2 Oracle Database and all the gotchas that come with that! I know that will be heavily requested just simply because some people want to use OWB 11g but don't want to upgrade their database just yet from 10gR2 to 11g! Until next time....

Lesson learned: New England Patriots and your fans...

2008-02-04T14:30:00.001-06:00

cheaters, you got what you deserved. :)

18-0 with 1 GIANT mistake. :)

Bi Publisher and nested tables and loops

2008-01-14T18:44:00.001-06:00

One thing I have noticed while developing RTF templates is that it always seems to be in your favor to try to use tables to structure your data. Not only this, but using or not using tables, depending on your XSL logic, can actually greatly change the output of your BI publisher report.

The following post will illustrate a few things I went through while developing a report that had a strict heirarchy. Now, this report is based off of an Oracle Apps Bill of Lading, and combined a bit with a Sales Order Acknowledgement document, so it might not look familiar. The point is, there are a few steps in understanding how to get your report looking right.

1. Understand what data needs to be on the report and HOW it needs to look and be structured.
2. Become familiar with the data and how it is structured hierarchically or if at all.
3. Become intimately familiar with your query or tool (like Oracle Reports) that needs to generate
the XML that your BI Publisher template will merge with.
4. Review your XML that is merging with the template so you truly understand what data is at what
level in the hierarchy, and where it might repeat as a group.
5. Learn to re-structure your report or application that serves the XML so it fits your needs.
6. Finally, learn to use BI Publisher's templating ability (and tables) to produce the output in the right
format.

That being said, for our example we will look at a heirarchy as follows:
LPN (license plate number) is the master node or top-most level.
- Item Number and Item Description are just underneath LPN, and there might be multiple
entries PER LPN.
- Quantity, Lot Number, Heat Number, and Mechanical Number are all attributes of an item
number, and thus they can all appear multiple times PER item number.

The XML data might look like:

LPN- GDN0000124
Item Desc- 2.0000.PL.7050.T7451.ALUM.BARE.48.5000.144.5000
Item #- 600296
Heat Number- 398662
Mechanical Number- 339394
Lot Number- 027.500/R014.2506276-A00
Quantity- 77
Unit of Measure- LBS

Note that this is the easiest situation, 1 LPN, 1 'set' of item description and item number, and 1 'set' of the item's attributes underneath. We need to design the BI Publisher template for a repeating possibility though, like:

LPN 1
ITEM 1
ITEM Attributes 1
.
.
ITEM Attributes N
.
.
ITEM N
.
.
.
LPN N

So multiple possible LPNs each with multiple possible items, each with multiple possible attributes. And yes, the people that read this report want it grouped nicely so that each attribute belonging to each item belonging to each LPN is 'grouped' and structured hierarchically. For any experienced reporting developer or any data analysis professional, this isn't too bad. However mastering this in BI Publisher with MS Word as your RTF Template tool, you really should master nested tables. The following is a screenshot of how we can get this to work in a report. It is a simple shot of the most basic situation with only 1 LPN, 1 item and 1 description. note the nested tables in this output:

The next shot is a more complicated output using the SAME template, this one having multiple items and descriptions for 1 LPN:

Again, notice the structuring of the tables I purposely left so you can see how this is built from top to bottom. You need to nest your tables hierarchically to get this structure to work.
Finally, the template and the XSL and code:

A few things to note in this quick template, we are indeed using barcodes as you might have noticed. Our initial for-each tag (in aqua) is outside the main table and will repeat basically for every single occurence of an LPN. Then we have a table nested inside the LPN table. This next FE (for-each) is shown in the 2nd nested table and it will repeat for each occurrence of an item number and item description pair. Finally, we nested a 3rd table inside the item's table, and it holds the item's attributes. The 3rd and final FE (for-each) will repeat for each Quantity, Lot, Heat, and Mechanical ID attributes it finds for the current item. You'll then see the EFE (end for-each) that stops the repeating for each of the 'FE' sections.

We also have a XSL tag that says split-by-page-break:
This is a tag that needs to be experimented with and placed JUST BEFORE the right EFE tag to see what it does. If done right, it will page break at the end of each of the elements for the EFE it proceeds. In this case, the tag is just before the last EFE which is related to the initial FE which if for the repeating LPN. So it will page break for each LPN we encounter...and it does. It keeps all items and attributes together but only makes a new page when the LPN changes.

Hope this helps!

Oracle Warehouse Builder OWB 11g New Features Part 4!

2008-01-14T17:38:00.002-06:00

This is a continuation from part 3. We will continue to explore some new features this time of OWB 11g. Last time we touched on the Siebel Connector and some general connectivity options.

First, you should know that there are a few new features in OWB 11g that were rolled out in the latest OWB 10g R2 patch version 10.2.0.3. As follows:

Version support for type 2 SCDs.
Set based Delete in mappings code generation.
Availability of a merge optimization property for table operators.
DML error logging to handle error tables.

So what are these and how are they 'new'?

Versioning of Type 2 SCDs (slowly changing dimensions)
Hierarchy versioning supports multiple hierarchies. When the non-leaf level (think of a tree or maybe a bush...the leaves are the furthest from the root or middle) of a dimension contains versioned attributes, the versioning of this non-leaf level results in the versioning of its corresponding child records if they have effective date and expiration date attributes and if you have enabled hierarchy versioning. In essence, what can happen to 'Dad' will happen to his kids too.

Hierarchy versioning is not enabled by default for type 2 SCDs (slowly changing dimensions). When you create a type 2 SCD by using the Create Dimension Wizard, hierarchy versioning is disabled. You must use the Data Object Editor to enable hierarchy versioning.

So what is a Type 2 Slowly Changing Dimension?

In Type 2 Slowly Changing Dimensions, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.

For example, say we have the following table:

Customer Key	Name	State
1001	Christina	Illinois

After Christina moved from Illinois to California, we add the new information as a new row into the table:

Customer Key	Name	State
1001	Christina	Illinois
1005	Christina	California

Advantages:

- This allows us to accurately keep all historical information.

Disadvantages:

This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.
This necessarily complicates the ETL process.

Usage:

About 50% of the time.

When to use Type 2:

Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Set-based DELETE in Mapping Code Generation
Beginning in Warehouse Builder 10.2.0.3, you can design a mapping with the loading type set to DELETE and code generation set to SET BASED.

Merge Optimization for Table Operators
Beginning in Warehouse Builder 10.2.0.3, you can enable the Merge Optimization property for table operators. When set to true, this property optimizes the invocation or execution of expressions and transformations in the MERGE statement. There is QUITE a bit of information and tips and tricks out there on using the merge operator to the full extent.

DML Error Logging to Handle Error Tables
Prior to release 10.2.0.3, OWB already generated error-handling code for row-based mode.
Beginning in Warehouse Builder 10.2.0.3, DML error logging is available for set-based maps.

Thats is all for now, soon we'll move on to more new features and how OWB11g is easier to install and maintain.

Oracle Warehouse Builder OWB 11g New Features Part 3!

2008-01-04T11:30:00.002-06:00

This is a continuation from part2. Here is part 3 of the new features and enhancements to Oracle Warehouse Builder - OWB 11g. It is in these posts that you can make the educated decision whether or not to pursue OWB 11g, or stay with OWB 10gR2. It is also here you can compare and contrast the differences between the 2 releases. Once again, props to Oracle for providing some of the info!

The new OWB 11g connectivity options....

Now there is database support for database 11g to be both the source and target.

Oracle Database 11g is supported as both an OWB source and an OWB target. Oracle Database 11g support is closely linked to the simplified install of OWB 11g as part of the Oracle Database 11g install.

Now there is also Gateway 11g support

The ability of OWB to connect transparently to non-Oracle systems continues with OWB 11g support for Gateway 11g.

Also, the new optional Siebel Connector fo Siebel Analytics

OWB Connector to Siebel Analytics...
The Warehouse Builder Connector to Siebel enables you to connect to—and extract data and metadata from—Siebel applications. The connection to the Siebel applications uses database users that have the appropriate privileges set by the DBA. After you import metadata from Siebel applications, you can use Siebel objects in mappings, create process flows containing Siebel objects, and generate SQL code.

You can use this connector with non-Oracle databases after you establish a connection to those databases. The table above lists the functionality available in the Warehouse Builder Connector to Siebel.

Next time in part 4 we will discuss more new features and enhancements!

Microsoft Internet Explorer SUCKS!

2008-01-04T00:31:00.000-06:00

I don't care what version you're using of IE. I hate every version now. I just wanted to let anyone reading my blog that you can view it the way it was meant to be seen with Mozilla Firefox.

If you use IE, or maybe any other browser than FireFox (Opera, Safari....haven't tested them) you might not see my blog the way it was meant to be rendered. Even my girlfriend religiously uses Firefox and it is NOT because I tell her to! And she isn't a engineering or technical person!

BI Publisher / XML Publisher CAN produce XML as an output format

2008-01-03T18:41:00.000-06:00

In the above diagram, we see quite a few formats on the right that are available as output formats. PDF, RTF (mostly Microsoft Word), HTML, EDI (Electronic Data Interchange), EFT (Electronic Funds Transfer), and Microsoft Excel. I have implemented every output format thus far except XML. I figured, why implement BI Publisher to output XML if I am already getting XML for BI Publisher to start working with in the first place? Well sometimes you need to add a little or take a little away from the XML that BI Publisher is fed to start the whole XML output creation process.

You CAN have BI Publisher create XML as an output however not using a template that you may have used before, such as an RTF or PDF template. To transform incoming XML to another form of an output XML, you need to create an XSL stylesheet

From that point, you can upload it to the Apps XML Publisher Template Manager (as I have discussed in some other BI Publisher posts here) and it has to be registered as an XML-XSL template, not an RTF or other format. The concurrent manager will then be able to process this like any other BI Publisher template and give you the transformed format.

If you were wondering, we decided to go with BI Publisher and outputting the XML with BIP instead of Oracle Reports because our client is adopting BIP not only inside Apps, but as the standalone Business Intelligence reporting tool of choice, and they need bar codes and other nice things to be able to print out on Zebra Printers over their network. Zebra printers have a proprietary format and XML is something that will work for them.

Oracle Warehouse Builder OWB 11g New Features Part 2!

2007-12-28T12:01:00.002-06:00

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!

2007-12-23T13:59:00.004-06:00

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

2007-12-23T00:42:00.000-06:00

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

2007-12-23T00:23:00.000-06:00

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

2007-12-17T19:27:00.001-06:00

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

2007-12-16T11:16:00.001-06:00

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

2007-12-06T14:16:00.001-06:00

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

2007-11-28T12:06:00.000-06:00

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!

2007-11-21T00:17:00.000-06:00

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!

2007-11-19T16:03:00.000-06:00

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?

2007-11-19T13:24:00.000-06:00

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.

2007-11-18T16:39:00.002-06:00

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

2007-11-18T16:18:00.001-06:00

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

2007-11-16T14:30:00.000-06:00

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

2007-11-16T11:24:00.001-06:00

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

2007-11-13T22:55:00.000-06:00

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

2007-11-08T21:59:00.000-06:00

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

2007-10-27T17:22:00.001-05:00

(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.

2007-09-29T11:57:00.000-05:00

"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

2007-09-29T11:48:00.000-05:00

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!

2007-09-26T23:36:00.001-05:00

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

2007-09-26T20:26:00.000-05:00

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

2007-09-16T14:42:00.000-05:00

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

2007-09-16T11:05:00.000-05:00

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

2007-09-16T01:47:00.003-05:00

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!