Business Intelligence and OBIEE Tips and Experiences

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

2010-02-22T09:06:00.000-08:00

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

2010-02-12T09:52:00.000-08:00

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

2010-02-04T09:11:00.000-08:00

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

2010-01-22T14:49:00.000-08:00

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

2010-01-22T13:15:00.000-08:00

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

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

2009-09-29T13:07:00.001-07:00

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

2009-08-12T11:22:00.000-07:00

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

Default Values for Null Data

2009-08-12T05:51:00.000-07:00

We came accross this requirement to replace Null Data with a specific value.
For Eg: The report contains a column Rating. The requirement is that if the rating is null, it should be displayed as "Not Rated".
Ideally, this should be taken care of by the source system itself.
However, if this is not possible or will take too long. The logic can be put into the report itself.

Options:
1. In the report, you can introduce an Expression in the Rating column as:

IFNULL("DW.Rating",'NOT RATED')

2. You can also use a case statement for multiple options.

Rating:
CASE
WHEN "DW.Status" = 'DANGER' AND "DW.Rating" IS NULL
THEN 'HIGH RISK'
ELSE
CASE
WHEN "DW.Rating" IS NULL
THEN 'NOT RATED'
ELSE "DW.Rating"
END
END

3. Similar statements can also be introduced at the Business Model and Mapping Layer.

DAC custom execution plan steps

2009-06-09T13:58:00.000-07:00

1. Create custom logical and physical folders :
Tools > seed data > task folders

2.Register the new folders : eg - Custom_mappings
Design > source system folders

3. Modify existing DAC tasks to use the custom mappings and workflows

4. Synchronize Task(s )Right click on DAC task and select synchronize. This will add the source and target tables to the task. make sure the DAC task name and informatica task name are the same.

5. Assemble the Subject Area

6. Generate execution plan(EP) parameters . EP parameters include the Infa repository folders related to the container that the EP belongs to . This could be the missing step.Execution > select EP> select Parameters sub-tab > verify/update the folder names > Generate

7. Build the EP

8. Run the EP

-A

OBIEE Write Back

2009-05-28T14:49:00.000-07:00

We were trying to implement an update of multiple columns in a single update.

A few things to remember:
1. The XML is case sensitive. (If you use a 'C' instead of a 'c' your XML will not work correctly.
2. Each column that has write back enabled, should have a corresponding SQL entry in the XML template.
3. If there is a text entry being written back dont forget to put quotes around the dynamic column name.
4. Everytime you change columns in the report, the columns numbers change so make sure the XML template and the column names match.
5. The XML template should be saved in : D:\OracleBI\web\msgdb\customMessages
Its name can be anything.xml
6. the template name is: , this will be the same name used when specifying write back features.
7. A great source for creating the write back functionality in OBIEE is Venkat's post at: http://oraclebizint.wordpress.com/2007/09/20/oracle-bi-ee-101332-write-back-option-budgetingplanning/
8. For an example of the XML used for update please see the next post.

Anisha

Updating Oracle BI Apps 7.9.5.1 to 7.6

2009-05-19T15:06:00.000-07:00

This is a compilation of information based on our current installation and based on the update and installation guide leading to a successful upgrade to 7.9.6. from 7.9.5.1.

Our source and target databases are Oracle.

This is just a synopsis of the steps we followed.

For a more detailed explanation you can visit OTN and download the relevant documentation.

1. You want to backup all existing information.

- DAC repository (thru DAC export option)

- DAC install folder

- BI Apps RPD

- BI Apps Catalogue

- Informatica repository

Remember all installation directories should be in locations which do not have a space in the location. eg: DO NOT install informatica or java in C:\Program Files. This will cause problems for DAC (which is needed to run the informatica mappings).

2. Uninstall the existing BI Applications and then restart your machine

3. Unset all informatica variables

4. Install BI apps 7.9.6

5. Install Informatica 8.6 + HotFix 4 - create integration and repository services if they do not exist. update Informatica repository

6. Install DAC - update DAC repository

7. Set parameters in DAC and Informatica to connect to databases

8. Check the pmrep and pmcmd connections

9. Build an execution plan with a single subject area (uncheck all options) to test that all connections are in place and then proceed to build with additional subject areas.

Hiding elements from an adhoc user in OBIEE

2009-05-13T07:17:00.000-07:00

Once we created reports, we presented them to users and we were asked to make some changes.

Some of the things pointed out were:
1. In the adhoc analysis some of the element names were not as intuitive to all business users so we needed to change some names.
2. Exposing the time dimension needed a lot more training. Users pulled in the time dimension with elements from unrelated facts and dimensions.When they tried to view the results of their analysis some element values would disappear or they would be presented with an error message.
3. We needed to use a few more elements to enable some additional functionality in the reports. These were meaningless to adhoc users.Unless they were trained to use them they would not work correctly or be useful.Plus most of the business users had no need to use these functionality elements. It just added a lot of clutter.

Some possible solutions for the points above and fall backs:
(None of these are wrong but none of these worked completely in our case)
1. We could change the names of all the elements and then we would have had to go back to each report and modify each element.(We could not find a simple way to ensure all reports would sustain the name changes without creating aliases)
2. We could have pulled in time dimension elements correctly for each of the Facts and Dimensions and named them appropriately, so that users would only use those elements. - This again would have required changes in the reports as we could not find away to create aliases when the folder name changed.
3.We could have created a folder which would be titled - 'For Development' and then make sure the users were told not to use the folder. - Curiosity would have got the better of us as the folder was still exposed and accessible to the users.

Our Solution
Before I go into that I guess I should mention that we are using LDAP authentication & authorization.
For this we had to create our own groups on the database. Groups and privileges were not downloaded from the LDAP server itself. If this would have worked we could have attempted to hide elements from a select user group and yet display them to other groups.

As we already had preexisting reports, we created a second subject area which would be used for adhoc analysis by the business users. This way all name changes and rearranging of elements could be achieved without breaking the reports in the subject area exposed to the users.
The subject areas now looked completely different even though they were fed by the same logical model.
As an administrator we could block access to the Administrative subject area from all non advanced users.This way when the users log in they could see only one of the subject areas with the more intuitive names and no technical elements. Only advanced users, who would need to create reports, would be trained on the Administrative subject area as ahdoc users would not need to see the technical elements.

Creating a navigation/drill thru report

2009-05-12T14:12:00.000-07:00

In my last post I talked about using the navigation capability in the reporting layer to create the illusion of Drilling down.

This is easily achieved by creating a report with all the elements you wish to see. (Kind of like a detailed view)
We were using "Loan ID" to click thru to this report.
To allow a user to prompt on "Loan ID", so that only the data relevant to that "Loan ID" is displayed, the "Loan ID" needs to be prompted.
This can be easily done in the filters section by setting the option to 'is prompted'
Now whenever a user click on a "Loan ID", the report will navigate to this detailed report and present the user with more detailed information about that "Loan ID"

So far we have gone through many iterations in our report development.
One of the changes made was that "Loan id" was renamed to "Loan ID". The fact that the case of the attribute name was different did not cause any of the pre-existing reports to fail. But it did not work when navigating using prompts. "Loan id", was not able to prompt "Loan ID".
If you are going through many changes and could miss something, my suggestion would be to always check the names & spelling of the attributes or they will not work as you imagine they should.

OBIEE Drill through/Drill down

2009-05-12T13:25:00.000-07:00

Our users asked to be able to drill down from an element to other elements.

In this case -
They wanted to click on "Loan ID" and see some additional elements for that "Loan ID".

Our two options were to either create a hierarcy in the 'Administrator' tool or to create a navigation path between two reports.

We chose the second option - to enable Drill thru as a navigation option between the reports.
This was useful as the information available after the Drill was easier to customize. An advanced user could be trained to add or remove elements at this level.

To achieve this, go to the Criteria tab of the report and click on the Column properties

Select the tab for column Format and under value integration, set it to Navigate.
You will then see the options to add navigation target (You can add more than one target but make sure you enter a unique caption for each target).
For the first target, enter the path to the report you wish to navigate to ( I have assumed you have already created your detailed report and that "Loan ID" is set to prompted(next post).Enter a meaningful caption.

If you wish to add more targets, click on 'Add Navigation Target' and you follow the same instructions above.
Click OK once you are done and want these changes to be applied only to the current report.
If you want the changes to be applied to all the reports which would use this element, click on Save in the bottom left corner, and select "as system wide default for "Loan Attributes"."Loan ID".
You can always go to other reports and turn this option off by setting the value interaction to - 'No Interaction'

Once done save this report and preview it.
You will see that when you hover over a Loan ID, the mouse pointer will turn into a hand and be clickable like a hyperlink.
If you have more than one navigation target, you will be given all the options as a sort of drop down menu when you click the Loan ID.

OBIEE Tips

2009-05-12T13:24:00.000-07:00

This blog contains some of the different issues I have run into during my implementations of OBIEE and OBIApps.
Feel free to leave your comments and/or tips.

Thanks,
Anisha