OBIEE 10g: Replace Column or Table in LTS

Even though I'm working hard on learning 11g, I still find little things in 10g that help.

Case in point, I was going through and trying to clean up the physical and BMM layers. Our physical model will now be shared between multiple projects. So how best to do that?

Using aliases along with naming conventions and display folders can help. By doing this, I will be able to "unhook" the source table from any and all joins. The goal is to keep these free...the aliases will be used to join to their respective tables (aliases).

The naming will be something like:

Dim_<source_table_name>_<subject_area>
Fact_<source_table_name>_<subject_area>
Outrigger_<source_table_name>_<subject_area>
Bridge_<source_table_name>_<subject_area>

To further organize them, I will put them in a Display Folder.



I'm just going to create one called "New."

I'm using the SH schema here, so it should be easy to follow along.

Next up, I'm going to create aliases for all my tables. For the sake of brevity and my sanity, I'm only going to use SALES, PRODUCTS, and TIMES.



When I initially started to clean things up, I would screenshot the Column Mappings section of the Logical Table Source. Fun right? There has to be a tool for this. Sure enough...

OK, this post has been sitting here all day...I'm cutting to the chase now.

So I've hooked up (joined) all my new tables. Now I just want to replace them. I could do it by hand, but what fun would that be. Here's the source for SALES.



If you go to Tools -> Utilities, you'll see this screen and the fun little tool we'll use for this exercise:



Now make sure you select "Replace whole table." On the left side, select the table you want to replace, on the right side, select the table you will replace it with.



Hit Next.



Blah blah blah, which translates into the column mappings. Since we are replacing the source table with a copy of itself, this part is easy.

Here you get a nice summary of what the tool will actually be doing.



Hit Next and voila!



Now, what does this buy you?

Since you are now sharing your physical layer with multiple Subject Areas, you've added a layer of abstraction to protect yourself. Let's say a new column was added to SALES. After you've updated your physical layer, you are now free to either keep the others the same or add it to that particular Subject Area. For my (mythical/theoretical) example, I just need that new column in one Subject Area, so I'll just need to pull that into the BMM and I'm done.

An even better example would involve joins. Maybe Subject Area 1 is based on one particular set of joins and the others are not. This should allow you to easily handle this kind of scenario.

I do apologize for this post breaking up as it did. I had the best intentions in the world this afternoon. Hopefully I got the gist of the tool across, if not, yell at me and I'll update it later.

OBIEE: obieerpdmigrateutil.exe

I needed to quickly spin up an OBIEE 11g instance (Windows Server 2003, 32 bit) for a PoC. The install went smoothly (unlike my other attempts on a 64 bit Windows environment). With the database and the application server, the machine is running at about 2.5 GB of RAM, 4 is recommended to simply install (including 11 GB of free space).

Anyway, after migrating my Discoverer EUL over to OBIEE, and figuring out the new file structure, I opened up the RPD and received the following goodness:



Yes, I should really read the documentation. I have been, apparently just haven't gotten to that part yet. (Admittedly, I've been reading from the 50,000 foot level).

In the bin directory (which isn't where it used to be) is a utility called obieerpdmigrateutil.exe

To run it issue the following from the command line:

<ORACLE_HOME>\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1>obieerpdmigrateutil.exe 
  -i <ORACLE_HOME>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository\obiee_10g.rpd 
  -u Administrator 
  -L obiee_10g_diff 
  -O <ORACLE_HOME>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository\obiee_11g.rpd

You'll be prompted for the Administrator password (which I left blank) and then an encryption password which becomes your new Administrator password.

Please enter the 10g Admin password:

Please enter the encryption password
New_Admin_Passw0rd

Parameters for the utility are as follows (and you must use them all)

obieerpdmigrateutil  -I repository_path
                     -O new_rpd_path
                     -L LDIF_file_path
                     [-U 10g_admin_username]
                     [-8]
  -O         Generate output repository.
  -I         The path of a repository.
  -L         Generate LDIF file for users and groups in the repository.
  -U         Name of a user with Administrator privilege in the 10g RPD
  -8         Use UTF-8 encoding for LDIF file.
  -h         Display this usage information and exit.

More on this tool and when it should be used (MUD) here.

Discoverer to OBIEE Migration Utility

I couldn't find a whole lot of information on this utility, so of course I'm putting it up here for my own reference.

File name is called migrateEUL.exe and is located in the OracleBI\server\bin directory.

It's not very difficult to run, just type in:

c:\migrateeul file.eex

Easy.

To generate the eex file from Discoverer, open up the Discoverer Administrator tool and then connect to the eul you want to export.

Cancel out of the Load Wizard



Then go to File, Export and select the business areas you want to export.

You'll be prompted to name that file and then hit finish.

Running the utility will produce something like the following:

C:\Documents and Settings\chet\Desktop>migrateeul pdi_eul.eex

        Oracle BI SE - EE Migration Assistant    Version 10.1.3.4.0


Reading Configuration File...

Error reading configuration !!! Reverting to defaults...[DONE]

Parsing EUL export file C:\Documents and Settings\chet\Desktop\eul.eex...[DONE]

Repository creation started...

Processing Business Area : Business Area 1....[DONE]

Processing Business Area : Business Area 2....[DONE]

Processing Business Area : Business Area 3....[DONE]

Processing Business Area : Business Area 4....[DONE]

Processing Business Area : Business Area 5....[DONE]

Processing Business Area : Business Area 6....[DONE]

All Business Area(s) processed

        The migrated repository is saved at C:\Documents and Settings\chet\Desktop\eul.rpd

        Migration log is saved at C:\Documents and Settings\chet\Desktop\eul.migration.log

                ------------------------------------------
                        EUL MIGRATION SUCCESSFUL
                ------------------------------------------

Now on to the fun part, using that newly created RPD and making everything pretty.

wordle

Cool stuff.

My del.ico.us cloud.



Do you think I need a job? ;)

EDI Fun

or Electronic Data Interchange, just a fancy phrase for sending and receiving files. We (IT) do love to complicate things don't we?

I've put the change data capture stuff on hold as my never-ending project goes into it's 12th week past deadline. It's at nine 9s: 99.9999999. It's finance related stuff and nothing less than 100% is acceptable. I'm tired.

Part of my project was to move from an already built in house table to the raw (files) tables. My feisty colleague took on that fun challenge for 6 months or so. He's heading up a new project though so he's had to pass the baton on to me. I've accepted it...reluctantly. ;)

Anyway, we store these inbound files all over the place (seemingly to me). I started writing a little Java application that would scan a directory, read these x12-820 files and tell me the interchange date, control number, total amount and some other useful information.

I plan on either putting this in the database and wrapping it up in PL/SQL or creating a service (Java Service Wrapper) and pushing this useful data to a table. So if you have to deal with the wonderful x12/820 formats, you may want to check back soon for the code. I can't promise it will be good, but it will work!

Instrumentation: DEBUG/LOGGING

In my previous entry on instrumenting code I detailed the way in which you could use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to help monitor your long running code.

In this one, I will detail my home grown version of Tom Kyte's debug routine. I do know that others have similar code but can't seem to find them right now.

You can find the source code here.

Contents:
2 tables
2 sequences
1 package
1 procedure
1 build file

debug_tab allows you to turn the debugging on and off. debug_details_tab will store each line that you write to the debug routine when turned on.

Here's an example of it in practice:

CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
  l_age_in_months INTEGER;
BEGIN
  --instrumentation calls
  debug( 'GET_AGE_IN_MONTHS' );
  debug( 'P_ID:  ' || p_id );
  debug( 'select value into variable' );

  SELECT age_in_months
  INTO l_age_in_months
  FROM people_tab
  WHERE id = p_id;

  debug( 'L_AGE_IN_MONTHS:  ' || l_age_in_months );

  RETURN l_age_in_months;

EXCEPTION
  WHEN no_data_found THEN
    debug( 'no data found' );
    RETURN NULL;
END get_age_in_months;
/
show errors

I mentioned in the previous article that I had had difficulty grasping this concept initially. I think once I related it to DBMS_OUTPUT.PUT_LINE it became much more clear to me.

This simple debug routine has helped me tremendously in the last year or two that I have used it. Especially when you get nested levels of logic. It gets very hard to keep track of where you are, but with this you can run your procedure or function and then issue a SELECT against the debug_details_tab and see what was happening when.

I even began using this in my APEX applications. I would preface each line with "APEX: " and then write whatever was necessary so that I could step through the various pieces of code. It became crucial when I was doing validation on a table of records in a collection...oh so much easier.

On large systems this will generate a lot of data. I definitely would not put this inside a loop doing some sort of batch processing, but it is helpful to find where things fail out.

It can certainly be improved on. I basically took the idea of DBMS_OUTPUT.PUT_LINE and wrote it to a table, nothing fancy there. Mr. Kyte mentions writing it to the file system as well. Since I don't have ready access to the database file system, this was the easiest.

Make your life easier and use a debug/logging routine in your code. No longer will you have to comment, the debug statements should do it for you!

Oracle Tools I've Used

Tools:
SQL*Plus
APEX - Application Express (formerly HTMLDB)
JDeveloper
SQL Developer
Reports Builder
OC4J
Discoverer
Oracle Server 8i, 9i, 10g (Windows)
Oracle Application Server 9i, 10g (Windows)
rman
oradim
lsnrctl
tkprof

Coming Soon:
Oracle Warehouse Builder

Database Features:
Java
Object Types
Workflow
Advanced Queueing (in conjunction with Workflow)
Heterogenous Services

Features I'd like to use:
Change Data Capture
XML
Spatial
interMedia
Regular Expressions

Database Supplied Packages:
DBMS_OUTPUT
DBMS_LOB
UTL_FILE
DBMS_APPLICATION_INFO
DBMS_CRYPTO
DBMS_UTILITY
DBMS_METADATA
DBMS_EPG
DBMS_HS_PASSTHROUGH
DBMS_JAVA
DBMS_JOB
DBMS_LOCK
DBMS_MVIEW
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM
DBMS_SESSION
DBMS_SQL
DBMS_STATS
DBMS_XDB
DBMS_XPLAN
HTMLDB_APPLICATION
HTMLDB_ITEM
HTMLDB_UTIL
HTP
OWA_COOKIE
UTL_MAIL
UTL_RAW
UTL_SMTP
WPG_DOCLOAD

Packages I'd like learn to use (10g):
UTL_DBWS
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_DATA_MINING
DBMS_DATAPUMP
DBMS_SCHEDULER
DBMS_OLAP
DBMS_PIPE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_MESSAGING