DAC email does not work as expected (Notification levels are ignored)

I have set up DAC to send emails to users (As per my previous entry- http://obieetips.blogspot.com/2010/02/dac-email-notification.html ), whenever the data load completes.
However, I have two problems.

1.
The email notifications do not work as expected.
Level 1 sends only failure emails but not success emails.
This is an open bug with oracle and the solution is to use Level 5 for the moment.
Level 5 works as expected- which is to send 'Running' as well as 'Failure'/'Success' emails, everytime an ETL load is started and completed.
This can be quite overwhelming for the users.
A bug already exists for this issue so we are eagerly awaiting a patch.
BUG:9133839 - DAC IGNORES NOTIFICATION LEVELS DEFINED FOR EMAIL RECIPIENTS

2.
The above email notifications are a bit too verbose for users who have limited time to check on the ETL status. Due to this I asked Oracle support if there was a way to change the email content so as to limit the amount of information to be sent over email to 'non-developers'. Unfortunately there is no way to modify the emails in version 10.1.3.4. It is part of the upcomign enhancements. I am looking forward to 11g.
ER is 7425308 / 8989562

Please let me know if you have any workarounds that we may be able to try or have any more updates on the latest from Oracle.

Thanks for reading,
Anisha

OBIEE Migration between Environments

We recently had to turn the production server over to the Admin team, which meant that we would lose most of our control over the server including the quick tricks we could use to migrate the RPD and reset all the passwords.

Ofcourse having 12 subject areas, connected to the same source through different connection pools added some complexity to this task.

Automating 'setting of passwords' the other properties of the connection pool can be done via a command line utility -

admintool.ext /command filename


On conducting some searches, I came across Kumar's blog which has a very detailed step by step process documented.

Here is the link:

http://obieeblog.wordpress.com/2009/08/04/simplifying-migration-process-%e2%80%93-changing-environment-specific-variables-in-rpd/


Anisha

DAC Email notification

DAC can be set up to send email in just a few steps.
This is also useful when you want to send only one success email from a number of infomatica workflows which are scheduled to run through DAC.
The DAC server has a built-in login-authentication based email (SMTP) client, which connects to any SMTP login-authenticating server to send emails.

Unlike informatica which needs the outlook client installed on the server, DAC does not.
1. Set up email recipients
2. configure email sender information



1. To set up email recepients:
Launch the DAC client.

Click Setup on the DAC toolbar, and then click the Email Recipients tab.
Click New.
In the Edit tab below, enter the relevant information for the recipient

Referenced Link:
http://download.oracle.com/docs/cd/E12104_01/books/AnyInstAdm/AnyInstAdmWinInstall24.html
http://download.oracle.com/docs/cd/E10783_01/doc/bi.79/e10742/anyinstadmwininstall.htm#i1089967


2. To set up DAC to connect to the email server:
Select Tools --> DAC server setup
Select the tab: Email configuration
Enter the relevant information as the user  who the email should be sent from.
Click Send Test  Email

The email should be sent to the recipient.


Anisha

Schedule DAC service to run without making it a Windows Service

The DAC service is dependent on the database connections which can sometimes be unpredictable because of network fluctuations. DAC services can also fail to start if the server has to be rebooted or the database crashes due to load on the database.

Since DAC is not a 'Windows' service, we noticed it frequently crashed, sending emails like :
'DAC Server shutting down' which were not very helpful.

After which scheduled loads would not run as the 'server' was down.
To mitigate this, we moved DAC to 'scheduled tasks '. This way we set it up to restart everyday at 4:00 AM before the load was scheduled to run. You can add other schedules as well to ensure that it restarts on every reboot or twice a day.

Since then we have never had a problem of missed loads or the server being down at odd hours of the day.

Thanks for reading.
Anisha

Select Serial Number in a SQL query

I was recently asked this question about listing records from a database and even though this is unrelated with most of this blog, I thought it has earned its place as part of this blog.

The question was:

How do I select records from a database table and display the results as a list in the UI (which uses a SQL query to access the DB)

Consider a simple 'Grocery Shopping' database from which we want to display information in the UI/web page based on selected Shopper as follows:

Shopper1 bought

1. Eggs -$3

2. Bread - $2.5

3. Cream -$6

OR

Shopper2 bought

1. Cheese -$5

2. Eggs -$3

3. Ham -$9

4. Bread -$2.5

We have two tables:

To create the above result, using either Oracle or SQL server, you can use the following query:

SELECT
l.Shopper_name,
row_number() over (order by i.item#) as srno,
i.item_name,
i.price
FROM
Shopping_list as l, Inventory as i
WHERE l.shopper_name :=selected_shopper --Prompted value through SQL
GROUP BY l.shopper_name,i.item_name,i.price
ORDER BY l.shopper_name




After getting the results as a list, you can use your code to format it appropriately in the UI.



Another use for this type of query could be to see the number of times a given item appeared in the 'Shopping_List' table Eg: List the Shoppers who bought Eggs with Serial Numbers attached.



Anisha

ORA-12154, ORA-12560 - Cannot Connect to the Oracle Database

These are some common errors for new installations. The solution pretty simple.
Errors like:
ORA-12154, ora-12560

Listed below are a few of the problems we faced...with a few things to check.

1. SQL+ does not connect to the DB - Check to see that the fire wall on the DB server is not blocking the port (usually 1521) - You can either turn off the firewall or get the system admins to open up the database port.
Also make sure that the listener is up and running.

2. When creating the ODBC connection, we could not test the database connection. - Again make sure the DB listener is turned on and the TNSfiles are copied over from the DB server to the server you are trying to connect from. Copy the files to the location similar to : \Network\Admin\

3. Sometimes you can simply re-install the DB client (Its easier than debugging)

Google is extremely helpful with the ORA-XXXX errors that the DB Connection might throw.

Hope this helps.

Anisha

OBI Server Hangs in Stopping status

Sometimes, when restarting OBIEE Server, the Service hangs.
The Windows service hangs in the "Stopping" State.
Sicne the Stop time is unpredictable one way to kill a windows service is to use the taskkill command from Command prompt.
Below is a screen shot of an attemp made to kill the task/windows service associated with NQSServer.exe

It worked like a charm.

-A