This discussion is archived
4 Replies Latest reply: Nov 14, 2012 1:57 AM by DavidGreenfield RSS

Dimension Attribute Write Back

938723 Newbie
Currently Being Moderated
Is it possible to write-back and update a dimension attribute. If so, could someone please post an example doing this.

Thanks!
  • 1. Re: Dimension Attribute Write Back
    Nasar Journeyer
    Currently Being Moderated
    I assume that your question is about Oracle-OLAP dimension attribute (and not Essbase).

    You will have to come up with a custom front-end (web or windows) where you will enter (or change) the attribute value. Then behind the scenes, you will call DBMS_CUBE.BUILD procedure to update the attribute value for that dimension member.

    Here is an example:
    Dimension = 'ACC', Hierarchy = 'ACCHIER', Dimension Member = 'CM1' and we want to update an attribute value called "LONG_DESCRIPTION" for this member 'CM1'.

    begin
    dbms_cube.build(q'! "ACC" USING (
    SET "ACC"."LONG_DESCRIPTION"["ACC" = 'CM1'] = CAST('Custom Member 1' AS VARCHAR2),
    MERGE INTO HIERARCHIES ("ACC"."ACCHIER")  VALUES ('CM1', 'ALL_ACC', NULL),
    COMPILE)!');
    end;
  • 2. Re: Dimension Attribute Write Back
    DavidGreenfield Expert
    Currently Being Moderated
    The feature Nasar mention is also available as an OLAP DML call. Search for documentation on UPDATE_ATTRIBUTE_VALUE.

    http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_commands_2070.htm#CCHBIGEG
    CALL UPDATE_ATTRIBUTE_VALUE(dim_member_id, attribute_name, attribute_value [, auto_compile ])
    This is all available in version 11.2.0.2 and higher.

    Edited by: David Greenfield on Nov 14, 2012 9:57 AM
    Corrected UPDATE_ATTRIBUTE_MEMBER to UPDATE_ATTRIBUTE_VALUE
  • 3. Re: Dimension Attribute Write Back
    938723 Newbie
    Currently Being Moderated
    The DML call did work. Call is, UPDATE_ATTRIBUTE_VALUE. There is no function UPDATE_ATTRIBUTE_MEMBER.

    Also, I used a call similar to:

    dbms_cube.build(q'!
    "PRODUCT" USING (SET "PRODUCT"."LONG_DESCRIPTION"["PRODUCT" = 'ITEM_OPT MOUSE']
    = CAST('Optical Mouse' AS VARCHAR2))
    !');

    Found here:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/d_cube.htm

    Worked like a charm!

    Edited by: user7853353 on Nov 13, 2012 7:18 PM
  • 4. Re: Dimension Attribute Write Back
    DavidGreenfield Expert
    Currently Being Moderated
    Thanks for catching the UPDATE_ATTRIBUTE_MEMBER error. That will teach me to copy and paste from the documentation! I will correct my previous post for the record.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points