OBIEE 11g Writeback Question — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE 11g Writeback Question

Received Response
91
Views
12
Comments
Md. Altas
Md. Altas Rank 3 - Community Apprentice
edited Aug 13, 2024 6:14PM in Oracle Analytics Cloud and Server

Hi All

A quick question on writeback template.

I have got following template which is not working -

<?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="UpdateSupInfo">

<XML>

<writeBack connectionPool="POD_Conn_Pool">

<insert></insert>

<update>UPDATE POD.PO_DETAILS SET Sup Info='@{c36281e59054c18d5}' WHERE PO Number='@{c9ac14dfb77047c4d}' AND Organization='@{cf79bab7c8634f73c}' AND Item='@{cb58f479f589e1f0}'</update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

I have got -

1: Schema Name and table mentioned here POD.PO_DETAILS

2: The column names like 'Sup Info', 'PO Number','Organization etc are from Presentation layer.

Do I need to give database column names or Presentation Layer Column name?

For example: - PO Number is stored in database column PO_NUM

So here I should give 'PO Number' or PO_NUM?

3: Also please help me validate if I can Update the column based on multiple conditions?

Thanks

«1

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    ad 2.) Database columns since that's what gets executed against the database itself.

    ad 3.) What do you mean?

  • Felipe_Idalgo
    Felipe_Idalgo Rank 5 - Community Champion

    Hi,

    Follow answers

    1: Schema Name and table mentioned here POD.PO_DETAILS

    Ok, that's right

    2: The column names like 'Sup Info', 'PO Number','Organization etc are from Presentation layer.

    Do I need to give database column names or Presentation Layer Column name?

    For example: - PO Number is stored in database column PO_NUM

    So here I should give 'PO Number' or PO_NUM?

    Yes, this instruction will be send directly to database, so you have to use database column and table information

    3: Also please help me validate if I can Update the column based on multiple conditions?

    Yes, use a AND on WHERE clause and be happy

    Felipe Idalgo

  • Md. Altas
    Md. Altas Rank 3 - Community Apprentice

    Great !!!

    Now other columns after WHERE clause also need to be database columns?

    They may be from different tables in database.

    In this case I changed the columns PO Number to PO_NUM, Item to SEGMENT1 and so on.

    But they are stored in different database table.

    <?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="UpdateSupInfo">

    <XML>

    <writeBack connectionPool="POD_Conn_Pool">

    <insert></insert>

    <update>UPDATE POD.PO_DETAILS SET SUP_IFO='@{c36281e59054c18d5}' WHERE PO_NUM='@{c9ac14dfb77047c4d}' AND ORG_CODE='@{cf79bab7c8634f73c}' AND SEGMENT1='@{cb58f479f589e1f0}'</update>

    </writeBack>

    </XML>

    </WebMessage>

    </WebMessageTable>

    </WebMessageTables>

  • Md. Altas
    Md. Altas Rank 3 - Community Apprentice

    Hi All,

    Now I get following message from front screen -

    The system is unable to read the Write Back Template 'UpdateSupInfo'. Please contact your system administrator.

    Anything to be done about the permissions or writebacktemplate?

    Thanks

  • Felipe_Idalgo
    Felipe_Idalgo Rank 5 - Community Champion

    Hi,

    Where did you store your XML template?

    Verify if your user has acess to writeback function. On browser go to administration tab and click in manage privilegies (you need an admin user). At the end of the page you'll see writeback privilegies

    After perform that, restart OPMN and clear your browser (cache)

    Felipe Idalgo

  • Md. Altas
    Md. Altas Rank 3 - Community Apprentice

    Thanks for the input.

    This was completed. I am still getting the same error.

    Is it something to do with permission to the file or folder?

  • Felipe_Idalgo
    Felipe_Idalgo Rank 5 - Community Champion

    Hi,

    Where did you store your XML template?

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    For your question

    1.@Felipe_Idalgo its right, you have to have your XML in a particular path in your server, you said that you are in OBIEE 11g, look for this path: ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obipsn/analyticsRes/customMessages and check if you have your XML in this path

    2. Second you also have to know if you have the permissions, to write, if you have the administrator user there is no problem , however, if you want to do a write back functionability for a particular user, you should to set up the permission, for do this, go to Manage Identity -> Permissions -> in the connection pool set up the read/write permissions.

    Kind Regards,

  • Md. Altas
    Md. Altas Rank 3 - Community Apprentice

    Thanks you for the suggestion about connection pool.

    The access error is resolved.

    Now I get following error -

    An error occurred while writing to the server. Please check to make sure you have entered appropriate values. If the problem persists, contact your system administrator.

    Below is the example I have. Please confirm whether this will work with writeback or not -

    Tablename - PO_DETAILS

    Columns - PO_LINE_LOCATION_ID

    ORGANIZATION_ID

    SUPINFO

    Tablename - ORGANIZATIONS

    Columns - ORGANIZATION_ID

    ORGANIZATION

    Tablename - PO_DETAILS

    PO_LINE_LOCATION_ID

    PO_NUM

    Now following columns are presented in Presentation Layer -

    SUPINFO as 'SupInfo'

    ORGANIZATION as 'Organization'

    PO_NUM as 'PO Number'

    Now my report is combination of 3 different underlying database tables matched by their IDs.

    Now when I do writeback, will OBIEE fetch match IDs and then prepare proper writeback SQL or is this designed for failure?

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    You should to know some consideraton:

    1. Setting the LightWriteback Element

    For users to write back values, you must manually add the LightWriteback element in the instanceconfig.xml file. Before you begin this procedure, ensure that you are familiar with the information in Section 3.4, "Using a Text Editor to Update Configuration Settings."

    <WebConfig>

      <ServerInstance>

      <LightWriteback>true</LightWriteback>

      </ServerInstance>

    <WebConfig>

    2.

    You only can UPDATE or INSERT a 1 Table By <WebMessage Name=""> Tag

    <WebMessage name="SetQuotaUseID">

      <XML>

      <writeBack connectionPool="Supplier">

      <insert>INSERT INTO regiontypequota VALUES(@{c0},@{c1},'@{c2}','@{c3}',@{c4})</insert>

      <update>UPDATE regiontypequota SET Dollars=@{c4} WHERE YR=@{c0} AND Quarter=@{c1} AND Region='@{c2}' AND ItemType='@{c3}'</update>

      </writeBack>

      </XML>

      </WebMessage>

    So if you want to make a trhee Update in a only one template, it doesnt work.

    3.

    When you use a Filter in the XML

    '@{c2}' the quotes ('') its for varchar2 datatype

    @{c1} without quotes ('') its for NUMBER datatype

    Check

    https://docs.oracle.com/cd/E23943_01/bi.1111/e10541/answersconfigset.htm#BIESG1354

    Kind Regards,