EXCEL Data source -WriteBack in OBIEE

EXCEL Data source -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)

Be sure that the Excel ODBC Data Source Name is not configured as Read Only.

Create a Business Model and Presentation Catalog

I created a simple business model to use the sample data that looked like this:

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.

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.

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”.

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.
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.

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.

Set Column Interaction as Write Back

In the Criteria tab, click the properties button on the column you want to update.

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.

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.
Log back in and run the query.

Verify Write Back Is Working

Run the query. The existing data appears.

Edit the cell whose value you want to change.
Click the update button.
Open the Excel workbook and verify the change.
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”.


Open the Excel workbook to verify the update occurred.

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, replacing subjectArea="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:

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.
Here is the same table view after making changes (“Jan” to “January” in the first row) and adding new data to the last row.

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 

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.

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).

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


Thanks
Satya Ranki Reddy