Oracle Tip: Migrate OBIEE 11g Dashboards, Answers and Prompts

You can easily move OBIEE Server Presentation objects (Dashboards, Answers and Prompts) from one environment to another (ej. from Development to Production) using OBIEE Catalog Manager.
The Catalog Manager appears in the OBIEE Client Menu. When you open it, you have to open the catalog by clicking on File -> Open Catalog.

 

You can choose if you want to open the Catalog online or off-line. 

Once you have logged in, you can see Catalog objects in a Folder structure. Locate the folders you want to export and select Archive from the File Menu. 

You have to specify the name and the path for the exported file.

Once you have exported the file you need, you have to load it in the new Catalog.
Close the Catalog and open the Catalog you want to migrate your objects to. Locate the location you want to import your objects to and select Unarchive. You will be asked to select the import file and click ok to complete the migration.

Oracle Tip: Oracle Warehouse Builder Workflow Installation

To my surprise, Oracle Workflows were not installed in my current Development  environment (OWB Tahoe Development Version 11.2.0.3 and Oracle Database 11.2). I found out when I tried to deploy a new workflow. I was asked to input connection  details and when I tested the connection, I got the following error:
 "The connection was succesful but failed to retrieve workflow version." 

After Googling it, I discovered you have to install the Oracle Workflow Server by executing the wfinstall.bat script. You can find it in <OWB_HOME>\wf\install.

The screenshot shows a Windows installation. You have to input the following parameters:

• Workflow Account: OWF_MGR is the default user
• Workflow Password: Choose a password for the Workflow Manager User
• SYS Password: password for SYS user 
• TNS Connect Descriptor: specify connection details in the format <host>:<port>:<SID>

and then click on Submit. You do not have to create the DB user previously, the process will create it automatically.
At the end of the process, you will receive an encouraging message "Workflow Configuration has completed successfully".

Now, you would probably try to deploy the workflow again. At least, this is what I did and I got this error:

 RPE-02260: Database User OWF_MGR must be a Control Center User. Please use the OWB Design Client against the Control Center repository to  grant the Control Center User role.

So you first need to register the OWF_MGR user as a Workspace user (in Oracle Warehouse Builder -> Administration -> Repository Assistant). Finally, you will be able to create and deploy your workflows.

Oracle Tip: OWB Workspace Installation Error

I am starting a new project and I need to use Oracle Warehouse Builder. I have been given a 64bit Windows machine. A 32 bit version of OWB had already been installed, but when I tried to run it I got the following error:

The application was unable to start correctly (0xc0000018). 
After asking Google and Oracle Forums for explainations, I realized I had to download and install  OWB 11 for Microsoft Windows 64-bit (http://www.oracle.com/technetwork/es/developer-tools/warehouse/downloads/index.html).
I have downloaded it and I installed it. I needed to create a new Workspace, but after a few steps, I got the error:

The Warehouse Builder workspace owner installation failed on user OWBSYS. ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: OWBSYS is not granted access to <OWB_HOME>\repos.properties: Please run UnifiedRepos/reset_owbcc_home.sql

What is that? I have discovered that since the new installation was forced to be on a different PATH from the original, the OWBSYS user did not have access to the .properties file and installation could not go further.
So, I did what suggestes. I have located the set_owbcc_home.sql script and I run it as SYSTEM.

This is the script output:

SQL> @reset_owbcc_home.sql
Enter the full path of the Oracle home for the OWB Control Center install.
If you are installing in a Windows environment, please ensure that the case of t
he path exactly matches the Oracle install path including the drive letter.

OWB Control Center Home:
D:\product\11.2.0\dbhome_2
PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

The script asked me for the Oracle Warehouse Builder path, as shown.

I then retried the Workspace creation and it worked fine.

Oracle Tip: ORA-30657 External Table Error

I was trying to load a CSV file into an external table. I copied the definition of the table from the final target table, adding the external table parameters: 

CREATE TABLE T1 
(field1 VARCHAR2(25) NOT NULL, 
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>'));

When I compiled the table, I got the following error. 

ORA-30657:  Operation not supported on external organized table  

Cause: User attempted on operation on an external table which is not supported.  

Action: Don't do that!

Quite hilarious... but what did I do wrong? The Oracle error message did not help me at all (and who could ever help?). I checked the directory. It was there and its name was typed correctly. File names were ok. So what was the issue? 

It was as easy as removing the NOT NULL clause on the field specification.  

CREATE TABLE T1 
(field1 VARCHAR2(25) NOT NULL, 
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>')

Silly error, but it took me a while to find out what was wrong. Hope this helps. 

Generar Documentación con ODI

Recién se acaba de publicar mi post sobre como generar documentación con ODI, en castellano.

http://blog.avanttic.com/2013/07/02/generar-documentacion-con-oracle-data-integrator/

Enjoy!

Beauty Tip: Naked Skin de Urban Decay

Hace unos meses entré en el Sephora de Plaza Catalunya (unos de mis lugares favoritos aqui en Barcelona) en busca de una base de maquillaje que tuviera un tono rosado. Tengo la piel muy clara y solía utilizar bases de MAC.
Quizás nunca conseguí encontrar mi tono, pero todas me dejaban un aspecto amarillento, a pesar de ser muy buenas.
Nada más entrar, me paró el encargado de Urban Decay que me hizo probar esta base.
Me enamoré en seguida (de la base, no del chico).
Tiene una textura muy liquida y la cobertura es ligera. Así que no lo usaría en el caso en que quisiera cubrir muchas imperfecciones.
Lo que más me gustó es el acabado. El efecto es muy natural. Parece que ni te has maquillado, pero sin embargo dura todo el día y la piel no coge nada de brillo con el paso de las horas.
Está disponible en muchísimas tonalidades, así que cada uno puede encontrar la suya (http://www.urbandecay.com/naked-skin-liquid-makeup-from-urban-decay/334,default,pd.html).
Al ser muy liquida, puede resultar incomoda al aplicar, pero el efecto final compensa la pequeña dificultad.
El único defecto que le puedo encontrar es el aplicador. Cuando se me estaba terminando, resultaba difícil hacer que el producto saliera.
Dentro de un par de meses me voy a EEUU (donde Urban Decay es más barato que en España) y esta base está en primera posición en mi lista de compras.

Tip: Generating Documentation in ODI

The most annoying part of my job is generating documentation. I hate when you finish a project and you have to go through all your code and interfaces to describe what they do. This is mainly for two reasons. First, I am not so well organized that I write down every little change when I apply it. Mea culpa. Second, because most of the time I am in a hurry and the most important thing is showing results instead of documenting what you are doing.
When you develop a considerable amount of interfaces, involving a considerable number of tables, columns and transformations, the last thing you want to do is re-open them to describe their behaviour.
Luckily, ODI provides a feature for generating PFD documents of your job. To use this functionality, you first have to configure it.
In ODI->User Parameters, you have to edit the following entries:

•  Default PDF generation directory: this is the complete path to the folder you want to save your documentation to. E.g. C:\TEMP\
• PDF Viewer: Full path to your PDF viewer. E.g. C:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe

 

ODI allows you to generate documentation for the following objects:

• Knowledge Modules: right click on a project, then Print -> Print Knowledge Modules
• Packages: right click on a project folder, then Print-> Print Packages
• Procedures: right click on a project folder, then Print -> Procedure
  
• Interfaces: right click on a project folder, then Print -> Interfaces
  
• Folders: right click on a project folder, then Print ->Folder

The result is a PDF file with detailed information of the object you printed. The most complete option is the one for printing folders, because you can generate documentation of every object in it.
The thing I like the most is the fact that for every interface you get the physical structure of every source and target table, every join and, most important, each transformation you apply to get a target column.