Showing posts with label WriteBack. Show all posts
Showing posts with label WriteBack. Show all posts

Oracle BI EE – Bulk Write backs to Essbase – Using JAPI, Global Temporary Tables and UTL_HTTP – Part 2

I had covered an approach here to do write backs into Essbase from BI EE. In some cases that approach would be sufficient. But in most of the cases, end users would want the capability to do a bulk write back similar to the default write back option provided by BI EE(like in relational sources). For example, if you look at the screenshot below, we have an Essbase report containing Q1, Eastern Market sales.
image
If you use the last blog entry’s approach you can write back to Essbase one row at a time. But what if our requirement is to write all the modified rows in a single shot to Essbase. In order to achieve that, the architecture for write back would be slightly different from what we saw before. The high level architecture is given below
image
As you see, in order to achieve the writebacks we would be using the usual Write back template provided by BI EE. The main difference between this and the relational write back is the fact that we would be writing into a global temporary table. We are using a Global Temporary table as it has the feature of truncating itself at the end of a transaction or a session. This Global Temporary table is used just for dummy purpose. During the insert into the temporary table, we would be calling a function which would in turn pass down the parameters to the UTL_HTTP function to call the custom JSP that we created last time. So let us first start with the JSP page that we had created before. For the sake of completeness, i am pasting the code again here.
<%@ page contentType="text/html;charset=windows-1252"%><%@ page import="java.io.*" %><%@ page import="java.util.Map" %><%@ page import="java.util.Map.Entry" %><%@ page import="java.util.jar.Attributes" %><%@ page import="java.util.Iterator" %><%@ page import="com.essbase.api.base.*" %><%@ page import="com.essbase.api.dataquery.*" %>

<%@ page import="com.essbase.api.session.*" %>

<%@ page import="com.essbase.api.datasource.*" %>

<%@ page import="com.essbase.api.domain.*" %>

<%@ page import="com.essbase.api.metadata.*" %> <%="WriteBack Started" %><%

String s_userName = "admin";

        String s_password = "password";

        String s_olapSvrName = "localhost";

        String s_provider = "http://localhost:13080/aps/JAPI";

try

        {

        IEssbase ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

        IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);

        IEssOlapServer olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);

        olapSvr.connect();

        IEssCubeView cv = dom.openCubeView("Data Update Example",s_olapSvrName, "Demo", "Basic");

String v_Market = request.getParameter("p_Market");

        String v_Product = request.getParameter("p_Product");

        String v_Accounts = request.getParameter("p_Accounts");

        String v_Scenario = request.getParameter("p_Scenario");

        String v_Year = request.getParameter("p_Year");

        String v_Value = request.getParameter("p_Value");

IEssGridView grid = cv.getGridView();

        grid.setSize(2, 5);

        grid.setValue(0, 1, v_Market);

        grid.setValue(0, 2, v_Product);

        grid.setValue(0, 3, v_Accounts); ;

        grid.setValue(0, 4, v_Scenario);

        grid.setValue(1, 0, v_Year);

cv.performOperation(cv.createIEssOpRetrieve());

        System.out.println("\nData Cell at 2nd-row, 2nd-column: " + grid.getValue(1,1).toString());

        System.out.println ("Market: "+v_Market+" Product: "+v_Product+" Accounts: "+v_Accounts+" Scenario: "+v_Scenario+" Year: "+v_Year+" Value: "+v_Value);

int row = 1, col = 1;

        if (grid.getCellContentType(row, col) ==

                IEssGridView.CELL_CONTENT_TYPE_DOUBLE) {

            IEssValueAny val = grid.getValue(row, col);

            double dblVal = val.getDouble();

grid.setValue(row, col, Double.valueOf(v_Value).doubleValue());

        } else if (grid.getCellContentType(row, col) ==

                IEssGridView.CELL_CONTENT_TYPE_MISSING) {

            grid.setValue(row, col, Double.valueOf(v_Value).doubleValue());

        }

IEssOpUpdate opUpd = cv.createIEssOpUpdate();

        cv.performOperation(opUpd);

}catch (EssException x){

            System.out.println("ERROR: " + x.getMessage());

        }

%>

<%="WriteBack Ended" %>

  <%="WriteBack Ended" %>

<%

    //response.sendRedirect("http://localhost:9704/analytics");

%>
After compiling the above jsp, deploy this to any Java Application server as an EAR file. The main reason for doing this is to have a URL that we can call using the UTL_HTTP database package. If more security is needed, one can code them directly into the JSP so that no one else is able to access the jsp outside of the UTL_HTTP package.
image
The next step is to create the Global Temporary Table. The structure of the Global temporary table can contain any number of columns. But we would need a column which we would be inserting into through the template via a function. In my case the Global Temporary table contains all the write back columns as well as an extra column that indicates whether the write back was successful or not.
create global temporary table EssbaseWriteBack_GTT(Year varchar2(100),Market varchar2(100),Product varchar2(100),

Scenario varchar2(100),

Sales Number,

WriteBackStatus Number

) ON COMMIT DELETE ROWS;
Once the global temporary table has been created, we need to create a function which will basically pass the input parameters to the JSP. The JSP is basically called through the UTL_HTTP package and the parameters are passed using the GET method. If you need more security, POST method can also be used.The idea is for every row insert, the row attributes are passed into this function which would basically write back into Essbase.
create or replaceFUNCTION WriteBack_GTT(p_Year varchar2,p_Market varchar2,p_Product varchar2,p_Scenario varchar2,

p_value NUMBER

) RETURN NUMBER IS

  req Utl_Http.Req;

  resp Utl_Http.Resp;  v_msg varchar2(80);

  v_url varchar2(32767) := 'http://localhost:9704/AllocEssbase-AllocEssbase-context-root/EssbaseWriteback.jsp?';

begin

  v_url := 'http://localhost:9704/AllocEssbase-AllocEssbase-context-root/EssbaseWriteback.jsp?';

  v_url := v_url||'p_Market='||p_Market||'&p_Year='||p_Year||'&p_Accounts=Sales&p_Scenario='||p_Scenario||'&p_Product='||p_Product||'&p_Value='||to_char(p_Value);

  Utl_Http.Set_Response_Error_Check ( enable => true );

  Utl_Http.Set_Detailed_Excp_Support ( enable => true );

  req := Utl_Http.Begin_Request (

    url => v_url,

    method => 'GET' );

  Utl_Http.Set_Header (

    r => req,

    name => 'User-Agent',

    value => 'Mozilla/4.0' );

 resp := Utl_Http.Get_Response ( r => req );

 Dbms_Output.Put_Line ( 'Status code: ' || resp.status_code );

 Dbms_Output.Put_Line ( 'Reason phrase: ' || resp.reason_phrase );

    Utl_Http.End_Response ( r => resp );

RETURN 1;

exception

when Utl_Http.Request_Failed then

    Dbms_Output.Put_Line ( 'Request_Failed: ' || Utl_Http.Get_Detailed_Sqlerrm );

    RETURN 0;

when Utl_Http.Http_Server_Error then

    Dbms_Output.Put_Line ( 'Http_Server_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );

    RETURN 0;

when Utl_Http.Http_Client_Error then

    Dbms_Output.Put_Line ( 'Http_Client_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );

    RETURN 0;

when others then

    Dbms_Output.Put_Line (SQLERRM);

    RETURN 0;

end;
The next step is to create the Write back template as shown below.
<?xml version="1.0" encoding="utf-8" ?><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable lang="en-us" system="WriteBack" table="Messages"><WebMessage name="WriteBack"><XML>

<writeBack connectionPool="SH">

<insert> </insert>

<update>INSERT INTO EssbaseWriteBack_GTT(YEAR,MARKET,PRODUCT,SCENARIO,Sales,WRITEBACKSTATUS)

VALUES ('@{c0}','@{c1}','@{c2}','@{c3}', @{c4}, WriteBack_GTT('@{c0}','@{c1}','@{c2}','@{c3}', @{c4})) </update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>
image
Place the write back template in the {OracleBI}\web\msgdb\customMessages folder. Restart the presentation services. Now navigate to the report and enable write back on the Sales column.
image
Once the write back is enabled, you should be able to update multiple rows back to Essbase simultaneously.
image
image
The above will work if you are on a Block storage cube(BSO). If you are on an Aggregate Storage(ASO) cube, you need to ensure that you are writing back only to level-0 intersections. Else the write back will fail. The other option for ASO cubes is to setup the write back partition with a BSO cube. The above architecture can even be expanded further to update cell comments, dimension members etc.

OBIEE write back, solution for insert and update in database

Here is my solution for write back (insert and update). I'm just trying to do some table data maintenance, and that is, insert and update one after the other. This solution includes two reports (one for insert and one for update, and two XML files). It's very fast to insert data, or update.

1. At first, we create table in the database:

create table wb_test(col1 number, col2 varchar2(50), col3 varchar2(255))

alter table wb_test add constraint wb_test_pk primary key (col1)

create sequence wb_test_seq start with 1 increment by 1

2. In Administrator, we need to disable cacheable option:



3. In Answers the user must have write back privilegies


4. In this write back insert/update solution user inserts new data through the one report and through the other report is doing update. Both reports are for the same database table. Write back option is taking XML file that we put in ..\OracleBI\web\msgdb\customMessages folder. Inside this XML there are SQL's for insert and/or update.

5. Insert report


Here is Id=0 in the filter. What does it mean? This insert report is taking only rows that have Id=0. So, for 'insert new row' we'll update this Id from 0 to some sequence value. You'll see later that we put update statement in insert XML. This dummy row is a necessary to be in the table, before (when the table is empty) and after we update (this is resolved by a after update trigger) because the table has the primary key (id), otherwise we have problems.


OBIEE has some problems with null values in the database. In this case we have 0 – null – null and we need to put in the col2 and col3 columns expression ifnull(WB_TEST.COL2,'n/a') and ifnull(WB_TEST.COL3,'n/a') to get null value from database and represents it as n/a.

Without this workaround if we try to insert something (test) we would have:


and we get an error:


We can ignore/fix this error, with ifnull(WB_TEST.COL2,'n/a') in the col2 and ifnull(WB_TEST.COL3,'n/a') in the col3. Try it.

Then we get:



N/a is null, so if we delete n/a from both columns we are saving null in the database with new inserted Id, and this works fine, like in case if we put some value:


After successful insert the next thing that we see is again dummy Id=0 row. This means that OBIEE saved our insert data in the database (mmarkic) i automatically give us dummy Id=o row. If we don't want to insert we close the insert report. After the user push insert button, XML code is generated.

Insert XML:


After insert the state in the database is:


We see new inserted row with Id 34, and a new dummy row appeared.

6. Update report



In this update report we show only inserted rows, with Id that is greater then zero (Id>0). We can update them. In case we don't have inserted rows this report would return no results.

In our case we have one row to update..


We have changed value from mmarkic u mmarkic2:


After insert we see in the report new data and the state in the database is:


Update XML:


XML files location:


We set writeback template here:


7. Database trigger:

create or replace trigger wb_test_af_upd_tr
after update
ON wb_test
declare
cursor c1
is select 1 as num
from wb_test
where col1=0;
v1 number;
begin
open c1;
fetch c1 into v1;
if v1=1 then
null;
else
insert into wb_test values (0,null,null);
end if;
end loop;
close c1;
exception
when others then
null;
end;
*Note
We use dummy row insert method.
After we insert row (we update dummy row) this new row we don't see in the update report because of Id=0 filter.
After update of dummy row we activate trigger in wb_test table which inserts then again this dummy Id=0 row if the one does not exist.
The trigger will be activated during regular update.
Update is only for rows that have Id greater then zero

Oracle BI EE 10.1.3.3/2 – Report Comments in Dashboards and Chat Options – Using Writebacks

I was asked by an end user the other day about the possibility of having an option in BI EE wherein users can enter comments about the reports and the report layouts. The user mentioned that this would be very useful during the initial project roll outs wherein end-users might need some changes to the final reports. He also mentioned that this can be useful for handling change requests. Though there is no out of the box feature in BI EE to make this functionality available, lets see how to mimic such a requirement using Writebacks. So, basically the idea is to create an external table that would store the feedback from the end users. Ensure that you have gone through my previous blog entry here which would give you details on how to enable Writebacks in BI EE. So, lets start with creating a simple table which would contain 2 columns (Username and Comments).
      
Insert a dummy row inside this table so that it does not throw out a No Data Found error in Answers(even better just insert Null for both the 2 columns).
Once this is done, import this table into the repository and design the corresponding Business Model Layer and Presentation Layer. Ensure that caching for this table is turned off.
      
Now go to answers and start creating a simple report containing the Username and Comments. In the Comments Formula Enter
CASE WHEN 1=0 THEN REPORT_COMMENTS.COMMENTS ELSE ” END
The above is to ensure that only one row is returned even when the data in the table is more than 1. In the User Column,enter USER() and hide this column.
      
      
Also, ensure that you are disabling the presentation services cache. Now, lets design a Writeback Template. The writeback template would look like the one below,
      
Ensure that you have the following SQL in your writeback template.
INSERT INTO REPORT_COMMENTS (USERNAME, COMMENTS) VALUES ( ‘@{c1}’,'@{c0}’)
Now go back to the report and ensure that you are enabling writeback for the COMMENTS column.
      
      
Now, the final step is to create another report which would again contain both the mentioned columns in the above report. But in addition this would contain a filter to remove the dummy row that we inserted earlier. Ensure that this has the presentation services cache disabled.
      
Add both these reports to the dashboard. So, now users would be in a position to enter their comments. As and when they enter the comments, it would be displayed in the other report. Administrators can make changes accordingly to any of the reports mentioned.
      
      
One other functionality where this can be useful is if you want end users to have a basic chat feature. You can use a combination of Java Script Page Refresh and writebacks to enable users to chat between themselves. The writeback column would enable users to enter a chat message and the report window would show the incoming message. This report window can be refreshed periodically to show incoming messages.

Oracle BI EE 10.1.3.3/2 – Write Back Option – Budgeting/Planning

OBI EE has another interesting feature that would allow end users to update or insert data back into the database. For example, one can have a column of data that can be manually entered by end users, which in turn can be updated back to the database. This would make a lot of sense for users who want to do planning or budgeting on a mini scale. They can use OBI EE both as a reporting tool and also partly for entering sales quotas or budgets etc depending on how the business is performing currently. Let us see how to go about setting up this option in OBI EE. The entire process of setting this up involves changes right from the connection pool to the presentation layer. The example that i am going to follow here is based on the data from SH schema. I have added a new column called Sales_Quota to the sales table which is what i would like to update and insert. The idea is to allow end users to enter data for sales_quota and insert new records into the database.
      
1.   The first step is to enter in the relevant details in the connection pool writeback properties section. If you are on Oracle, leave them as default.
      
2.   The second step is to make the sales table not cacheable. This would ensure that we would get the results as and when we update or insert.
      
3.   Once this done, log into OBI EE Answer and give in the writeback privilege to those users who would need them. In my case, i would be giving them to Administrator since it is not enabled by default.
      
4.   Next is to create a sample report that would include the sales_quota column. I have a report with all the 5 dimension keys and sales_quota.
      
5.   Once this is done, go to the column properties of sales_quota and enable writeback.
      
6.   After enabling the write back, save the report. The next step is to specify the query that we would like the BI Server to execute while writing back to the database. The queries (insert and update) are specified in a XML Template.The XML Templates are generally placed in {ORACLEBI}/web/customMessages folder. The structure of my XML template file is
The filename of the XML file can be anything. But the WebMessage name is the actual XML template name i.e SHNEW is the XML Template name. One can have inserts as well as updates. @{cn-1} specifies the actual coulmns that i have pulled into the report.
7.   Go to the table properties of the report and click on the write back option. Enter in the template details.
      
      
8.   Once this is done, enter in some data and test out the write back.
      
      
More details can be obtained from the BI Server and Presentation Services guides.

WriteBack in OBIEE

Writeback” – presenting an Answers interface so that users can update data in database tables or insert new rows – first appeared in version 7.8.4. To use OBIEE’s writeback capabilities, there are several set up steps. While each one is simple and fairly straightforward, it’s easy to forget all the steps and where each one is located. This document contains the instructions in case you find yourself forgetting them. Since many people like to demo using Excel as a data source, it focuses first on using Excel. Later, an example using an Oracle table is discussed.

Sample Data

Here’s some data from an Excel 2003 workbook. The data includes columns with three different data types: numbers, character, and dates. The data is from a named range called “Data” in the Excel workbook.(DSN)
image
Be sure that the Excel ODBC Data Source Name is not configured as Read Only.
clip_image002

Create a Business Model and Presentation Catalog

I created a simple business model to use the sample data that looked like this:
clip_image004
Note that logical column Col1 is defined as the key. This isn’t critical, but if a key exists that maps to a column that is a key in the physical source table, it makes the resulting update SQL easier to write.
Both logical tables “Dim” and “Facts” use the same physical source, which is the Excel “table” (named range) called “Data” defined in the physical layer of the metadata. The physical column “Col1” is the functional key, and it could be shown as the key in the metadata, but it doesn’t really matter if it is or not.
clip_image006
To make it easier to verify that the writeback process is working, disable connection pooling using the checkbox on the Connection Pool properties dialog General tab.
clip_image008
You may have noticed here another tab called “Write Back”. Ignore it. That tab is for other use cases where the BI Server has to write back to the database.

Enable Write Back Privilege

With web administration, enable the privilege to “Write Back to Database”.
clip_image010

Create a Query with a Table View

Here is the query that will be used to update the Excel workbook. This query includes all the columns from the physical table, but for updates this would not be necessary.clip_image012
Settle on the query structure, and save it, before going on to the next step.

Create a “Write Back Template”

The SQL the BI Server will use to generate the updates to the physical table comes from a template you will write. This template, the “Write Back Template”, is an XML file. For OBI EE versions 10.x, this file should be in the OracleBIData\web\msgdb\customMessages folder. This is true whether IIS or OC4J is the web server being used. The full path and name of this file is OracleBIData\web\msgdb\customMessages\WriteBackTemplate.xml.
Here is the text of the file, and it actually contains three separate templates. They are called “UpdateExcelData”, “UpdateExcelCharData”, and “UpdateExcelDateTime”. Each template is a separate WebMessage name. Each template will be used by a different query to illustrate how to update columns of different data types. I included comments to make it easier to understand the mapping of the logical columns to the physical columns, which makes the SQL update and insert templates easier to understand.
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
<WebMessageTable lang="en-us" system="WriteBackTemplates" table="Templates">
<!-- Testing templates used for writing back into an Excel workbook used as a data source -->
<!-- Save this file in the OracleBIData\web\msgdb\customMessages folder as WriteBackTemplate.xml -->
<WebMessage name= "UpdateExcelData">
<XML>
<writeBack connectionPool="WriteBack">
<!-- Table has columns Col1, Col2, Col3Char, Col4Date  and in query c0 maps to Col1, c1 maps to Col3Char, c2 maps to Col4Date, c3 maps to Col2-->
<insert>INSERT INTO Data VALUES(@{c0},'@{c1}',@{c2},@{c3})</insert>   
<update>UPDATE Data SET "Col2"=@{c3} WHERE "Col1"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
<WebMessage name= "UpdateExcelCharData">
<XML>
<writeBack connectionPool="WriteBack">
<insert>INSERT INTO Data VALUES(@{c0},'@{c1}',@{c2},@{c3})</insert>
<!-- Table has columns Col1, Col2, Col3Char, Col4Date  and in query c0 maps to Col1, c1 maps to Col3Char, c2 maps to Col4Date, c3 maps to Col2-->
<update>UPDATE Data SET "Col3Char"='@{c1}' WHERE "Col1"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
<WebMessage name= "UpdateExcelDateTime">
<XML>
<writeBack connectionPool="WriteBack">
<insert>INSERT INTO Data VALUES(@{c0},'@{c1}',@{c2},@{c3})</insert>
<!-- Table has columns Col1, Col2, Col3Char, Col4Date  and in query c0 maps to Col1, c1 maps to Col3Char, c2 maps to Col4Date, c3 maps to Col2-->
<update>UPDATE Data SET "Col4Date"= '@{c2}' WHERE "Col1"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
Note that @{c0} means the column in the first position on the Criteria tab. The positional columns are defined at the time you first save the query. If you rearrange column order, (e.g. moving the first column to the second position), the value of n in @{cn} does not change.
Columns that are a character (text) datatype have single quotes around them. Dates or datetime columns also require single quotes around them for Excel. The form of the SQL templates, particularly for datetime columns, will depend on the target database platform.
What does table="Templates" mean? Its an XML thing. It tells what style of table is being created. In this case a template.

Configure Table Write Back Properties

Using the query you created and saved, go to the table view and click on the Write Back Properties icon.
clip_image014
When the Write Back Dialog opens, check Enable Write Back and enter the “Template Name”. The "template name" is the name of the message in the template file, not the file name itself. Each WebMessage element has a name attribute. In the example above, there are three Webmessage names: “UpdateExcelData”, “UpdateExcelCharData”, and “UpdateExcelDateTime”. For this first query, enter UpdateExcelData. This contains a SQL template for updating a numeric column.
Enter the button text and select the button position.
clip_image016

Set Column Interaction as Write Back

In the Criteria tab, click the properties button on the column you want to update.
clip_image018
On the second tab of the column properties dialog, “Column Format”, select Write Back as the Value Interaction. Enter the field size. Here the field size will hold 6 characters.
clip_image020
Save the query. Log out of Answers.

Restart the Presentation Server

The writeback template will not be available to use until after you restart the presentation server. You have to restart each time you change the WriteBackTemplate.xml fle.clip_image022
Log back in and run the query.

Verify Write Back Is Working

Run the query. The existing data appears.clip_image023

Edit the cell whose value you want to change.clip_image024
Click the update button. clip_image026
Open the Excel workbook and verify the change.clip_image028
Close the Excel workbook. (Leaving the Excel workbook open will cause an error when you try to query it again).

Configure Other Queries to Update Other Columns

I modified the first query and saved two other queries in order to illustrate updating character and datetime columns. Each of these queries uses a separate template: “UpdateExcelCharData” and “UpdateExcelDateTime”.
clip_image030
clip_image032
Open the Excel workbook to verify the update occurred.clip_image034

Inserts in Excel

So far, we have only looked at updating values in existing rows. What about inserting new rows? The writeback Webmessages contain both Update and Insert templates. However, you may have noticed, if you looked at the query logs, that Insert statements have never been generated.
To generate an insert, the OBIEE checks whether the first column in the edited row was originally null. If it was, it sets the action to insert rather than update.
You can get this to work in Excel by adding a row into the named range containing the data that has the first column = Null. Since you may not want to have bogus values in the rest of the column, you could set all the columns to null. (Note – adding several null rows with the idea that you might want to insert multiple rows at once probably will not work. The reason is that the query to retrieve rows will be a SELECT DISTINCT, and so the result set displayed will contain only a single null row.)
Filling in values in the null row and clicking the Writeback button in the table view will result in an insert statement. That's the good news.
The bad news is that in this context, Excel will not perform the insert. The error message will read: "Cannot expand named range..". So Inserts apparently won't work in Excel when the "table" is a named range.

Writeback in Oracle

I created a copy of the Excel data in the SH schema in Oracle 10g in a table named “WriteBack”. WriteBack had one row where all values are null.
I set up a subject area and business model identical to the one used for showing writeback in Excel. I did this by copying the existing Excel Writeback business model, along with its presentation catalog, and then used the utility to replace the Excel Data table with the Oracle Writeback table (the utility is in the Tools|Utility menu list).
I added this Webmessage to the writeback XML file:
<WebMessage name= "OracleWriteBack">
<XML>
<writeBack connectionPool="orcl SH">
<insert>INSERT INTO WRITEBACK VALUES(@{c0},@{c3},'@{c1}', TO_DATE('@{c2}', 'MM/DD/YYYY HH:MI:SS AM') )</insert>
<update>UPDATE WRITEBACK SET "COL2"=@{c3},"COL3CHAR"='@{c1}',"COL4DATE"= TO_DATE('@{c2}', 'MM/DD/YYYY HH:MI:SS AM') WHERE "COL1"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
Note that:
1. The column names are upper case, since that is how they exist in the Oracle table and the template encloses them in quotation marks.
2. The format mask for the TO_DATE function matches the date format displayed in the table.
3. None of the single or double quotation marks are “smart quotes”. Smart quotes will cause the SQL to fail.
I also turned off caching for the Oracle Writeback table in the metadata.
To use a query that I had created for Excel, I first modified it by editing the XML in the Advanced tab, replacingsubjectArea="ExcelWriteBack" with subjectArea="OracleWriteBack” . I then clicked the Set XML button.
Note: Don’t make the subject area change in the Logical SQL window, or you will lose all the other table formatting and setup work you have already done.
The next step was to edit the writeback properties of the table view in order to use the correct writeback template:
clip_image036 clip_image038
I saved the revised query, logged out of Answers, and restarted the presentation server.
Remember to restart the presentation server any time you make a change to the WriteBack.XML file.
The table view for updating and inserting looked like this. Note the nulls in the last row. clip_image040
Here is the same table view after making changes (“Jan” to “January” in the first row) and adding new data to the last row.
clip_image042
Clicking the “Writeback” button generated the Update and Insert statements, followed by a Select statement that updated the table view.
UPDATE WRITEBACK SET "COL2"=10,"COL3CHAR"='January',"COL4DATE"= TO_DATE('1/31/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') WHERE "COL1"=1


INSERT INTO WRITEBACK VALUES(11,110,'Nov',TO_DATE('11/30/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'))


select T26869.COL1 as c1,
     T26869.COL3CHAR as c2,
     T26869.COL4DATE as c3,
     sum(T26869.COL2) as c4
from 
     WRITEBACK T26869
group by T26869.COL1, T26869.COL3CHAR, T26869.COL4DATE
order by c1, c2, c3 


clip_image044 


The full date and time do not need to be entered. Just entering the Date part alone will work. The last row shows the resulting data after adding another row where the input date was just 12/31/2008.


clip_image046 


Tables with Keys



If COL1 in the example above had been a key, NULLs could not exist in that column. In that case, how could you do an insert?




One way to solve it (there may be better ways but this is the only solution I came up with) is to create another data table, I called it WRITEBACKNULL, similar in structure to WRITEBACKKEYED but without a key. It contained one row where all the values were null.





I modified the business model to include WRITEBACKNULL as an additional source and set up fragmentation content so that queries would always use both sources (unless, for some reason, the user selected Dim.Col1 = 1000 or 1001, two non-existent values – but that could also be prevented by fragmenting on a logical column that wasn’t exposed in the presentation layer).


clip_image048 


clip_image050


clip_image052 


This will produce the null row needed in the table view to bring about an insert.