Oracle Tip: BI Publisher Report URL without Login

My current customer wants to integrate BI Publisher reports in its web portal. They want to publish URLs that allow users to view reports, skipping the login page.
We can retrieve the Report URL by selecting any of Share Report Link options.

 Copy the URL and add the parameters id and passwd for logging into BI Publisher.

http://<server>:<port>/xmlpserver/Components/<Report_Name>.xdo?id=<user_id>&passwd=<user_password>&_xpf=&_xpt=0&_xdo=<Path to the Report>.xdo&_xmode=4&_params<parameters_if_any>&_xt=<Layout_Name>&_xf=analyze&_xana=view

The main disadvantage of this method is that user credentials are not encrypted. This is not the safest method on earth, but it works and it made my customer happy.

Oracle Tip: Oracle Warehouse Builder (OWB) import table error

I have installed the latest version of Oracle Warehouse Builder 11g 64 bit on a Windows pc (my work pc, actually) since I needed to connect to a customer OWB ETL implementation. 
Everything looked ok (I could connect to the OWB Workspace, navigate through the project, open and deploy mappings, etc.), since I have tried to import new tables in the Oracle Schema. 
As soon as I clicked on Import Database Object, I got the following (and cryptic) error:

SQL Exception 

Persistent Layer Error: SQL Exception

Class name: CacheMediator

Method Name: getSQLResult

Error Message from Persisten Layer: ORA-02085

....

If I clicked on OK, the next step was a grey window with much more known error Java null pointer exception.

I really needed to import the new tables I had created to be able to work, so I started Googling the error, with no success. After a considerable amount of time I got on this note:

https://blogs.oracle.com/warehousebuilder/entry/advanced_properties_of_location_new_for_11gr2

"Note the property "User Global Name", it specifies the unique name of the database, which is composed of the database name and the domain, in the form of database_name.database_domain. If you see the error ORA-02085: database link *** connects to *** during the import of metadata of database objects into OWB. It may mean the database has the system parameter global_names set to true, but you did not select the option "Use Global Name" of the Oracle location, as a result, the generated database link name for the import doesn't include the global database name and hence cannot access the database."

Basically I needed to specify the DB global name in each location that specified a connection to the DB. 
If you do not know the global name of your DB, this SQL query can give you a precious hand. 

SELECT * FROM global_name;

So unregister your location, specify the DB global name option and register the location again.

Oracle Tip: Generating Documentation Reports with Oracle SQL Developer Data Modeler

This is another tip for the lazy programmer. When it comes to documentation, no developer would like to go back extracting the code for every single table in his model.
Oracle SQL Developer Data Modeler has a very useful tool for generating reports.

Selecting Reports from the File Menu, you can choose if you want it generated in:

• RTF
• PDF 
• HTML 

format.

You can create a template (e.g. if you want to select only tables of your models) or selecting only some objects of your model.
When you execute the report, it will be stored in a local route.
This is how a PDF report looks like. Ready to be sent to the customer or do some nice cut&paste from it.

Oracle Tip: Changing ODI 11g Database IP address

Yesterday we had an issue with a customer. Overnight they had changed the IP address of the database where ODI is installed.
To make everything work again, we had to:
1) Change Master Repository IP address
On login, choose the Edit Connection Option and change JDBC connection to the Master Repository. Test it and then login to the Master Repository only.

2) Change Work Repository IP address 
This was the trickiest part of the job because the option for editing the Work Repository connection string is not that easy to find in ODI 11g.
When you locate the Work Repository and you click on it, it will appear all greyed out and it seems there is no way to edit it until you spot a small icon on the upper left side.

This will allow you to edit the JDBC URL.

3) Change any Oracle Database Connection in the Topology 
Remember to change any Oracle Database Connection that refers to the database for which the IP address has changed

4) Change Agent IP address
If any agent has been installed on the server that has undergone the IP change, change Agent host details.

OBIEE Tip: Sort a column based on another column

I was struggling with ordering Months in my Calendar Dimension. I wanted to show month names in my graphs and prompts and I wanted them to appear in temporal order (e.g. Jan-Feb-...-Dec). I could not use the Sort option beause it would sort values in alphabetical order.
Since in my dimension I have an attribute with the month number, it would have been great using it for sorting month names. Luckily OBIEE lets you do so, by selecting the column you need to sort in the Logical Layer and applying the Sort Order based on another column.

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.