Write Back in 10G
I was working on the Write back function in OBIEE and found it quite interesting.
Introduction
Imagine if we can enter in data in the report for certain columns and that becomes a part of the reports generated. Yes this is the basic example of Write Back. Data entered in the report will be added into the database. Basically we can follow two types of functions i,e INSERT and UPDATE.
For doing so just follow the basic steps mentioned below.
1. First I created a database with 3 columns
Our aim is to enter values for the column ‘EXPERIENCE’ from the report so that it gets reflected in the database.
2. I created a ODBC connectioned and imported it in the RPD
3.Once this is done remember to untick the cacheable option
( You see a Write Back tab in the Connection Pool but since our database is Oracle you dont have to do anthing in this).
5. Create a Fact Dimension Model in ther BMM Layer and then save the changes
6.In the Answers first provide privilege to the Group for Write Back(Administration–>Manage Privilege–>Write Back)
7. Choose the column format for the column you want to add data and choose the option Write Back. Inthis case it is the column “EXPERIENCE”
8. Then choose the second button above and fill in the information. Save this report and follow exactly the next step below
9. Now the next step is to write the INSERT and UPDATE statements in an XML which is to be placed in the path location OracleBI/web/customMessages. You can give any name for this XML. The only thing you have to make sure is that the ‘WebMessageName’ in the XML should be the same that is given in the answers with this one. The XML pattern is like the one you see below
INSERT NULL FROM DUAL
UPDATE WRITESAMPLE SET “EXPERIENCE”=’@{c2}’ WHERE “EMPID”=@{c0}
Here the things to be noted is that
– you have to give the WebMessageName the same as that you have given in the Answer
– Give the the Connection Pool name as that of the one set in the RPD
– Give a insert statement even though your not inserting any record otherwise it will throw an error
11. Once this is done make sure that the Presentation Services are started. Then open the report you have saved and update a data you have entered and click update.
12. Go to the database to see if the change is reflected.
Walla… Write back works!!!
MS Office Integration with OBIEE 11g
I was interested to experiment the understanding of MS Office Integration with OBIEE 11g. Please find the detailed steps below to do so.
1. Go to the Home page and in the right corner you see Download BI Desktop Tools as shown below choose MS Office. exe file.
2. Then install the file. I have encountered an issue because I didnt have the .Net Framework2. Please install it from the following link mentioned below.
3. Follow the screen shots mentioned below.
4. After the installation go to MS Office Excel or PowerPoint and when you open the application you will get a message as mentioned below.
5.Then we have to configure a server since we are doing it for the first time. for that once you click on OK you will be prompted with the screen below and then click on new
Here in
Server Name: Enter any name (eg. BI_Jethin)
BI Office Server: The server were 11g is hosted. In my case it is my local system so(eg. localhost)
Port: put it as 7001
Click on Test Connection and you are to get the message below
6. After this step you can see Oracle BI tab in your Excel or powerpoint and then click on that and press Login.
7. You will encounter an error which is stated below for that we have to bring a change in the XML.
8. Follow the below mentioned step to rectify this issue.
Go to the path mentioned below and edit the XML file
E:\OBIEE11G\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\bioffice_11.1.1\hsq62b\war\WEB-INF
Here edit the tag
http://localhost:9704/analytics/saw.dll
to
http://localhost:7001/analytics/saw.dll
9. Restarted the services and after taht when you login again in Power point you will not face the issue and can use it.
Enjoy !!!
Its All About Data - By Jethin Abraham 