Monday, 25 February 2013

Write back in OBIEE 7.8.4/10g/11g–My First Project Attempt for Writing back in Excel via OBIEE 10g

Write back, Dashboard Commentary, Report Comments and blog type comments are buzz words for a quite significant times in OBIEE reporting world. Believe me my first project about the write back which I successfully presented in 2008 almost 5/6 years back. I have been successful to implement the right back with some of the technical notes available at that time from Oracle Development Team.

This blog is an attempt an attempt to share documentation from my knowledge base which uses OBIEE and Excel to demonstrate a simple write back using Excel and also focuses using Oracle table in later part of the document.

Introduction - Writeback

“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 OBI EE’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 also 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.

1/31/08 0:00
2/29/08 0:00
3/31/08 0:00
4/30/08 0:00
5/31/08 0:00
6/30/08 0:00
7/31/08 0:00
8/31/08 0:00
9/30/08 0:00
10/31/08 0:00

Configure the Excel ODBC Data Source Name (DSN)

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

Create a Business Model and Presentation Catalog

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

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.

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.
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="">
<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">
<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>
<WebMessage name= "UpdateExcelCharData">
<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>
<WebMessage name= "UpdateExcelDateTime">
<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>

The file contains three separate templates i.e.  UpdateExcelData, UpdateExcelCharData,and UpdateExcelDateTime. Each template is a separate Web Message 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.
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.

Configure Table Write Back Properties

Using the query a request is been 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 and 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 file.
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. 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

After 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.
Open the Excel workbook to verify the update occurred.

Inserts in Excel

The write back Web messages 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 if the first field that was edited was originally null. If it was, it sets the action to insert rather than update.
One 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 if the set metadata in the business model causes a group by the un-aggregated columns, the result set 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 Excel will not perform the insert and come back with the error message
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

For demoing inserts we have set writeback in Oracle. The subject area and business model identical to the one used for showing writeback in Excel are designed.
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 Write back table (the utility is in the ToolsàUtility menu list), this helped to avoid rework.
The following web messages are added into the writeback XML file:
<WebMessage name= "OracleWriteBack">
<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>

Some of the important notes regarding the template for Oracle Database are as follows

·         The column names are upper case, since  that is how they exist in the Oracle table and the template encloses them in quotation marks.

·         The format mask for the TO_DATE function matches  the date format displayed in the table.

·         None of the single or double quotation marks are “smart quotes”. Smart quotes will cause the SQL to fail.

Truing off caching for oracle writeback table in the metadata is a must activity.
After creating a query, next step was to edit the writeback properties of the table view in order to use the correct writeback template:

Once report is saved, restart presentation server to make a change to the WriteBack.XML file. The table view for updating and inserting looked like figure 1 below. Note the nulls in the last row. Second view 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
     WRITEBACK T26869
group by T26869.COL1, T26869.COL3CHAR, T26869.COL4DATE
order by c1, c2, c3

Tables with Keys

If COL1 in the example above had been a key, NULLs could not exist in that column. In that case, one of the solution for allowing inserts are as below.
One way to solve it to create another data table, called as WRITEBACKNULL, similar in structure to WRITEBACKKEYED but without a key. It contained one row where all the values were null.
After modifying 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.
I hope you find this simple demonstration of the write back useful, I am not a sole writer of the contents of this blog, the relevant text is been copied from various other documents. I don’t see any reason why this writeback should not work with the OBIEE 11g with minor changes, please refer the following writeback blog for 11g.

Friday, 22 February 2013

Setup for Oracle BI Enterprise Edition (OBIEE 10g/11g) Multi-User Development Integrated with Source Control – Part A

One of the important challenges I come across during many years of working in OBIEE APPS world is to provide of a robust, comprehensive project management technique for managing multiuser development integrated with source control.


Customers having large, complex or development teams that are not co-located often have multi developers simultaneously working on the same RPD. So challenge to make sure that development work is not lost or overwritten and it should be restorable to a previous freeze date as and when required.

This blog is an attempt to share some of the information I have used to setup the multi-user development integrated with source control. The text in this blog is owed to my colleagues and counterpart on multiple projects; I am just a messenger or publisher of this information. This two part blog covers multiuser development integrated with source control for 10g in the first section while second would be covering multiuser development and source control for 11g, which is more sophisticated that the earlier(10g) one.

Multi-User Development Resources

Refer technical note from oracle support and Bookshelf documentation for a detailed discussion on Multi-user development process specific to Oracle BI before started working on following configuration steps.

Step by Step Approach

1.    Administrator creates a multi-user development (MUD) folder on their desktop. This folder can be located independent of the BI installation folders.

2.    Administrator checks out the latest version of “Master” RPD from your SCM and copies it to the repository folder of your BI installation.

3.    Administrator logs into the BI application and opens the base-line version of the RPD from the repository folder in offline mode.

4.    From the Menu, Pick Manage > Projects and create new projects that are manageable in size. E.g. separate projects for each reporting area such as Finance, Procure to Pay etc

5.    After creating a project, assign objects to this project, for example, when the Sample Sales presentation catalog object is “Added”, all the BMM and Physical objects referenced by this presentation catalog will be included in this project.

6.    After defining all the projects, save your work in the RPD.

7.    From the menu pick Tools > Options and select the More tab to set the MUD directory.

8.    Save your changes and close this RPD (you do not need to exit the BI Admin tool).

9.    Using windows explorer copy this RPD to the MUD folder and change the name to identify that this is the “master” rpd. Ex: Master_Dev.rpd.

10.    From the File Menu, select Multi-User Checkout (BI checkout).

11.    After login with admin credentials the administrator is asked to pick one or more of the projects defined. Here I have been choosing B-Inventory Fact project.

12.    The following prompt requests for a file name that should be used to save this project(s). Note: The default folder this RPD is saved to is the BI install Repository folder.

13.    The new RPD is opened in Offline mode. This RPD will consist of all the objects that are associated to the projects chosen in the checkout process. Save the new RPD.

a.    The Repository folder will have two RPD files, the OriginalInventory.rpd and Inventory.rpd.

b.    A log file is created on the MUD folder providing information consisting of checkout data/time, creation of the two RPDs discussed above, and the Login and machine name.
14.   Administrator can continue to checkout (BI Admin Tool) other projects by following the steps 10-15 for each project. Please remember to always save the project RPDs before closing them.

15.   Administrator can next check-in (SCM Check-in) the project RPDs and Master_Dev.RPD into SCM.

16.  Developers can individually work on these project RPDs by following SCM’s check-in, check-out processes, and ensure that projects are copied to the BI installation Repository folder on their desktops.

17.   On a pre-set freeze date, Administrator will request that all project RPDs be checked-back (SCM Check-in process) into SCM.

18.   After this freeze date

a.    Administrator will check-out (SCM check out) the Master_Dev.RPD from SCM into the MUD folder.
b.    Administrator will then connect to BI Admin Tool and check-out (BI Admin Tool Check out) projects from the Master_Dev.RPD into the Administrator’s Repository folder. Note: These project RPDs must be named the same as the ones in source control.
c.    Administrator must then check-out the project RPDs from SCM and overwrite the project RPDs checked-out from the Master_Dev.rpd.

 19.Administrator will open one of the project RPDs and performs a compare

20.    After compare then choose Merge Local Changes

21. The lock information screen appears where Administrator can add comments regarding this check in. Once comment in put in the next screen Stats can be reviewed to monitor what was changed and the Merge button needs to be clicked.

22. Once done the next step is to Publish to Network, this step assures that the changes from this project are saved.

23.  Steps from 19-22 will be repeated for checking in ( merging) changes from each of the projects created.

24. Copy the “Master” RPD from the MUD folder to the Development server. Note: the Development Analytics Server will have to be stopped and re-started in this process.

25. Administrator to create a new project in your source control software and copy the “Master” RPD.

26. The above process is repeated for the next development cycle, with the following two important caveats.
  • The Administrator will open the RPD from the repository folder and remove and re-add presentation catalogs to each project again. This step is required to ensure that all new objects created in the previous development cycle are included for the next check-out. Alternatively, Administrator can choose to redefine projects based on new requirements
  • The source control files will need to be re-based.
Key Factors for Success Assurances  

1.    Only one key developer is tasked to execute this entire process as the Administrator to ensure consistency and to avoid miscommunication.

2.    Development cycles (phases) should be clearly defined and strictly adhered to. Initially this can be 2 weeks, but ideally team should strive to keep this at 4 weeks.

3.    Projects need to be defined in stripes covering presentation, BMM and Physical layers. These stripes should be of manageable size to ensure the following

a.    Minimize project contention issues, as only one developer can check-out a project from your source control system at a given time.

b.    Minimize the risk of overstepping each other’s work. The same object could be part of two projects, and so two developers following this process could still overwrite each other’s work. The check-in process is a true merge, which could minimize this risk, but developers will still need to be aware and educated on these consequences

c.    Developers: Ensure all new physical objects created have a corresponding BMM object defined and all new BMM objects have a corresponding Presentation object defined. Since the Administrator includes the Presentation catalog objects to a project, this check will ensure that the other layers of the RPD are automatically part of this project.

d.    d. If a given project needs to be backed out after Administrator checks the project in, Administrator can go back to the previous set of files (project RPDs and Master.RPD) on your source control system and check in all EXCEPT that one project again. This is the fall-back approach to be able to selectively roll-back a specific project’s changes.

e.    In case, a given project is not ready to be checked-in by Freeze time (meaning, developers have made modifications to a project and checked-in their changes into source control system, but the project is not complete for testing purposes). In this case, Administrator can selectively avoid checking that one project in (Oracle BI check in), but continue with the remaining projects. Later, a developer can merge the older version’s RPD into the new RPD that will be checked-out for the same project and merge the changes.

                                          i.    Caveat: Administrator has to ensure that the same project is checked out again in
                    the next cycle to achieve this workaround.

This is not ideal solution, it has many limitations. Let us discuss these limitations in next blog and also touch on what OBIEE 11g offers for source control.